Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    14

    Unanswered: Team split over which answer is right for relational schema

    Hey there guys,

    I am working with two others in a group for the design of a database to deal with a universities database and we are completely split as to which way we should go as we now have two very different answes and I would appreciate it if someone would simply tell us which one is closer to the correct answer as both parties are adamant that they have the correct answer. The question is contained within the attachment showing the logical design and the attributes. I will post the two answers next.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2006
    Posts
    14
    ATTEMPT B


    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)

  3. #3
    Join Date
    Mar 2006
    Posts
    14
    <B><U>ATTEMPT A</B></U>


    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, eCode)
    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)

  4. #4
    Join Date
    Mar 2006
    Posts
    14
    I am not looking for anyone to point out an errors in the design just which design direction is correct as they are completely different from what I can see.

    Your help in this matter would be greatly appreciated.
    Cheers

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It would be much easier (for me) to see this visually (ie. a relationship diagram snapshot with MSAccess tables).

    Without knowing more details on program specifics, it's a bit difficult to say exactly which setup is correctly. These designs are so different that more details on how the program operates are needed to say which one is correct/not correct. I usually determine what works best for deleting/updating records in the relational tables along with other data returns to the front-end when deciding upon the correct structure. I know that's not much help but it seems as though you're deciding on whether to utilize the staff/student_modules tables.

    I would just make sure every data table has an autonumber field (I assume this might be the primary key field indicated in the tables.) An autonumber 'can' be the primary key (and typically is) but I usually identify these separately in the schematics.
    Last edited by pkstormy; 11-15-09 at 17:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Mar 2006
    Posts
    14
    the diagram is in the attached document of the first post

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I saw that. As mentioned, it's not an easy relational diagram to look at. The relational Diagrams produced in MSAccess are much nicer. Can you explain a little more in detail on how the process is supposed to work? Otherwise it's difficult to answer your question.
    Last edited by pkstormy; 11-16-09 at 00:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •