Database development problem

[jaymcfly]

The attached file shows the design of a database ER model, I am working with a group to decide on the relational schema of the table but we are now at a crossroads as there are two differing opinions and neither party is will to budge.

I would be very grateful if someone would have a look at the attached document and tell me which of the two answers is closest to the correct one.

I will post the two different answers in second, Thank you for your help it is appreciated.

[jaymcfly]

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

[jaymcfly]

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

[jaymcfly]

can someone with any database design experience please have a look at the attached document in post 1 and see which of the two answers given would be closer to the correct answer.

[leinad31]

Pearl of wisdom... look at the data being captured and the transaction where they belong. If there is an existing system (not a school lab work) then look at fields in relevant documents. Be realistic and observe what happens in the real world rather than exerting effort to implement a design that looks good only on paper.

All high level requirements aside, for practical purposes the SELECT statement actually expresses the relationship between tables, but of course we are required to implement stricter rules. Constraints ensure data relationships are correct and there are no orphaned records but if misused can introduce business requirements/limitations that were not asked for.

Take the case of module FOREIGN KEY tchStaffNo REFERENCES Staff(staffNo)... what if that entity isn't always required?

Take the case of module FOREIGN KEY crdStaffNo REFERENCES Staff(staffNo)... what if department head needs to enter module into system even if staff has yet to be assigned (hiring yet to be finalized).

The transactions matter more as the order of steps in transaction determine order in which you insert to tables. With too many FK constraints you might end up in scenario where table A needs record in table B, but table B needs record in table C, but table C needs parent in table A. For practical purposes how are you supposed to do an insert with that design?

Take the case of student FOREIGN KEY mCode REFERENCES Module(mCode)... what if you have new student (first step of transaction from POV of student/registrar)? How are you supposed to implement constraint that he already knows his mCode when that comes several months later during enrollment?

[leinad31]

Follow-up to previous post. Related to identifying transactions is identifying encoders and what info they provide. Take the case of module; it assumes that right then and there encoder (there is only one) knows all info that has to be provided especially constraints to staff.

What if in reality there are several encoders; one who encodes module basic info (mCode, title, startDate, endDate, staff FKs not known), and someone else assigns the staff later (staffing info better implemented as separate table), and the said staff assigns the coursework, exams, and other info.

In short you are deliberating too much over FK constraints when you should be reviewing table structure in light of transactions and encoders (process owners).