Relational Schema Problem

[jaymcfly]

I am working on the relational schema of a table from a design that we have been given along with two other people and we have come to a stalemate sort of scenario as both are arguing that we have designed the tables correctly despite two completely different designs.

 

I would be very grateful if someone here could look at the attached document which shows the question and then have a look at the two answers that I have posted below and tell me which one is on the right tracks.

 

I would greatly appreciate your help in this matter, I am not looking for anyone to do any work or point out any errors just which of the two answers is closest to the correct answer.

 

Thank you

QUESTION.doc

[jaymcfly]

ATTEMPT 1

-----------

 

Module(mCode, title, startDate, endDate, coursework, exam, staffNo, matricNo)

PRIMARY KEY mCode

ALTERNATE KEY title

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

FOREIGN KEY matricNo REFERENCES Student(matricNo)

Books(mCode, texts)

PRIMARY KEY mCode, texts

FOREIGN KEY mCode REFERENCES Module(mCode)

 

Course(cCode, title, duration, deptName)

PRIMARY KEY cCode

ALTERNATE KEY title

FOREIGN KEY deptName REFERENCES Department(deptName)

 

Student(matricNo, fName, town, street, postcode, dob, sex, loan, computerId, cCode, mCode NokName, NokAddress, NokPhone, NokRelationship)

PRIMARY KEY matricNo

ALTERNATE KEY computerId

FOREIGN KEY mCode REFERENCES Module(mCode)

FOREIGN KEY cCode REFERENCES Course(cCode)

 

Student_Module(matricNo, mCode , performance)

PRIMARY KEY matricNo, mCode

FOREIGN KEY matricNo REFERENCES Student(matricNo)

FOREIGN KEY mCode REFERENCES Module(mCode)

 

Department(deptName, phone, faxNo, Location, startDate, staffNo)

PRIMARY KEY deptName

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

 

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

computerId, qualifications, mCode)

PRIMARY KEY staffNo

FOREIGN KEY mCode REFERENCES Module(mCode)

 

Staff_Modules(staffNo, mCode, hours)

PRIMARY KEY staffNo, mCode

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

FOREIGN KEY mCode REFERENCES Module(mCode)

Qualifications(staffNo, qualifications)

PRIMARY KEY staffNo, qualifications

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

---

Merged post follows:

Consecutive posts merged

ATTEMPT 2

-----------

 

Module (mCode, title, startDate, endDate, coursework, exam, crdStaffNo, tchStaffNo, matricNo, cCode)

PRIMARY KEY mCode

ALTERNATE KEY title

FOREIGN KEY matricNo REFERENCES Student(matricNo)

FOREIGN KEY crdStaffNo REFERENCES Staff(staffNo)

FOREIGN KEY tchStaffNo REFERENCES Staff(staffNo)

FOREIGN KEY eCode REFERENCES Course(cCode)

 

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

PRIMARY KEY cCode

ALTERNATE KEY title

FOREIGN KEY deptName REFERENCES Department(deptName)

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

 

Student (matricNo, fName, lName, town, street, postcode, dob, sex, loan, computerID, cCode)

PRIMARY KEY matricNo

ALTERNATE KEY computerID

FOREIGN KEY eCode REFERENCES Course(eCode)

 

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

FOREIGN KEY matricNo REFERENCES Student(matricNo)

 

Department (deptName, phone, faxNo, location, staffNo)

PRIMARY KEY deptName

ALTERNATE KEY phone

ALTERNATE KEY faxNo

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

 

 

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

PRIMARY KEY staffNo

ALTERNATE KEY computerID

FOREIGN KEY deptName REFERENCES Department(deptName)

 

Teaches (staffNo, mCode, hours)

PRIMARY KEY staffNo, mCode

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

FOREIGN KEY mCode REFERENCES Module(mCode)

 

Manages (staffNo, deptName, startDate)

PRIMARY KEY staffNo, deptName

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

FOREIGN KEY deptName REFERENCES Department(deptName)

 

Undertake (matricNo, mCode, performance)

PRIMARY KEY matricNo, mCode

FOREIGN KEY matricNo REFERENCES Student(matricNo)

FOREIGN KEY mCode REFERENCES Module(mCode)

 

Qualifications (staffNo, qualification)

FOREIGN KEY staffNo REFERENCES Staff(staffNo)

 

Texts (staffNo, text)

FOREIGN KEY staffNo REFERENCES Staff(staffNo)