Concepts of Database Management Seventh Edition Chapter 5

Concepts of Database Management Seventh Edition Chapter 5 Database Design 1: Normalization

Objectives • Discuss functional dependence and primary keys • Define first normal form, second normal form, and fourth normal form • Describe the problems associated with tables (relations) that are not in first normal form, second normal form, or third normal form, along with the mechanism for converting to all three • Understand how normalization is used in the database design process 2

Introduction • Normalization process – Identifying potential problems, called update anomalies, in the design of a relational database – Methods for correcting these problems • Normal form: table has desirable properties – First normal form (1 NF) – Second normal form (2 NF) – Third normal form (3 NF) 3

Introduction (continued) • Normalization – Table in first normal form better than table not in first normal form – Table in second normal form better than table in first normal form, and so on – Goal: new collection of tables that is free of update anomalies 4

Functional Dependence A B A certain field say Column B is functionally dependent on another field say Column A if Column B’s value depend on the value of Column A. And also that Column A’s value is associated only with a exactly one value of Column B. And so if Column B depends on Column A then it also means that Column A functionally determines Column B. 5

Functional Dependence (continued) Let’s assume that in Premiere Products all Sales Rep in any given Pay class earn the Commission Rate. So, which means a Sale’s Rep Pay Class determines his/her Commission Rate And his/her Commission Rate therefore depends on his/her Pay Class Pay. Class Rate FIGURE 5 -2: Rep table with additional column, Pay. Class 6

Functional Dependence (continued) Let’s make it a local example here. Suppose we have a Courses table below: Course Code Course Description IS 230 Database Design CA 100 Computer Literacy BU 101 Intro to Business Course Code Course Description That is, Course Code determines his/her Course Description And Course Description depends on Course Code 7

Functional Dependence (continued) Given a Salary table for Faculties of a community college which one field determines which field and which field depends which field? Position Annual Salary Instructor 16, 000 Assistant Professor 19, 000 Associate Professor 24, 000 Professor 29, 000 Professor Emeritus 35, 000 8

Let us examine Rep table on Premier Database FIGURE 5 -3: Rep table FIGURE 5 -4: Rep table with second rep named Kaiser added 9

Question? Is Street functionally depend on Firstname or Lastname? 10

Question? FIGURE 5 -3: Rep table Is Customer. Name Functionally Dependent on Rep. Num? 11

Question? Is Quoted. Price Functionally Dependent on Order. Num? Is Quoted. Price Functionally Dependent on Part. Num? So, on which columns does Quoted. Price is functionally dependent? 12

Non-Graded Exercise Identify which field(s) is functionally dependent on which field(s) And then which field(s) functionally determines which field(s). Stude. Last ID Stud. First High. School Num High. School Name Advisor. Nu m Advisor Name 1 Cruz John 101 CCA 990 Smith 2 Moore Anna 102 SDA 991 Song 3 Friend Fe 101 CCA 991 Song 4 Zap Mario 103 MNHS 990 Smith 5 Bass Gerard 103 MNHS 992 George 13

Primary Key and Functional Depedence • Remember the primary key concept that we learn on Chapter 4? • Primary key uniquely identifies a record or row. • The key in determining if column is functionally dependent to another column is to ask the question, is a certain column functionally dependent to the Primary Key. 14

Primary Key and Functional Depedence Is Warehouse functionally dependent on Class? Is the Combination of Partnum and Descriptin is the Primary Key? What is the Primary Key of Part table? 15

Primary Key and Functional Depedence Is Customer. Num the Primary Key for Customer table? Does Customer. Num determines the values of the other fields? 16

Question? FIGURE 5 -3: Rep table Is Order. Num the Primary Key of Order. Line table? What is the Primary Key of Order. Line Table? 17

Nothing but the Key • The key thought in normalization is the primary key. • To Quote E. F. Codd the father of relational database systems. – “[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key. ” • Take this into mind as we go on three basic normal forms in Database Design. 18

Three Normal Forms Mnemonics • In order to easily remember the three normal forms just remember the word Re. Pea. T ignoring the vowels (which are in small letters) which are: R – 1 ST Normal Form - No Repeating groups or multi-valued fields P – 2 nd Normal Form - No Partial Dependence T – 3 rd Normal Form - No Transitional Dependence 19

First Normal Form • There should be no repeating group or multivalued columns in order for a Table to be in first normal form. – Repeating group: multiple entries for a single record – Unnormalized relation: contains a repeating group 20

First Normal Form (continued) Orders (Order. Num, Order. Date, (Part. Num, Num. Ordered) ) Multi-valued Columns FIGURE 5 -5: Sample unnormalized table 21

First Normal Form (continued) Orders (Order. Num, Order. Date, Part. Num, Num. Ordered) Converted to First Normal Form No more Multi-valued fields FIGURE 5 -6: Result of normalization (conversion to first normal form) 22

First Normal Form (continued) Below is a Table students and the course they are taking here at COM: Students Student. ID Lastname Firstname Program Courses. Taken 457411 Red Ray CIS IS 230, IS 220 256742 Zen Anna Education EN 210, EN 215, EN 110 444771 Call Sabrina Business BU 250, BU 260 Multi-Valued Column Violates 1 NF 23

First Normal Form (continued) To convert to First Normal Form (1 NF) is to remove the multi-value column Students Student. ID Lastname Firstname Program Courses. Taken 457411 Red Ray CIS IS 230, IS 220 256742 Zen Anna Education EN 210, EN 215, EN 110 444771 Call Sabrina Business BU 250, BU 260 X Remove Multi-Value Column 24

First Normal Form (continued) And create a new Table let’s say named Courses. Taken and relate the two. Students Student. ID Lastname Firstname Program 457411 Red Ray CIS 256742 Zen Anna Education 444771 Call Sabrina Business Courses. Taken Course. ID Student. ID Course. Code 101 457411 IS 230 102 457411 IS 220 103 256742 ED 210 104 256742 ED 215 105 256742 EN 110 106 444771 BU 250 107 444771 BU 260 25

First Normal Form (continued) Below is a Table students and the course they are taking here at COM: Courses. Taken Student. ID Lastname Firstname Program Course. Code 222333 Khan Bert CIS IS 230 222333 Khan Bert CIS IS 220 222333 Khan Bert CIS MS 100 Repeating Groups violates 1 NF 26

First Normal Form (continued) To convert to First Normal Form (1 NF) is to remove the multi-value column Courses. Taken Student. ID Lastname Firstname Program Course. Code 222333 Khan Bert CIS IS 230 222333 Khan Bert CIS IS 220 222333 Khan Bert CIS MS 100 X Remove Repeating Groups 27

First Normal Form (continued) And create a new Table let’s say named Students and relate the two. Courses. Taken Course. ID Student. ID Course. Code 101 222333 IS 230 102 222333 IS 220 103 222333 ED 210 Students Student. ID Lastname Firstname Program 222333 Khan Bert CIS 28

Non-Graded Exercise Convert to 1 NF the Table below which records the employee and his/her computer skills. Employees Employee. ID Lastname Firstname Gender Computer Skills 1 James George M Encoding, MS Office, Photoshop 2 Miles May F Encoding, Programming, Database Design 3 Gates Alan M Programming, MS Office 29

Non-Graded Exercise Convert to 1 NF the Table below which records the students and the school club that he/she joins in. Students Student. ID Lastname Firstname School. Club 88855 Combe Aber Math Club 88855 Combe Aber Computer Club 77744 Vibrant Vive Social Club 30

Second Normal Form (continued) • Table (relation) in second normal form (2 NF) – Table is in first normal form – No nonkey column (not a primary key) column should be partially dependent of a composite primary key. • Partial dependencies: only on a portion of the primary key 31

Second Normal Form Primary Key : Order. Num and Part. Num Order. Date is partially dependent on Order. Num but not on both Order. Num and Part. Num which is the composite Primary Key. Description is partially dependent on Part. Num but not on both Order. Num and Part. Num which are the composite Primary Key. 32

Converting to Second Normal Form Order. Num X Order. Date Because the Primary Key is Order. Num and Partnum 33

Converting to Second Normal Form X Remove partially dependent field Order. Date And make a new table out of it let’s say in this case Orders table 34

Converting to Second Normal Form Part. Num X Description Because the Primary Key is Order. Num and Partnum 35

Converting to Second Normal Form X Remove partially dependent field Description And make a new table out of it let’s say in this case Part table 36

Converting to Second Normal Form X X The Original table becomes a new table which is Normalized. And let’s say we name it Oder. Line table. 37

Second Normal Form (continued) FIGURE 5 -9: Conversion to second normal form 38

Second Normal Form (continued) Below is a Table of the courses taken by students Course. Taken Student. ID Lastname Firstname Program Courses. Code Course. Descripti on 457411 Red Ray CIS IS 230 Database Design 457411 Red Ray CIS CA 105 Data Analysis 444771 Call Sabrina Business BU 101 Intro to Business Lastname, Firstname, Program are dependent on Student. ID but not on Course. Code and Student. ID Course. Description is dependent on Course. Code but not on Course. Code and 39 Student. ID

Second Normal Form (continued) To convert to 2 NF remove partially dependent fields and make it as another table. Course. Taken Student. ID Lastname Firstname 457411 Red Ray 444771 Call Sabrina Program Courses. Code Course. Descripti on CIS IS 230 Database Design CIS CA 105 Business BU 101 X Remove Partially Dependent Fields X Data Analysis Intro to Business Remove Partially Dependent Field 40

Second Normal Form (continued) Converting into a new Table those who are partially dependent Students Courses Student. ID Lastname Firstname Program Courses. Code Course. Description 457411 Red Ray CIS IS 230 Database Design 444771 Call Sabrina Business CA 105 Data Analysis BU 101 Intro to Business Courses. Taken Student. ID Courses. Code 457411 IS 230 457411 CA 105 444771 BU 101 41

Non-Graded Exercise Convert to 2 NF the Table below which Customer’s purchase from which store location. Customers. Purchase Customer. ID Store. Location 1 1 Manila 2 2 Pohnpei 2 1 Manila 3 4 Hilo 4 3 LA 5 4 Hilo 42

Third Normal Form (continued) • Table (relation) in third normal form (3 NF) – It is in second normal form – There should no non-primary key that is transitional dependent to a primary key. 43

Third Normal Form (continued) FIGURE 5 -10: Sample Customer table 44

Third Normal Form • Customer (Customer. Num, Customer. Name, Balance, Credit. Limit, Rep. Num, Last. Name, First. Name) • Functional dependencies: – Customer. Num → Customer. Name, Balance, Credit. Limit, Rep. Num, Last. Name, First. Name – Rep. Num → Last. Name, First. Name 45

Third Normal Form (continued) • Correction procedure – Remove each column that is transitionally dependent. – Create a new table, transferring the removed columns to the newly created table. – Make a primary key of the new table – And use the primary key as the foreign key from the table where the columns were removed earlier. 46

Third Normal Form (continued) FIGURE 5 -12: Conversion to third normal form 47

Third Normal Form (continued) FIGURE 5 -12: Conversion to third normal form (continued) 48

Incorrect Decompositions • Decomposition must be done using method described for 3 NF • Incorrect decompositions can lead to tables with the same problems as original table 49

Incorrect Decompositions (continued) FIGURE 5 -13: Incorrect decomposition of the Customer table 50

Incorrect Decompositions (continued) FIGURE 5 -13: Incorrect decomposition of the Customer table (continued) 51

Incorrect Decompositions (continued) FIGURE 5 -14: Second incorrect decomposition of the Customer table 52

Incorrect Decompositions (continued) FIGURE 5 -14: Second incorrect decomposition of the Customer table (continued) 53

Third Normal Form (continued) Below is a Table students the program he/she belongs here at COM Students Student. ID Lastname Firstname Program. C ode Program. Name 12345 Green Arnel CIS Computer Information Systems 23456 Azure Zenaida Gen. Ed General Education 34567 Brown Country LA Liberal Arts Program. Name is Dependent on Program. Code not Student. ID which is 54 the PK

Third Normal Form (continued) To convert to Third Normal Form (3 NF) is to remove the Transitory Dependent column: Students Student. ID Lastname Firstname Program. C ode Program. Name 12345 Green Arnel CIS Computer Information Systems 23456 Azure Zenaida Gen. Ed General Education 34567 Brown Country LA Liberal Arts X Remove Transitory Dependent Column 55

Third Normal Form (continued) And create a new Table out of it let’s say we name it Programs and relate the two. Students Student. ID Lastname Firstname Program. Code 12345 Green Arnel CIS 23456 Azure Zenaida Gen. Ed 34567 Brown Country LA Programs Program. Code Program. Name CIS Computer Information Systems Gen. Ed General Education LA Liberal Arts 56

Third Normal Form (continued) Or we could create a new Primary Key for Programs and do like this: Students Student. ID Lastname Firstname Program. ID 12345 Green Arnel 1 23456 Azure Zenaida 2 34567 Brown Country 3 Programs Program. ID Program. Code Program. Name 1 CIS Computer Information Systems 2 Gen. Ed General Education 3 LA Liberal Arts 57

Non-Graded Exercise Convert to 3 NF the Table (i. e. Employees) below which records the Employee’s info including his/her department. Employees Employee Lastname ID Firstname Department. Code Department. Name 1001 Mills Karen SAL 01 Sales North 1002 Courtney Francis SAL 02 Sales South 1003 Smith Phillip ENG 01 Engineering Design 1005 Xavier Duran ENG 02 Engineering Construction 1004 Morrison John SAL 02 Sales South 58

More Practice Exercises 59

Non-Graded Exercise Let’s say we want to store into a table, Students who are borrowing books from the Library Student. ID Lastname Firstname Books. Borrowed 1001 Mills Karen College Algebra, Cooking in Micronesia, Data Analysis 1002 Courtney Francis Statistics, Chronicles of Narnia 1003 Smith Phillip Hermeneutics, Bible Mysteries solved What Normal Form did it violate? How should we normalized the above table? 60

Non-Graded Exercise Let’s say we want to record the books that Students borrow. And let’s assume that there is already a Table named Students that contains basic information for Students like first name, last name, student ID etc. Student. ID Date. Borr owed Return. Date Book. Number Book. Title 22222 09/16/13 09/23/13 BKN 13981 War and Peace 23232 09/18/13 09/20/13 XYZ 39825 Algorithm 24242 09/12/13 09/19/13 ABC 36987 Peace with GOD 22222 09/16/13 09/23/13 JIP 879874 Incredible Journey What Normal Form did it violate? How should we normalized the above table? 61

Non-Graded Exercise Let’s say we want to record the Courses that Faculties taught this semester on a Table like one below and Course. Assign. ID is the Primary Key. Course Assign. ID Faculty. Num Lastname Firstname Course. Code Section 1 2010 -12 Ullman Kathy IS 230 1 2 1998 -25 Gent Kris EN 110 5 3 2013 -01 Chiu Ching CA 100 1 4 2008 -78 Moore Alexis CA 100 2 What Normal Form did it violate? How should we normalized the above table? 62

Graded Case Study – Alexamara Problem 1 : Normalize the table below which is about owners and the boat(s) they owned Owner. Num AD 57 Last. Name Adney First. Name Bruce and Jean Boat. Name Weight Marina Ad. Bruce X 1, 000 lbs East Zinger 1, 500 lbs East AN 75 Anderson Bill Yellow Beast 2, 000 lbs West BL 72 Blake Mary Kumodo 1, 200 lbs East Kryptonite 1, 000 lbs West Shark Fin 1, 300 lbs East Two Cute 900 lbs East Ride North 1, 400 lbs West EL 25 Elend Sandy and Bill 63

Graded Case Study - Alexamara Problem 2 : Normalize the table below regarding the Boats in Marina Slip and its corresponding owners. Slip. ID Marina. Num Slip. Num Length Rental. Fee Boat. Name Boat. Type Owner. Num Owner. Last. Name Owner. First. Name 1 1 A 1 40 $3, 800. 00 Anderson II Sprite 4000 AN 75 Anderson Bill 2 1 A 2 40 $3, 800. 00 Our Toy Ray 4025 EL 25 Elend Sand Bill 3 1 A 3 40 $3, 600. 00 Escape Sprite 4000 KE 22 Kelly Allysa 4 1 B 1 30 $2, 400. 00 Gypsy Dolphin 28 JU 92 Juarez Maria 5 1 B 2 30 $2, 600. 00 Anderson III Sprite 3000 AN 75 Anderson Bill 6 21 25 $1, 800. 00 Bravo Dolphin 25 AD 57 Adney Bruce and Jean 7 22 25 $1, 800. 00 Chinook Dolphin 22 FE 82 Feenstra Daniel 8 23 25 $2, 000. 00 Listy Dolphin 25 SM 72 Smeltz Beck and Dave 9 24 30 $2, 500. 00 Mermaid Dolphin 28 BL 72 Blake Mary 10 25 40 $4, 200. 00 Axxon II Dolphin 40 NO 27 Norton Peter 11 26 40 $4, 200. 00 Karvel Ray 4025 TR 72 Trent Ashton 64

Graded Case Study – Henry Books Problem 1 : Normalize the table below regarding Publishers and the Books they published. Publisher. Code AH Publisher. Name Arkham House City Sauk City WI Book. Title Year. Published Dream House 1999 Partial Recall 2011 AP Arcade Publishing New York Games Played 1982 BA Basic Books Boulder CO Dance Fundamentals 1980 Booking the Flight 1993 BP Berkley Publishing Boston Bastketball glory 2001 VB Vintage Books New York Archive Reload 1998 Rusty Road 2002 WN W. W. Norton New York War and Breeze 2006 WP Westview Press Boulder CO General Goodwill 1978 65

Graded Case Study – Henry Books Problem 2 : Normalize the table below regarding Books and their corresponding author. Book. Code Title Author. Code Author. Firstname Author. Lastname 0180 A Deepness in the Sky 1001 George Graham 0189 Magic Terror 1002 Earl Johnson 0200 The Stranger 1001 George Graham 0378 Venice 1003 Vitali Pablo 079 X Second Wind 1004 Strong Mary 0808 The Edge 1002 Earl Johnson 66

Summary • Column (attribute) B is functionally dependent on another column A (or collection of columns) when each value for A in the database is associated with exactly one value of B • Column(s) A is the primary key if all other columns are functionally dependent on A and no subcollection of columns in A also have this property 67

Summary (continued) • Table (relation) in first normal form (1 NF) does not contain repeating groups • Nonkey column (or nonkey attribute) is not a part of the primary key • Table (relation) is in the second normal form (2 NF) when it is in 1 NF and no nonkey column is dependent on only a portion of the primary key • Determinant is a column that functionally determines another column 68

Summary (continued) • Table (relation) is in third normal form (3 NF) when it is in 2 NF and its only determinants are candidate keys • Collection of tables (relations) that is not in third normal form has inherent problems called update anomalies 69