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 of any constraints you would have
declared if mySQL's CHECK command supported either non-deterministic
functions or subqueries. Even though mySql does not support
such 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. It should contain any CHECK commands that mySQL is able to enforce and it must contain foreign key constraints.
- 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
- 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
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. You should assume that it is possible for either apartments or residence halls to be in separate cities, since some universities are located in "twin" cities or in densely organized urban areas like Silicon Valley where there are multiple, contiguous cities in a very small area.
- You may find that you have a similar issue with apartment bedrooms and
residence hall rooms. You may want to have one common super entity that factors
out the common attributes and then separate relations that contain the separate
attributes. However, depending on your design, you may find that all attributes
are similar in which case you might go with a single relation for the rooms.
- 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
- Each apartment has an apartment number that uniquely identifies it.
The apartment number is the same as the flat number (i.e., they
- 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.
- You should consider the student advisor to be someone different than the residence staff. You should think of an Advisor as an academic advisor much like the professional departmental advisors we have here at UTK. Residence staff members do not advise students but instead perform functions that keep the residences operating properly.