Graduate Student Project
If you are registered for the graduate version of the course, 565, then
you will be implementing the case study described in a Canvas handout
"The University Accommodation Office Case Study." Go to Course Materials and
then select the University_Case_Study.pdf link. The handout's description
of the case study is by necessity somewhat brief. You will probably have
questions about certain parts of the case study. Please post your questions
to piazza, so I can share the answers with everyone. You may be frustrated
about the need to ask questions, but in the real world you will need to ask
your user/clients questions, so get used to it. Further, make sure you do not
start the project at the last minute because you may find that you need answers
to questions, and you may have to go through piazza to find answers, or ask
questions if you cannot locate the information on piazza. I will
not be repeating answers to questions that have already been asked.
You will need to
- Design and implement the database using mysql. Please use the
database account that you were assigned for this course.
- To design and implement the database, you will need to design and
implement a set of relations, populate them with example data,
and test the sample query transactions in B.1.2 of the handout. Make sure your
database works with all of these sample query transactions as
the TA may test any or all of them. In addition, make sure that
your database can answer the following query:
List the names of students, their apartment number, and their bedroom number
renting an apartment at 1678 Cardiff Rd.
- You should submit the following documents to Canvas via the
Graduate Project submission link:
- an ER diagram for your DB,
- an English language description about the tables you used, including
- their names
- their attributes,
- whether these attributes are mandatory or optional (can be NULL)
- their primary and foreign keys,
- any constraints you used. Even though mySql does not support
constraints, you should try to identify constraints that you think
should hold, based on the books description.
- a source file named university.sql
containing the SQL DDL code and sample data you used to create your database.
- a separate source file named queries.sql
containing all the sample queries from B.1.2 plus the
query listed above. Please put all the queries
in one file and put them in the same order as they appear in the
write-up.
- The TA will let you know if you need to demo the database or not.
If your submission is clear enough, then a demo probably will not
be needed. If a demo is needed, then you will need to see the
TA during office hours or schedule an appointment to demo your
project.
Here are some clarifying assumptions about the project:
- ignore the student program of study at the end of the project description--it has already been specified as
a major with the student's information
- A place number uniquely identifies a room in the entire residence system, hence you may assume that if a room in a flat has a particular place number, then no room in the residence halls will have that place number.
- You will probably not be able to create a single table for apartment and residence halls. I think you need separate tables to handle them well. One possible solution is to place common information for the two into one relation (this relation would hold information about both apts and halls), and then create two other relations, one for apts and one for halls, that contains additional information that is unique to each. It is up to you though to figure out the best way to handle the situation, and your solution may vary, depending on how you design the rest of the database. You are certainly welcome to introduce primary keys or other information as you deem fit, just make sure you can justify it. It is
possible that some queries involving housing information could be answered using
set union between the two tables.
- Assume that an invoice must be paid in full and only one is issued per
semester for each student.
As for determining total rent, the spec indicates that payment amounts are recorded. Hence somewhere in your DB you will store information about these payments and a query should be able to sum them up using an aggregate operation.
- The description of student flats in the project description is ambiguous.
Please make the following assumptions about what is written:
- The university owns apartment buildings, each of which contains multiple
apartments.
- Each apartment has an apartment number that uniquely identifies it.
The apartment number is the same as the flat number (i.e., they
are synonyms).
- The room number for a bedroom is its number within the apartment
(i.e., 1, 2, 3, 4, or 5).
- You may ignore the Courses at the end of the case study write-up. It is
unused in the rest of the case study.