1 / 16

Normalization Lite

Normalization Lite. Pepper. Golden Rule. Every attribute must depend upon the key, --- > 1NF the whole key, --- > 2NF and nothing but the key. -  3NF and BCNF. Forms. Codd stated:

mateja
Download Presentation

Normalization Lite

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related