Normalization

1
Normalization

• 1NF, 2NF, 3NF

2
Introduction

• In Kp.88 we have the suppliers and parts database
• S S, SNAME, STATUS, CITY
• P P, PNAME, COLOR, WEIGHT, CITY
• SP S, P, QTY
• What happens if the design is changed in some way
  like
• Suppliers CITY is inserted in SP ? SCP
• See Kp. 408 Fig.11.1

3
Introduction (cont.1)

• In Fig. 11.1(kp.408), the sample value for relvar
  SCP
• There are many redundant information
• S1s city ? London 6 times
• S4s city ? London 3 times
• Whatll be remained if update on S1s city
  happens incorrectly due to the redundancy?
• Some S1s city is London
• Some S1s city is Amsterdam
• ? good design principle
• One fact in one place avoiding redundancy

4
Introduction (cont.2)

• Normalization
• Concerns about how a given relation containing
  certain undesirable properties can be converted
  to a more desirable form
• Normal forms
• If a relation satisfies a certain specified set
  of constraints

5
Normal forms

• Levels of normalization
• Universe of relvars
• Normalized and unnormalized
• 1NF relvars
• Normalized
• 2NF relvars
• 3NF relvars
• BCNF relvars
• 4NF relvars
• 5NF relvars
• See Kp. 411 Fig.11.2
• The normalization procedure is reversible
• Ex) 2NF ? 3NF
• No information is lost

More highly normalized
6
Nonloss Decomposition

• Nonloss decomposition satisfies the following two
  properties
• Breaking down a relvar does not lose information
• Reversibility
• The original relvar is equal to the join of
  decomposed relvars (join of its projections)
• Correct further normalization has to satisfy this
  property!

7
Example of nonloss decomposition

• SS, STATUS, CITY is decomposed into two ways
• SSTS, STATUS, SCS, CITY ? nonloss
• SSTS, STATUS, STCSTATUS. CITY ? lossy

8
FD diagrams

• Pictorial representation of FDs
• Ex) FD diagrams for relvars S, SP, and P

9
First Normal Form

• A relvar is in 1NF
• Iff every tuple contains exactly one value for
  each attribute in every legal value of the relvar
• Ex) also see fig.11.6 (kp.418)
• FIRSTS, STATUS, CITY, P, QTY
• PRIMARY KEY S, P

10
Anomalies in 1NF

• Lets think about anomalies due to the FD S?CITY
• Insert
• We cannot just insert a suppliers city unless
  the supplier must supply at least one part
• Ex) insertion of ltS5, , Athens, , gt ? primary key
  (S, p) value becomes null not allowed

11
Anomalies in 1NF (cont.1)

• Delete
• If we delete a sole tuple for a particular
  supplier, we lose
• not only his shipment
• but also his city.
• Ex) ltS3, 10, Paris, P2, 200gt 4th from the bottom
  in fig.11.6 (kp.418)

12
Anomalies in 1NF (cont.2)

• Update
• If we update the city value for a particular
  supplier, we may have to update many tuples
• Ex) ltS1, Londongt ? ltS1, Amsterdamgt
• We have to update 6 tuples
• May cause inconsistency if we miss updating any
  tuple

13
Solution for the anomalies of 1NF

• Decompose relvar FIRSTS, STATUS, CITY, P, QTY
  into 2 relvars
• SECONDS, STATUS, CITY
• SPS, P, QTY
• See fig. 11.8 (kp. 420)

14
Solution for the anomalies of 1NF(cont.)

• New relvars
• SECONDS, STATUS, CITY
• SPS, P, QTY
• See fig. 11.8 (kp. 420) and check
• Can we insert ltS5, , Athensgt?
• Can we delete ltS3, p2, 200gt without deleting S3s
  information in SECOND?
• Can we update S1s city in one tuple only?

15
Second Normal Form

• A relvar is in 2NF iff
• 1NF and
• Every nonkey attribute is irreducibly dependent
  on the primary key
• Ex)
• FIRSTS, STATUS, CITY, P, QTY
• Not 2NF because of FDs S?CITY, S?STATUS
• S is reduced from the primary key S, P
• SECONDS, STATUS, CITY, SPS, P, QTY
• 2NF

16
Problem of 2NF

• Lack of mutual independence among its nonkey
  attributes
• Ex) in SECONDS, STATUS, CITY, SPS, P, QTY,
  
• we still have an FD CITY?STATUS
• Because S?CITY, CITY?STATUS,
• S?STATUS transitive dependency

17
Anomalies of SECOND

• SECONDS, STATUS, CITY,
• FD S?STATUS, S?CITY, CITY?STATUS
• Insert
• We cannot insert the fact only that a particular
  city has a particular status (CITY? STATUS)
• We can insert the fact only when a supplier is
  actually in the city
• Delete
• If we delete a tuple in SECOND, we may delete the
  STATUS information of the CITY
• Ex) if we delete ltS5, 30, Athensgt in Fig.11.8
  (kp.420), we lose STATUS information of Athens
  also.

18
Anomalies of SECOND (cont.)

• SECONDS, STATUS, CITY,
• FD S?STATUS, S?CITY, CITY?STATUS
• Update
• If we update the STATUS value for a particular
  CITY, we may have to update many tuples
• Ex) ltLondon, 20gt ? ltLondon, 30gt
• We have to update 2 tuples
• May cause inconsistency if we miss updating any
  tuple

19
Solution for Anomalies of SECOND

• Decompose
• SECONDS, STATUS, CITY,
• FD S?STATUS, S?CITY, CITY?STATUS
• Into SCS, CITY, CSCITY, STATUS
• The effect of the decomposition is to eliminate
  the transitive dependencies
• See fig.11.10 (kp. 423) for example tables

20
Third Normal Form

• A relvar is 3NF iff
• 2NF
• Every nonkey attribute is nontransitively
  dependent on the primary key
• In other words, no mutual dependency
• Ex) SC, and CS 3NF