Case Study: Tiny College Student Accommodation
Tiny College is a private College with many students. The unit student Accommodation proposes to construct a database to monitor the Accommodation of students in the University. Each student requiring Accommodation is required to complete an application form that contains full information on the student and the type of Accommodation required together with the duration of occupancy. Students have two choices, either to rent a room in the hostel or an apartment owned by the university. The hostel has only a few single rooms with room number, location number and monthly rental. The location uniquely identifies each room in all the halls under the control of the Accommodation unit and used when renting out the room to students.
Staff from the Tiny College Accommodation unit manages each hostel. The Accommodation unit also offers student apartments. Each apartment is uniquely recognized through the apartment number. Each apartment is fully furnished and can be occupied by three, four or five students per apartment. Each room in the apartment has monthly rental rate, room number and allocation number. The location number uniquely identifies each room and is used when renting out the room to students. The Housing unit inspects the apartment at fixed intervals to ensure that the apartment is constantly well kept.
The rental agreement is signed at the beginning of the academic year with the minimum rental period of one semester and the maximum period of one academic year. Students will pay the rental fee for the whole year and will be given an invoice at the beginning of every semester. If the rental payment is not settled within a stated period, two reminders will be issued.
a) Find the case requirements and analyze them.
b) A fully annotated E-R diagram 1 and 2 showing the entities, primary and foreign keys, composite keys and relationships.
c) A Relational schema of your database in 3NF, clearly indicating attributes, the data type of each attribute, primary and foreign keys, candidate keys, and which attributes are nullable, giving reasons. List any assumptions you need to make.
d) Listing of major queries in SQL (update, sorting, joining tables,
conditions using the where and having clause, order and grouping, logical and comparison operators, set functions, sub-queries etc.)