Map the high-level data model shown below to a set of relational tables. Identify primary, alternate, and foreign keys.

Question

What will be an ideal response?

Answer 1


Employee (SSN, fName, MI, lName, address, birthDate, sex, salary, number)
Primary Key SSN
Foreign Key number references Department (number)

Department (number, name, mgrSSN, startDate)
Primary Key number
Alternate Key name
Foreign Key mgrSSN references Employee (SSN)

DepartmentLocations (number, location)
Primary Key number, location
Foreign Key number references Department (number)

WorksOn (SSN, number, hours)
Primary Key SSN, number
Foreign Key SSN references Employee (SSN)
Foreign Key number references Project (number)

Project (number, name, location, dNumber)
Primary Key number
Foreign Key dNumber references Department (number)

SupervisoryTeam (supSSN, SSN)
Primary Key SSN
Foreign Key supSSN references Employee (SSN)
Foreign Key SSN references Employee (SSN)

(Or do not create SupervisoryTeam relation and simply add supSSN to Employee relation)

Dependent (SSN, name, sex, birthDate relationship)
Primary Key name, SSN
Foreign Key SSN references Employee (SSN)