lecture29

Download
Share  Add to  Flag Embed
Uploaded from authorPOINTLite
Views:
 
Category: Entertainment
     
 
License:   All Rights Reserved

Presentation Description

No description available.

Comments

 

By: asjd (15 month(s) ago)

pls send me the copy of presentation or link

 

By: yaghojo (25 month(s) ago)

Its usefull presentation ... Please I need to download it

 

By: sudhanshu_nigam (26 month(s) ago)

Very good presentaton...fullfill all requirements and information .Thank you

 

By: k8080 (36 month(s) ago)

thanks alot

Presentation Transcript

Slide1: 

Theory, Practice & Methodology of Relational Database Design and Programming Copyright © Ellis Cohen 2002-2007 Basic Normal Forms These slides are licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. For more information on how you may use them, please see http://www.openlineconsult.com/db

Overview of Lecture: 

Overview of Lecture Normal Forms 1st Normal Form 2nd Normal Form Conceptual 2NF Normalization PURE 3rd Normal Form MIXED 3rd Normal Form Factoring & MIXED 3rd Normal Form Normalization with Nested Determinants Normalization with Overlapping Determinants

Normal Forms: 

Normal Forms Normal Forms 1NF, 2NF, 3NF, BCNF, 4NF, 5NF(PJNF) Property of a table From 2NF on Each one defines a new kind of non-redundancy requirement Each one requires the previous one To deal with a relation that fails to satisfy a particular normal form: the relation is decomposed in a particular way

Slide4: 

1st Normal Form

1NF: 1st Normal Form: 

1NF: 1st Normal Form All fields in a relation must be atomic 1NF is not about Redundancy, but about ensuring that entity classes / relations are in a form that we can easily normalize (A form of normalization can actually be defined for relations with non-atomic field, but it is more complicated) empno … kids Emps

Relational 1NF Normalization: 

Relational 1NF Normalization empno ename Employees empno childnam Children Children empno childnam Empsnew empno ename Empsold empno ename *kids Normalize by 1) Remove multivalued attribute from relation 2) Create new relation with composite key: single-valued version of attribute + original relation's primary key What's the corresponding conceptual normalization?

Conceptual 1NF Normalization: 

Conceptual 1NF Normalization Normalize By Remove multivalued attribute from entity class Add a weak entity class dependent upon the original class with the single-valued version of the attribute Employee empno ename Child childnam has Employee empno ename *kids Multivalued

Slide8: 

2nd Normal Form

2NF: 2nd Normal Form : 

2NF: 2nd Normal Form No partial key dependencies: The determinants of a non-prime attribute CANNOT consist of only part of a candidate key Entries( invid, linenum, customer, prodid ) invid  customer prime attributes non-prime attributes invid linenum customer prodid Entries

2NF Violation Diagram: Partial Key Violation : 

2NF Violation Diagram: Partial Key Violation prime attributes non-prime attributes Candidate key linenum customer Determinant(s) invid

Comparing NF Diagrams : 

Comparing NF Diagrams prime attributes non-prime attributes Candidate key linenum customer Determinant(s) invid linenum prodid invid 2NF Violation In 2NF

Relational 2NF Normalization: 

Relational 2NF Normalization invid customer Invoices Invoices invid customer Entriesnew invid linenum prodid Entriesold invid linenum customer prodid Normalize by 1) Create a new relation with the determinant and everything dependent upon it 2) Remove everything dependent upon the determinant from the original relation 3) Add foreign key constraint, possibly w cascading delete invid linenum prodid Entries invid  customer

Corresponding Conceptual Model: 

Corresponding Conceptual Model Owner Entry invid customer linenum prodid Invoices invid customer Entries invid linenum prodid

Normalization Exercise: 

Normalization Exercise Original Table: Dog( ssno, dogname, dogdob, oaddr, ophone ) ssno soc sec# of dog's owner dogname name of dog dogdob date of birth of dog oaddr addr of the owner ophone phone # of the owner What are the candidate keys? What are the prime attributes? What FD's are determined by a candidate key? What FD's are not determined by a candidate key?

Normalization Answer: FD's: 

Normalization Answer: FD's Candidate Keys ssno + dogname Prime Attributes ssno, dogname Candidate Key FD's ssno + dogname  dogdob Non Candidate Key FD's ssno  oaddr, ophone Normalize!

Normalization Answer: Design: 

Normalization Answer: Design What's the corresponding conceptual model? Dogs ssno dogname dogdob Owners ssno oaddr ophone

Normalized Conceptual Model: 

Normalized Conceptual Model Dogs ssno dogname dogdob Owners ssno oaddr ophone Owner Dog ssno oaddr ophone dogname dogdob

Slide18: 

Conceptual 2NF Normalization

Conceptual 2NF Normalization: 

Conceptual 2NF Normalization Partial key violations can be seen and fixed during Conceptual Modeling. Given the following entity class and FD, how should the entity class be decomposed? Entry invid linenum customer prodid invid  customer

Conceptual 2NF Normalization: 

Conceptual 2NF Normalization Entry invid linenum customer prodid Invoice Entry invid customer linenum prodid Normalize By Move determinant and everything dependent on it to a new entity class. The original class becomes a weak entity class, with the remaining parts of the key as the discriminator invid  customer invid + linenum  prodid invid  customer What's the corresponding relational normalization?

Relational 2NF Normalization: 

Relational 2NF Normalization Invoices invid customer Entriesnew invid linenum prodid Entriesold invid linenum customer prodid Normalize by 1) Create a new relation with the determinant and everything dependent upon it 2) Remove everything dependent upon the determinant from the original relation 3) Add foreign key constraint, possibly w cascading delete invid  customer

Another Normalization Exercise: 

Another Normalization Exercise Original Entity Class Assign( empno, ename, pno, pname, taskid, tname, startdate, hrsPerWeek ) Minimal Non-Candidate Key FD's empno + pno  startdate empno  ename pno  pname taskid  tname Start by doing 2NF conceptual normalization based on this FD In many cases, the entire normalization can be done using the conceptual model

Initial Normalization Step: 

Initial Normalization Step ProjAssign Assign owned by empno ename pno pname startdate taskid tname hrsPerWeek Now normalize Assign based on taskid  tname This is also 2NF, because taskid is a discriminator, and only part of the primary key empno + pno  startdate empno + pno + taskid  hrsPerWeek

Normalization by Task: 

Normalization by Task ProjAssign Assign empno ename pno pname startdate hrPerWeek Now normalize based on empno  ename Task taskid tname empno + pno  startdate empno + pno + taskid  hrsPerWeek taskid  tname

Normalization by Employee: 

Normalization by Employee ProjAssign pno pname startdate Employee empno ename empno + pno + taskid  hrsPerWeek taskid  tname Assign hrPerWeek Task taskid tname Now normalize based on pno  pname

Complete Normalization: 

Complete Normalization ProjAssign startdate Employee empno ename Project pno pname empno + pno + taskid  hrsPerWeek taskid  tname Assign hrPerWeek Task taskid tname

Slide27: 

PURE 3rd Normal Form

2NF & 3NF Normalizations: 

2NF & 3NF Normalizations The decomposition rules are Create a new relation/class with the determinant and everything dependent upon it Relational: Remove everything dependent upon the determinant from the original relation Conceptual: Remove the determinant and everything dependent upon it from the original class Each normal form variant results in a different design pattern Relational fingerprints vary wrt resulting primary and foreign keys Conceptual models vary wrt primary keys, discriminators, and kinds of relationships

3NF: 3rd Normal Form: 

3NF: 3rd Normal Form A table is in 3NF if every non-prime attribute is minimally determined by a candidate key If not, we have 2NF Violation a non-prime attribute is determined by only part of a candidate key 3NF-ONLY Violations the determinant of a non-prime attribute includes some other non-prime attribute

2NF/Pure 3NF Violations: 

2NF/Pure 3NF Violations prime attributes non-prime attributes C A B X 2NF Partial Key Violation 3NF Transitivity Violation X B A Candidate Key Pure

Pure 3NF Violation Example: 

Pure 3NF Violation Example prime attributes non-prime attributes deptno dname empno empno ename address deptno dname Emps

Relational Pure 3NF Normalization: 

Relational Pure 3NF Normalization Normalize by 1) Create a new relation with the determinant and everything dependent upon it 2) Remove everything dependent upon the determinant from the original relation 3) Add foreign key constraint, possibly w cascading delete empno ename address deptno dname Empsold deptno  dname Empsnew empno ename address deptno Depts deptno dname

Corresponding Conceptual Model: 

Corresponding Conceptual Model Employee Dept empno ename address deptno dname Emps empno ename address deptno Depts deptno dname

Another Pure 3NF Example: 

Another Pure 3NF Example Video( vidid, title, year, custid, studio ) title + year  studio vidid title year custid studio Videos prime attributes non-prime attributes

Corresponding Conceptual Model: 

Corresponding Conceptual Model Films title year studio Videos vidid title year custid Video Film vidid custid title year studio

Conceptual Pure 3NF Normalization: 

Conceptual Pure 3NF Normalization Video vidid title year custid studio Normalize by Move determinant and everything dependent on it to a new entity class. The original entity class becomes the child entity class in a 1:M relationship with the new class Video Film vidid custid title year studio Child entity class Parent entity class vidid  custid title + year  studio vidid  title, year Note how FD's become unique or key constraints What about Video's lifetime?

Deciding Lifetime Dependency: 

Deciding Lifetime Dependency Video Film is a copy of a vidid custid title year studio Film Video title year studio vidid custid is a copy of a Should we treat Video as a strong entity class, or as a dependent entity class (i.e. with a lifetime dependency constraint) Normalization doesn't tell us what to do here. This is an independent design issue.

Slide38: 

MIXED 3rd Normal Form

2NF/3NF Violation Diagrams: 

2NF/3NF Violation Diagrams prime attributes non-prime attributes C A B X 2NF Partial Key Violation 3NF Transitivity Violation X X B C A Pure Mixed B A

MIXED 3NF Violation Example: 

MIXED 3NF Violation Example prime attributes non-prime attributes divnam deptno dname empno MIXED Emps divnam empno ename deptno dname divnam + deptno  dname

3NF: 3rd Normal Form (MIXED): 

3NF: 3rd Normal Form (MIXED) Mixed Transitivity Violation: The determinants are a mixture of prime and non-prime attributes Emp( divnam, empno, ename, deptno, dname ) divnam + deptno  dname divnam empno ename deptno dname Emps prime attributes non-prime attributes

Relational Mixed 3NF Normalization: 

Relational Mixed 3NF Normalization Depts divnam deptno dname Empsnew divnam empno ename deptno Empsold divnam empno ename deptno dname Normalize by 1) Create a new relation with the determinant and everything dependent upon it 2) Remove everything dependent upon the determinant from the original relation 3) Add foreign key constraint, possibly w cascading delete divnam + deptno  dname What's the corresponding conceptual model?

Potential Weak Entity Class Model : 

Potential Weak Entity Class Model Dept Employee divnam deptno dname empno ename Depts Emps Does this conceptual model correspond? divnam deptno dname divnam empno ename deptno

Weak Entity Class Model Dependency: 

Weak Entity Class Model Dependency Dept Employee divnam deptno dname empno ename This model implies that an employee is identified relative to to a Dept Emps divnam empno ename deptno But the result of normalization identifies an employee relative to a Division

Relational Mapping : 

Relational Mapping Dept Employee divnam deptno dname empno ename deptno is also part of the primary key Can we remove deptno as part of the primary key? Depts Emps divnam deptno dname divnam empno ename deptno

Shrinking Primary Key via Constraints : 

Shrinking Primary Key via Constraints If we can ensure that divnam + empno is a candidate key then deptno does not need to be part of Emps' primary key. So, we can use the ER model below so long as we add the conceptual state constraint: Within any given division, employee numbers are unique. This means that an Employee is uniquely identified by divnam + empno, so deptno is not needed in the primary key Depts divnam deptno dname Emps divnam empno ename deptno Dept Employee divnam deptno dname empno ename

Conceptual Mixed 3NF Normalization: 

Conceptual Mixed 3NF Normalization Employee divnam empno ename deptno dname Normalize by Move determinant and everything dependent on it to a new entity class. The original class becomes a weak entity class, with the remaining parts of the key as the discriminator divnam + empno  ename divnam + deptno  dname divnam + empno  deptno Note how FD's become unique or key constraints Dept Employee divnam deptno dname empno ename + A dept with employees can't be deleted

Slide48: 

Factoring & MIXED 3rd Normal Form

Potential 1:M Model : 

Potential 1:M Model Depts divnam deptno dname Emps divnam empno ename deptno Dept Employee divnam deptno dname divnam empno ename Does this conceptual model correspond?

Actual Relational Mapping : 

Actual Relational Mapping Dept Employee divnam deptno dname divnam empno ename Depts divnam deptno dname Emps divnam empno ename ddivnam deptno Nothing ensures that an employee is in the same division as the employee's department! Can we fix this by adding a state constraint?

Constrained Relational Mapping : 

Constrained Relational Mapping + Conceptual State Constraint: an employee is in the same division as the employee's department Dept Employee divnam deptno dname divnam empno ename Depts divnam deptno dname Emps divnam empno ename deptno

Adding a Division Class : 

Adding a Division Class Dept Employee divnam deptno dname divnam empno ename Division divnam Because divnam shows up in both Employee & Dept, it suggests that it should be represented by its own Division Class Are there any problems with this model?

Induced Entity Attributes : 

Induced Entity Attributes Dept Employee divnam deptno dname divnam empno ename Division divnam The divnam attributes in Employee and Dept are both now illegal entity attributes.

Using Weak Entity Classes: 

Using Weak Entity Classes works for empno ename deptno dname Division divnam Dept Employee + Conceptual State Constraint: an employee is in the same division as the employee's department This constraint can be depicted directly on the ER diagram!

Using Factored Relationships: 

Using Factored Relationships (by Division) Factored Relationship: An employee of a given division works for a department in the same division works for empno ename deptno dname Division divnam Dept Employee Crow Magnum

Containment Model for Factoring: 

Containment Model for Factoring Division Everything inside the outer box holds for a single division at a time An employee of a given division works for a department in the same division divnam works for empno ename deptno dname Dept Employee has* has* An employee is identified wrt a division A division has many (*) employees Underlining indicates an identifying relationship with Division Crow Magnum

Containment Model in UML: 

Containment Model in UML Employee* DK empno ename Division PK divnam Not quite standard due to DK & because UML doesn't allow attributes in containment diagrams Dept* DK deptno dname * 0..1

Slide58: 

Normalization with Nested Determinants

Conceptual Normalization: 

Conceptual Normalization Normalization can be used initially at the conceptual level to improve designs. However, when there are multiple related composite functional dependencies, relational normalization may still be required (though can sometimes be avoided by doing conceptual normalizations in the right order)

Nested Determinants: 

Nested Determinants Video( vidid, acqdate, empno, ename, renttime, custid, cphone ) Candidate Keys: vidid Minimal Non Key FD's empno  ename custid  cphone custid + renttime  empno start with these Do Conceptual Normalization custid custid + renttime Nested Determinants

Split out Customer & Employee: 

Split out Customer & Employee Video Employee vidid acqdate renttime empno ename Customer custid cphone empno  ename custid  cphone Simple 3NF Transitivity Violations No further conceptual normalization is possible Draw the Relational Schema

Split out Customer & Employee: 

Split out Customer & Employee Video Employee vidid acqdate renttime empno ename Customer custid cphone custid + renttime  empno is a 3NF violation only in the Relational Model. Resolve it using Relational Normalization vidid acqdate custid renttime empno Videos Employees empno ename Customers custid cphone

Relational Splicing: 

Relational Splicing vidid acqdate custid renttime empno Videos Employees empno ename Customers custid cphone Rentals custid renttime empno custid + rentime  empno Pure 3NF Violation Normalization tells us how to decompose, but not how to connect (w FK constraints). In this case, we had to splice Rentals in between Customers & Employees

Alternate Decomposition: 

Alternate Decomposition Video( vidid, acqdate, empno, ename, renttime, custid, cphone ) Candidate Keys: vidid Minimal Non Key FD's empno  ename custid  cphone custid + renttime  empno empno + renttime  custid start with these Do Conceptual Normalization custid custid + renttime Nested Determinants

Split out Rental: 

Split out Rental Video vidid acqdate Rental custid renttime empno cphone ename Now decompose using empno  ename custid  cphone

Complete Conceptual Normalization: 

Complete Conceptual Normalization Video vidid acqdate Rental custid renttime empno cphone ename Video Employee vidid acqdate empno ename Customer custid cphone Rental renttime Splitting Rental out first allows a complete Conceptual Normalization via 2NF via Pure 3NF Method: Decompose first using FD's with largest # of determinant fields e.g. custid + renttime  empno Now decompose using empno  ename custid  cphone

Slide67: 

Normalization with Overlapping Determinants

Overlapping Determinants: 

Overlapping Determinants Assign ( empno, pno, task, startdate, payrate ) Candidate Keys: empno + pno + task Minimal Non Key FDs: empno + pno  startdate empno + task  payrate Do Conceptual Normalization starting with either FD empno + pno empno + task Overlapping Determinants

Incomplete Conceptual Normalization: 

Incomplete Conceptual Normalization Assign ProjAssign task payrate empno pno startdate empno + pno  startdate Assign TaskAssign pno startdate empno task payrate empno + task  payrate Starting with either FD resolves a 2NF violation. Either result is in Conceptual Normal Form, but does not result in a complete normalization Pick either one, map to a Relational Schema, and complete the relational normalization

Complete Relational Normalization: 

Complete Relational Normalization empno pno task Assigns ProjAssigns empno pno startdate empno task payrate TaskAssigns Reverse Engineer the corresponding Conceptual Diagram Hint: Look at Conceptual Normalization of Mixed 3NF Violations

Factored Relational Model: 

Factored Relational Model empno pno task Assigns ProjAssigns empno pno startdate empno task payrate TaskAssigns Factor out empno What's left is an M:N relationship between ProjAssigns & TaskAssigns

Factored Normalization Answer: 

Factored Normalization Answer pno startdate task payrate TaskAssign ProjAssign Employee Everything inside the outer box holds for a single employee at a time For any given employee, there is a many-many relationship between their projects and their tasks empno has* has* corresponds to

Using Factored Relationships: 

Using Factored Relationships pno startdate task payrate Employee empno TaskAssign ProjAssign (by Employee) corresponds to For any given employee, there is a many-many relationship between their projects and their tasks

Normalization Methodology: 

Normalization Methodology Based on your knowledge, expertise, wisdom & prescience, design the best conceptual model you can Do conceptual normalization. If an entity class has two FDs with nested determinants, resolve the FD with the larger determinant first If determinants overlap, conceptual normalization will not completely normalize a design Map to a relational schema Do relational normalization