Create and validate a logical data model from the local conceptual data model for the University Accommodation Office case study created

Question

What will be an ideal response?

Answer 1

Student (matricNo, fName, lName, street, city, postcode, DOB, sex, category,
nationality, smoker, special needs, comments, status, courseNo)
Primary Key matricNo
Foreign Key courseNo references Course(courseNo)
NOK (matricNo, name, street, city, postcode, contactTelNo)
Primary Key matricNo
Foreign Key matricNo references Student(matricNo)
Course (courseNo, courseTitle, courseLeader, courseLeaderTelNo,
courseLeaderRoomNo, deptName)
Primary Key courseNo
Alternate Key courseTitle
Hall (halINo, hName, hAddress, hTeINo, mgrStaffNo)
Primary Key nalINo
Alternate Key hName
Alternate Key hTelNo
Foreign Key mgrStaffNo references Staff(staffNo)
Flat (flatNo, fAddress, noOfRooms)
Primary Key flatNo
Room (placeNo, roomNo, monthlyRent, flatNo, hallNo)
Primary Key placeNo
Foreign Key halINo references Hall(hallNo)
Foreign Key flatNo references Flat(flatNo)
Flatlnspection (flatNo, iDate, condition, comments, inspStaffNo)
Primary Key flatNo, iDate
Foreign Key flatNo references Flat(flatNo)
Foreign Key inspStaffNo references Staff(staffNo)
Lease (leaseNo, duration, matricNo, placeNo, dateEnter, dateLeave)
Primary Key leaseNo
Alternate Key placeNo, dateEnter
Alternate Key matricNo, dateEnter
Foreign Key matricNo references Student(matricNo)
Foreign Key placeNo references Room(placeNo)
AccommStaff (staffNo, fName, lName, street, city, postcode, DOB, sex, position,
location)
Primary Key staffNo
Foreign Key location references Hall(halINo)
Invoice (invoiceNo, semester, paymentDue, datePaid, paymentMethod,
dateReminder1, dateReminder2, leaseNo)
Primary Key invoiceNo
Foreign Key leaseNo references Lease(leaseNo)