Chapter 6:
Logical database design
and the relational model
Objectives of logical design...
Translate the conceptual design into a logical
database design that can be implemented on a
chosen DBMS
• Input: conceptual model (ERD)
• Output: relational schema, normalized relations
Resulting database must meet user needs for:
• Data sharing
• Ease of access
• Flexibility
Relational database components
Data structure
• Data organized into tables
Data manipulation
• Add, delete, modify, and retrieve using SQL
Data integrity
• Maintained using business rules
Why do I need to know this?
Mapping conceptual models to relational schema is
straight-forward
CASE tools can perform many of the steps, but..
• Often CASE cannot model complexity of data and
relationship (e.G., Ternary relationships,
supertype/subtypes)
• There are times when legitimate alternates must be
evaluated
• You must be able to perform a quality check on CASE
tool results
Some rules...
Every table has a unique name.
Attributes in tables have unique names.
Every attribute value is atomic.
• Multi-valued and composite attributes?
Every row is unique.
The order of the columns is irrelevant.
The order of the rows is irrelevant.
The key...
Relational modeling uses primary keys and
foreign keys to maintain relationships
Primary keys are typically the unique identifier
noted on the conceptual model
Foreign keys are the primary key of another entity
to which an entity has a relationship
Composite keys are primary keys that are made of
more than one attribute
• Weak entities
• Associative entities
Implementing it
Attribute
Instance
Field
Entity
What about relationships?
Constraints
Domain constraints
• Allowable values for an attribute as defined in
the domain
Entity integrity constraints
• No primary key attribute may be null
Operational constraints
• Business rules
Referential integrity constraints
Referential integrity constraint
Maintains consistency among rows of two
entities
• matching of primary and foreign keys
Enforcement options for deleting instances
• Restrict
• Cascade
• Set-to-Null
Transforming the EER diagram
into relations
The steps:
Map regular entities
Map weak entities
Map binary relationships
Map associative entities
Map unary relationships
Map ternary relationships
Map supertype/subtype relationships
Transforming E-R diagrams into
relations
Mapping regular entities to relations
• Composite attributes: use only their
simple, component attributes
• Multi-valued attributes: become a
separate relation with a foreign key taken
from the superior entity
Mapping a composite attribute
Looks like this using relational
schema notation
Transforming E-R diagrams into
relations
Mapping weak entities
• Becomes a separate relation with a
foreign key taken from the superior
entity
Example of mapping a weak entity
Looks like this using relational
schema notation
Transforming E-R diagrams into
relations
Mapping binary relationships
• One-to-many - primary key on the one side
becomes a foreign key on the many side
• Many-to-many - create a new relation (associative
entity) with the primary keys of the two entities as
its primary key
– I like to call these intersection entities to
distinguish them from associative entities
created at the conceptual level
• One-to-one - primary key on the mandatory side
becomes a foreign key on the optional side
Example of mapping a 1:M
relationship
Looks like this using relational
schema notation
Example of mapping an M:M
relationship
Looks like this using relational
schema notation
Mapping a binary 1:1
relationship
Looks like this using relational
schema notation
Transforming E-R diagrams into
relations
Mapping associative entities
• Identifier not assigned
– Default primary key for the association
relation is the primary keys of the two
entities
• Identifier assigned
– It is natural and familiar to end-users
– Default identifier may not be unique
Mapping an associative entity
with an identifier
Looks like this using relational
schema notation
Transforming E-R diagrams into
relations
Mapping unary relationships
• One-to-many - recursive foreign key in the
same relation
• Many-to-many - two relations:
– One for the entity type
– One for an associative relation in which the
primary key has two attributes, both taken
from the primary key of the entity
For example...
Emp_Num
EMPLOYEE
Emp-Name Supervises
Emp_Address
Would look like...
references
Emp_Num Emp_Name Emp_Address Boss_Num
And..
Num_Units
Comp_Num
COMPONENT
Description BOM
Unit_of-Measure
Would look like...
COMPONENT
Comp_Num Desc Unit_of_Measure
BOM
Num-of_Units Comp_Num Subassembly_Num
Transforming E-R diagrams into
relations
Mapping ternary (and n-ary) relationships
• One relation for each entity and one for
the associative entity
Mapping a ternary relationship
Looks like this using relational
schema notation
Transforming E-R diagrams into
relations
Mapping Supertype/subtype relationships
• Create a separate relation for the
supertype and each of the subtypes
• Assign common attributes to supertype
• Assign primary key and unique attributes
to each subtype
• Assign an attribute of the supertype to
act as subtype discriminator
Mapping Supertype/subtype
relationships
Would look like this...
Let’s try a couple….
Well-structured relations
Well-structured relations contain minimal
redundancy and allow insertion,
modification, and deletion without errors or
inconsistencies
Anomalies are errors or inconsistencies
resulting from redundancy
• Insertion anomaly
• Deletion anomaly
• Modification anomaly
Data normalization
Normalization is a formal process for deciding
which attributes should be grouped together in a
relation
• Objective: to validate and improve a logical
design so that it satisfies certain constraints that
avoid unnecessary duplication of data
• Definition: the process of decomposing
relations with anomalies to produce smaller,
well-structured relations
Steps in
normalization
Functional dependencies and
keys
Functional dependency: the value of one attribute
(the determinant) determines the value of another
attribute
• A -> B, for every valid instance of A, that
value of A uniquely determines the value of B
Candidate key: an attribute or combination of
attributes that uniquely identifies an instance
• Uniqueness: each non-key field is functionally
dependent on every candidate key
• Non-redundancy
First normal form
No multi-valued attributes.
Every attribute value is atomic.
Second normal form
1NF and every non-key attribute is fully
functionally dependent on the primary key.
Every non-key attribute must be defined by
the entire key, not by only part of the key.
No partial functional dependencies.
Third normal form
2NF and no transitive dependencies
(functional dependency between non-key
attributes.)
Relation with transitive
dependency
Transitive dependency in SALES
relation
Removing a transitive
dependency
Relations in 3NF
Let’s practice...
Other considerations...
Synonyms: different names, same meaning.
Homonyms: same name, different
meanings.
