Chapter 8: Relational Database Design Normalization in Databases

1. Chapter 8: Relational Database DesignNormalization in Databases

2. Chapter 8: Relational Database Design • Features of Good Relational Design • Atomic Domains and First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF)

3. Combine Schemas? • Suppose we combine instructor and department into inst_dept • (No connection to relationship set inst_dept) • Result is possible repetition of information

4. A Combined Schema Without Repetition • Consider combining relations • sec_class(sec_id, building, room_number) and • section(course_id, sec_id, semester, year) into one relation • section(course_id, sec_id, semester, year, building, room_number) • No repetition in this case

5. What About Smaller Schemas? • Suppose we had started with inst_dept. How would we know to split up (decompose) it into instructor and department? • Write a rule “if there were a schema (dept_name, building, budget), then dept_name would be a candidate key” • Denote as a functional dependency: dept_namebuilding, budget • In inst_dept, because dept_name is not a candidate key, the building and budget of a department may have to be repeated. • This indicates the need to decompose inst_dept • Not all decompositions are good. Suppose we decomposeemployee(ID, name, street, city, salary) into employee1 (ID, name) employee2 (name, street, city, salary) • The next slide shows how we lose information -- we cannot reconstruct the original employee relation -- and so, this is a lossy decomposition.

6. A Lossy Decomposition

7. Example of Lossless-Join Decomposition • Lossless join decomposition • Decomposition of R = (A, B, C) R1 = (A, B) R2 = (B, C) A B C A B B C   1 2 A B   1 2 1 2 A B r B,C(r) A,B(r) A B C A (r) B (r)   1 2 A B

8. Normal Forms • 1NF • 2NF • 3NF • Other… • Not covered.

9. Normal Forms: Review • Unnormalized – There are multivalued attributes or repeating groups • 1 NF – No multivalued attributes or repeating groups. • 2 NF – 1 NF plus no partial dependencies • 3 NF – 2 NF plus no transitive dependencies

10. First Normal Form • Domain is atomic if its elements are considered to be indivisible units • Examples of non-atomic domains: • Set of names, composite attributes • Identification numbers like CS101 that can be broken up into parts • A relational schema R is in first normal form if the domains of all attributes of R are atomic • Non-atomic values complicate storage and encourage redundant (repeated) storage of data • Example: Set of accounts stored with each customer, and set of owners stored with each account

11. First Normal Form (Cont’d) • Atomicity is actually a property of how the elements of the domain are used. • Example: Strings would normally be considered indivisible • Suppose that students are given roll numbers which are strings of the form CS0012 or EE1127 • If the first two characters are extracted to find the department, the domain of roll numbers is not atomic. • Doing so is a bad idea: leads to encoding of information in application program rather than in the database.

12. Example 1: Table Violating 1NF

13. Example 1: Table Not Violating 1NF It violates other normal forms, though.

14. Example 2: Table Violating 1NF

15. Example 2: Table Not Violating 1NF It violates other normal forms, though.

16. Types of Normalization • First Normal Form • each field contains the smallest meaningful value • the table does not contain repeating groups of fields or repeating data within the same field • Create a separate field/table for each set of related data. • Identify each set of related data with a primary key

17. Tables Violating First Normal Form Really Bad Set-up! Better, but still flawed!

18. Table Conforming to 1NF

19. Second Normal Form – 2NF • usually used in tables with a multiple-field primary key (composite key) • each non-key field relates to the entire primary key • any field that does not relate to the primary key is placed in a separate table • MAIN POINT – • eliminate redundant data in a table • Create separate tables for sets of values that apply to multiple records

20. Table Violating 2NF Where is the problem?

21. Table Violating 2NF

22. Tables Conforming to 2NF PART_STOCK TABLE ∞ WAREHOUSE TABLE 1

23. Third Normal Form – 3NF • Usually used in tables with a single- field primary key • Records do not depend on anything other than a table's primary key • Each non-key field is a fact about the key • Values in a record that are not part of that record's key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.

24. Table Violating 3NF EMPLOYEE_DEPARTMENT TABLE The underlying problem is the transitive dependency to which the DeptName attribute is subject. DeptName actually depends on WORKDEPT, which in turn depends on the key EmpNO.

25. Tables Conforming to Third Normal Form EMPLOYEE TABLE ∞ DEPARTMENT TABLE 1

26. A Note on 2NF • A table may have multiple candidate key. • A functional dependency on part of any candidate key is a violation of 2NF. • It is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.

27. Example Candidate Key PK Example taken from Wikipedia: http://en.wikipedia.org/wiki/Second_normal_form

28. Example Electric Toothbrush Manufacturers Electric Toothbrush Models

29. More Examples

30. Example 1 • Un-normalized Table:

31. Table in First Normal Form • No Repeating Fields • Data in Smallest Parts

32. Is table in 2NF? • What is the key?

33. Is table in 2NF? • What is the key? • What do we notice? • Advisor fields depend on Student#

34. Tables in Second Normal Form • Redundant Data Eliminated Table: Registration Table: Students

35. Tables Registration in 2NF • Who about the Students? Table: Registration Table: Students What is the candidate key for Students?

36. Table: Advisors • Tables in 2NF. Table: Registration Table: Students

37. Advisors Advisor# AdvFirstName AdvLastName Adv-Room Students Student# Advisor# Registration Student# Class# Relationships for Example 1

38. Example 2 • Un-normalized Table:

39. Table in First Normal Form What is the candidate key?

40. Tables in Second Normal Form Table: Employees and Projects Table: Employees Are they in 3NF? The underlying problem is the transitive dependency to which the Dept Name attribute is subject. Dept Name actually depends on Dept Code, which in turn depends on the key EmpID.

41. Tables in Third Normal Form Table: Employees_and_Projects Table: Employees Table: Departments

42. Departments DeptCode DeptName Employees EmpID FirstName LastName DeptCode Employees_and_Projects EmpID ProjectNumber TimeonProject Relationships for Example 2

43. Example 3 • Un-normalized Table:

44. Table in First Normal FormFields contain smallest meaningful values

45. Table in First Normal FormNo more repeated fields What is the candidate key? Is the table in 2NF?

46. Second/Third Normal FormRemove Repeated Data From Table Step 1

47. Tables in Second Normal Form Removed Repeated Data From Table Step 2 We look for the transitive dependency.

48. Tables in Second Normal Form How about 3NF? Step 3 We look the transitive dependency. If I know Dept, then I know ManagerID and Sector. If I know EmpID then I know Dept.

49. Tables in Third Normal Form Employees Table Manager Table Dependents Table Department Table

50. Example 4 Table Violating 1st Normal Form Table in 1st Normal Form