Accounting 6500

Relational Databases:Accounting Applications

Introduction to Normalization

Accounting 6500

What is Normalization?

The object of normalization is to arrive at an optimal database structure. This “optimal” structure will minimize redundancy in the database. As a result, the integrity of the information in the database will be protected as data are added, modified, and deleted.

Accounting 6500

Why Study Normalization?

• Effective use of relational database products such as MS Access, SQL Server, Oracle, Sybase, and Informix require knowledge of data structures.

• Relational systems require that data be properly normalized (i.e. redundancy removed).

• Normalization provides a method for trouble-shooting a database design.

Accounting 6500

What We Will Learn Today About Normalization

• How to recognize a poor data structure;• How to determine why the data structure

is incorrect;• How to correct structural errors in the

database.

Accounting 6500

Types of Database Relationships

• One-to-one (Customer ID# ---> Customer Name)

• One-to-many (Customer ---> Invoice)• Many-to-many (Invoice <<--->> Products)

Accounting 6500

Example Invoice

Jenson Auto

Invoice # 101 Date: 01/12/1999

Customer# 1Customer Name AdamsCustomer Address Logan

Items PurchaseDescription Catalog # Quantity Price Extended Price

BMW B1 1 $38,000 $38,000Mercedes M1 1 $45,000 $45,000Porsche P1 1 $30,000 $30,000

Total $113,000

Accounting 6500

Prior to Normalization

INV# DATE CUST# CNAME ADDR. CAT# DESC QTY PRICE101 01/12/99 1 ADAMS LOGAN B1 BMW 1 $38,000.00101 01/12/99 1 ADAMS LOGAN M1 MERCEDES 1 $45,000.00101 01/12/99 1 ADAMS LOGAN P1 PORSCHE 1 $30,000.00102 02/17/99 2 BAKER SLC B1 BMW 2 $38,000.00103 03/04/99 3 COOK OGDEN B1 BMW 1 $38,000.00104 05/13/99 1 ADAMS LOGAN P1 PORSCHE 1 $30,000.00105 06/23/99 2 BAKER SLC M1 MERCEDES 2 $45,000.00106 07/31/99 1 ADAMS LOGAN B1 BMW 1 $38,000.00

Accounting 6500

First Normal Form (1NF)

• There is a one-to-one relationship (correspondence) between the key and the non-key fields.

Accounting 6500

1NF Violation (Table 1)

INV#

INV#

DATE

CUST#

CNAME

ADDR.

1:1 CAT#

DESC

QTY

PRICE

1:M

Accounting 6500

Table 1(a) Repeating Groups Removed

INV# DATE CUST# CNAME ADDR.101 01/12/99 1 ADAMS LOGAN102 02/17/99 2 BAKER SLC103 03/04/99 3 COOK OGDEN104 05/13/99 1 ADAMS LOGAN105 06/23/99 2 BAKER SLC106 07/31/99 1 ADAMS LOGAN

Accounting 6500

Table 2. Repeating Group File

INV# CAT# DESC QTY PRICE101 B1 BMW 1 $38,000.00101 M1 MERCEDES 1 $45,000.00101 P1 PORSCHE 1 $30,000.00102 B1 BMW 2 $38,000.00103 B1 BMW 1 $38,000.00104 P1 PORSCHE 1 $30,000.00105 M1 MERCEDES 2 $45,000.00106 B1 BMW 1 $38,000.00

Accounting 6500

Second Normal Form (2NF)

• The table satisfies 1NF and• All non-key fields are dependent on the

whole key.

(Relevant only for tables with composite keys)

Accounting 6500

2NF Violation (Table 2)

INV# CAT#

DESC

QTY

PRICE

Accounting 6500

Table 3. (For fields dependent only on CAT#)

CAT# DESC PRICEB1 BMW $38,000.00M1 MERCEDES $45,000.00P1 PORSCHE $30,000.00

Accounting 6500

Table 2(a) Partial Key Dependencies Removed

INV# CAT# QTY101 B1 1101 M1 1101 P1 1102 B1 2103 B1 1104 P1 1105 M1 2106 B1 1

Accounting 6500

Table 1(a). Revisited (Check 3NF)

INV# DATE CUST# CNAME ADDR.101 01/12/99 1 ADAMS LOGAN102 02/17/99 2 BAKER SLC103 03/04/99 3 COOK OGDEN104 05/13/99 1 ADAMS LOGAN105 06/23/99 2 BAKER SLC106 07/31/99 1 ADAMS LOGAN

Accounting 6500

Third Normal Form

• The table satisfies 1NF and 2NF and• Non-key fields are dependent only on the

key.

Accounting 6500

Alternative Definition of Third Normal Form

Each non-key field is dependent on the whole key and nothing but the key.

Accounting 6500

3NF Violation (Table 1(a))

INV#

DATE

CUST#

CNAME

ADDR.

Accounting 6500

Table 4. Separate File for Customer Data

CUST# CNAME ADDR.1 ADAMS LOGAN2 BAKER SLC3 COOK OGDEN

Accounting 6500

Table 1(b). Non-primary Key Dependencies Removed

INV# DATE CUST#101 01/12/99 1102 02/17/99 2103 03/04/99 3104 05/13/99 1105 06/23/99 2106 07/31/99 1

Accounting 6500

The Database in 3NF

CUST# CNAME ADDR.1 ADAMS LOGAN2 BAKER SLC3 COOK OGDEN

INV# DATE CUST#101 01/12/99 1102 02/17/99 2103 03/04/99 3104 05/13/99 1105 06/23/99 2106 07/31/99 1

CAT# DESC PRICEB1 BMW $38,000.00M1 MERCEDES $45,000.00P1 PORSCHE $30,000.00

INV# CAT# QTY101 B1 1101 M1 1101 P1 1102 B1 2103 B1 1104 P1 1105 M1 2106 B1 1

Accounting 6500

The Database in 3NF

CUST# CNAME ADDR.1 ADAMS LOGAN2 BAKER SLC3 COOK OGDEN

INV# DATE CUST#101 01/12/99 1102 02/17/99 2103 03/04/99 3104 05/13/99 1105 06/23/99 2106 07/31/99 1

CAT# DESC PRICEB1 BMW $38,000.00M1 MERCEDES $45,000.00P1 PORSCHE $30,000.00

INV# CAT# QTY101 B1 1101 M1 1101 P1 1102 B1 2103 B1 1104 P1 1105 M1 2106 B1 1

Accounting 6500

Normalization in Class Exercise

• Customer#, fname, lname, phone, address,birthday, rental#, rental date, due date, video#, copy #, title, rating, Rental $• assume each video number has multiple

copies and each rental $ is determined by video #

• Remember can rent more than one video at a time

• Put this information into 3rd normal form identifying primary keys and foreign keys

Accounting 6500

Practical Steps for getting tables into 3NF1 Divide original table into tables that have related information

2 Select candidate key for each table

3 Analyze each table and check for

a 1 NF (no repeating groups--no one to many relationships from key to any non-key attribute

b 2 NF (1 NF & each non-key item dependent on whole key--applies to composite key tables only

c 3 NF (2 NF & remove all transitive dependencies--non-key field not determine any other non-key field

4 If have NF violations, fix by dividing out the offending fields into another table--divide and conquer. (May have to iteratively go through steps 2-4

5 Connect tables with appropriate foreign keys