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?
A property can have more than one owner, and an OwnerID column holds one. The property's second owner would have nowhere to go.
An owner can have several properties managed by the company, and a PropertyID column holds one. The owner's second property would have nowhere to go.
An owner can have several properties, and a property can have several owners, so this is many-to-many. Each row of the table in between is one owner of one property.
"More than one owner" has no upper limit, so a third owner would have nowhere to go. Finding every property one owner has would also mean searching both columns.
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?
A property can have many units, and a UnitID column holds one. The property's second unit would have nowhere to go.
A table in between is for when both sides can have many. A unit only ever belongs to one property, so a column in Units can say which.
The PropertyID in Units already says which property each unit is in. A UnitID in Properties would hold only one of its units, and could disagree with Units.
A property has many units, and a unit belongs to one property. That's one-to-many, and the foreign key goes in Units, the table on the many side.
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?
Both properties have a unit 101, so two rows of Units would have the same unit number. A primary key can't repeat.
The type decides what a unit number can look like. Both properties can still have a unit 101, and a primary key can't repeat.
A primary key picks out one row, and 101 would pick out two. An ID of its own picks out each unit, and a table that points at a unit needs only that one column.
A primary key can be any column, or columns, that no two rows share. The trouble with the unit number is that two units have 101.
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?
The tenant's row says which unit they're in now. When a tenant moves to another unit, their old payments would look like they were made on the new one.
The unit a payment was made on is part of the payment, and it stays the same if the tenant moves later. A tenant's unit can change, so it's stored on the payment. A unit's property can't, so Payments doesn't need a PropertyID.
A unit can have many tenants, so the unit can't say which of them made the payment.
A tenant makes a payment every month, and a PaymentID column holds one. The tenant's second payment would have nowhere to go.
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?
An INT holds whole numbers, so the tenant pays 1450.50 and MySQL stores 1451.
As text, the amounts can't be added up with SUM or compared as numbers. A typo like '$1,45.50' would go in too.
Two digits after the decimal point hold the cents, and 10 digits in all go up to 99,999,999.99. The 1450.50 is stored exactly.
Four digits in all, two of them after the point, holds at most 99.99. The 1450.50 doesn't fit.
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?
The severity is one whole number for each request. There's nothing else to store about a severity level, so it doesn't need a table of its own.
A row of Severities would hold just its number, from 1 to 10. When a level has nothing else to store about it, the number itself can go in the request's row.
As text, '10' sorts before '2', so sorting requests by severity puts them out of order. Any text goes in too, like 'high'.
Nine of the ten would always be 0, and finding the requests of severity 8 or more would mean checking three columns.
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?
A request that isn't done has no date to put there, and NOT NULL won't let the column be empty. Without NOT NULL, a date that stays empty until the request is done would work, and it would say when.
A unit can have many requests, some done and some not. A unit's row has room for one status.
Then the severity of a finished request is lost, and a 0 in the severity column means something other than a severity.
Every request has a 1 or a 0, so every status has a place. A new request starts at 0, and it changes to 1 when it's done.
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:
Owners have names, phone numbers, and a date that they joined the management service.
An owner can have multiple properties managed by the company, and a property can have one or more owners.
Properties have a street address and an estimated market value.
Properties have one or more units, and a unit can only belong to one property.
Each unit has a unit number and a monthly cost.
A unit can have many tenants, but a tenant can only be in one unit.
Tenants have a first name, last name, and email.
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.
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?
Owner 1 is on two of the rows, one for each of their properties. A primary key can't repeat.
Property 1 is on two of the rows, one for each of its owners. A primary key can't repeat.
An owner either owns a property or doesn't, so no two rows need the same pair. With the pair as the key, the database refuses owner 1 listed twice for property 1.
With an ID as the key, owner 1 could be listed twice for property 1, and nothing would stop it. No two rows need the same OwnerID and PropertyID, so the pair can be the key, and it refuses that second row.
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.