28 terms

Logical Design

STUDY
PLAY

Terms in this set (...)

Logical Design : Purpose
-To convert an organization's data model (ERD) into a set of
database structures
-To ensure those relations are well-structured and free of
most anomalies (normalization)
Logical Design : Output
A set of third normal form (3NF) relations
Candidate keys
One or more sets of attributes that can
uniquely identify any record (entity instance) in a relation
Composite key
Keys that consist of multiple attributes
Primary key
Candidate key that is chosen as identifiier
Alternate key
Candidate keys that are not chosen to be
the primary key
Converting a Single Entity
- Entity becomes a table
- Attributes become fields of the table
- Primary key is underlined
Ex: SALESPERSON(SP_Number, SP_Name, Commission, Year_of_Hire)
Converting (1:1) Binary Relationship
Copy primary key of "mandatory/parent" entity to
"optional/child" entity
Converting (1:N) Binary Relationship
Primary key on the "one" side becomes foreign key on the
"many" side
Converting (M:N) Binary Relationship
Create intersection table (associative entity) with a
composite primary key consisting of PKs of both parent
entities
Converting (1:1) Unary Relationship
Same as binary - Add primary key as a foreign key in the
SAME table. Create a unique name for FK
Converting (1:N) Unary Relationship
Add primary key as a foreign key in the SAME table
Converting (M:N) Unary Relationship
Create an intersection table with a composite primary key, PK of original relation used for each part of composite PK
Converting Ternary Relationship
Ternary (n-ary) relationships map to FOUR (n+1) tables, One table for each original entity, One table for the associative entity
Well-Structured Tables Definition
A table that contains minimal data redundancy and allows
users to insert, modify, and delete rows in the table without
errors or inconsistencies
Three anomalies to avoid in a well-structured table
-Insertion anomalies
-Update anomalies
-Deletion anomalies
Insertion anomalies
Occurs when we cannot store a value
for one field because the value of another field is unknown.
Update anomalies
Occurs when we have to change
multiple records in a table to update a single value of a
field.
Deletion anomalies
Occurs when deleting the value for
one field unexpectedly also removes the value for another
field.
Normalization
The process of creating well-structured relations that
-Are relatively free from insertion, update, and delete
anomalies
-Have minimum data redundancy
First Normal Form (1NF)
-Criterion 1: Each field value is atomic (no field is multi-valued)
-Criterion 2: There are no repeating groups (Modify PK to eliminate multi-valued fields in any
cell)
Functional Dependencies
Value of one particular field is associated with a single,
specific value of another field
Partial dependency
some non-key field(s) is determined by
part of the PK and not the entire PK
Full functional dependency
every non-key field is
determined by the entire PK (directly or indirectly)
Second Normal Form (2NF)
-Criteria: 1NF + no partial dependency (Solution: Decompose 1NF table into two tables)
Transitive dependency
functional dependencies between
non-key attributes
Third Normal Form (3NF)
-Criteria: 2NF + no transitive dependencies (Solution: Decompose 2NF table into multiple tables)
Denormalization: Why?
Normalization may lead to too many tables, costly to join