Relational Databases: Accounting Applications Introduction to Normalization

1
Relational DatabasesAccounting
ApplicationsIntroduction to Normalization
2
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.

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

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

5
Types of Database Relationships

• One-to-one (Customer ID ---gt Customer Name)
• One-to-many (Customer ---gt Invoice)
• Many-to-many (Invoice ltlt---gtgt Products)

6
Example Invoice
7
Prior to Normalization
8
First Normal Form (1NF)

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

9
1NF Violation (Table 1)

• INV
• INV
• DATE
• CUST
• CNAME
• ADDR.
• 11 CAT
• DESC
• 
  QTY
• 
  PRICE

1M
10
Table 1(a) Repeating Groups Removed
11
Table 2. Repeating Group File
12
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)

13
2NF Violation (Table 2)

• INV CAT
• DESC
• 
  QTY
• 
  
• 
  PRICE

14
Table 3. (For fields dependent only on CAT)
15
Table 2(a) Partial Key Dependencies Removed
16
Table 1(a). Revisited (Check 3NF)
17
Third Normal Form

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

18
Alternative Definition of Third Normal Form

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

19
3NF Violation (Table 1(a))

• INV
• DATE
• CUST
• 
  CNAME
• 
  ADDR.

20
Table 4. Separate File for Customer Data
21
Table 1(b). Non-primary Key Dependencies Removed
22
The Database in 3NF
23
The Database in 3NF
24
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

25
Practical Steps for getting tables into 3NF

• 1 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