8.2: Examples

Example 1

Consider the ERD:

The mapping rules lead to the relations:

Notes:

• The Member relation does not have a composite attribute name.
• Since Borrows is a many-to-many relationship the Borrow relation is defined with a composite primary key {memberId, bookId, dateTimeBorrowed}.
• memberId in the Borrow relation is a foreign key referencing Member.
• bookId in the Borrow relation is a foreign key referencing Book.

Example 2

Consider the ERD:

The mapping rules lead to the relation:

Notes:

• The attribute supervisor is a foreign key referencing Employee.
• A foreign key is placed on the ‘many’ side of a relationship and so in this case the foreign key references the employee who is the supervisor (the role name on the ‘one’ side); hence the name supervisor was chosen as the attribute name.

Example 3

Consider the ERD:

The mapping rules lead to the relations.

Notes:

• • • deptCode was chosen as the primary key of Department.
    • deptName is a key and so a unique index can be defined to ensure uniqueness.
    • Since Course is a weak entity type and is involved in an identifying relationship, the primary key of Course is composite comprising {deptCode, courseNo}.
    • deptCode in Course is a foreign key referencing Department.