1. General Information

The purpose of this assignment is to provide you with experience in analyzing, designing and implementing a solution for a University Accommodation Office. Your solution should be implemented as a program for a database system using Microsoft Access 2000. This assignment will help you to understand the nature and purpose of database analysis, design and implementation. It offers you experience in managing a technical database project.

This assignment is to be attempted by groups of 4 to 5 students. Each group is collectively responsible for both the submission and the outcome. Individual efforts will not be marked. There are no restrictions on the use of word processors or similar tools for the production of submissions for this assignment.

Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for non-completion of this assignment.

Submit Your Coursework to the Moodle site of this subject. Marked assignments will be available from Moodle website of this subject.

2. Problem Description: The University Accommodation Case Study

University Accommodation Office requires you to design and implement a database to assist with the administration of the office. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the University Accommodation Office database followed by examples of query transactions that should be supported by the database.

University Accommodation Office stores the details of each full-time student. These details include: the student number, name (first and last name), home address (street, city, postcode), mobile phone number, email, date of birth, gender, category of student (i.e. undergraduate or postgraduate), nationality, special needs, any additional comments, current status (i.e. currently stay at the university accommodation or waiting for university accommodation), the course the student is enrolled in.

The student information stored relates to those currently renting a room and those on the waiting list. Students may rent a room in a hall of residence or student apartment.

When a student joins the university he/she is assigned to a member of staff who acts as his or her Adviser. The Adviser is responsible for monitoring the student's welfare and academic progression throughout his or her time at the university. The data held on a students’ adviser includes: advisor’s full name (first and last name), position, name of department, internal telephone-number, email, 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, building number, and monthly rent rate. The building number and room number together uniquely identifies each room in all halls controlled by the Residence Office and is used when renting a room to a student.

A student may rent a room in a hall of residence for a period of time. New lease agreements are negotiated at the start of each academic year, with a minimum rental period of one trimester and a

maximum rental period of one year, which includes trimesters 1 and 2. Each individual lease agreement between a student and the University Accommodation Office is uniquely identified using a lease number. The data stored on each lease includes: the lease number, duration of the lease (given as trimesters), student's name and student number, building number for hall of residence, room number, address details of the residential hall including building number, and the date the student wishes to enter the room, and the date the student wishes to leave the room.

At the start of each trimester, each student is sent an invoice for the following rental period. Each invoice has a unique invoice number. The data stored on each invoice includes the invoice number, lease number, trimester number and year (i.e. trimester 2 2014), payment due date, student's full name and student number, building number, room number, and the address of the hall. Additional data is also held' regarding the payment of the invoice and includes the date the invoice was paid, the method of payment (check, cash, Visa card) and amount paid.

Staff on a regular basis inspects each student hall of residence. This is 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.

University Accommodation Office stores the details of its entire staff, which includes the staff number, name (first and last name), email, home address (street, city, postcode), date of birth, gender, position (fur example, Hall Manager, Administrative Assistant, Cleaner) and telephone number. Hall manager has several telephone numbers.

The University Accommodation Office stores information on the courses offered by the university, including the course number, course title (including year) and department name. Each student is also associated with a single programme of studies.

The University Accommodation Office stores information about the cleaning history of their offered accommodations (hall of residence). The details recorded about cleaning services include, accommodation details (hall of residence), date of cleaning, staff number of staff that performed the cleaning.

The University Accommodation Office has five seminar rooms in the main office. These seminar rooms are only rented only to staff to organise a seminar. The hiring of a seminar room is free of charge for staff. Seminar room details are stored in University Accommodation Office database. The seminar room details are: room number, room size, room location. Detail of staff that hired a seminar room, and the staff that performed the booking of seminar rooms for staff are also stored in the University Accommodation Office database. These details are: staff number, staff first and last name, dates for which a seminar room is hired and details of all seminar rooms hired by all staff and the details of the staff that process the rental of a seminar room for other staff is recorded. Staff can order food for their seminar. The detail of the food ordered is stored in the data base of University Accommodation Office. These details are: staff number who ordered the food, staff number of staff that hired the seminar room, type of food ordered, total cost of food ordered.

3. Requirements

The University Accommodation Office database program developed by your team should have well designed screens that are easy to use, understand and follow by staff and students. A novice user should be able to use the University Accommodation Office database. For security reasons the views of students and staff should be different (Hint: use switchboard in MS Access to achieve this task).

The University Accommodation Office database system designed and developed by your team should:

Support Usability

That is it has well designed screens that are easy to use, understood by novice university accommodation staff and students. A novice user should be able to use the University Accommodation Office database application (program) by reading instruction on the computer screen of your program.

Security

For security reasons the views of customer and staff should be different. Reports

All reports and input screens should be easy to read, use and understand. It should be logically laid out for novice users. All reports and input screens should be easy to read, use and understand. It should be logically laid out for novice users.

The University Accommodation Office staff should be able to use the database system to:

1. Present a report listing the names and student numbers of students with the details of their lease agreements.
2. Display the details of the total rent paid by all students.
3. Present a report on students who have not paid their invoices.
4. Present a report on students who have paid their invoices.
5. Display the details of inspections where the property was found to be in an unsatisfactory condition.
6. Present a report listing the details of all students currently on the waiting list for accommodation.
7. Check details of all seminar rooms that are book.
8. Check the average income of all seminar rooms that are booked so far.

The students should be able to use the University Accommodation Office database to:

1. Check the telephone number of each staff and their office number.
2. Check the details of the rooms available for rental.

4. Assignment submission

For the scenario in the problem above:

1. Identify entity types and their attributes, including the primary keys and any foreign keys for each entity.

1. Develop a data dictionary for your database.

1. Compile the E-R diagram of the system (state all assumptions that you have made).

Include with your assignment a cover page containing the first and last name of all students in your team as well as their student id, your tutorial day and time, subject name and number.

You need to include with your program the details of all assumptions that you have made for the University Accommodation Office. Submit all your design documents with your database programs on the due date specified via Moodle website of this subject.

Marking Scheme DOCUMENTATION including

Entity types and their attributes, including the primary keys and any

Foreign keys for each entity (Normalization to 3NF) 10 Marks

E-R diagrams 10 Marks

Data Dictionary 10 Marks

===========

Subtotal: 30 Marks

IMPLEMENTATION including

Tables and data 10 Marks

Queries 10 Marks

Forms 5 Marks

Reports 5 Marks

========

Subtotal: 30 Marks

TOTAL: 60 Marks [(Documentation (30 Marks) + Implementation (30 Marks)]

Note:

Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for non-completion of this assignment. Every group member is advised to retain a full copy of the material handed in for this group assignment.