Entity Relationship Diagram for Exams Database

Question 1 (Concepts)

1. Explain the entity integrity ruleand the referential integrity rule within the relational model. Provide and explain brief example(s) of your choice that show a violation of both rules.
2. Briefly explain what redundant datameans in the context of databases. Explain one problem redundant data causes (please provide an example). Discuss what we can do erase redundancy and solve this problem?

Question 2 (ER Model)

Consider the following simple Exams database:

An exam is identified by an exam ID and has a date and a unit. An exam uses at least 1 or more exercises. An exercise is identified by an exercise ID and has a description and a solution. Each exercise must be used in exactly 1 exam.  Lecturers are identified by a lecturer ID and have a name. They devise at least one or more exams. An exam in turn is devised by exactly 1 lecturer. A lecturer marks at least one or more exams. An exam is marked by exactly 1 lecturer. Students are identified by a student ID and have a name. They take at least 1 or more exams. An exam is taken by at least 1 or more students.

Draw an Entity Relationship Diagram that precisely reflects above scenario. Make sure you cover all attributes you can identify in the scenario and also specify the cardinalities.

The following ER Diagram is an example solution. for entities (2 each), for relationships (Deduction if multiplicity is not correct), for attributes (up to deduction if primary key not specified).

Question 3 (UML question)

The following description of the functionality of an online book store is given:

Customers should be able to search a book. Furthermore they should be able to order a book, which includes inserting credit card details. Customers should also have the ability to modify an order. Ordering a book and modifying an order require to log in to the system.

1. Draw a complete Use Case Diagram reflecting above book store scenario
2. Explain the term “inheritance” in the context of UML Class Diagrams!
3. What does “inheritance” mean in the context ofUML Use Case Diagrams?

Question 4 (Normalisation)

An engineering consultancy firm supplies temporary specialized staff to bigger companies in the country to work on their project for certain amount of time. The table below lists the time spent by each of the company’s employees at other companies to carry out projects. The National Insurance Number (NIN) is unique for every member of staff.

1. Explain in which normal form this table is
2. Find the Primary Key for this relation and explain your choice.
3. Find the Fully Functional Dependencies on the PK and the Partial Dependencies on the PK.
4. Normalise the table to 2NF