COMP1160 Project Description
The head of the UIC student housing office hires you to design and create a database to assist with the administration of the office. The information about the students, halls of residence and the leasing status should be recorded. The data requirements are given as follows:
Each student has a unique student_ id, name(first and last name), address, telephone_number, date of birth, programme, sex, nationality and a student_type. The student_type can be UG1 (first year undergraduate students), UG2, UG3, PG, and so on. You may consider some useful information such as special needs (e.g. disabled students might need rooms in the first floor), background (local, mainland, other countries) and current status (placed or waiting).
Each hall of residence has a unique name, address, telephone number, and a hall warden who supervises the operation of the hall. The halls provide only single rooms for one, two and maximum three students. Each room has its specified floor, size and type. These rooms have a unique room number in a hall. When renting a room to one or more than one students, the housing office should be able to identify a room in a hall, the availability and the monthly rent rate.
Each lease agreement between a student and the office has a unique lease number, duration of the lease, student_id, name, and the details of the room. The agreement is negotiated at the start of each academic year with a minimum rental period of one semester and a maximum rental period of one year. A year should include Spring, Fall or Summer semesters. Other useful details such as the expected date of the student wishes to enter the room and the expected date the students wishes to leave the room should be included.
Each student living in a hall has an account, which has a unique account_id, also an active_flag of the account. The account records the rental payments of the student. At the start of each semester each student is sent an invoice for the rental period. Each invoice has a unique invoice number, lease number, semester, payment due, student id, name, hall address. Additional information may include the method of payment (such as cheque, cash, visa, direct transfer from a bank, etc). For late payment, reminders should be sent and recorded. Recommended actions (such as deducting the rental deposit or informing the university registration) should also be recorded for unsettled payments for two months.
Each hall staff member of the office has the staff_id, name, age, address, pos, (such as hall warden, administrative assistant, cleaner, receptionist) and salary. The rooms are inspected by staff on a regular basis to ensure that the accommodation is well maintained. A log will be recorded for the inspection. Each log has the name of the member of staff who carried out the inspection, the date and time of inspection, and the result of inspection (e.g. satisfactory or not) and any additional comments. You may also consider some reasonable constraints such as a hall should have a warden who manages only one hall.
Assumptions
No description can completely describe a real application. In this project, you can imagine that you are the head of the student housing office and see what kinds of data (i.e. some data should be private for others) and what kinds of report you want to see from the system. For example, you may also consider some activities running intra-hall and inter-halls. You may think of what you should do if a student needs an early termination of the lease contract. You may also need a student’s next-of-kin information for emergency.
Important Dates
1. 8th Dec, 2008, Deadline of the ER diagram of the database (hardcopy). The ER diagram will be corrected only roughly and returned to you. You may revise your design for the SQL implementation.
2. 22th Dec, 2008, Deadline of the Final project. Please submit the soft copy of the following items:
2.1. The (revised) ER diagram
2.2. SQL statements for creating tables and inserting data into the database; indicates all primary and foreign keys, existential constraints, etc.; include triggers and indexes defined
2.3. SQL statements operating on your database and giving correct results.
Make the entire set of source code, together with documentation. Further details will be provided. Since electronic submission is used, your ER diagram must be drawn with some graphical tools.
3. 30thDec, 2008, Project Demo,timeslots for different project groups will be announced later –. Your presentation should be able to show the following items:
3.1. Completeness of all requirements
3.2. Outstanding features, if any, are implemented in your system
Marks will also be given according to the smoothness, time control and team collaboration of your presentation.
Marking Scheme
• Initial submission of ER design 10%
• Correctness and completeness (in design and functionalities) 50%
• Documentations (reasonable assumptions and justifications.) 10%
• User interface design (convenient to use) 15%
• Demonstration (clarity and smoothness of the demo) 15%
• Bonus (outstanding or innovative features in addition to the implementation of correct and complete fundamental functions) maximum 10%
Requirement list For COMP1160 Final Project
1. Correctness and completeness: The system should have the following fundamental functions and return correct results.
a) General Queries - The system should be able to retrieve all data stored in the database.
Example queries:
i. Check the hall warden’s name and phone number when given some hall information.
ii. Check the names and other details of students living in a hall
iii. Check the lease agreement of a student.
iv. Check the details of the rent paid by a student.
v. Who have not paid their invoices in a given period of time after the due date.
vi. Which flats are found to be unsatisfactory?
vii. Query by different ways the address such as name, id, age, .etc
viii. Display the logs ranges from a certain date.
b) Statistics Collection - The system should be able to generate all statistics information. Example queries:
i. Find the total number of students living in a hall according to their programme.
ii. What are the next-of-kin of a student?
iii. Find the total number of vacant places in a date
iv. Find the minimum, maximum and average monthly rent for rooms.
v. Find the total number of rooms in each hall.
vi. What are details of the students that are on the waiting list?
vii. Generate event and payment report for a particular student
c) Modification of Data - The system should provide interface for user to add, remove, update the data in the database. The following are some examples:
i. Handling hall application
ii. Adding the new information of rooms, halls, staff, and so on.
iii. Moving in/out of residents
d) Dealing with special events in the system. The following are some examples: what if
i a student delays the moving out?
ii a student’s payment has been overdue too long (how long is “too long”)?
iii a student has information updates such as changing from UG3 to PG?
iv a student has left the university without paying the rent?
v a student lost his/her key/magnetic entry card?
vi a student wants to renew the lease agreement after one semester?
vii a student does not show up on the date when the room is released?
viii a student wants to make a swap from one room to another?
ix a student requests an early move out before the end of the semester?
2. Documentations
a) Reasonable assumptions are given.
b) The framework of the whole project (e.g. UI level, system level, application level and database level) is clearly shown.
c) List and explain clearly all the functionalities of the system.
d) The techniques utilized in the project.
3. Running Presentation/Demonstration
a) All the group members should arrive on time.
b) All the group members should participate in the presentation/demonstration and describe clearly the parts in the project s/he contributes.
c) The presentation/demonstration should be completed within 30 minutes.
d) The presentation/demonstration should be presented in English.
e) You should show clearly the basic functionalities and outstanding features of the project
4. User Interface
a) The user interface should be intuitive and effective to use
b) Please bear in mind that a casual user should not have problems to check some simple information.
c) The system should provide an English UI. You can also design multi-linguistic UI.
5. Bonus
a) Show any outstanding or innovative features (e.g. data design, user interface, functionalities or techniques, etc.) of the project in the document and demonstration.
b) The necessary condition to get the full bonus is that the correctness and completeness of the system must be shown up to a substantial level. Otherwise, only a proportion of bonus will be given even the features are excellent.
c) We are surely not penurious and are willing to provide bonus to good extra efforts!