University of Toronto CSC343, Winter 2021
From ER Diagram to Database Schema: Solutions
1. Consider the following ER diagram:
(a) Department (b) Name and City
(c) No
(d) At most one
(e) Management, Membership, Participation
2. Translate the ER diagram into a relational schema without making any simplifications. That is, create one relation for every entity set and one relation for every relationship set. Underline the key of each relation, and draw arrows to indicate foreign keys.
Here, we assume the cardinality of all attributes is (1,1) unless otherwise specified.
• Employee(Code, Surname, Salary, Age)
All attributes of Employee are not null.
• Project(Name, Budget, ReleaseDate)
Name and Budget are not null. ReleaseDate, however, could be null.
• Participation(Emp, Proj, StartDate)
Emp foreign key of Employee, Proj foreign key of Project. All attributes of Participation are not null.
• Branch(City, Number, Street, PostCode)
All attributes of Branch are not null.
• Department(Name, City)
City is foreign key of Branch (note that this foreign key represents the relationship Composition). All attributes of Department are not null.
• DeptPhone(Name, City, Phone)
(Name, City) foreign key of Department. All attributes of DeptPhone are not null.
• Management(Emp, Dept, City )
Emp foreign key of Employee. (Dept, City) is unique and a foreign key of Department. All attributes of Management are not null.
• Membership(Emp, Dept, City, StartDate)
Emp is a foreign key of Employee. (Dept, City) is unique and a foreign key of Department. All attributes of Membership are not null.
• There are several relationships with a “minimum 1” constraint. These can be expressed in the relational model as follows:
– Project[Name] ⊆ Participation[Proj]
(This enforces the minimum 1 constraint on Project’s involvement in the Participation relationship.)
– Branch[City] ⊆ Department[City]
(This enforces the minimum 1 constraint on Branch’s involvement in the Composition relationship.)
– Department[Name, City] ⊆ Membership[Dept, City]
(This enforces the minimum 1 constraint on Department’s involvement in the Membership rela- tionship.)
– Department[Name, City] ⊆ Management[Dept, City]
(This enforces the minimum 1 constraint on Department’s involvement in the Management rela- tionship.)
None of these can be expressed as FOREIGN KEY constraints in SQL, except for the final one, since they don’t refer to attributes that are either PRIMARY KEY or UNIQUE in their home table.
• We have not enforced the “minimum 1” constraint on the phone attribute of Department.
3. Which relationship sets represent information that can be collapsed onto an entity set? Revise the schema
accordingly.
• Management and Composition (Composition is already collapsed above)
• Management can be collapsed into Department. In that case, the minimum 1 constraint cannot be expressed as a constraint between Deparment and Management as shown above. However, we can still enforce it using a not-null constraint.