Description
In this assignment, you will have 3 problems related to ER-Model. You will have 2 weeks to finish this
assignment. Please submit your answer to blackboard before 11 October 2020, 23:59.
1 Problem 1 (10 marks)
Construct appropriate relational schemes for each of the E-R diagrams below. (5 marks each)
Figure 1: Paper citing
Figure 2: Car-insurance company
2 Problem 2 (10 marks)
Draw an ER-diagram for the following relational schemes. (Hint: bed and wardrobe are both furniture.)
1
Customer (customer id, name)
buy (customer id, product id)
Furniture (product id, stuff type, price)
Bed (product id, size)
Wardrobe (product id, height)
3 Problem 3 (25 marks)
Consider the following ER-diagram and assumptions for a university.
1. Every department has a unique dname.
2. Every department can be uniquely identified by its dhead and location.
3. Every advisor has a unique aid.
4. Every student has a unique sid.
5. Every project has a unique pid.
6. Every project can be uniquely identified by its pname and funding.
7. A advisor works in exactly one department.
8. A department has at least one advisor working in it.
9. A student can be assigned any number of projects.
Figure 3: Problem3
a) (5 marks) List all the superkey(s) of “Project”.
b) (4 marks) List all the candidate key(s) of “Department”
2
c) (6 marks) Assume that pid is a primary key, complete the ER-diagram by adding all missing
constraints (weak entity, key constraints, participation constraints and relationship constraints).
d) (10 marks) This ER model does not allow a advisor to assign one project to the same student twice.
Please briefly state why this is happening and try to modify the diagram to make it work.
3
