9/23/2012isc329 isabelle bichindaritz1 normalization
TRANSCRIPT
![Page 1: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/1.jpg)
ISC329 Isabelle Bichindaritz 19/23/2012
Normalization
![Page 2: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/2.jpg)
ISC329 Isabelle Bichindaritz 29/23/2012
Learning Objectives• Define what is normalization and its
purpose in database design• Differentiate between the types of normal
forms 1NF, 2NF, 3NF, BCNF, and 4NF • Transform from lower normal forms to
higher normal forms• Use concurrently normalization and E-R
modeling to produce a good database design
• Practice the usefulness of denormalization to generate information efficiently
![Page 3: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/3.jpg)
ISC329 Isabelle Bichindaritz 39/23/2012
Acknowledgments
• Some of these slides have been adapted from Thomas Connolly and Carolyn Begg
![Page 4: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/4.jpg)
ISC329 Isabelle Bichindaritz 49/23/2012
Normalization• Main objective in developing a logical
data model for relational database systems is to create an accurate representation of the data, its relationships, and constraints.
• To achieve this objective, must identify a suitable set of relations.
![Page 5: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/5.jpg)
ISC329 Isabelle Bichindaritz 59/23/2012
Normalization
• Four most commonly used normal forms are first (1NF), second (2NF) and third (3NF) normal forms, and Boyce–Codd normal form (BCNF).
• Based on functional dependencies among the attributes of a relation.
• A relation can be normalized to a specific form to prevent possible occurrence of update anomalies.
![Page 6: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/6.jpg)
ISC329 Isabelle Bichindaritz 69/23/2012
Normalization
• Normalization is the process for assigning attributes to entities– Reduces data redundancies– Helps eliminate data anomalies– Produces controlled redundancies to link tables
• Normalization stages– 1NF - First normal form– 2NF - Second normal form– 3NF - Third normal form– 4NF - Fourth normal form
![Page 7: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/7.jpg)
ISC329 Isabelle Bichindaritz 79/23/2012
Data Redundancy• Major aim of relational database design is to
group attributes into relations to minimize data redundancy and reduce file storage space required by base relations.
• Problems associated with data redundancy are illustrated by comparing the following Staff and Branch relations with the StaffBranch relation.
![Page 8: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/8.jpg)
ISC329 Isabelle Bichindaritz 89/23/2012
Data Redundancy
![Page 9: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/9.jpg)
ISC329 Isabelle Bichindaritz 99/23/2012
Data Redundancy
• StaffBranch relation has redundant data: details of a branch are repeated for every member of staff.
• In contrast, branch information appears only once for each branch in Branch relation and only branchNo is repeated in Staff relation, to represent where each member of staff works.
![Page 10: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/10.jpg)
ISC329 Isabelle Bichindaritz 109/23/2012
Update Anomalies
• Relations that contain redundant information may potentially suffer from update anomalies.
• Types of update anomalies include:– Insertion,– Deletion,– Modification.
![Page 11: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/11.jpg)
ISC329 Isabelle Bichindaritz 119/23/2012
Functional Dependency• Main concept associated with normalization.
• Functional Dependency– Describes relationship between attributes in a
relation. – If A and B are attributes of relation R, B is
functionally dependent on A (denoted A B), if each value of A in R is associated with exactly one value of B in R.
![Page 12: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/12.jpg)
ISC329 Isabelle Bichindaritz 129/23/2012
Functional Dependency• Property of the meaning (or semantics)
of the attributes in a relation.
• Diagrammatic representation:
Determinant of a functional dependency refers to attribute or group of attributes on left-hand side of the arrow.
![Page 13: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/13.jpg)
ISC329 Isabelle Bichindaritz 139/23/2012
Example - Functional Dependency
![Page 14: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/14.jpg)
ISC329 Isabelle Bichindaritz 149/23/2012
Functional Dependency• Main characteristics of functional
dependencies used in normalization:
– have a 1:1 relationship between attribute(s) on left and right-hand side of a dependency;
– hold for all time;– are nontrivial.
![Page 15: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/15.jpg)
ISC329 Isabelle Bichindaritz 159/23/2012
Functional Dependency • Complete set of functional dependencies for a
given relation can be very large.
• Important to find an approach that can reduce set to a manageable size.
• Need to identify set of functional dependencies (X) for a relation that is smaller than complete set of functional dependencies (Y) for that relation and has property that every functional dependency in Y is implied by functional dependencies in X.
![Page 16: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/16.jpg)
ISC329 Isabelle Bichindaritz 169/23/2012
Dependency Diagram (1NF)
![Page 17: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/17.jpg)
ISC329 Isabelle Bichindaritz 179/23/2012
The Process of Normalization
• Formal technique for analyzing a relation based on its primary key and functional dependencies between its attributes.
• Often executed as a series of steps. Each step corresponds to a specific normal form, which has known properties.
• As normalization proceeds, relations become progressively more restricted (stronger) in format and also less vulnerable to update anomalies.
![Page 18: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/18.jpg)
ISC329 Isabelle Bichindaritz 189/23/2012
Relationship Between Normal Forms
![Page 19: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/19.jpg)
ISC329 Isabelle Bichindaritz 199/23/2012
Unnormalized Form (UNF)
• A table that contains one or more repeating groups.
• To create an unnormalized table: – transform data from information source
(e.g. form) into table format with columns and rows.
![Page 20: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/20.jpg)
ISC329 Isabelle Bichindaritz 209/23/2012
First Normal Form (1NF)
• A relation in which intersection of each row and column contains one and only one value.
![Page 21: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/21.jpg)
ISC329 Isabelle Bichindaritz 219/23/2012
UNF to 1NF• Nominate an attribute or group of
attributes to act as the key for the unnormalized table.
• Identify repeating group(s) in unnormalized table which repeats for the key attribute(s).
![Page 22: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/22.jpg)
ISC329 Isabelle Bichindaritz 229/23/2012
UNF to 1NF
• All key attributes defined• No repeating groups in table• All attributes dependent on
primary key
![Page 23: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/23.jpg)
ISC329 Isabelle Bichindaritz 239/23/2012
Second Normal Form (2NF)
• Based on concept of full functional dependency:– A and B are attributes of a relation, – B is fully dependent on A if B is functionally
dependent on A but not on any proper subset of A.
• 2NF - A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key (no partial dependency)
![Page 24: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/24.jpg)
ISC329 Isabelle Bichindaritz 249/23/2012
1NF to 2NF• Identify primary key for the 1NF
relation.
• Identify functional dependencies in the relation.
• If partial dependencies exist on the primary key remove them by placing them in a new relation along with copy of their determinant.
![Page 25: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/25.jpg)
ISC329 Isabelle Bichindaritz 259/23/2012
2NF Conversion ResultsFigure 4.5
![Page 26: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/26.jpg)
ISC329 Isabelle Bichindaritz 269/23/2012
Third Normal Form (3NF)
• Based on concept of transitive dependency:– A, B and C are attributes of a relation such that if A B
and B C, – then C is transitively dependent on A through B.
(Provided that A is not functionally dependent on B or C).
• 3NF - A relation that is in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on the primary key.
![Page 27: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/27.jpg)
ISC329 Isabelle Bichindaritz 279/23/2012
2NF to 3NF• Identify the primary key in the 2NF
relation.
• Identify functional dependencies in the relation.
• If transitive dependencies exist on the primary key remove them by placing them in a new relation along with copy of their determinant.
![Page 28: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/28.jpg)
ISC329 Isabelle Bichindaritz 289/23/2012
3NF Conversion Results
• Prevent referential integrity violation by adding a JOB_CODE
PROJECT (PROJ_NUM, PROJ_NAME)ASSIGN (PROJ_NUM, EMP_NUM, HOURS)EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)JOB (JOB_CODE, JOB_DESCRIPTION, CHG_HOUR)
![Page 29: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/29.jpg)
ISC329 Isabelle Bichindaritz 299/23/2012
General Definitions of 2NF and 3NF
• Second normal form (2NF)– A relation that is in 1NF and every non-
primary-key attribute is fully functionally dependent on any candidate key.
• Third normal form (3NF)– A relation that is in 1NF and 2NF and in
which no non-primary-key attribute is transitively dependent on any candidate key.
![Page 30: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/30.jpg)
ISC329 Isabelle Bichindaritz 309/23/2012
Boyce–Codd Normal Form (BCNF)
• Based on functional dependencies that take into account all candidate keys in a relation, however BCNF also has additional constraints compared with general definition of 3NF.
• BCNF - A relation is in BCNF if and only if every determinant is a candidate key.
![Page 31: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/31.jpg)
ISC329 Isabelle Bichindaritz 319/23/2012
Boyce–Codd normal form (BCNF)
• Difference between 3NF and BCNF is that for a functional dependency A B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key.
• Whereas, BCNF insists that for this dependency to remain in a relation, A must be a candidate key.
• Every relation in BCNF is also in 3NF. However, relation in 3NF may not be in BCNF.
![Page 32: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/32.jpg)
ISC329 Isabelle Bichindaritz 329/23/2012
Boyce–Codd normal form (BCNF) • Violation of BCNF is quite rare.
• Potential to violate BCNF may occur in a relation that:– contains two (or more) composite candidate
keys;– the candidate keys overlap (i.e. have at least
one attribute in common).
![Page 33: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/33.jpg)
ISC329 Isabelle Bichindaritz 339/23/2012
3NF Table Not in BCNF
Figure 4.7
![Page 34: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/34.jpg)
ISC329 Isabelle Bichindaritz 349/23/2012
Decomposition of Table Structure to Meet BCNF
![Page 35: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/35.jpg)
ISC329 Isabelle Bichindaritz 359/23/2012
BCNF Conversion Results
![Page 36: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/36.jpg)
ISC329 Isabelle Bichindaritz 369/23/2012
Review of Normalization (UNF to BCNF)
![Page 37: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/37.jpg)
ISC329 Isabelle Bichindaritz 379/23/2012
Review of Normalization (UNF to BCNF)
![Page 38: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/38.jpg)
ISC329 Isabelle Bichindaritz 389/23/2012
Review of Normalization (UNF to BCNF)
![Page 39: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/39.jpg)
ISC329 Isabelle Bichindaritz 399/23/2012
Review of Normalization (UNF to BCNF)
![Page 40: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/40.jpg)
ISC329 Isabelle Bichindaritz 409/23/2012
Fourth Normal Form (4NF) • Although BCNF removes anomalies due to
functional dependencies, another type of dependency called a multi-valued dependency (MVD) can also cause data redundancy.
• Possible existence of MVDs in a relation is due to 1NF and can result in data redundancy.
![Page 41: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/41.jpg)
ISC329 Isabelle Bichindaritz 419/23/2012
Fourth Normal Form (4NF) - MVD• Dependency between attributes (for
example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, set of values for B and C are independent of each other.
![Page 42: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/42.jpg)
ISC329 Isabelle Bichindaritz 429/23/2012
Fourth Normal Form (4NF)• MVD between attributes A, B, and C in a
relation using the following notation:A ¾¾ØØ B
A ¾¾ØØ C
![Page 43: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/43.jpg)
ISC329 Isabelle Bichindaritz 439/23/2012
Fourth Normal Form (4NF)• MVD can be further defined as being trivial or
nontrivial. – MVD A ¾¾ØØ B in relation R is defined as
being trivial if (a) B is a subset of A or (b) A B = R.
– MVD is defined as being nontrivial if neither (a) nor (b) are satisfied.
– Trivial MVD does not specify a constraint on a relation, while a nontrivial MVD does specify a constraint.
![Page 44: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/44.jpg)
ISC329 Isabelle Bichindaritz 449/23/2012
Fourth Normal Form (4NF)• Defined as a relation that is in BCNF and
contains no nontrivial MVDs.
![Page 45: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/45.jpg)
ISC329 Isabelle Bichindaritz 459/23/2012
4NF - Example
![Page 46: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/46.jpg)
ISC329 Isabelle Bichindaritz 469/23/2012
3NF Table Not in BCNF
Figure 4.7
![Page 47: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/47.jpg)
ISC329 Isabelle Bichindaritz 479/23/2012
Decomposition of Table Structure to Meet BCNF
![Page 48: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/48.jpg)
ISC329 Isabelle Bichindaritz 489/23/2012
Decomposition into BCNF
Figure 4.9
![Page 49: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/49.jpg)
ISC329 Isabelle Bichindaritz 499/23/2012
4NF Conversion Results
Multivalued Dependencies (an employee can work for many services and on many projects
Set of Tables in 4NF
![Page 50: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/50.jpg)
ISC329 Isabelle Bichindaritz 509/23/2012
Denormalization• Normalization is one of many database design
goals • Normalized table requirements
– Additional processing– Loss of system speed
• Normalization purity is difficult to sustain due to conflict in:– Design efficiency– Information requirements– Processing
![Page 51: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/51.jpg)
ISC329 Isabelle Bichindaritz 519/23/2012
Unnormalized Table Defects• Data updates less efficient• Indexing more cumbersome• No simple strategies for creating views
![Page 52: 9/23/2012ISC329 Isabelle Bichindaritz1 Normalization](https://reader034.vdocuments.us/reader034/viewer/2022051316/5697bf701a28abf838c7da4b/html5/thumbnails/52.jpg)
ISC329 Isabelle Bichindaritz 529/23/2012
Summary• We will use normalization in database
design to create a set of relations in 3FN normal form:– Each entity has a unique primary key, and each
attribute depends upon the primary key– No partial dependency– No transitive dependency