Transcript Avoiding Database Anomalies Unnormalized Relation Invoice Transaction Service # Date Type # 1/5/2003 Customer Name Bookkeeping 6 Stinson & Assoc. Partnership Personal Personal Bookkeeping Partnership Bookkeeping 6208 402525 1,2501601,750136257102 Stinson & Assoc. Jack Randall Judy and June Warren Cleaning Warren Cleaning Bernie Contrell Personal Tax Planning Bookkeeping Personal 56 7540 375 3,775102154 Bernie.

Avoiding Database Anomalies
Unnormalized Relation
Invoice Transaction Service
#
Date
Type #
101
1/5/2003
Customer
Name
1
Bookkeeping 6
25
150
251
Stinson & Assoc.
Partnership
Personal
Personal
Bookkeeping
Partnership
Bookkeeping
25
6
4
20
35
8
50
40
40
25
50
25
1,250
240
160
500
1,750
200
251
136
200
257
257
102
Stinson & Assoc.
Jack Randall
Judy and June
Warren Cleaning
Warren Cleaning
Bernie Contrell
Personal
Tax Planning
Bookkeeping
Personal
16
5
151
6
40
75
25
40
640
375
3,775
240
102
102
385
154
Bernie Contrell
Bernie Contrell
Kirk Company
Amy Holt
102
103
104
1/28/2003
2/10/2003
2/18/2003
105
2/25/2003
3
4
4
1
3
1
3/5/2003
3/9/2003
4
5
1
4
106
107
REVENUE TRANSACTIONS
Service
Hourly
Customer
Name Hours Rate Amount
#
3a. First Normal Form (1NF)
A relation is in first normal form if it does not
contain repeating groups.
Unnormalized Relation
Invoice Transaction Service
#
Date
Type #
101
1/5/2003
Customer
Name
1
Bookkeeping 6
25
150
251
Stinson & Assoc.
Partnership
Personal
Personal
Bookkeeping
Partnership
Bookkeeping
25
6
4
20
35
8
50
40
40
25
50
25
1,250
240
160
500
1,750
200
251
136
200
257
257
102
Stinson & Assoc.
Jack Randall
Judy and June
Warren Cleaning
Warren Cleaning
Bernie Contrell
Personal
Tax Planning
Bookkeeping
Personal
16
5
151
6
40
75
25
40
640
375
3,775
240
102
102
385
154
Bernie Contrell
Bernie Contrell
Kirk Company
Amy Holt
102
103
104
1/28/2003
2/10/2003
2/18/2003
105
2/25/2003
3
4
4
1
3
1
3/5/2003
3/9/2003
4
5
1
4
106
107
REVENUE TRANSACTIONS
Service
Hourly
Customer
Name Hours Rate Amount
#
First Normal Form (1NF)
Changes to service names
have to be made
on each line on which the
the name appears.
Invoice
#
101
101
102
103
104
104
105
105
105
106
107
Transaction Service
Date
Type #
1/5/2003
REVENUE TRANSACTIONS
Service
Hourly
Customer
Name
Hours Rate Amount
#
Customer
Name
1
Bookkeeping
6
25
150
251
Stinson & Assoc.
2/25/2003
3
4
4
1
3
1
Partnership
Personal
Personal
Bookkeeping
Partnership
Bookkeeping
25
6
4
20
35
8
50
40
40
25
50
25
1,250
240
160
500
1,750
200
251
136
200
257
257
102
Stinson & Assoc.
Jack Randall
Judy and June
Warren Cleaning
Warren Cleaning
Bernie Contrell
3/5/2003
3/9/2003
4
5
1
4
Personal
16
Tax Planning 5
Bookkeeping 151
Personal
6
40
75
25
40
640
375
3,775
240
102
102
385
154
Bernie Contrell
Bernie Contrell
Kirk Company
Amy Holt
1/28/2003
2/10/2003
2/18/2003
First Normal Form (1NF)
Inconsistent data could result
when the same service name
has several name
variations.
Invoice
#
101
101
102
103
104
104
105
105
105
106
107
Transaction Service
Date
Type #
1/5/2003
REVENUE TRANSACTIONS
Service
Hourly
Customer
Name
Hours Rate Amount
#
Customer
Name
1
Bookkeeping
6
25
150
251
Stinson & Assoc.
2/25/2003
3
4
4
1
3
1
Partnership
Personal
Personal
Bookkeeping
Partnership
Bookkeeping
25
6
4
20
35
8
50
40
40
25
50
25
1,250
240
160
500
1,750
200
251
136
200
257
257
102
Stinson & Assoc.
Jack Randall
Judy and June
Warren Cleaning
Warren Cleaning
Bernie Contrell
3/5/2003
3/9/2003
4
5
1
4
Personal
16
Tax Planning 5
Bookkeeping 151
Personal
6
40
75
25
40
640
375
3,775
240
102
102
385
154
Bernie Contrell
Bernie Contrell
Kirk Company
Amy Holt
1/28/2003
2/10/2003
2/18/2003
First Normal Form (1NF)
New services
can not be added unless there
is an existing transaction.
Invoice
#
101
101
102
103
104
104
105
105
105
106
107
Transaction Service
Date
Type #
1/5/2003
REVENUE TRANSACTIONS
Service
Hourly
Customer
Name
Hours Rate Amount
#
Customer
Name
1
Bookkeeping
6
25
150
251
Stinson & Assoc.
2/25/2003
3
4
4
1
3
1
Partnership
Personal
Personal
Bookkeeping
Partnership
Bookkeeping
25
6
4
20
35
8
50
40
40
25
50
25
1,250
240
160
500
1,750
200
251
136
200
257
257
102
Stinson & Assoc.
Jack Randall
Judy and June
Warren Cleaning
Warren Cleaning
Bernie Contrell
3/5/2003
3/9/2003
4
5
1
4
Personal
16
Tax Planning 5
Bookkeeping 151
Personal
6
40
75
25
40
640
375
3,775
240
102
102
385
154
Bernie Contrell
Bernie Contrell
Kirk Company
Amy Holt
1/28/2003
2/10/2003
2/18/2003
First Normal Form (1NF)
Deletion of an invoice could
result in a loss of
service information.
Invoice
#
101
101
102
103
104
104
105
105
105
106
107
Transaction Service
Date
Type #
1/5/2003
REVENUE TRANSACTIONS
Service
Hourly
Customer
Name
Hours Rate Amount
#
Customer
Name
1
Bookkeeping
6
25
150
251
Stinson & Assoc.
2/25/2003
3
4
4
1
3
1
Partnership
Personal
Personal
Bookkeeping
Partnership
Bookkeeping
25
6
4
20
35
8
50
40
40
25
50
25
1,250
240
160
500
1,750
200
251
136
200
257
257
102
Stinson & Assoc.
Jack Randall
Judy and June
Warren Cleaning
Warren Cleaning
Bernie Contrell
3/5/2003
3/9/2003
4
5
1
4
Personal
16
Tax Planning 5
Bookkeeping 151
Personal
6
40
75
25
40
640
375
3,775
240
102
102
385
154
Bernie Contrell
Bernie Contrell
Kirk Company
Amy Holt
1/28/2003
2/10/2003
2/18/2003
Second Normal Form (2NF)
A relation is in second normal form if it is in
first normal form and there are no partial
dependencies. A partial dependency occurs
when a table has a composite primary key
and a non-key attribute is dependent on only
part of the primary key.
REVENUE TRANSACTIONS
Invoice Transaction Service Service
Hourly
Customer
#
Date Type # Name Hours Rate Amount
#
Customer
Name
Relations in Second Normal Form (2NF)
SERVICES
Service
Service
Type #
Name
Rate
1
Bookkeeping 25
2
Bus. Consult. 60
3
Partnership
50
4
Personal
40
5
Tax Planning 75
Invoice
#
101
102
103
104
105
106
107
Changes to customer names
have to be made on each line on
which the customer appears.
REVENUE TRANSACTIONS
Transaction Customer
Customer
Date
#
Name
1/5/2003
251
Stinson & Assoc.
1/28/2003
136
Jack Randall
2/10/2003
200
Judy and June
2/18/2003
257
Warren Cleaning
2/25/2003
102
Bernie Contrell
3/5/2003
385
Kirk Company
3/9/2003
154
Amy Holt
REVENUE TRANSACTION LINES
Invoice
Service
#
Type #
Hours
101
1
6
101
3
25
102
4
6
103
4
4
104
1
20
104
3
35
105
1
8
105
4
16
105
5
5
106
1
151
107
4
6
Relations in Second Normal Form (2NF)
SERVICES
Service
Service
Type #
Name
Rate
1
Bookkeeping 25
2
Bus. Consult. 60
3
Partnership
50
4
Personal
40
5
Tax Planning 75
Invoice
#
101
102
103
104
105
106
107
Inconsistent data could result
when the same customer has
several name variations.
REVENUE TRANSACTIONS
Transaction Customer
Customer
Date
#
Name
1/5/2003
251
Stinson & Assoc.
1/28/2003
136
Jack Randall
2/10/2003
200
Judy and June
2/18/2003
257
Warren Cleaning
2/25/2003
102
Bernie Contrell
3/5/2003
385
Kirk Company
3/9/2003
154
Amy Holt
REVENUE TRANSACTION LINES
Invoice
Service
#
Type #
Hours
101
1
6
101
3
25
102
4
6
103
4
4
104
1
20
104
3
35
105
1
8
105
4
16
105
5
5
106
1
151
107
4
6
Relations in Second Normal Form (2NF)
SERVICES
Service
Service
Type #
Name
Rate
1
Bookkeeping 25
2
Bus. Consult. 60
3
Partnership
50
4
Personal
40
5
Tax Planning 75
Invoice
#
101
102
103
104
105
106
107
New customers can not be added
unless there is an existing
transaction for the customer.
REVENUE TRANSACTIONS
Transaction Customer
Customer
Date
#
Name
1/5/2003
251
Stinson & Assoc.
1/28/2003
136
Jack Randall
2/10/2003
200
Judy and June
2/18/2003
257
Warren Cleaning
2/25/2003
102
Bernie Contrell
3/5/2003
385
Kirk Company
3/9/2003
154
Amy Holt
REVENUE TRANSACTION LINES
Invoice
Service
#
Type #
Hours
101
1
6
101
3
25
102
4
6
103
4
4
104
1
20
104
3
35
105
1
8
105
4
16
105
5
5
106
1
151
107
4
6
Relations in Second Normal Form (2NF)
SERVICES
Service
Service
Type #
Name
Rate
1
Bookkeeping 25
2
Bus. Consult. 60
3
Partnership
50
4
Personal
40
5
Tax Planning 75
Invoice
#
101
102
103
104
105
106
107
Deletion of an invoice could
result in the loss of customer
information.
REVENUE TRANSACTIONS
Transaction Customer
Customer
Date
#
Name
1/5/2003
251
Stinson & Assoc.
1/28/2003
136
Jack Randall
2/10/2003
200
Judy and June
2/18/2003
257
Warren Cleaning
2/25/2003
102
Bernie Contrell
3/5/2003
385
Kirk Company
3/9/2003
154
Amy Holt
REVENUE TRANSACTION LINES
Invoice
Service
#
Type #
Hours
101
1
6
101
3
25
102
4
6
103
4
4
104
1
20
104
3
35
105
1
8
105
4
16
105
5
5
106
1
151
107
4
6
Third Normal Form (3NF)
A relation is in third normal form if it is in
second normal form and there are no
transitive dependencies. A transitive
dependency occurs when a table has a nonkey attribute that is dependent on another
non-key attribute.
Invoice
#
REVENUE TRANSACTIONS
Transaction Customer
Customer
Date
#
Name
Relations in Third Normal Form (3NF)
SERVICES
Service
Service
Type #
Name
Rate
1
Bookkeeping 25
2
Bus. Consult. 60
3
Partnership
50
4
Personal
40
5
Tax Planning 75
REVENUE TRANSACTIONS
Invoice Transaction Customer
#
Date
#
101
1/5/2003
251
102
1/28/2003
136
103
2/10/2003
200
104
2/18/2003
257
105
2/25/2003
102
106
3/5/2003
385
107
3/9/2003
154
Customers Accounts
Customer
Customer
#
Name
102
Bernie Contrell
136
Jack Randall
154
Amy Holt
200
Judy and June
251
Stinson & Assoc.
257
Warren Cleaning
385
Kirk Company
REVENUE TRANSACTION LINES
Invoice
Service
#
Type #
Hours
101
1
6
101
3
25
102
4
6
103
4
4
104
1
20
104
3
35
105
1
8
105
4
16
105
5
5
106
1
151
107
4
6
Table Relationships
1-M Relationship
CUSTOMER ACCOUNTS
Customer #
Customer Name
REVENUE TRANSACTIONS
Invoice #
Transaction
Customer #
Date
Foreign Key
M-N Relationship
REVENUE TRANSACTIONS
SERVICES
Hourly
Service Type # Service Name
Rate
1
M
Invoice #
Transaction
Date
Bridge Table
REVENUE TRANSACTION LINES
Invoice #
Service Type #
Hours
Customer #
1
M
Success!