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 mergedATTEMPT 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)

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