Session Six Initial Database Design

Topics

• Database Design Overview
• Foreign Keys
• Steps in Initial Database Design
• Map Entities
• Map Attributes
• Map UIDs
• Map Relationships
• Choose Arcs
• Choose Subtypes
• Worksheet 5
• Project 4 - Initial Database Design

Database Design Overview

Back to the top

1 Map the E-R Model to relational tables to produce an initial design.
2 Refine the initial design to produce a complete database design.

Database Design Deliverable

The Database Design Stage produces design specifications for a relational database including definitions for relational tables, indexes, views, and storage space.

Back to the top

The rule behind Foreign Keys

"The Children get the Presents."

Back to the top

---

Steps in Initial Database Design

1.  Map Entities - Map the simple entities to tables.
2.  Map Attributes - Map attributes to columns and document sample data.
3.  Map UIDs - Map unique identifiers to primary keys.
4.  Map Relationships - Map relationships to foreign keys.
5.  Map Arcs - Choose arc options.
6.  Map Subtypes - Choose subtype options.

Back to the top

• Map each simple entity to one table.
• Create a Table Instance Chart for the new table.

Back to the top

• Mandatory Attributes - Use NN for a column that must be defined NOT NULL.
• Optional Attribute - Leave Nulls / Unique item blank.

Back to the top

Special Handout on IDD Steps

The valid Key Types are PK for a Primary Key column, and FK for a Foreign Key column.

Use NN for a column that must be defined NOT NULL.
Use U for a column that must be unique.

Primary keys will by definition be NN and U.

If multiple columns must be unique in combination, label them with a suffix, for example U1.
Label a single column PK as NN, U
Label a multiple column PK as NN, U1

Use suffixes to distinguish between multiple FK columns in a single table.  For example if an entity has three relationships it should have one of them labeled FK1, one labeled FK2 and the last one labeled FK3.

If an entity's UID includes a relationship( the UID Bar), add foreign key columns to the table and mark them as part of the primary key.

Back to the top

Back to the top

Arcs represent a kind of multiple alternative foreign key. Choose between two alternative designs for mapping arcs to foreign keys.

Alternative Designs

• Explicit Arc Design - creates a foreign key column for each relationship included in the arc

• Generic Arc Design - creates a single foreign key column and one relationship flag column for the arc. Since the relationships are exclusive, only one FK value will exist for each row in the table.

Back to the top

Option 1 - Single Table Subtype Design

• Map the subtypes onto a single table for the supertype.
• The single table will contain instances of all subtypes.
• Use when the subtypes have few subtype-specific attributes and relationships.
• Design Advantages
• Access to the supertype is straightforward.
• The subtypes can be accessed and modified using views.
• Design Disadvantages
• Subtype NOT NULL requirements cannot be enforced at the database level.
• Application logic will have to cater to different sets of attributes, depending on TYPE.

Option 2 - Separate Tables Subtype Design

• Map the subtypes onto separate tables - one for each subtype.
• Each table will contain only instances of that subtype.
• Use when there are many subtype-specific attributes or relationships.
• Design Advantages
• The subtype's attribute optionality is enforced at the database level.
• Application logic does not require checks for subtypes.
• Design Disadvantages
• Access to the supertype requires the UNION operator or a view with the UNION operator.
• Views that join the two tables are display only.
• Application program code must be specific to the individual subtype tables.
• Maintenance of UID's across subtypes is difficult to implement.

Back to the top