More on normalization CSC 240 Blum 1 Normalization

More on normalization CSC 240 (Blum) 1

Normalization Review ¡ ¡ CSC 240 (Blum) 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. 2

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

Un-normalized table example PA Pennsylvania Harrisburgh Borski Brady Coyne … NJ New Jersey Trenton Andrews Robert E. Ferguso Mike n … … CSC 240 (Blum) Robert A. William J. … 4

Flattened table (now in the First Normal Form) PA Pennsylvania Harrisburgh Borski Robert A. PA Pennsylvania Harrisburgh Brady Robert A. PA Pennsylvania Harrisburgh Coyne William J. … … … NJ New Jersey Trenton Andrews Robert E. NJ New Jersey Trenton Ferguson Mike … … CSC 240 (Blum) … 5

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. ¡ CSC 240 (Blum) 6

Example Maj Student. ID Last. Name First. Name Middle Maj 1 2 Dept 9874923 Mc. Dermott Mary Margaret ENG 9840495 Jameson John James CSC INF Crse. No Course. Name Semester Grade 108 Writing II Fall 2002 A- PHL 151 Critical Thinking Fall 2002 B+ MTH 150 Thinking Math Fall 2002 B ENG 230 American Lit Spring 2003 B+ REL 150 Rel in America Spring 2003 B- CSC T MTH 230 Programming Fall 2002 A- 160 Discrete Math Fall 2002 B- ENG 107 Writing I Fall 2002 B+ PHL 151 Critical Thinking Spring 2003 B REL 150 Rel in America Spring 2003 B+ When the table is flattened, something about the course must be part of the primary key – in this case we need Dept, Crse. No and Semester. CSC 240 (Blum) 7

The Gain: search-ability ¡ Flattening the file will help with searching and querying. l l CSC 240 (Blum) 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. 8

The loss: data redundancy ¡ ¡ Data redundancy is the unnecessary repetition of information. Data redundancy makes it hard to maintain data integrity (correctness). l ¡ CSC 240 (Blum) 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. 9

Flattened table (now in the First Normal Form) PA Pennsylvania Harrisburgh Borski Robert A. PA Pennsylvania Harrisburgh Brady Robert A. PA Pennsylvania Harrisburgh Coyne William J. … … … • Repetition of PA is necessary to maintain the relationship between a House. Member and a State. • Repetition of Pennsylvania and Harrisburgh is unnecessary. CSC 240 (Blum) 10

Identifying and reducing redundancy ¡ ¡ ¡ CSC 240 (Blum) The reason Pennsylvania and Harrisburgh are unnecessarily repeated is because the relationship is fully realized by just using the state. Symbol. The state. Name and state. Capital are uniquely determined by the state. Symbol. state. Name and state. Capital are said to be functionally dependent on state. Symbol. 11

Uniquely!! ¡ ¡ CSC 240 (Blum) 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. 12

Reducing Redundancy ¡ ¡ ¡ CSC 240 (Blum) Identifying functional dependencies is the key to reducing redundancy. There 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. 13

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. l l CSC 240 (Blum) state. Name depends on state. Symbol is the determinant of state. Name 14

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. CSC 240 (Blum) 15

Not a two-way street ¡ Recall that B being functionally dependent on A does not mean A is functionally dependent on B. l CSC 240 (Blum) date. Of. Birth is functionally dependent on soc. Sec. Num, but soc. Sec. Num is not functionally dependent on date. Of. Birth 16

Transitive dependence ¡ CSC 240 (Blum) If attribute B is functionally dependent on A and attribute C is functionally dependent on B, then C is said to transitively dependent on A provided that A is not functionally dependent on C. 17

A Transitive Dependency Example ¡ ¡ CSC 240 (Blum) employee. Number is functionally dependent on soc. Sec. Num and salary is functionally dependent on employee. Number, then salary is “transitively functionally dependent” on soc. Sec. Num. Rephrased: soc. Sec. Num is a determinant of employee. Number and employee. Number is a determinant of salary, then soc. Sec. Num is a determinant of salary. 18

Another Transitive Dependency Example Imagine a table in which you sell a stock – a Stock. Transaction table. ¡ stock. ID is functionally dependent on transaction. ID and stock. Name is functionally dependent on stock. ID, then stock. Name is transitively dependent on transaction. ID. ¡ CSC 240 (Blum) 19

Primary key ¡ Recall the primary key is an attribute or set of attributes that uniquely identify each row in a table. l l ¡ 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. CSC 240 (Blum) 20

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). l Note that a table with a simple (noncomposite) primary key is necessarily in Second Normal Form. CSC 240 (Blum) 21

Second Normal Form Example ¡ Character. Featured. In. Episode(character. ID, episode. ID, first. Name, last. Name, character. Description, title, episode. Description, original. Air. Date) l ¡ In the notation above the underlined fields are serving as the primary key. The primary key is composite. l l The attributes first. Name, last. Name and character. Description are partially functionally dependent on the primary key because they are determined only by character. ID. The attributes title episode. Description and original. Air. Date are partially functionally dependent because they are determined only by episode. ID. CSC 240 (Blum) 22

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. ¡ CSC 240 (Blum) 23

Second Normal Form Example (Cont. ) Character(character. ID, last. Name, first. Name, character. Description) ¡ Episode(episode. ID, title, episode. Description, original. Air. Date) ¡ Episode. Feature(character. ID, episode. ID) ¡ l CSC 240 (Blum) Note that although no field depends on both character. ID and episode. ID, we must keep the table with both keys to maintain the relationship (the lossless join property). 24

Some Redundancy May Remain ¡ PA Pennsylvania Harrisburgh Borski Robert A. PA Pennsylvania Harrisburgh Brady Robert A. PA Pennsylvania Harrisburgh Coyne William J. … … … In the above example, the first. Name, last. Name combination may serve as the primary key, each of the other attributes state. Symbol, state. Name and state. Capital are fully dependent on the primary key. So it’s in Second Normal Form. But clearly there is still redundancy. CSC 240 (Blum) 25

Third Normal Form ¡ CSC 240 (Blum) Eliminating any field (via table decomposition) that transitively depends on the primary key puts the table into Third Normal Form (provided the table was in the Second Normal Form prior to decomposition). 26

¡ PA Pennsylvania Harrisburgh Borski Robert A. PA Pennsylvania Harrisburgh Brady Robert A. PA Pennsylvania Harrisburgh Coyne William J. … … … first. Name, last. Name determines state. Symbol which in turn determines state. Name and state. Capital. (transitive dependence) CSC 240 (Blum) 27

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. ¡ (last. Name, first. Name, state. Symbol) ¡ (state. Symbol, state. Name, state. Capital) ¡ CSC 240 (Blum) 28

Another transitive dependence example ¡ ¡ CSC 240 (Blum) Customer(customer. ID, last. Name, first. Name, street, city, state, zipcode, state. Tax, city. Tax) There is a simple primary key, so the table is in Second Normal Form (2 NF). 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. 29

Another transitive dependence example (Cont. ) ¡ ¡ ¡ Customer(customer. ID, last. Name, first. Name, street, zipcode) Address. Info(zipcode, city, state, city. Tax, state. Tax) There could be further decomposition since state. Tax depends on state. l CSC 240 (Blum) 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. 30

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. l It also depends on whether one means a 5 -digit zipcode or a 9 -digit zipcode. l CSC 240 (Blum) 31

Another transitive dependence example (Cont. ) Customer(customer. ID, last. Name, first. Name, street, zipcode) ¡ Zip. Info(zipcode, city, state, city. Tax, state. Tax) ¡ l CSC 240 (Blum) There could be further decomposition since state. Tax depends on state. 32

Another transitive dependence example (Cont. ) Customer(customer. ID, last. Name, first. Name, street, zipcode) ¡ Zip. Info(zipcode, city, state) ¡ State. Tax(state, state. Tax) ¡ City. Tax(state, city. Tax) ¡ CSC 240 (Blum) 33

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

Introducing primary keys ¡ If one has an awkward primary key l l ¡ ¡ CSC 240 (Blum) Perhaps it is composite, e. g. First. Name, Last. Name Perhaps it may change, Item. Name 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 2 NF decomposition can depend on one’s choice of primary key. 35

Some Redundancy May Remain at 3 NF: 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: Lot. Assessment(lot. ID, county, municipality, assessment) CSC 240 (Blum) 36

Some Redundancy May Remain at 3 NF: Lot Example (Cont. ) ¡ The next stage is to select a primary key. There are two candidate keys: 1. Lot. Assessment(lot. ID, county, municipality, assessment) 2. Lot. Assessment(lot. ID, county, municipality, assessment) CSC 240 (Blum) 37

Some Redundancy May Remain at 3 NF: Lot Example (Cont. ) ¡ The next thing to note in this example is that county is functionally dependent on municipality. 1. Lot. Assessment(lot. ID, county, municipality, assessment) 2. Lot. Assessment(lot. ID, county, municipality, assessment) ¡ CSC 240 (Blum) Note that choice two is not in 2 NF. 38

Some Redundancy May Remain at 3 NF: Lot Example (Cont. ) ¡ The second choice Lot. Assessment(lot. ID, county, municipality, assessment) is decomposed as follows: Lot. Assessment(lot. ID, municipality, assessment) City. County(municipality, county) CSC 240 (Blum) 39

Some Redundancy May Remain at 3 NF: Lot Example (Cont. ) ¡ The first choice Lot. Assessment(lot. ID, county, municipality, assessment) on the other hand, is in 2 NF and 3 NF. l l CSC 240 (Blum) There is no partial dependence on the primary key. There is no transitive dependency on the primary key. 40

Some Redundancy May Remain at 3 NF: Lot Example (Cont. ) ¡ A possible feature of tables that are in 3 NF but may still have redundancy is that there are various candidate keys from which one chooses the primary key. l CSC 240 (Blum) 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. 41

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. ” ¡ CSC 240 (Blum) 42

Return to the lot ¡ ¡ CSC 240 (Blum) Note that the first lot table is not in generalized 2 NF because there is a partial dependence on a candidate key. So with the generalized version of 2 NF, 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. 43

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. ¡ CSC 240 (Blum) 44

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. l ¡ ¡ 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. CSC 240 (Blum) 45

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. CSC 240 (Blum) 46

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) l This table is in 2 NF but not 3 NF because tutor-tutee course is second part of a transitive dependence of course on the primary key. CSC 240 (Blum) 47

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) l This table is not in 2 NF because course is partially dependent on the primary key. CSC 240 (Blum) 48

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) l This table is in 2 NF. It is not in 3 NF but the intermediate attribute (tutor-tutee) is comprised of part of the primary and part non-primary key. CSC 240 (Blum) 49

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) l This table is in 2 NF. It is not in 3 NF but the intermediate attribute (tutor-tutee) is comprised of part of the primary and part non-primary key. CSC 240 (Blum) 50

Primary Key Choice: Tutoring Example (Cont. ) While the tutoring example has to be decomposed to reach the 3 NF, it does demonstrate that there can be many different primary keys. ¡ If the generalized version of the 2 NF is used then decomposition occurs there (because it occurs there for one of the candidate keys). ¡ CSC 240 (Blum) 51

Primary Key Choice: Tutoring Example (Cont. ) ¡ The resulting tables are Tutoring(tutor, tutee, room, date, time) Subject(tutor, tutee, course) ¡ CSC 240 (Blum) This decomposition does not force a choice of the primary key in the first table. 52

Many candidate keys week date host. City host. State host. Name away. City away. State away. Name host. Score away. Score 1 9/12/04 9/13/04 … Philadelphia Charlotte … PA NC … Eagles Panthers … New York Green Bay … NY WI … Giants Packers … 31 14 … 17 24 … 2 9/19/04 Kansas City MO Chiefs Charlotte NC Panthers 17 28 ¡ Another example with many possible keys is the football schedule table. We could use l l Week, Host. Name Week, Away. Name Date, Host. Name Date, Away. Name CSC 240 (Blum) 53

Choose Date over Week ¡ ¡ CSC 240 (Blum) Note that Date Week (but not vice versa). In the third and fourth choices this is a partial dependence on the primary key – and so Second Normal Form requires a table consisting of (Date, Week). Generalized Second Normal Form would also require the decomposition because it is a partial dependence on a candidate key. Boyce-Codd Normal Form would require the decomposition because the determinant (Date) is not by itself a candidate key. 54

Connelly and Begg: Review of Normalization (UNF to BCNF) CSC 240 (Blum) 55

Connelly and Begg: Review of Normalization (UNF to BCNF) CSC 240 (Blum) 56

Connelly and Begg: Review of Normalization (UNF to BCNF) CSC 240 (Blum) 57

Connelly and Begg: Review of Normalization (UNF to BCNF) CSC 240 (Blum) 58

References Database Systems, Rob and Coronel ¡ Database Systems, Connolly and Begg ¡ Fundamentals of Relational Databases, Mata-Toledo and Cushman ¡ Concepts of Database Management, Pratt and Adamski ¡ CSC 240 (Blum) 59