More on normalization

1. More on normalization

2. Normalization Review • In the normalization approach, one has data in a table-like format and begins to rearrange the data into smaller tables that minimize the data redundancy without losing any of the information in the relationships. • This decomposition can be done in steps, each step called a Normal Form having more stringent conditions than the previous.

3. Normalization Starting Point: Un-normalized table • The first step in normalization is to eliminate any multi-valued fields by flattening the table. • A single row of the table with multi-valued fields is replaced with many rows, one for each value in the multi-valued field. • When multi-valued fields are thus eliminated, the table is said to be in the First Normal Form.

4. Un-normalized table example

5. Flattened table (now in the First Normal Form)

6. A note on primary keys of flattened tables • Recall that a primary key must uniquely identify each record in a table. • When a table is flattened to eliminate multi-valued fields, one of those previously multi-valued fields must be the primary key or part of a composite primary key.

7. Example When the table is flattened, something about the course must be part of the primary key – in this case we need Dept, CrseNo and Semester.

8. The Gain: search-ability • Flattening the file will help with searching and querying. • We do not have to look for “Coyne” buried in the midst of a long list that must be parsed (broken into pieces), etc. • If you can imagine that you might want to search on it, it should be “atomic” in a field of a record by itself.

9. The loss: data redundancy • Data redundancy is the unnecessary repetition of information. • Data redundancy makes it hard to maintain data integrity (correctness). • E.g. when you change your address with one department in an organization, but the other departments still have your old address. • Some repetition is necessary to maintain relationships.

10. Flattened table (now in the First Normal Form) • Repetition of PA is necessary to maintain the relationship between a HouseMember and a State. • Repetition of Pennsylvania and Harrisburgh is unnecessary.

11. Identifying and reducing redundancy • The reason Pennsylvania and Harrisburgh are unnecessarily repeated is because the relationship is fully realized by just using the stateSymbol. • The stateName and stateCapital are uniquely determined by the stateSymbol. • stateName and stateCapital are said to be functionally dependent on stateSymbol.

12. Uniquely!! • In a Billboard example, the song may be a duet and thus song would not determine the artist uniquely. • Jay-Z and Alicia Keys both sing “Empire State of Mind,” thus “Empire State of Mind” cannot be used to determine the artist uniquely.

13. Reducing Redundancy • Identifying functional dependencies is the key to reducing redundancy. • There are a few normal forms (Second Normal Form, Third Normal Form and Boyce-Codd Normal Form) which eliminate increasing degrees of redundancy. • What distinguishes the various forms is the field(s) upon which something is functionally dependent and the type of functional dependence.

14. Determinant • To avoid awkward phrases like “the field(s) upon which something is functionally dependent”, we introduce the term determinant. • The determinant attribute determines some other attribute, i.e. this other attribute is functionally dependent upon the determinant field. • stateName depends on stateSymbol • stateSymbol is the determinant of stateName

15. Types of functional dependence • Attribute B is functionally dependent on Attribute A if knowing the value of A means one can in turn know the value of B uniquely. • Attribute A (the determinant) may be a composite attribute – i.e., made up of more than one field. • If the full knowledge of A (all of its composite fields) is necessary to determine B, then B is fully dependent on A. • If only partial knowledge of A (some of its composite fields) is necessary to determine B, then B is partially dependent on A.

16. Not a two-way street • Recall that B being functionally dependent on A does not mean A is functionally dependent on B. • dateOfBirth is functionally dependent on socSecNum, but socSecNum is not functionally dependent on dateOfBirth

17. Transitive dependence • If attribute B is functionally dependent on A and attribute C is functionally dependent on B, then C is said to transitively dependenton A provided that A is not functionally dependent on C.

18. A Transitive Dependency Example • employeeNumber is functionally dependent on socSecNum and salary is functionally dependent on employeeNumber, then salary is “transitively functionally dependent” on socSecNum. • Rephrased: socSecNum is a determinant of employeeNumber and employeeNumber is a determinant of salary, then socSecNum is a determinant of salary.

19. Another Transitive Dependency Example • Imagine a table in which you sell a stock – a StockTransaction table. • stockID is functionally dependent on transactionID and stockName is functionally dependent on stockID, then stockName is transitively dependent on transactionID.

20. Primary key • Recall the primary key is an attribute or set of attributes that uniquely identify each row in a table. • Thus every attribute that is not part of the primary key is functionally dependent on the primary key. • Rephrased: The primary key is a determinant of any non-primary-key attribute. • The level of decomposition (the Normal Form) is determined by the type of dependence the field has on the primary key.

21. Second Normal Form • Eliminating any field (via table decomposition) that partially depends on the primary key puts the table into Second Normal Form (provided the table was in the First Normal Form prior to decomposition). • Note that a table with a simple (non-composite) primary key is necessarily in Second Normal Form.

22. Second Normal Form Example • CharacterFeaturedInEpisode(characterID, episodeID, firstName, lastName, characterDescription, title, episodeDescription, originalAirDate) • In the notation above the underlined fields are serving as the primary key. • The primary key is composite. • The attributes firstName, lastName and characterDescription are partially functionally dependent on the primary key because they are determined only by characterID. • The attributes title episodeDescription and originalAirDate are partially functionally dependent because they are determined only by episodeID.

23. Second Normal Form Example (Cont.) • Create tables having primary keys which are subsets (including a proper subset but not the empty set) of the primary keys of the original table. • Place the non-primary-key attributes in the table in which they are fully dependent.

24. Second Normal Form Example (Cont.) • Character(characterID, lastName, firstName, characterDescription) • Episode(episodeID, title, episodeDescription, originalAirDate) • EpisodeFeature(characterID, episodeID) • Note that although no field depends on both characterID and episodeID, we must keep the table with both keys to maintain the relationship (the lossless join property).

25. Some Redundancy May Remain • In the above example, the firstName, lastName combination may serve as the primary key, each of the other attributes stateSymbol, stateName and stateCapital are fully dependent on the primary key. So it’s in Second Normal Form. But clearly there is still redundancy.

26. Third Normal Form • Eliminating any field (via table decomposition) that transitivelydepends on the primary key puts the table into Third Normal Form (provided the table was in the Second Normal Form prior to decomposition).

27. firstName, lastName determines stateSymbol which in turn determines stateName and stateCapital. (transitive dependence)

28. Decomposition into the Third Normal Form • Create another table that has as a primary key the attribute which is the intermediate attribute in the transitive dependence. • (lastName, firstName, stateSymbol) • (stateSymbol, stateName, stateCapital)

29. Another transitive dependence example • Customer(customerID, lastName, firstName, street, city, state, zipcode, stateTax, cityTax) • There is a simple primary key, so the table is in Second Normal Form (2NF). • But the city tax is dependent on the city and the state tax is dependent on the state. • In fact city and state are dependent on zipcode.

30. Another transitive dependence example (Cont.) • Customer(customerID, lastName, firstName, street, zipcode) • AddressInfo(zipcode, city, state, cityTax, stateTax) • There could be further decomposition since stateTax depends on state. • For practical purposes, many draw the line at some of these decomposition even if they do reduce data redundancy. A question to ask is how likely is an update anomaly for the particular set of data.

31. Zipcode Dependence (Cont.) • In fact city and state may also be dependent on zipcode. • Sometimes a small city shares a zipcode with a bordering city or neighborhood of a bordering city. • It also depends on whether one means a 5-digit zipcode or a 9-digit zipcode.

32. Another transitive dependence example (Cont.) • Customer(customerID, lastName, firstName, street, zipcode) • ZipInfo(zipcode, city, state, cityTax, stateTax) • There could be further decomposition since stateTax depends on state.

33. Another transitive dependence example (Cont.) • Customer(customerID, lastName, firstName,street, zipcode) • ZipInfo(zipcode, city, state) • StateTax(state, stateTax) • CityTax(state, city, cityTax)

34. Recall the price $$$ • While redundancy has its price (increased storage and the possibility for update anomalies), minimizing redundancy also has a price: • It introduces more tables. • More tables means more joins when it comes to querying the database.

35. Introducing primary keys • If one has an awkward primary key • Perhaps it is composite, e.g. FirstName, LastName • Perhaps it may change, ItemName • Then it is valid to introduce an ID to serve as a primary key. • Just don’t let the introduction of simple key get in the way of eliminating data redundancy. This can be a problem with second normal form which is defined as having no partial dependence on the primary key. Thus the 2NF decomposition can depend on one’s choice of primary key.

36. Some Redundancy May Remain at 3NF: Lot Example • Let us say the land within a county is broken up into lots and each lot is assigned a number. • A county is also broken down into municipalities (cities, townships, etc.). • The lots are assessed at some value. • The table might look like: LotAssessment(lotID, county, municipality, assessment)

37. Some Redundancy May Remain at 3NF: Lot Example (Cont.) • The next stage is to select a primary key. There are two candidate keys: 1. LotAssessment(lotID, county, municipality, assessment) 2. LotAssessment(lotID, county, municipality, assessment)

38. Some Redundancy May Remain at 3NF: Lot Example (Cont.) • The next thing to note in this example is that county is functionally dependent on municipality. 1. LotAssessment(lotID, county, municipality, assessment) 2. LotAssessment(lotID, county, municipality, assessment) • Note that choice two is not in 2NF.

39. Some Redundancy May Remain at 3NF: Lot Example (Cont.) • The second choice LotAssessment(lotID, county, municipality, assessment) is decomposed as follows: LotAssessment(lotID, municipality, assessment) CityCounty(municipality, county)

40. Some Redundancy May Remain at 3NF: Lot Example (Cont.) • The first choice LotAssessment(lotID, county, municipality, assessment) on the other hand, is in 2NF and 3NF. • There is no partial dependence on the primary key. • There is no transitive dependency on the primary key.

41. Some Redundancy May Remain at 3NF: Lot Example (Cont.) • A possible feature of tables that are in 3NF but may still have redundancy is that there are various candidate keys from which one chooses the primary key. • We will introduce a generalization and/or extension of the Normal Form idea to ensure that we get further in our redundancy reduction independent of our initial choice of primary key.

42. Generalization: Primary  Candidate Key • One way to avoid the type of problem that occurred in the Lot example is to extend the definitions of the Second and Third Normal Forms. • To extend the definitions of the Second and Third Normal Forms, replace the term “depends on the primary key” with “depends on any candidate key.”

43. Return to the lot • Note that the first lot table is not in generalized 2NF because there is a partial dependence on a candidate key. • So with the generalized version of 2NF, this table would be decomposed. • In fact, the decomposed table would have the second candidate key as its primary key. • In effect, you are forced to choose the candidate key that yields decomposition.

44. Boyce-Codd Normal Form • After Third Normal form, the next stricter form is called the Boyce-Codd Normal Form. • A table is in Boyce-Codd Normal Form if the only determinants (attributes that determine other attributes) are candidate keys.

45. Primary Key Choice: Tutoring Example • Let us say we have a tutoring center in which tutees (students) come in to see tutors to be tutored in a course. • A tutor is assigned to a tutee for a particular course. • Thus the tutor-tutee pair is a determinant of course. • The tutor and tutee meet on a certain date at a certain time and are assigned a room for the tutoring session. • A tutor and tutee meet at most once a day.

46. Primary Key Choice: Tutoring Example (Cont.) • The starting table might look like: Tutoring(tutor, tutee, course, room, date, time) • The next stage is to identify the primary key. In this example, there are many choices, i.e. there are many candidate keys.

47. Primary Key Choice: Tutoring Example (Cont.) • On a given date, at a given time, in a given room, there can only be one tutor-tutee pair studying a course. So one choice for the primary key is date, time, room Tutoring(tutor, tutee, course, room, date, time) • This table is in 2NF but not 3NF because tutor-tuteecourse is second part of a transitive dependence of course on the primary key.

48. Primary Key Choice: Tutoring Example (Cont.) • On a given date, a given tutor-tutee pair meet just once to study their assigned course. So another choice for the primary key is date, tutor, tutee Tutoring(tutor, tutee, course, room, date, time) • This table is not in 2NF because course is partially dependent on the primary key.

49. Primary Key Choice: Tutoring Example (Cont.) • On a given date, at a given time, a given tutor meets a tutee in a room to study their assigned course. So another choice for the primary key is date, time, tutor Tutoring(tutor, tutee, course, room, date, time) • This table is in 2NF. It is not in 3NF but the intermediate attribute (tutor-tutee) is comprised of part of the primary and part non-primary key.

50. Primary Key Choice: Tutoring Example (Cont.) • On a given date, at a given time, a given tutee meets a tutor in a room to study their assigned course. So another choice for the primary key is date, time, tutee Tutoring(tutor, tutee, course, room, date, time) • This table is in 2NF. It is not in 3NF but the intermediate attribute (tutor-tutee) is comprised of part of the primary and part non-primary key.