Geog 495: GIS Database Design

10/24/05

---

Normalization

---

What is normalization?

Breaking down a table into functionally independent tables

Follows three-step procedures {first normal form, second NF, third NF}

Why normalization?

Reduce data redundancy, and thus data anomalies

---

Illustration: from all-in-one table (report format) to normalized tables

Conversion to First Normal Form

(1) Eliminate repeating groups: each row should define a single entity

(2) Identify the Primary Key: PK must uniquely identify any attribute value

All attributes are dependent on PROJ_NUM + EMP_NUM

(3) Identify all dependencies

Draw Dependency Diagram

Partial dependency: attributes are dependent on only a part of a composite PK

Transitive dependency: nonkey (nonprime) attributes are dependent on another nonkey attribute

Conversion to Second Normal Form

(1) Identify all key components

(2) Eliminate partial dependency

Conversion to Third Normal Form

(1) Identify each new determinant

(2) Eliminate transitive dependency

---

First normal form (1NF)

All the key attributes are defined

There are no repeating groups in the table

All attributes are dependent on the primary key

Second normal form (2NF)

It is in 1NF

AND

It includes no partial dependencies

Third normal form (3NF)

It is in 2NF

AND

It includes no transitive dependencies

---

Boyce-Codd normal form (BCNF)

The table is in 3NF

AND

It contains a nonkey attribute that determines a key attribute

Fourth normal form (4NF)

It is in 3NF

AND

Has no multiple sets of multivalued dependencies

---

Lab

Using the INVOICE table structure shown below, draw its dependency diagram and identify all dependencies (including all partial and transitive dependencies). You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.)

Attribute Name	Sample Value	Sample Value	Sample Value	Sample Value	Sample Value
INV_NUM	211347	211347	211347	211348	211349
PROD_NUM	AA-E3422QW	QD-300932X	RU-995748G	AA-E3422QW	GH-778345P
SALE_DATE	15-Jan-2004	15-Jan-2004	15-Jan-2004	15-Jan-2004	16-Jan-2004
PROD_LABEL	Rotary sander	0.25-in. drill bit	Band saw	Rotary sander	Power drill
VEND_CODE	211	211	309	211	157
VEND_NAME	NeverFail, Inc.	NeverFail, Inc.	BeGood, Inc.	NeverFail, Inc.	ToughGo, Inc.
QUANT_SOLD	1	8	1	2	1
PROD_PRICE	$49.95	$3.45	$39.99	$49.95	$87.75

Draw Crow’s foot model based on normalized tables