09-23-2008.ppt
TRANSCRIPT
CS 8630 Database Administration, Dr. Guimaraes
09-23-2008, TuesdayNormalization / Converting E-R to Tables
ClassWill
Start Momentarily…
CS8630 Database AdministrationDr. Mario Guimaraes
CS 8630 Database Administration, Dr. Guimaraes
Normalization
• Normalization may be used as an alternative oras a supplement to E-R Diagrams.
All non-key attributes of a table must be dependent on THE KEY, THE WHOLE KEY, and NOTHING BUT THE KEY.
Normalization: helps maintenance (update, insert , delete).Not intended to speed up queries.
CS 8630 Database Administration, Dr. Guimaraes
4NF
• Dependency between attributes (for example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, set of values for B and C are independent of each other.
– Example Employee ->> Degree Employee ->> Dependents
Don’t combine both tables.
CS 8630 Database Administration, Dr. Guimaraes
Convert E-R to Tables
• 1-N
• N-N
• 1-1
• 1-1 Recursive
• 1-N Recursive
• Super-Type & Sub-Types
CS 8630 Database Administration, Dr. Guimaraes
Methodology – Logical DD
• Build and validate local logical data model for each view – Remove features not compatible with the
relational model (optional step)– Derive tables for local logical data model– Validate tables using normalization– Validate tables against user transactions– Define integrity constraints– Review local logical data model with user
• Build and validate global logical data model– Merge local logical data models into global
model– Validate global logical data model– Check for future growth– Review global logical data model with users
CS 8630 Database Administration, Dr. Guimaraes
Build & Validate
Remove features not compatible with the relational model (optional step)
• To refine the local conceptual data model to remove features that are not compatible with the relational model. This involves:
– remove *:* binary relationship types; – remove *:* recursive relationship
types; – remove complex relationship types; – remove multi-valued attributes.
CS 8630 Database Administration, Dr. Guimaraes
Remove *:* Binary Relationship
CS 8630 Database Administration, Dr. Guimaraes
Remove *:* Recursive
CS 8630 Database Administration, Dr. Guimaraes
Remove Complex Relationships
CS 8630 Database Administration, Dr. Guimaraes
Remove Multi-valued Attributes
CS 8630 Database Administration, Dr. Guimaraes1:* binary
• (3) 1:* binary relationship types– Entity on ‘one side’ is designated the parent entity
and entity on ‘many side’ is the child entity.– Post copy of the primary key attribute(s) of parent
entity into relation representing child entity, to act as a foreign key.
– May create an association table (not common) to avoid nulls
1:* unary: Add a column – FK (default) or association table
CS 8630 Database Administration, Dr. Guimaraes
1:1 binary
(4) 1:1 binary relationship types
– (a) mandatory participation on both sides of 1:1 relationship; Combine both entities into one table
– (b) mandatory participation on one side of 1:1 relationship; FK on side that is optional.
Example: Nurse (1,1) is in charge of (0,1) CareCenterFK nurseid must be added to CareCenter
– (c) optional participation on both sides of 1:1 relationship. Add FK on either side or 3rd table
CS 8630 Database Administration, Dr. Guimaraes
Superclass/Subclass
Note: 1) Mandatory = Total or Complete. Optional: Partial or Incomplete.
Nondisjoint = overlapping 2) The solution (Relations required) is not necessarily the best. It will
also depend on client’s specific performance needs and what type of queries and updates occur more.
CS 8630 Database Administration, Dr. Guimaraes
Summary
CS 8630 Database Administration, Dr. Guimaraes
Also Draw E-R (Emp-Proj-Man)
CS 8630 Database Administration, Dr. Guimaraes
Stolen Car Database
CS 8630 Database Administration, Dr. Guimaraes
Is the table in 3NF ?
CS 8630 Database Administration, Dr. Guimaraes
DATA MODELS
• Conceptual Model – data as viewed by client• Relational Model – data that is associated to
a Relational Database (includes FK).• Physical Model – data associated with a
specific hardware/software configuration. Associated with a specific DBMS.
CS 8630 Database Administration, Dr. Guimaraes
Convert E-R to Tables
• 1-N - add FK on child side (default)
• N-N - create associate table (3rd table)
• 1-1 - 3 possibilities. Look at minimum cardinality for the best solution
• 1-1 Recursive 2 possib. Look at min. card.
• 1-N Recursive 2 possib. Look at min. card.
• N-N Recursive – create associate table (2nd table)
• Super-Type & Sub-Types – one table for super-typeand one for each sub-type is usually the
default
CS 8630 Database Administration, Dr. Guimaraes
Choose best solution
CS 8630 Database Administration, Dr. Guimaraes
CS 8630 Database Administration, Dr. Guimaraes
End of Lecture
End Of
Today’s
Lecture.