Part A: Relational Database Design

Question 1: Payroll Relation

: Functional Dependencies and Candidate Keys

Functional dependencies are based on business rules. The dependencies for the Payroll relation are:

StaffID → StaffMobileNumber, StaffName (a staff member has one mobile number and name)

StoreID → StoreAddress (a store has one address)

SalaryGroup → BaseRate (each salary group has a unique base rate)

StaffID, DateOfShift → HoursWorked, Amount (the amount and hours worked depend on the staff and shift date)

Candidate key: Since StaffID uniquely identifies each staff member, and DateOfShift determines hours worked and amount, a candidate key could be StaffID, DateOfShift.

: Highest Normal Form

To identify the highest normal form:

1NF: The relation is in 1NF if it has no repeating groups, and each attribute contains atomic values. The Payroll relation is already in 1NF.

2NF: It’s in 2NF if it’s in 1NF and there are no partial dependencies (non-prime attributes depending on a part of the candidate key). Since we have dependencies like StaffID → StaffName, StaffMobileNumber, we have partial dependencies, so it’s not in 2NF.

3NF: A relation is in 3NF if it’s in 2NF and has no transitive dependencies. Since we have SalaryGroup → BaseRate, this is a transitive dependency, so it’s not in 3NF yet.

: Decomposing into 3NF

To achieve 3NF, we need to decompose the table:

Remove partial dependencies by creating separate tables:

Staff(StaffID, StaffName, StaffMobileNumber)

Store(StoreID, StoreAddress)

Remove transitive dependencies:

SalaryGroup(SalaryGroup, BaseRate)

Final schema in 3NF:

Staff(StaffID, StaffName, StaffMobileNumber)

Store(StoreID, StoreAddress)

Payroll(StaffID*, DateOfShift, HoursWorked, Amount, StoreID*, SalaryGroup*)

SalaryGroup(SalaryGroup, BaseRate)

Question 2: Car Rental Relation

: Candidate Keys

Functional dependencies for CarRental relation:

CustID → FirstName, LastName, Address, PostCode

RentID → CustID, RegNo, RentDate, ReturnDate

RegNo → ModelID, Rate

ModelID → CarMark, CarModel, CarYear

Candidate key: RentID since it uniquely identifies the rental transactions.

: Highest Normal Form

The relation is in 1NF (no repeating groups).

It’s not in 2NF due to partial dependencies like CustID → FirstName, LastName, Address, PostCode.

It’s not in 3NF because of transitive dependencies like ModelID → CarMark, CarModel, CarYear.

: Decomposing into 3NF

To achieve 3NF, we decompose the table as follows:

Create separate tables for partial and transitive dependencies:

Customer(CustID, FirstName, LastName, Address, PostCode)

Car(RegNo, ModelID, Rate)

Model(ModelID, CarMark, CarModel, CarYear)

Final schema in 3NF:

Customer(CustID, FirstName, LastName, Address, PostCode)

CarRental(RentID, CustID*, RegNo*, RentDate, ReturnDate)

Car(RegNo, ModelID*, Rate)

Model(ModelID, CarMark, CarModel, CarYear)