Graduate Student Project

You will need to

1. Design and implement the database using mysql. Please use the database account that you were assigned for this course.

2. 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.

3. You should submit the following documents to Canvas via the Graduate Project submission link:
   1. an ER diagram for your DB,
   2. 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.
   3. 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.
   4. 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.

4. 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:

1. 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

2. 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.

3. 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.

4. 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.

5. 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.

6. 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).

7. You may ignore the Courses at the end of the case study write-up. It is unused in the rest of the case study.

8. 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.