Lab 4. Design and implement a University Accommodation Database (Part 2)
Task 1
For the Relational Database you have created in Lab 3 (University Accommodation Database), now add the following data requirements into your database design (EER diagram) and implement them:
Student flat inspections
Student flats are inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspection is the name of the member of staff who carried out the inspection, the date of inspection, an indication of whether the property was found to be in a satisfactory condition (yes or no), and any additional comments.
Accommodation staff
Some information is also held on members of staff of the Accommodation Office and includes the staff number, name (first and last name), home address (street, city, postcode), date of birth, sex, position (for example, Hall Manager, Administrative Assistant, Cleaner) and location (for example, Accommodation Office or Hall).
You need to insert at least two rows of data to the relations created for the above requirements. Task 2
Using INSERT statement, insert the following data into your University Accommodation Database:
1. A new student whose name is the same as your own name, with student number S356, unknown
date of birth and sex, nationality of China, no special need, currently on a waiting list, unknown course.
2. A new contract of student San Zhang, made on 1st September 2017, contract number of 101089, contract period is 3 semesters, room allocated for the contract is S310, moving in date is 5th September 2017, and unknown moving out date.
3. A new invoice for contract 103523, issued on 5th February 2019, invoice number 1108645819, first reminder date is 20th February 2019, second reminder date is 25th February 2019, and the invoice is currently unpaid.
Task 3
Formulate the following queries in MySQL:
a. Present a report listing the names and student numbers of students with the details of their contracts, order by student surname.
b. Display the details of contracts that include the Summer Semester.
c. Display the total rent paid by John Doe.
d. Display the details of flat inspections where the property was found to be in an unsatisfactory
condition.
e. Presentareportofthenamesandstudentnumbersofstudentswiththeirroomnumberandplace
number in Hall Gryffindor.
f. Display the minimum, maximum, and average monthly rent for rooms of each hall of residence,
order by hall names.
g. Produce a report of unpaid invoices.
h. List the students whose payment method is cash.
i. What¡¯s the total income from all paid invoices so far for the current academic year (September
2018- August 2019)?
j. Present a report of students that have paid their invoices within 10 days of the invoice being issues.
Submission: The finished EER diagram, screenshots of your tables in the database (with all data), and screenshots of the queries and query results in Task 3 should be included in a report(pdf file) and submitted to QMplus by 11pm 8th May 2019 (Beijing time).