home—lects—exams—hws
D2L—breeze (snow day)
lect15-ERD-review
database/ERD review
We spent 40min reviewing databases.
Example:
Suppose we want a database for movies and actors. What tables? What columns?
- underline primary key
- (when to) create an ID field?
Try not to (since you're making up more info).
One good reason though: when figuring out what the PK is,
you keep adding more and more columns, and decide that even every column
is not enough of a composite primary key.
(E.g. registrar has might have a Students table
with a name plus gender plus year-admitted,
but that's not always enough to uniquely identify somebody.)
For DB-efficiency, people might make up synthetic keys (spec. for indexing),
but use better approaches when possible.
- name tables: plural-noun (and each row is just one of that thing).
- relation names: Mad-lib:
“A noun verb num to num nouns.”
where:
the nouns are table-names;
num is one of {zero,one,many};
the verb names the relation between the tables.
-
The clockwise rule:
imagine a clock-center on the middle of the line,
and have the hand sweep from the first table to the second (as per the above mad-lib).
That's the side to label with your verb.
- Make it an active verb! (as possible).
Note that in the other direction, it's usually easy to come up with
a passive verb, but if you can come up with another active verb,
great — include both on the ERD!
- A table with one column? (In a programming language, you'd use an enumerated-types.)
Foreign key.
home—lects—exams—hws
D2L—breeze (snow day)
radford.edu