Normalization Converting ER to Tables

1
Normalization / Converting E-R to Tables
IS8080 Dr. Mario Guimaraes
Class Will Start Momentarily
2
Normalization

• Normalization may be used as an alternative or
• as 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.

3
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 -gtgt Degree
• Employee -gtgt Dependents
• Dont combine both tables.

4
Convert E-R to Tables

• 1-N
• N-N
• 1-1
• 1-1 Recursive
• 1-N Recursive
• Super-Type Sub-Types

5
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

6
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.

7
Remove Binary Relationship
8
Remove Recursive
9
Remove Complex Relationships
10
Remove Multi-valued Attributes
11
1 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
12
11 binary

• (4) 11 binary relationship types
• (a) mandatory participation on both sides of 11
  relationship Combine both entities into one
  table
• (b) mandatory participation on one side of 11
  relationship FK on side that is optional.
• Example Nurse (1,1) is in charge of (0,1)
  CareCenter
• FK nurseid must be added to CareCenter
• (c) optional participation on both sides of 11
  relationship. Add FK on either side or 3rd table

13
Superclass/Subclass

• Note
• 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
  clients specific performance needs and what type
  of queries and updates occur more.

14
Summary
15
Also Draw E-R (Emp-Proj-Man)
16
Stolen Car Database
17
Is the table in 3NF ?
18
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.

19
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-type
• and one for each sub-type is usually the
  default

20
Choose best solution
21
(No Transcript)
22
End of Lecture

• End
• Of
• Todays
• Lecture.