Question

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)

Answer 1

Department (deptName, phone, faxNo, location, mgrStaffNo, mgrStartDate) Functional Dependencies phone, faxNo, location → deptName mgrStartDate -mgrStaffNo Course (cCode, title, duration, leaderStaffNo, deptName) Functional Dependencies title, duration, leaderStaffNocCode Staff(staffNo, fName, IName, address, phone, officeNo, sex, salary, post, computerlD deptName) Functional Dependencies , fName, IName, address, phone, officeNo, sex, salary, poststaffNo Student (matricNo, fName, IName, town, street, postCode, dob, sex, loan, courseCode) Functional Dependencies fName, IName, town, street, postCode, dob, sex, loan matricNo

Next-Of-Kin (matricNo, name, phone, relationship) Functional Dependencies phone, relationship-matricNo, name Undertakes (stdMatricNo, moduleCode, performance) Functional Dependencies PerformancestdMatricNo, moduleCode Teaches (teachStaffNo, moduleCode, hours) Functional Dependencies hours → teachStaffNo, modulecode Texts (moduleCode,text) Qualifications (qualStaffNo, qualification)

Rule 1-Be in INF (Each table cell should contain a single value.) Rule 2- Be in 2NF(Rule 2- Single Column Primary Key) Rule 3-Has no transitive functional dependencies In our Example we can see that some table like Uindertakes, Teaches, Texts, Qualifications are table which have Candidate Key with two column which is not follow rule No 2 so it is not 3NF

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........''