Home‎ > ‎CIS 54‎ > ‎

Practice Final

You are building software that keeps track of the apartment buildings that are managed by your company. The schema keeps track of buildings and the apartments in them as well as tenants.


Part 1: Data Modeling

Your data model must have the entities and attributes listed in this section. Do not add other data items except for surrogate keys if you need them. Use appropriate data types in your model. The entities are:

Apartment Building

Your software keeps track of the buildings that you manage. Each building has the following information:

  1. The building name

  2. The building address

  3. The name of the building manager

Apartment

For each apartment in a building you keep track of the following information:

  1. The apartment number or letter

  2. The number of bedrooms

  3. The number of bathrooms

  4. The market rental price

Tenant

You must keep track of the tenants in every apartment (if there is one). Keep track of the following information:

  1. The tenant name

  2. The tenant’s vehicle license plate number

Lease

In order for a tenant to occupy an apartment they must have a lease. Each lease can have one or more tenants on it and a tenant may have multiple leases. Each lease has an agreed upon price associated with it, that may be different from the market price.


Question 1: What are the functional dependencies in the above data? Do not add any surrogate keys.


Question 2: Update your functional dependencies from Question 1 to include surrogate keys where you think they are necessary.


Question 3: Use your functional dependencies from Question 2 to create tables for each of the entities in the database. Draw your tables in MySQL workbench and connect each of them with a suitable relationship.


Part 2: Inserting Data


Create a schema from your data model. Hint: MySQL workbench can do this for you. Your schema should include valid primary keys and foreign key constraints. If you did Part 1 properly those should already be correct. Insert test data into your schema, two to three rows per entity.

Export the data from your database into an SQL file.


Part 3: Queries


Your database can be used to answer important questions. Given the schema you designed write queries to perform the following tasks:


Query 1: Write a query that shows what the rental market value of each building is.


Query 2: Write a query that shows how much money is made from the sum of all the leases in each building. This query would show $0 for a building that has no tenants.


Query 3: Write a query that shows the difference between what a building could make and what it actually does make. In other words write a query that shows the difference between Query 1 and Query 2 for every building.


Query 4: Write a query that shows the name of every tenant and what apartments they lease.


Part 4: Security


Different users have different privileges in your database. You have the following users:


  • Mike the Manager

    • Username: mike

    • Password: 123456

    • Access: Can do anything

  • Robbie the Rental Agent

    • Username: robbie

    • Password: rrrrobbie

    • Access: Can create and update lease information


Write grant statements that give the users the access they need. You can assume that they are able to login from any computer (i.e. ‘%’).


Comments