Map your high-level data model to a set of relational tables that represent the entity and relationship types. Identify primary, alternate, and foreign keys.

Question

What will be an ideal response?

Answer 1

Outlet (outletNo, address, telNo, faxNo, mgrStaffNo)
Primary Key outletNo
Foreign Key mgrStaffNo references Staff(staffNo)

Staff (staffNo, fName, lName, address, telNo, DOB, sex, NIN, dateJoined, jobTitle,
salary, outletNo)
Primary Key staffNo
Alternate Key NIN
Foreign Key outletNo references Outlet(outletNo)

Vehicle (regNo, model, make, engineSize, capacity currentMileage, motDate, dRate,
outletNo)
Primary Key regNo
Foreign Key outletNo references Outlet(outletNo)

FaultReport (staffNo, regNo, dateChecked, faults)
Primary Key regNo, dateChecked
Foreign Key staffNo references Staff(staffNo)
Foreign Key regNo references Vehicle(regNo)

Note: Assumption is that a vehicle is checked for faults, only once on a given date.

PersonalClient (pclientNo, fName, lName, address, telNo, DOB, licenceNo)
Primary Key pclientNo
Alternate Key licenceNo

BusinessClient (bclientNo, bName, bType, address, telNo, faxNo)
Primary Key bclientNo

HireAgreement (hireNo, clientNo, regNo, dateStart, dateFinish, mileageBefore,
mileageAfter)
Primary Key hireNo
Foreign Key clientNo references PersonalClient(pclientNo) and
BusinessClient(bclientNo)
Foreign Key regNo references Vehicle(regNo)