Question: Use the technique of normalization to validate the structure of your relational schema.

Question:

1. Use the technique of normalization to validate the structure of your relational schema. Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional dependencies between attributes in each relation. Note, if any of your relations are not in 3NF, this may indicate that your ER model is structurally incorrect or that you have introduced errors in the process of deriving relations from your model.

The Relational Schema is as follows:

Department (deptName, phone, faxNo, location, mgrStaffNo, mgrStartDate)

Primary Key: deptName

Foreign Key: mgrStaffNo references Staff(staffNo)

Course (cCode, title, duration, leaderStaffNo, deptName)

Primary Key: cCode

Alternate Key: course

Foreign Key: leaderStaffNo references Staff(staffNo)

Foreign Key: deptName references Department(deptName)

Module (mCode, title, startDate, endDate, coursework, exam, courseCode, drctStaffNo)

Primary Key: mCode

Foreign Key: courseCode references Course(cCode)

Foreign Key: drctStaffNo references Staff(staffNo)

Staff (staffNo, fName, lName, address, phone, officeNo, sex, salary, post, computerID,

deptName)

Primary Key: staffNo

Alternate Key: phone

Alternate Key: computerID

Foreign Key: deptName references Department(deptName)

Student (matricNo, fName, lName, town, street, postCode, dob, sex, loan, courseCode)

Primary Key: matricNo

Alternate Key: computerID

Foreign Key: courseCode references Course(cCode)

Next-Of-Kin (matricNo, name, phone, relationship)

Primary Key: matricNo, name

Foreign Key: matricNo references Student(matricNo)

Undertakes (stdMatricNo, moduleCode, performance)

Primary Key: stdMatricNo, moduleCode

Foreign Key: stdMatricNo references Student(matricNo)

Foreign Key: moduleCode references Module(mCode)

Teaches (teachStaffNo, moduleCode, hours)

Primary Key: teachStaffNo, moduleCode

Foreign Key: teachStaffNo references Staff(staffNo)

Foreign Key: moduleCode references Module(mCode)

Texts (moduleCode, text)

Primary Key: moduleCode, text

Foreign Key: moduleCode references Module(mCode)

Qualifications (qualStaffNo, qualification)

Primary Key: qualStaffNo, qualification

Foreign Key: qualStaffNo references Staff(staffNo)