Relational Database Design - University of Michigan



Relational Database DesignStarting from scratch, here’s the general procedure for designing a relational database:Identify the basic “entities” that will be stored in the database. For the actors example from topic B1, the basic entities would be actors and movies, although in a large database you might also have tables for studios, directors, and more.Create a table for each entity, making sure that each table has a valid primary key.Verify that each table obeys the golden rule—every non-key field depends on the key, the whole key, and nothing but the key. If not, it means that you probably need some more tables.Identify the relationships between the tables.For many-to-many relationships, you will have to create correlation tables to link the data.Database design is as much art as science. Designs which seem complete become incomplete when more detail is required. For example, if the only information you need about a movie’s director is her name, you don’t need a Directors’ table. But as soon as you start adding information about the directors, you’ll need to create a separate directors’ table to avoid breaking the golden rule.In addition, databases in which data is actively being added (or deleted/modified) are generally designed differently from historical databases which are not being modified. For historical data, it is common to move AWAY from third normal form, putting most of the data into one or two tables. This makes querying the data easier and faster.ExampleHere’s an example of relational database design.Still-Solvent MotorsThis little-known car company quietly churns out a profit year after year, due mainly to phenomenal database design. Let’s see if we can guess what their dealers’ inventory databases might look like.The main inventory table will have one row for each car on the dealer’s lot (or in the showroom).Still-Solvent offers three car models: The SS1, SS2, and SSSUV. Here are their brochure descriptions, including options available.SS1: A sporty compact car. Seats 4. Available with standard 4-cylinder engine or 6-cylinder fuel-injected engine for more power. Manual or automatic transmission. Options available are air conditioning, power brakes, power steering, sun roof, driver-side airbag.SS2: A great midsize family car. Seats 5. Available only with 6-cylinder fuel-injected engine and automatic transmission. Options available are air conditioning, power brakes, power steering, sun roof, driver-side airbag, side-curtain airbags, rear-seat airbags, IPod/MP3 compatible surround sound stereo system, GPS Navigation system, anti-lock brakes.SSSUV: Most intimidating SUV on the road. Hummers flee in fear. Seats 14. Available with 6-liter 450 HP 10-cylinder engine or 9-liter 600 HP 16-cylinder engine. 100- gallon gas tank so you only need to fuel up every 200 miles (financing available). Optional 4WD. Other options available are air conditioning, power brakes, power steering, sun roof, driver-side airbag, side-curtain airbags, rear-seat airbags. Choice of IPod/MP3 compatible surround sound stereo system or live band (including storage compartment), GPS Navigation system, anti-lock brakes, pedestrian catcher, and top wheels for getting out of those nasty rollovers.The TablesThe base table will be Inventory. The natural primary key for any table of actual cars is the Vehicle Identification Number, or VIN.The one-to-many fields, as described in the brochure, would include model, engine, and transmission. For simplicity, we’ll treat engine and transmission as being fully described by a short text description, so we won’t make tables for them.However, we have important additional information about the models, so we’ll make a separate Models table.Engine and transmission are one-to-many because while each engine type or transmission might be in many cars on the lot, each car has only one of each.The other options, however, can be mixed in any combination in any car. A particular option, like power brakes, will exist in many cars. And a particular car may have many options.Therefore, we will need a correlation table between inventory and options.Tables SummaryInventory VIN: Primary KeyModelName EngineTransmissionModels ModelName: Primary KeyClassSeats?Options OptionID:Primary KeyOptionName OptionDescription InventoryOptions VIN: Primary KeyOptionID: Primary KeyThose tables should be enough to query the inventory to find cars of particular models with particular options. However, if a customer wants to order a car from the factory, we should include another correlation table:ModelOptions ModelName: Primary KeyOptionID: Primary KeyFinal Relationship Diagram: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download