Database Question

Case Study

The Director of the University 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 matriculation number, name (first and last name), home address (street, city/town, postcode), date of birth, sex, category of student (for example, first year undergraduate, postgraduate, etc), nationality, smoker (yes or no), special needs, any additional comments, current status (placed/waiting), and what course the student is studying on.

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

When a student joins the University 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, name of 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 student flats. These flats are fully furnished and provide single-room accommodation for groups of three, four, or five students. The information held on student flats includes a flat number, address, and the number of single bedrooms available in each flat. The flat number uniquely identifies each flat.

Each bedroom in a flat has a monthly rent rate, room number, and a place number. The place number uniquely identifies each room available in all student flats and is used when renting a room to a student.

A student may rent a room in a hall or student flat 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 the Summer Semester. Each individual lease agreement between a student and the 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 semesters), name and matriculation number of the student, place number, room number, address details of the hall or student flat, the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).

At the start of each semester 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, semester, payment due, student's full name and matriculation number, place number, room number, and the address of the hall or flat. Additional data is also held on the payment of the invoice and includes the date the invoice was paid, the method of payment (check, cash, Visa, etc), the date the first and second reminder is sent (if necessary).

Student flats 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).

The Accommodation Office also stores a limited amount of information on the courses run by the University including the course number, course title (including year), course leader's name, internal telephone number, and room number, and department name. Each student is associated with a single course.

Next-of-kin

Whenever possible, information on a student's next-of-kin is stored which includes the name, relationship, address (street, city/town, postcode), and contact telephone number.

Data Queries

1. List the manager's name and telephone number for each hall of residence.

2. List the names and matriculation numbers of students with the details of their lease agreements.

3. Display the details of lease agreements that include the Summer Semester.

4. Display the details of the total rent paid by a given student.

5. List students that have not paid their invoices by a given date.

6. Display the details of flat inspections where the property was found to be in an unsatisfactory condition.

7. List the names and matriculation numbers of students with their room number and place number in a particular hall of residence.

8. List the details of all students currently on the waiting list for accommodation, that is, not placed.

9. Display the total number of students in each student category.

10. List the names and matriculation numbers for all students who have not supplied details of their next-of-kin.

11. Display the name and internal telephone number of the Advisor of Studies for a particular student.

12. Display the minimum, maximum, and average monthly rent for rooms in halls of residence.

13. Display the total number of places in each hall of residence.

14. Display the staff number, name, age, and current location of all members of the accomodation staff who are over 60 years old today.

Project Deliverables

Phase I

You will be responsible for submitting the Entity-Relationship (ER) diagram. The ER diagram must show all of your entities, relationships and cardinality of your relationships. You should follow the UML notations.•

Create an entity relationship model

Create a conceptual schema for the Acme General using the concepts of the ER model. To simplify each diagram, only show entities and relationships. Specify the cardinality ratio and participation constraint of each relationship type. State any assumptions you make when creating the ER model (if necessary).

Phase II

The logical model (relational schema) for your ER. The relational schema should list each entity with all of its attributes as well as all of your primary and foreign keys. You should follow the RM format that we used during week 3. No other format will be accepted.

• Map your high-level conceptual schema to a logical model. In this step you will create the relational schema and identify all attributes as well as your primary and foreign keys.

• Make sure your RM is in the 3NF

Phase III

• Once your ER models and relational schema are complete, you will begin working on Phase III. Using and DBMS you like, you will actually build (implement) the database that you designed in Phase II. Your project will need to include the following:

Tables

Create all of the tables specified in your logical model. Following are some additional specifications for table creation:

• Make sure that your primary keys are assigned in each table.

• Verify that all of your fields are assigned an appropriate data type.

• Verify that all of your fields are assigned an appropriate field length.

• Use default values where appropriate (i.e. default for "OrderDate" as today's date).

• Insert 2-3 rows of data in each table. You make up the data.