09-23-2008.ppt

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.