- 1 -

ITD102 Final Project

Database Design for
University Accommodation Office

Due: 12//14/2013, 6:30pm. Each group needs to:
1. submit a softcopy via your EDMODO.COM account (per member) before class,
and
2. submit a hardcopy to the instructor at 6:30pm in class.

Estimated Time: 15 to 22 man hours

Overview:
This project gives you an opportunity to applying what youve learned about database
design in this class. You are to develop a database in Oracle for the University
Accommodation Office.

Grading:
The project will be graded after implementation and all materials are handed in. However,
each initial individual task must be submitted by the Due Date listed below. Individual
tasks will be commented by the instructor during the course along with any feedback for
improvements. Students are encouraged to improve each task right up to the final
submission.

Deliverable Due Date Submission Method
Task 1 11/27, 6:30pm
Submit via EDMODO by each member. Task 2 11/30, 6:30pm
Task 3 12/4, 6:30pm
Final Project (Task 1-4)
12/14, 6:30pm
Submit a hardcopy to instructor as well as
submit a softcopy via EDMODO by each
member.
Peer Evaluation Form Submit via EDMODO.com by each student.

Grade Component Points
Task 1 30
Task 2 15
Task 3 25
Task 4 (SQL part) 15
Misc. (consistency among Task 1-4 & formal write-up
part in Task 4)
15
Total 100
The above will include an overall 10% peer evaluation of each team members
contribution to the project. That is, the total points awarded to each team can be shifted
by as much as 10% among the individuals of the team based upon other team members
assessment of relative contributions. Peer evaluation for the team will be done at the end
of the project by filling a peer evaluation form (on p.7 of this document).

Tasks:

Task 1. Create an E-R Diagram

Given the scenario of the University Accommodation Office (see p.4 of this document),
develop an E-R diagram that reflects the data requirements within the scope of the system
desired. This part of Task 1 is similar to your Assignment #1.

- 2 -
Along with the E-R Diagram, you should provide a chart displaying details about your
entities and another chart displaying details about your attributes. The entity chart should,
for each entity type, contain the following information:
1. Name
2. Description
3. Appropriate type (fundamental, associative, weak, subtype, supertype)
4. Approximate number of occurrences of this entity, if known
The attribute chart should, for each attribute, contain the following information
1. Name
2. Description
3. Entity type of which it is a part
4. Type (i.e. primary key, if so designated)
5. Data type (use SQL data types)
6. Additional Domain specification, e.g. enumerated list of permitted values, not
NULL, may be NULL, etc.
Submit one Word document with:
1. the E-R diagram and any assumptions you made, and
2. Entity chart and attribute chart. You can use the entity chart and attribute chart in
Appendix as template.

Task 2. Develop a Relational Schema

Develop a relational schema representing the E-R Diagram you created in Task 1. Be
sure to identify all primary keys and foreign keys.
Submit one Word document with:
1. Task 1 with any necessary changes or improvements (please indicate or highlight
them in document), and
2. the relational schema.

Task 3. Implement the Database

1. Create a collection of tables in Oracle that conform to the relational schema you
developed in Task 2. Include any domain integrity constraints, referential
integrity, etc.
2. Add some data (at least 4 rows in each of the main tables).
Submit one Word document with:
1. Task 1 and 2 with any necessary changes or improvements (please indicate or
highlight them in document), and
2. a statement with the following information: the ID and password of the account in
which your group implemented the database.

Task 4. Run SQL Queries and Summarize Project
Listed below are some examples of query transactions that should be supported by the
University Accommodation Office database. Write a SQL statement for each of the
following questions and be sure to include BOTH the SQL you wrote as well as the
results you received from running that statement. This task is similar to your Assignment
#3.
1. List the names and student IDs of students with the details of their lease
agreements.
2. Display the details of lease agreements that include the Summer Semester.
3. Display the details of the total rent paid by a given student.
4. List the names and student IDs of students who have paid their rent late for
January 2005.
5. Display the details of flat inspections where the property was found to be in an
unsatisfactory condition.
- 3 -
6. List the names and student IDs of students with their room number and place
number in a particular flat.
7. Display the total number of students in each student category.
8. List the details of all students currently on the waiting list for accommodation,
that is, not placed.
9. Display the minimum, maximum, and average monthly rent for rooms in each flat.
10. Display the staff number, name, and monthly salary for all office staffs.
Submit one Word document with:
1. Task 1, 2 and 3 with any necessary changes or improvements (please indicate or
highlight them in document), and
2. the questions, the SQL you wrote and the results of running that statement for
above questions, and
3. a formal write-up (about 1-page, single space, 12-point font) summarizing your
experiences with the project (e.g., what you liked about it, what you disliked
about it) and what you learned by doing this project.
- 4 -
The University Accommodation Office Scenario

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 Directors view has provided
the following data requirements specification for the University Accommodation Office
database.

The University Accommodation Office stores information for those students who
currently rent a room and those on the waiting list. The data stored on each full-time
student includes: the student ID, name (first and last name), home address (street, city,
postcode), date of birth, sex, category of student (for example, first year undergraduate,
postgraduate), nationality, smoker (yes or no), special needs, any additional comments,
and current status (placed/waiting). Students may rent a room in a student flat.

Each student flat has 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 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 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 student ID of the student, place number, room number, and the date
the student wishes to enter the room, and the date the student wishes to leave the room (if
known).

Students pay their rent by check on a monthly basis. If they miss the payment due
date, they will need to pay a penalty, which is 10% of their rent. Each payment has a
unique payment number. The data stored on each payment includes the payment number,
due date, actual payment date, payment amount (including penalty when applicable) and
check number.

Student flats are inspected by staff on a regular basis to ensure that the
accommodation is well maintained. The information recorded for each inspection
includes 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,
postcode), date of birth, and gender. There are three types of staff: office staff (such as
Flat Manager and Administrative Assistant), cleaner and maintenance staff. Monthly
salary needs to be recorded for office staffs. For cleaners and maintenance staff, hourly
pay rate needs to be recorded. Each maintenance staff can have multiple skills (e.g.,
providing electrical service, repairing heating system, and so on). The data stored for
each skill includes a unique skill number and a short description.
- 5 -
Appendix

Entity Chart (delete the example before work on your own entity chart)

Name Description Type
Number of
Occurrences
CUSTOMER A customer is a person who did or is
doing business with Cool Coat Paint
Company. Customer places order
and is the receiver of the painting
service provided by Cool Coat Paint
Company.
Fundamental Several hundred






- 6 -
Attribute Chart (delete the examples before work on your own attribute chart)

Name Description Entity Type Type Data Type
Additional Domain
Specification
CUST_FirstName Customer's first name. This information, together
with other contact information, enables the company
to contact customers.
CUSTOMER VARCHAR(20) Not NULL
CUST_State The state customer lives. This information, together
with the address and city where the customer lives
and the zip code of that place, enables employees to
contact customer by mail.
CUSTOMER CHAR(2) Two-character notion of
United States' states. Not
NULL.







- 7 -
ITD102
Project Peer Evaluation Form

Name: _______________________ Team #: ___________________

Assign % to team members (other than you) by the value of their contribution (the
total % should add up to 100%):

Other Team Member Names % of Contribution



Total 100%

If the % of contribution for certain members are significantly less than others, please
provide justifications below: