First Normal Form

1. First Normal Form A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain. Edgar Codd, in a 1971 conference paper, defined a relation in first normal form to be one such that none of the domains of that relation should have elements which are themselves sets

2. Consider the schema Customer (CutomerId , FirstName ,surname, TeleNo ) Here The telephone Number field of the customer 126 and 124 contains a set of values So it is not in first normal form

3. In the first normal form, the previous table can be represented in the following way. PK

4. Another Example

5. In the first normal form, the previous table can be represented in the following way. PK

6. Second Normal Form • a table is in 2NF if and only if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. • A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table. • a table is in 2NF if and only if it is in 1NF and every non-prime attribute of the table is dependent on the whole of a candidate key. Consider the relation • Orders(OrdNo,OrdDate,PartNum,Description,NumOrderded,QtPrice)

7. pK OrdNum->OrdDate PartNum->Description OrdNum,PartNum->NumOrdered,QuotedPrice,ordDate,Description

8. The anomalies • Update anomalies A Change to the description of part DR93 requires two changes Insert anomalies It is not possible to add a part and description without an order Delete anomalies If we delete order 21608 we lose all information about part AT94 So convert to second normal form. to do so first take each subset of columns that makes up the primary key;then begin a new table with this subset as the primary key (OrdNo,OrdDate) (PartNum,Description) (OrdNo,PartNum,Numordered,QtPrice)

9. Orders Part OrderLine

10. A table for which there are no partial functional dependencies on the primary key is typically, but not always, in 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys. Consider another Shema Student( Regno , Rollno,classno,classname,stuName,mark,age)

11. Regno->{Rollno,classno,classname,stuname,mark,age} • {Classno,rollno}->{Regno,classname,stuname,mark,age} • Classno->classname • Even if the designer has specified the primary key as {RegNo}, the table is not in 2NF. {RollNo,classno} is also a candidate key, and classname is dependent on a proper subset of it: classnoTo make the design conform to 2NF, it is necessary to have two tables:

12. class student

13. *A table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds: X contains A (that is, X → A is trivial functional dependency), or X is a superkey, or A is a part of some key of R • Third Normal Form • The relation R (table) is in second normal form (2NF) • Every non-prime attribute of R is non-transitively dependent on every keyof R. For Example consider the schema Employ(ssn,name,salary,age,deptno,deptname) Key(ssn)

14. Employ The dependencies are Ssn->name,salary,age,deptno,deptname Deptno->deptname There Exist a transitive dependency between ssn and DeptName

15. Employe • So remove the transitive dependency by splitting the tables Department

16. Another Example Tournamentwinners(Tournament,Year,winner,winnerdateofbirth) Key(tournament,year)

17. PK Tournamentwinners The dependencies are Tournament,Year->(winner,winnerdateofbirth) Winner->winnerdateofbirth Tournamet,year transitively determines winnerdateofbirth

18. Tournamentwinners Winnersdateofbirth

19. Boys-codd Normal form(BCNF) It is a slightly stronger version of the third normal form (3NF). BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by 3NF. • A relational schema R is in Boyce–Codd normal form if and only if for every one of its dependenciesX → Y, at least one of the following conditions hold: X → Y is a trivial functional dependency (Y ⊆ X) Xis a superkeyfor schema R

20. Consider the relation Reserve(sid,bid,date,card) Key(sid,bid,date) FD Sid->card The FD Sid->card states that a sailor uses a unique credit card to pay for reservations.This relation is not in 3 NF because sid,card pairs are stored redundantly.

21. But if we know that credit cards uniquely identify the owner ,we have the FD card->sid means that card,bid,date is also a key for reserves. Therefore the dependancysid->card does not violate 3NF.and reserves is in 3 NF • But in all tuples containing the same sid value the same (sid,card) pair is redundantly stored Key(sid,bid,date) Key(card,bid,date) Sid->card Card->sid reserves is in 3 NF

22. sidcard • But the relation is not in BCNF So split the relation Reserves

23. K K Another Example: Courtbooking(court,starttime,endtime,ratetype) K K

24. Each row in the table represents a court booking at a tennis club that has one hard court (Court 1) and one grass court (Court 2) • A booking is defined by its Court and the period for which the Court is reserved • Additionally, each booking has a Rate Type associated with it. There are four distinct rate types: • SAVER, for Court 1 bookings made by members • STANDARD, for Court 1 bookings made by non-members • PREMIUM-A, for Court 2 bookings made by members • PREMIUM-B, for Court 2 bookings made by non-members

25. The dependendies are • court,starttime->endtime,ratetype • court,endtime->starttime,ratetype • endtime,ratetype->court,starttime • starttime,ratetype->court,endttime • Ratetype->court This table is in 3NF because here there is no non prime attributes But it is not in BCNF

26. Ratecourt • So split the table to convert it to BCNF courtbooking

27. Properties of decomposition A decomposition of a relation schema R consists of replacing the relation schema by two (or more) relation schemas that each contain a subset of the attributes of R and together include all attributes in R. Lossless-Join Decomposition Let R be a relation schema and let F be a set of FDs over R. A decomposition of R into two schemas with attribute sets X and Y is said to be a lossless-join decomposition with respect to F if for every instance r of R that satisfies the dependencies in F, πX (r) πY (r) = r.

28. Example for lossy decomposition Πsp ( r ) Π PD ( r ) Instance r Πsp (r ) ╳ Πpd ( r )

29. All decompositions used to eliminate redundancy must be lossless. The following simple test is very useful: • Let R be a relation and F be a set of FDs that hold over R. The decomposition of R into relations with attribute sets R1 and R2 is lossless if and only if F + contains either the FD R1 ∩ R2 → R1 or the FD R1 ∩ R2 → R2 . In other words, the attributes common to R1 and R2 must contain a key for either R1 or R2 . If a relation is decomposed into two relations, this test is a necessary and sufficient condition for the decomposition to be lossless-join.

30. Consider the Hourly Emps relation . It has attributes SNLRWH, and the FD R → W causes a violation of 3NF. We dealt with this violation by decomposing the relation into SNLRH RW. Since R is common to both decomposed relations, and R → W holds, this decomposition is lossless-join. • If an FD X → Y holds over a relation R and X ∩Y is empty, the decomposition of R into R − Y and XY is lossless. • X appears in both R − Y (since X ∩ Y is empty) and XY, and it is a key for XY. Thus, the above observation follows from the test for a lossless-join decomposition.

31. Another important observation Suppose that a relation R is decomposed into R1 and R2 through a lossless-join decomposition, and that R1 is decomposed into R11 and R12 through another lossless-join decomposition. Then the decomposition of R into R11, R12, and R2 is lossless-join; by joining R11 and R12 we can recover R1, and by then joining R1 and R2, we can recover R.

32. DEPENDENCY PRESERVING DECOMPOSITION Consider the Contracts relation with attributes CSJDPQV. FDs are C → CSJDPQV, JP → C, and SD → P. Because SD is not a key the dependency SD → P causes a violation of 3 NF So we can decompose Contracts into two relations with schemas CSJDQV and SDP .the decomposition is lossless-join.