Tutorial 3

Solution Notes

ER to Relational Conversion

Apply the COMP2400/6240 Algorithm for Converting an ER Diagram into a Relational Schema to each of the following ER diagrams. Show all the steps in the algorithm.

First ER Diagram

STEP 1

TASK (Proj-id, Task-no, [pk] Supervisor, Phone)

MACHINE (Mach-no [pk] Type, Location)


STEP 2

USES (Proj-id [fk1], Task-no [fk1], Mach-no [fk2], [pk] Hours)


STEP 3

Not Applicable


STEP 4

Change USES to TASK-MACHINE
TASK-MACHINE (Proj-id [fk1], Task-no [fk1], Mach-no [fk2], [pk] Hours)

Change Mach-no to Machine-no
Change Type to Machine-type
MACHINE (Machine-no [pk] Machine-Type, Location)
TASK-MACHINE (Proj-id [fk1], Task-no [fk1], Machine-no [fk2], [pk] Hours)

STEP 5

Final Relational Schema

TASK (Proj-id, Task-no, [pk] Supervisor, Phone)

TASK-MACHINE (Proj-id [fk1], Task-no [fk1], Machine-no [fk2], [pk] Hours)

MACHINE (Machine-no [pk] Machine-Type, Location)

A Common Problem

Running STEPs 4 and 5 together:

STEP 1

TASK (Proj-id, Task-no, [pk] Supervisor, Phone)

MACHINE (Mach-no [pk] Type, Location)


STEP 2

USES (Proj-id [fk1], Task-no [fk1], Mach-no [fk2], [pk] Hours)


STEP 3

Not Applicable


Final Relational Schema

TASK (Proj-id, Task-no, [pk] Supervisor, Phone)

TASK-MACHINE (Proj-id [fk1], Task-no [fk1], Mach-no [fk2], [pk] Hours)

MACHINE (Machine-no [pk] Machine-Type, Location)

Second ER Diagram

STEP 1

STEP 2

Not Applicable

STEP 3

We do not need to add Payroll-no to QUALIFICATION, but we do need to designate Payroll-no as a foreign key.

QUALIFICATION

(Payroll-no [fk], Qual-no, [pk] Date-acquired, Awarding-body)

STEP 4

Change Name in EMPLOYEE to Employee-name.
Change Track-record in EMPLOYEE to Performance-rating

STEP 5

Final Relational Schema

Third ER Diagram

STEP 1

STEP 2

STEP 3

STEP 4 (Some Possibilities)

Rename ASSIGNED-TO to PROJECT-EMPLOYEE.

Rename WORKED-ON to TIMESHEET.

Rename Budget to Project-budget.

Rename Date-assigned-to to Date-assigned-to-project.

Rename Expected-duration to Expected-duration-of-activity.

Assuming that Managers and Supervisors are Employees:

Rename Manager to Manager-payroll-no and show it as a foreign key.

Rename Supervisor to Supervisor-payroll-no and show it as a foreign key.

Assuming that Managers and Supervisors are not Employees:

Assume that Manager exists as a primary key elsewhere in the database and show it as a foreign key (perhaps as Manager-id).

Assume that Supervisor exists as a primary key elsewhere in the database and show it as a foreign key (perhaps as Supervisor-id).

STEP 5

Final Relational Schema (One Possibility)

Common Problems

Doing STEP 3 in STEP 1

Designating foreign keys incorrectly in STEP 2

Adding Project-id in STEP 3

Fourth ER Diagram

STEP 1

Map each entity to a relation:

STEP 2

Map each many-to-many relationship to a relation:

STEP 3

Incorporate each one-to-many (many-to-one) relationship into the schema:

STEP 4

Review the schema:

Rename the relations mapped from the many-to-many relationships.

CONNECTS-WITH given a more meaningful name:

SHOP-CONNECTIONS

(Shop-name-#1 [fk1], Shop-name-#2 [fk2], [pk])

REQUIRES and HAS renamed used the convention of concatenating the names of the entities at either end of the relationship:

MACHINE-SKILL

(Serial-no [fk1], Skill-id [fk2], [pk])

OPERATOR-SKILL

(Operator-id [fk1], Skill-id [fk2], [pk] Date-acquired)

Attributes given more explicit names

Condition changed to Machine-condition and Serial-no changed to Machine-serial-no:

MACHINE

(Machine-serial-no, [pk] Machine-condition, Date-purchased, Shop-name [fk])

Bonus changed to Skill-bonus:

SKILL

(Skill-id, [pk] Skill-bonus)

Name changed to Operator-name:

OPERATOR

(Operator-id, [pk] Operator-name, Base-wage)

STEP 5

Final Relational Schema

[Top]