Lesson 5: Relational Mapping and Transformation

Relational Mapping

The process of transforming a conceptual ERD into relational tables.

Conceptual Design

Entities, attributes, relationship. No product in mind

Logical Design

The relational schema: tables, keys, constraints

Physical Design

Storage, indexes, and tuning

Implementation

Executable DDL on a running server

Strong entity type

One relation, keyed on its own identifier

Weak entity type

One relational, keyed on the owner’s key plus the partial key

Binary 1:1

No new relational, a foreign key, made unique

Binary 1:M

No new relation. A foreign key on the many side

Binary M:N

A new relational keyed on both foreign keys

Multivalued attributes

A new relation keyed on the owner plus the value

Degree three or more

A new relation with one foreign key per entity

Recursive relationship

Same table at borh ends

Recursive 1:M

A foreign key on the same tavle, at its own primary key

Recursive M:N

A new relation with two foreign key to the same table

Supertype and subtypes

One relation for the supertype, one per subtype

Cost: Every subtype query needs a join

Subtypes only

One relation per subtype, none for the supertype

Problem: Cannot record a supertype instance in no subtype

Single Relation, One Type Column

One relation with a discriminator column

Problem: Many NULLs, and no NULL rule can be enforced

Single relation, boolean columns

One relation with a flag per subtype

Problem: Many NULLs, and flags may disagree with the data

Surrogate key

A key that provides stability and simplicity but loses meaning and can allow duplicates if the natural key is not preserved

Stability

The key never changes, whatever happens to the data

Simplicity

One short column instead of a three-column composite

Meaning

The key says nothing, so it cannot be checked by eye

Duplicates

Two rows may differ only by the surrogate, and both look valid

Logical schema document

Standard notation with the primary key underlined and foreign keys marked, plus a data dictionary

Data Dictionary

A description containing each attribute's type, null status, and meaning

DDL

Executable database statements used to implement the derived tables

Dependency order

A table may only be created after the tables it points at

Constraint naming

Name every constraint, such as pk_student and fk_section_course

Referential action

The action defined for what happens to related records when referenced records are changed or deleted. The lecture states that silence means RESTRICT

Forward Engineering

The process where Workbench turns an EER model into a CREATE TABLE script