Normalization Lite

1. Normalization Lite Pepper

2. Golden Rule Every attribute must depend upon the key, --- > 1NF the whole key, --- > 2NF and nothing but the key. - 3NF and BCNF

3. Forms • Codd stated: • There is, in fact, a very simple elimination* procedure which we shall call normalization. Through decomposition nonsimple domains are replaced by "domains whose elements are atomic (nondecomposable) values."

4. 1NF • Eliminate duplicative columns from the same table. Every item has its own field. • (no name which is really first and last name) • (no phone1, phone 2, phone3) • Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

5. 1NF example Table with: • Person • Favorite Color • Foods Not Eaten 1 • Foods Not Eaten 2 • Foods Not Eaten 3 How to fix this?

6. 1NF example Table 1 with: • Person • Favorite Color • Table 2 with • Person • Foods Not Eaten

7. 2NF • Meet all the requirements of the first normal form. • Remove subsets of data that apply to multiple rows of a table and place them in separate tables. • Create relationships between these new tables and their predecessors through the use of foreign keys. • Ex: Student address in the enrollment table – repeated for every course. • Yes, student is part of the key, but not the full key • When you notice data repeated in your table, pull it out into its own table

8. 2NF problem HOW TO FIX?

9. 2NF solution

10. 3NF • Meet all the requirements of the second normal form. • Remove columns that are not dependent upon the primary key. • Same as 2NF, but looking at dependencies not on part of primary key • Problem example: • PART_NUMBER (PRIMARY KEY) • MANUFACTURER_NAME • MANUFACTURER_ADDRESS

11. 3NF solution • Table 1: • MANUFACTURER_NAME (PRIMARY KEY) • MANUFACTURER_ADDRESS • Table 2: • PART_NUMBER (PRIMARY KEY) • MANUFACTURER_NAME (FOREIGN KEY)

12. BCNF • the key uniquely identifies a row, but the key includes more columns than are actually required to uniquely identify a row, then no good • Consider: CREATE TABLE t_employees1 ( employee_id INT IDENTITY, last_name VARCHAR(25) NOT NULL, first_name VARCHAR(25) NOT NULL CONSTRAINT XPKt_employees1 PRIMARY KEY (employee_id, last_name, first_name))

13. BCNF solution CREATE TABLE t_employees1 ( employee_id INT IDENTITY, last_name VARCHAR(25) NOT NULL, first_name VARCHAR(25) NOT NULL CONSTRAINT XPKt_employees1 PRIMARY KEY (employee_id))

14. 4NF • Meet all the requirements of the third normal form. • A relation is in 4NF if it has no multi-valued dependencies • Problem table: (employees have different combinations of qualifications and training courses) • EMPLOYEE_ID • QUALIFICATION_ID • TRAINING_COURSE_ID

15. 4NF Solution • employee_qualification table: • EMPLOYEE_ID • QUALIFICATION_ID • employee_training_course table: • EMPLOYEE_ID • TRAINING_COURSE_ID • But this is ok: • EMPLOYEE_ID • DEGREE_ID • UNIVERSITY_ID

16. Golden rules • Don’t repeat data • Watch empty fields • Don’t have summary fields in tables – leave them in the detail and calculate them • Every field should rely on the full primary key