Due: 9:30am, Thursday, May 5th, 2005

1. Introduction

Throughout the course (so far) we elaborated on several techniques which are used during the design stage of a database system. The first step was the (E)ER methodology as a formal tool to represent a model of the relevant entities for the enterprise whose information system one needs to develop, along with the relationships among them. Subsequently, we defined the Relational Model and described the “rules of translation” that one can use to transform a representation in an (E)ER into an equivalent representation in a Relational Model[1].

So far, we have always assumed that the Requirements Specification (AKA “fact collection”; “discovery”) is implicit’ given to us, completed by some “oracle” and we used intuitive examples of some specifications of an enterprise in order to introduce and develop the modeling techniques. In some way, we will adopt the same behavior in this project.

Goal.

In this project, you will be given a complete description of the requirements specification of a given enterprise. From the specifications, you will have to demonstrate an ability to use the methodologies that we have tackled in the course so far. More formally, based on specifications below, in this project you will have to:

1. Develop an ER diagram representing the entities involved and the relationships among them.

2. Translate the ER model into an equivalent representation using the Relational Model.

3. Represent the tables and their attributes (i.e., the schemas) in Microsoft Access.

The commercial tool of choice for this project is Microsoft Access for a two-fold reason:

· It is widely available;

· It has a (somewhat) familiar graphical interface and yet it can give you a “close enough” idea of what to do, should you use another commercial DBMS in the future.

2. Requirements Specification

For this project, we will describe the data requirements for a small University Accommodation Office.

Students.

The data needed for each full-time student includes id#, name (first and last), home address (street, city, zip-code), date of birth, sex, category (e.g., sophomore, junior, …, graduate), nationality, special needs, current status (placed/waiting) and which courses is the student registered for. Optionally, one may also add additional comments for a particular student.

The student information pertains to those currently renting a room and those who are on the waiting list. A particular student may rent a room either in the hall of residence or student flat.

When a student joins the University, he is assigned a member of staff who acts as his Advisor. The Advisor is responsible for monitoring the student welfare and academic progress throughout his time at the University. The data kept for a student’s Advisor includes full name, position, name of the department, phone number and office location/number. We assume that there are no two advisors in the same department will have the same name.

Halls of Residence.

Each hall of residence has a name, address, telephone number and a manager who supervises the operation of the hall. The halls provide only single rooms which have room number, place number and monthly rent rate.

The place number uniquely identifies each room in all halls controlled by the Accommodation Office and is used when renting a room to a student.

Student Flats.

The Accommodation Office also offers student flats. These flats are fully furnished and provide single-room accommodation for groups of two, three or four students. The information held on student flats includes a flat number, address and the number of rooms available in each flat. The flat number uniquely identifies each flat.

Leases.

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 quarter and a maximum rental period of one year (including the Summer quarter). Each individual lease agreement between a student and the Accommodation Office is uniquely identified using a lease number.

The information pertaining to each lease includes the lease number, duration of the lease (in number of quarters), name and the id# of the student, place number or flat number, address details (of the hall or the flat) and the date the student wishes to enter the room, along with the date the student wishes to leave the room.

Invoices.

At the start of each quarter, each student is sent an invoice for the respective rental period. Each invoice has a unique invoice number. The rest of the data include the lease number, quarter, payment due, student’s full name, student’s id#, place number or flat number, and the address (hall/flat). Additional data is kept as a Payment, which reflects the invoice number, date of payment, method of payment (cheque, cash, credit card (indicate type…)) plus, if needed, the dates the first and second reminders were sent.

Students Flat Inspections.

Each flat is inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspection includes name of the staff member who carried out the inspection, the date the inspection was made, an indication of whether the property was found to be in a satisfactory condition (yes/no), plus additional comments, if needed.

Accommodation Staff.

Some information needs to be held for the staff members too, and it includes name (first and last), home address (street, city, zipcode), date of birth, sex, position, (e.g, Hall Manager, Administrative Assistant, Cleaner) and Location (e.g., Accommodation Office or Hall). Here we assume that there are no two staff members having the same name.

Courses.

The Accomodation Office also stores a limited amount of information on the course run by the University course number, course title, year, instructor, room number, and department name. Each student is associated with the courses he is registered for. Here we assume that the course number and the department name can uniquely identify a course.

Contacts.

For each student, the office keeps a record of a contact person, which includes SSN, name (first and last), relationship with the student, address (street, city, zipcode) and the contact phone number.

3. What to Turn In

Once you have completed this project, you are supposed to provide the following:

1. A hard-copy (printout) of the ER diagram. You may use a tool of your choice to draw it.

1.1 A printout which describes the restrictions on the domains/types of the attributes used

2. A “quazi-relational” model of the database schema which you obtained using the Microsoft Access, in which the relationships from the ER diagram are retained and represented as links among the attributes of the tables in the Microsoft Access schema description.

3. A hard-copy of the description of how you obtained each schema in the “pure” Relational Model (i.e., which rule did you apply to translate a particular item from the ER diagram into the corresponding schema of the Relational Model) where everything is represented as table…

4. The pure relational model (tables only) which, again, you have generated using Microsoft Access. Note that now you should not have links between attributes in different schemas.

NOTE: For the items #2 and #4 above, you are to email Jin with the following format for the subject title:

CS317 lastname firstname p1

The names of the corresponding .mdb files, describing each database schema should be:

your_id_11.mdb and,

your_id_12.mdb, respectively.

Likewise in hw1, you are allowed to turn in hand-drawn models so long as they are NEAT and LEGIBLE!

Items #1 and #3 above should be turned in during class.

Project Due Date: the beginning of the class of Thursday, May 5th, 2005.

Extra Credit.

If any of you would like to earn up to 15% extra credit, then you are more than welcome to identify the functional dependencies and generate an improved design of the relational model (database schema) which is at least in the BCNF. Keep in mind that because this is extra credit, it is not fair to ask for help from the TAs or the Professor.

[1] Thus far we have only completed the correctness of the translation and we are currently analyzing the techniques used to improve the quality of the design of the Relational Model, i.e., the normalization.