The Director College Accommodation Office Scenario The Director of the College 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 based on the Director’s view has
provided the following requirements specification for the Accommodation Office database.
The data stored on each full-time student includes the student number, name (first and last name), home address (street,
city/town, etc), date of birth, gender, category of student (for example, first year undergraduate (1UG), postgraduate (PG)),
nationality, special needs, any additional comments, current status (placed/waiting), and what course the student is studying
on etc.
The student information stored relates to those currently renting a room and those on the waiting list.
Students may rent a room in a college owned hall of residence or a private house.
When a student joins the college he or she is assigned to a member of staff who acts as his or her Advisor of Studies. The
Advisor of Studies is responsible for monitoring the student’s welfare and academic progress.
The data held on a student’s Advisor includes their full name, position,the name of the department, internal telephone number, and
room number.
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 the halls controlled by the Accommodation Office and is used when renting a room to a student.
The Accommodation Office also offers rooms in private houses. These houses are fully furnished and provide single room accommodation for groups of 3, 4 or 5 students. The information held on private houses includes a house number, address, and
the number of bedrooms available in each house. The house number uniquely identifies each house. Each bedroom in a house has
a monthly rent rate, a room number, and a place number. The place number uniquely identifies each room available in all student houses and is used when renting a room to a student.
A student may rent a room in a hall or private house for various periods of time. New lease agreements are negotiated at the start
of each academic year with a minimum rental period of one semester (15 weeks) and a maximum rental period of one year,
which includes Semesters 1, 2, and a Summer Semester. Each individual lease agreement between a student and the Accommodation
The office is uniquely identified using a lease number. The data stored on each lease includes the lease number, duration of the
lease (given as semesters), name, and student number of the student, place number, room number, address details of the hall
or private house, the date the student wishes to enter the room, and the date the student wishes to leave the room (if
known).
Private houses 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.
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/town, postcode), date of birth, sex, position (for example, Hall Manager,
Administrative Assistant, Cleaner), and location (for example, Accommodation Office or Hall).
