Lab 3. Design and implement a University Accommodation Database
The Director of the University Accommodation Office requires you to design a database to assist with the administration of the office. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the University Accommodation Office database followed by examples of query transactions that should be supported by the database.
Data Requirements
Students
The data stored on each full-time student includes: the student number, name (first and last name), date of birth, sex, nationality, special needs, current status (placed/waiting), and what course the student is studying on.
The student information stored relates to those currently renting a room and those on the waiting list. Students may rent a room in a hall of residence or student flat.
Halls of residence
Each hall of residence has a name, address, telephone number, and a hall manager who supervises the operation of the hall. The halls provide only single rooms, which have a room number, place number, and monthly rent rate.
The place number uniquely identifies each room in all halls controlled by the Accommodation Office and is used when renting a room to a student.
Student flats
The Accommodation Office also offers student flats. These flats have three, four or five single bedrooms and can provide accommodation for groups of three, four, or five students. The information held on student flats includes a flat number, address, and the number of single bedrooms available in each flat. The flat number uniquely identifies each flat.
Each bedroom in a flat has a monthly rent rate, room number, and a place number. The place number uniquely identifies each room available in all student flats and is used when renting a room to a student.
Contracts
A student may rent a room in a hall or student flat for various periods of time. New contracts are negotiated at the start of each academic year with a minimum rental period of one semester and a maximum rental period of one year, which includes Semesters 1, 2, and the Summer Semester. Each individual contract between a student and the Accommodation Office is uniquely identified using a contract number.
The data stored on each contract includes the contract number, duration of the contract (given as semesters), name and student number of the student, place number, which hall or student flat, room number, and the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).
Invoices
At the start of each semester each student is sent an invoice for the following rental period(the following semester). Each invoice has a unique invoice number.
The data stored on each invoice includes the invoice number, contract number, semester, payment due, student¡¯s full name and student number, place number. Additional data is also held on the payment of the invoice and includes the date the invoice was paid, the method of payment (cheque, cash, Visa, etc.), the date the first and second reminder is sent (if needed).
Query Transactions (Sample)
Listed below are some examples of query transactions that should be supported by the University Accommodation Office database system.
(a) Present a report listing the Manager¡¯s name and telephone number for each hall of residence.
(b) Present a report listing the names and student numbers of students with the details of their contracts.
(c) Display the details of contracts that include the Summer Semester.
(d) Display the details of the total rent paid by a given student.
(e) Present a report on students that have not paid their invoices by a given date.
Task
Based on the above data requirements and sample queries, create an EER diagram using MySQL Workbench and create a database. Populate the database using the given Lab 3 sample data (you can add more data if you like).