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
manipulation integrity
Data organized into tables
Data
Add, delete, modify, and retrieve using SQL
Data
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.
in tables have unique names.
Attributes Every
attribute value is atomic. row is unique.
Multi-valued and composite attributes?
Every
The
The
order of the columns is irrelevant.
order of the rows is irrelevant.
The key...
Relational Primary Foreign
modeling uses primary keys and foreign keys to maintain relationships keys are typically the unique identifier noted on the conceptual model
keys are the primary key of another entity to which an entity has a relationship
keys are primary keys that are made of more than one attribute Weak entities Associative entities
Composite
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 constraints
No primary key attribute may be null
Operational
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 Map Map Map Map Map Map
regular entities weak entities
binary relationships
associative entities unary relationships ternary relationships 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 Emp_Address Supervises
Would look like...
references
Emp_Num Emp_Name Emp_Address
Boss_Num
And..
Num_Units
Comp_Num COMPONENT Description Unit_of-Measure BOM
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...
Lets 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.
attribute value is atomic.
Every
Second normal form
1NF
and every non-key attribute is fully functionally dependent on the primary key. non-key attribute must be defined by the entire key, not by only part of the key. partial functional dependencies.
Every
No
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
Lets practice...
Other considerations...
Synonyms:
different names, same meaning.
same name, different
Homonyms:
meanings.
