Unit 10 · Practice

Practice

Your diagrams are saved in this browser, and each Check goes into your submission.

Part 1 · Settling the design

A property management company manages rental properties for landlords in the greater Seattle area, for a percentage of the rental income. It needs a database for its owners, properties, units, tenants, payments, and maintenance requests.

1

The office manager says: "A lot of our buildings have more than one owner, usually a married couple or business partners. And plenty of our owners have a few buildings with us." How should the database store which owners own which properties?

2

The office manager says: "Our properties go from single houses, which are one unit, up to apartment buildings with forty units. A unit is only ever in the one building." How should the database connect properties and units?

3

The office manager says: "Unit numbers are whatever the building uses. We've got a 101 on Pine Street and another 101 at Lakeview." Can the unit number be the primary key of Units?

4

The office manager says: "When rent comes in, we need to know who paid it and which unit it was for." Which foreign keys does Payments need?

5

The office manager says: "One of our tenants paid $1,450.50 this month. At the end of the month, the payments have to add up to what we deposited." Which type fits the amount column in Payments?

6

The office manager says: "Every repair request gets a severity from 1 to 10, so we know what to fix first. A burst pipe is a 10, and a sticky drawer is a 1." Where does the severity go, and as what type?

7

The office manager says: "A repair request is either fixed or it isn't, and everything starts out not fixed." Which of these can store the status of every request?

Part 2 · Drawing and testing the design
8

Draw the whole design for the property management company: every table, every column with a type, a primary key in each table, and the lines between them. You choose the table and column names. These are the company's requirements as you understand them:

  1. Owners have names, phone numbers, and a date that they joined the management service.
  2. An owner can have multiple properties managed by the company, and a property can have one or more owners.
  3. Properties have a street address and an estimated market value.
  4. Properties have one or more units, and a unit can only belong to one property.
  5. Each unit has a unit number and a monthly cost.
  6. A unit can have many tenants, but a tenant can only be in one unit.
  7. Tenants have a first name, last name, and email.
  8. A payment has a date and an amount paid. It also must track the tenant who made the payment and the unit that the payment was made on.
  9. Tenants can make maintenance requests on units. A maintenance request has a description, a severity (1 to 10), and a completion status (done or not done). It also must track the tenant who made the request and the unit the request was made on.

A design that covers all nine takes seven tables. If you end up with more or fewer, rethink the design.

Name your tables and columns with the requirements' own words, like a Properties table with Address and MarketValue columns.

Try your design

In the box below, write INSERT statements that put at least two rows in every table. Include a property with two owners, and a tenant who has made a payment and a maintenance request. Run the script, and fix the design or the script until every row goes in.

Run makes the tables your diagram describes, then runs your script on them. The script is saved with your diagram.

⌘/Ctrl + Enter
Show the SQL
9

In a design for the company, the table between owners and properties has one row for each owner of each property. Here are three of its rows:

OwnerID PropertyID
1 1
2 1
1 2

What should its primary key be?

Submit

When you're done, download your submission file and upload it to this unit's practice assignment in Canvas. It carries a summary of your progress and the record of every attempt you made on this page. You can submit as many times as you like; the last one counts.

Attempts are saved in this browser. To keep going on another computer, download your submission here and resume from it there.