Business scenario for the College Accommodation Management System

The director of the Western College Accommodation Office requires you to design a database to assist with the administration of the College Residences. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the Western College Accommodation Office database.

Data Requirements

The data stored for each Student includes: name (first name, family name), home address (street, town, postcode) contact phone number, email address, date of birth, gender, category (university preparation, undergraduate, postgraduate), and status (waiting, resident).

The accommodation available is organised into residences, each of which has a unique name, address, telephone number, and a hall manager who supervises the operation of the residence. Each residence contains a number of rooms. Each room is assigned a unique room number and the cost of occupancy for a semester is recorded for each room. The room number uniquely identifies each room in all residences controlled by the Accommodation Office and is used when renting a room to a student.

Each room is inspected on a random basis. Data held about each inspection includes the date of inspection, name of the staff who carried out the inspection, inspection outcome (satisfactory, unsatisfactory) and general comments.

At the start of each semester, each student accepted into a residence is assigned a room for that semester this constitutes the Occupancy record. It is necessary to maintain a historical record of occupancies, that is, to be able to tell who occupied a particular room between specified dates.

Each semester, every resident student is issued with an invoice related to their occupancy of a particular room, detailing the date the invoice was issued, and cost for the semester. Recording payment of the invoice is NOT part of this system.

Using the information supplied, represent the data requirements for your logical database schema in a single ER diagram