CIS 520 Relations and Nomalization Friday, February 15, 2002

1
CIS 520Relations and NomalizationFriday,
February 15, 2002
2
The Relational Model defined by Codd and Date is
the accepted standard for databases.
3
The model is based on relations a two -
dimensional table consisting of rows and columns.
4
The rows are called tuples, rows, records. Row or
record being the norm. All rows contain the
exact same fields - in the same order. No two
rows can contain identical data.
5
The columns are called attributes, columns,
fields.

• Column or field being the norm.Columns are
  atomic , that is, a field contains one value per
  row and all values are of the same data type.The
  order of the columns or the rows is
  insignificant.

6
A relation is expressed in the following
formRELATIONNAME(attributes, ....., .....) 
7
Functional Dependency or Independence is the
relationship between the attributes.

• An attribute is said to functional dependent if
  it can be derived or is determined by another
  attribute.Total Price QuantityTotal is
  functionally dependent on Price and Quantity.

8
An attribute Ais a Determinant of another
attribute B if knowledge of A is necessary to
determine the value of B. This is expressedA
à B
9
The dependency algebra

• if A-gt B, then (A,C) -gt B augmentation.if A -gt B
  B -gt C, then A -gt C transitivityif A -gt B
  (B,C) -gt D, then (A,C) -gt D pseudo transitivityA
  -gt A identityif A -gt B A -gt C, then A -gt
  (B,C) unionif A -gt B, then A -gt C iff B is a
  subset of C

10
A Key is an attribute or field one or more that
uniquely identifies a row in the relation.

• Unique identifierKey à all attributes in the
  relationKey can not be NULL

11
Normalization is the process of removing
anomalies.

• deletioninsertionmodification

12
Any table or relation is said to be in that
normal form if it does not violate the rules of
that form and the rules of the prior forms. 
13
First Normal Form 1NF - the relation has no
multi value fields.
14
 
OWNER ID O_Id Car VIN 1.1 Make 1.1
0.1 ----- -----
OWNER(O_Id, VIN, Make, , , ) OWNER(O_Id,
, , ) CAR (O_Id, VIN, Make, , , )
15
Second Normal Form 2NF - Every non-key
attribute is dependent or determined by the
relation key.
16
 
EMPLOYEE(EMPID, Name, Dept, SupV, Salary, Course
, Date, Grade)
EMPLOYEE(EMPID, Name, Dept, SupV, Salary
) EMPCRSE(EMPID, Course, Date, Grade )
Also, solves a 1NF problem, maybe.
17
Third Normal Form 3NF - There exist no
transitive dependencies, that is, no attribute is
the determinant of another non-key
attribute.This is usually good enough.
18
 
EMPLOYEE(EMPID, Name, Dept, SupV, Salary
) EMPCRSE(EMPID, Course, Date, Grade )
EMPLOYEE(EMPID, Name, Dept, Salary ) DEPT(Dept,
SupV) EMPCRSE(EMPID, Course, Date, Grade )
19
Boyce - Codd Normal Form BCNF - All
determinants can be candidate keys.
20
 
A Table That Is in 3NF But Not in BCNF
21
Decomposition into BCNF
22
Fourth Normal Form 4NF - In BCNF and free from
multi value dependencies.
23
  
24
  
Now Add 100, Skiing it must be added twice...
25
SMAJOR( SID, Major ) SACTIVE( SID, Activity )
26
Fifth Normal Form 5NF - never discussed by
normal people.  
27
Domain Key Normal Form DKNF - Done when
constraints dictate.One Relation asserts One
Theme. 
28
It can be over done and some breaking of rules
may not hurt. Optimization.
29
Lets Look at

• 5.19
• 5.20