An assignment

Tanax · 02-17-2009, 01:26 PM

Hi! I have a school-assigment in databases, and I'm just wondering if this is a good way of solving it.

Assignment description:

Quote:

A company in town has an Accommodation Office for university students and wishes to create an information system to monitor the allocation of accommodation to students. Each student requiring accommodation fills out an application form, which holds the student's details and an indication of the type of accommodation required and the duration. Students may rent a room in a hall of residence or student apartment. The halls provide only single rooms, which have a room number, place number, and monthly rental 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. Each hall is managed by a member of the Accommodation Office.

The Accommodation Office also offers student apartments, each identified by a unique apartment number. These apartments are fully furnished and provide single room accommodation for groups of three, four, or five students. Each bedroom in an apartment has a monthly rental rate, a room number, and a place number. The place number uniquely identifies each room available in all student apartments and is used when renting a room to a student.

Apartments are inspected by members of the Accommodation Office on a regular basis to ensure that the accommodation is well maintained. New lease agreements 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. The students pay for their accommodation throughout the academic year and are sent an invoice at the start of each semester. If a student does not pay by a certain date, two reminder letters are sent.

People that use the information system have different responsibilities as earlier mentioned administrative staff, inspector. To be realistic in this project you should discuss in your group necessary and suitable attributes taken from a real world situation. This means that each class should contain a number of attributes that fit the situation.

Another group of users is repairmen who get orders from inspections and enter things that need to be fixed. The company hires a number of different repairmen with different specialities such as ventilation, heating, plumbing, painters, carpenters and electricians. The system handles the repair orders and if the cost is based on unnatural using of the room then the student is responsible for the payment and gets the sum on the next rental payment.

So basicly, we have to write a database that does that.
It's stricly conceptual at this point, so it's no SQL or anything like that.

I've done like this right now:

Code:

Student
-student_id
-student_fname
-student_lname
-student_age
-student_accommodation
-student_duration

Single room
-room_id
-room_placenr
-room_rent
-room_belongtype
-room_belongid

Hall
-hall_id
-hall_manager

Student appartment
-app_id
-app_rooms
-app_inspector
-app_agreement
-app_minperiod
-app_maxperiod

Every student has an id, firstname, lastname, age, the desired accommodation type(hall or appartment) and the duration he or she wants to rent it.

Every single room has an id(which is the room-nr), the placenr(which I don't really understand what it's for and how it should be used/linked), the rent that the room costs, and the "belongtype" is either a hall or app, and belongid is obviously either hall_id or app_id.

Every hall has an id(which is inserted in the different rooms that is in that hall under the belongid section), and a hall_manager which is the id of the staff(haven't created that yet).

Every appartment has an id, a different amount of rooms(3, 4, 5), inspector which will be the id of the staff, the agreement, and the minimum period and maximum period it can be rented.

Anyone thinks I can solve this in a better way before I continue this?