On Tue, 8 Nov 2005, Ross Werner wrote:
On Tue, 8 Nov 2005, Levi Pearson wrote:
I'd say the general technique to follow is thus:
Exhaustively catalog the entities that you wish to model and their
relationships to one another
Draw up an ER or UML diagram of the entities and relationships
Convert the diagram to a database schema
Normalize the schema
Convert schema and constraints to SQL
I think that's very excellent advice, but this sort of information (with each
step made explicit with lots of examples, especially of common pitfalls and
difficulties) isn't really what I saw in the database class at BYU. Now I
didn't actually take the class, so I probably have no room
to talk, but I've spoken with several people who *have* taken the class and
this sort of stuff, to the best of my knowledge, wasn't covered in much more
depth than what you've listed here. Would anyone who has actually taken the
class care to chime in and relieve me of my ignorance?
When I was at BYU the class was CS 452 (I think), and it covered ER
modeling, and translating ER models to relations etc. It also covered
normalization very thoroughly. CS 453 went into physical implementation of
databases, covering topics such as B+-tree indexes. Fun stuff. It would be a
sad day indeed if BYU had stopped teaching these fundamentals in a rigorous way.
One question I've been wondering about that maybe the SQL gurus can help me
out with:
Let's say you have a table "foo" that needs to reference the "fruit" table.
So you have a "fruit_id" column in table "foo". Now, each "fruit" can be
either an "orange" or a "banana" but not both. Also, the columns required for
each table are vastly different--an entirely different set for "orange"s than
"banana"s. What's the best way to model this in the database?
Basically in object-oriented terms, "orange" and "banana" both extend the
same abstract class "fruit".
In a perfect world you'd be able to create declarative database-level
constraints that would prohibit something being both an orange and a
banana at the same time. As was mentioned in another post, about the only
thing that comes close to that is a trigger, but that would be a
procedural (and therefore sub-optimal) solution.
Date covers such constraints in "Database In Depth".
-- Dan
/*
PLUG: http://plug.org, #utah on irc.freenode.net
Unsubscribe: http://plug.org/mailman/options/plug
Don't fear the penguin.
*/
