Transcript BACS 485 - University of Northern Colorado

BACS 485
Normalization
Data Anomalies
WORKER (WORKER-ID, NAME, SKILL-TYPE, SUPV-ID, BLD-ID)
WORKER-ID
NAME
SKILL-TYPE
SUPV-ID
BLD-ID
1235
M. Faraday
Electric
1311
312
1235
M. Faraday
Electric
1311
515
1412
C. Nemo
Plumbing
--
312
1412
C. Nemo
Plumbing
--
460
1412
C. Nemo
Plumbing
--
435
1412
C. Nemo
Plumbing
--
515
1311
C. Coulomb
Electric
--
435
Normalization Process
DK/
NF
5NF
4NF
BC/NF
3NF
2NF
1NF
0NF
0NF
ORDERS
(ORDER-NUM, ORDER-DATE, {PART-NUM, PART-DESC, QTY-ORD, PRICE})
ORDERNUM
ORDERDATE
PART-NUM
PART-DESC
QTYORD
PRICE
12489
90295
AX12
Washer
11
699
12491
90295
BT04
AX12
Dryer
Washer
1
1
450
699
12494
90295
CB03
Sink
4
150
12495
90495
CX11
Mixer
2
95
12498
90595
AZ52
BA74
Skates
Basketball
2
1
169
49
1NF
ORDERS
(ORDER-NUM, ORDER-DATE, PART-NUM, PART-DESC, QTY-ORD, PRICE)
ORDERNUM
ORDERDATE
PARTNUM
PART-DESC
QTY-ORD
PRICE
12489
90295
AX12
Washer
11
699
12491
90295
BT04
Dryer
1
450
12491
90295
AX12
Washer
1
699
12494
90295
CB03
Sink
4
150
12495
90495
CX11
Mixer
2
95
12498
90595
AZ52
Skates
2
169
12498
90595
BA74
Basketball
1
49
1NF
ORDERS
(ORDER-NUM, ORDER-DATE, PART-NUM, PART-DESC, QTY-ORD, PRICE)
ORDER-NUM
PART-NUM
ORDER-DATE
PART-DESC
ORDER-NUM, PART-NUM ---> QTY-ORD
ORDER-NUM ---> ORDER-DATE
PART-NUM ---> PART-DESC, PRICE
QTY-ORD
PRICE
ORDERS (ORDER-NUM, ORDER-DATE)
PART (PART-NUM, PART-DESC, PRICE)
ORDER-LINE (ORDER-NUM, PART-NUM, QTY-ORD)
2NF
Part
Orders
ORDER-NUM
ORDER-DATE
PART-NUM
PART-DESC
PRICE
12489
90295
AX12
Washer
699
12491
90295
BT04
Dryer
450
12494
90295
CB03
Sink
150
12495
90495
CX11
Mixer
95
12498
90595
AZ52
Skates
169
BA74
Basketball
49
Order_Line
ORDER-NUM
PART-NUM
QTY-ORD
12489
AX12
11
12491
BT04
1
12491
AX12
1
12494
CB03
4
12495
CX11
2
12498
AZ52
2
12498
BA74
1
3NF Problems
CUSTOMER (CUST-NUM,
SALES-NAME)
CUST-NAME,
CUST-ADDR,
SALES-NUM,
Customer
CUST-NUM
CUST-NAME
CUST-ADDR
SALES-NUM
SALESNAME
124
S. Adams
123 Oak St.
3
M. Jones
256
A. Samuels
456 Elm St.
6
W. Smith
311
C. Don
48 College Ave.
12
S. Brown
315
T. Daniels
519 Cherry St.
6
W. Smith
405
A. Williams
16 Watson Rd.
12
S. Brown
CUST-NUM ---> CUST-NAME, CUST-ADDR, SALES-NUM, SALES-NAME
SALES-NUM ---> SALES-NAME
3NF Problems
CUST-NUM
CUST-NAME
CUST-ADDR
SALES-NUM
SALES-NAME
CUST-NUM ---> CUST-NAME, CUST-ADDR, SALES-NUM, SALES-NAME
SALES-NUM ---> SALES-NAME
3NF
CUSTOMER (CUST-NUM, CUST-NAME, CUST-ADDR, SALES-NUM)
SALES-REP (SALES-NUM, SALES-NAME)
CUST-NUM
CUST-NAME
CUST-ADDR
124
S. Adams
123 Oak St.
3
256
A. Samuels
456 Elm St.
6
311
C. Don
48 College Ave.
12
315
T. Daniels
519 Cherry St.
6
405
A. Williams
16 Watson Rd.
12
SALES-NUM
SALES-NAME
3
M. Jones
6
W. Smith
12
S. Brown
SALES-NUM
BC/NF Problem
FACULTY (NAME, DEPT, OFFICE, RANK, DATE-HIRED)
NAME
DEPT
OFFICE
RANK
Smith
CIS
B-20
Instructor
880101
Jones
MUSIC
M-15
Professor
650401
Henry
MUSIC
M-13
Assist. Professor
861005
Frank
CHEM
C-17
Assoc. Professor
930615
OFFICE ---> DEPT
NAME, DEPT ---> OFFICE, RANK, DATE-HIRED
NAME, OFFICE ---> DEPT, RANK, DATE-HIRED
DATEHIRED
BC/NF Problem
NAME
DEPT
OFFICE
RANK
OFFICE ---> DEPT
NAME, DEPT ---> OFFICE, RANK, DATE-HIRED
NAME, OFFICE ---> DEPT, RANK, DATE-HIRED
DATE-HIRED
BC/NF Solution
FAC-LOC (OFFICE, DEPT)
FACULTY (NAME, OFFICE, RANK, DATE-HIRED)
• The NAME,OFFICE candidate key was chosen as the
primary key because the NAME,DEPT candidate key
would not be in BCNF. In fact, it would not even be in
2NF since there would be a partial dependency between
OFFICE and DEPT.
0NF Relation
FACULTY (FAC-ID, {STU-ID}, {COMMITTEE-CODE})
FAC-ID
STU-ID
COMMITTEE-CODE
123
12805
24139
ADV
PER
HSG
456
37573
24139
36237
CUR
444
57384
HSG
Normalized?
FACULTY (FAC-ID, STU-ID, COMMITTEE-CODE)
FAC-ID
STU-ID
COMMITTEE-CODE
123
12805
ADV
123
24139
ADV
123
12805
PER
123
24139
PER
123
12805
HSG
123
24139
HSG
456
37573
CUR
456
24139
CUR
456
36237
CUR
444
57384
HSG
FAC-NAME --->> STU-ID
FAC-NAME --->> COMMITTEE-CODE
5NF
FAC-STU (FAC-ID, STU-ID)
FAC-COMM (FAC-ID, COMMITTEE-CODE)
FAC-ID
STU-ID
123
12805
123
24139
456
37573
456
24139
456
36237
444
57384
FAC-ID
COMMITTEE-CODE
123
ADV
123
PER
123
HSG
456
CUR
444
HSG
Normalization “Rules”
• 0NF to 1NF – Remove repeating groups
• 1NF to 2NF – Remove partial functional dependencies
• 2NF to 3NF – Remove transitive dependencies
• 3NF to BC/NF – Every determinate is a candidate key
• BC/NF to 4NF – Remove multi-valued dependencies
• 4NF to 5NF – Remove join dependencies
• DK/NF – Every constraint is a consequence of domain
and key constraints