chapter 13 normalization transparencies. 2 chapter 13 - objectives u how to undertake process of...
TRANSCRIPT
![Page 1: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/1.jpg)
Chapter 13
Normalization
Transparencies
![Page 2: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/2.jpg)
2
Chapter 13 - Objectives
How to undertake process of normalization.
How to identify most commonly used normal forms, namely 1NF, 2NF, 3NF
![Page 3: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/3.jpg)
3
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 4: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/4.jpg)
4
Example - Functional Dependency
![Page 5: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/5.jpg)
5
StaffNo sName, position, salary, branchNo, bAddressbranchNo bAddressbAddress branchNobranchNo, position salarybAddress, position salary
![Page 6: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/6.jpg)
6
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 7: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/7.jpg)
7
Relationship Between Normal Forms
![Page 8: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/8.jpg)
8
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 9: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/9.jpg)
9
First Normal Form (1NF)
A relation in which intersection of each row and column contains one and only one value.
![Page 10: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/10.jpg)
10
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 11: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/11.jpg)
11
UNF to 1NF
Remove repeating group by:– entering appropriate data into the empty
columns of rows containing repeating data (‘flattening’ the table).
Or by– placing repeating data along with copy of
the original key attribute(s) into a separate relation.
![Page 12: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/12.jpg)
12
1st Normal From
![Page 13: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/13.jpg)
13
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.
![Page 14: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/14.jpg)
14
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 15: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/15.jpg)
15
Partial Dependency
A,B C, D, E B C
D is partially depend on B
![Page 16: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/16.jpg)
16
Convert 1st Normal form to 2nd Normal Form
Table (A, B, C, D, E) A, B C, D, E B C
Table_1 (A, B, D, E) Table_2 (B, C)
There is no partial dependency in Table_1 and Table_2.
![Page 17: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/17.jpg)
17
Example: The CAR relation schema:
CAR(State, Reg#, SerialNo, Make, Model, Year)
has two keys
Key1 = {State, Reg#}, Key2 = {SerialNo},
which are also superkeys. {SerialNo, Make} is a superkey but not a key.
Is there a partial dependency?
![Page 18: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/18.jpg)
18
Example
clientNo CfName ClName propertyNo viewDate comment
CR56 Aline Stewart PA14 5/24/2001 too small
CR56 Aline Stewart PG36 4/28/2001
CR56 Aline Stewart PG4 5/26/2001
CR62 Mary Tiregear PA14 5/14/2001 no dining room
CR74 Mike Richie PG4 4/21/2001
CR76 John Kay PG4 4/20/2001 too remote
Primary key: Functional Dependencies:
![Page 19: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/19.jpg)
19
ClientNo CfName ClName
ClientNo propertyNo viewDate comment
Viewing
Client
2nd Normal Form
![Page 20: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/20.jpg)
20
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 21: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/21.jpg)
21
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 22: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/22.jpg)
22
Transitive Dependency
Table (A, B, C, D, E)
A, B C, D, E D E A, B D E (Transitivity)
![Page 23: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/23.jpg)
23
3rd Normal Form
Table (A, B, C, D, E) A, B C, D, E B C (Partial Dependency) D E (Transitive Dependency) Table_1 (A, B, D, E) Table_2 (B, C)
Table_1 (A, B, D) Table_3 (D, E) Table_2 (B, C)
2nd Normal Form
3rd Normal Form
![Page 24: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/24.jpg)
24
StaffNo sName, position, salary, branchNo, bAddressbranchNo bAddressbAddress branchNobranchNo, position salarybAddress, position salary
Transitive dependency
Primary Key:
![Page 25: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/25.jpg)
25
3rd Normal Form
![Page 26: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/26.jpg)
26
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 27: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/27.jpg)
27
Review of Normalization (UNF to 3rd Normal Form)
![Page 28: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/28.jpg)
28
Review of Normalization (UNF to 3rd)
![Page 29: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/29.jpg)
29
Review of Normalization (UNF to BCNF)
![Page 30: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/30.jpg)
30
Review of Normalization (UNF to BCNF)
![Page 31: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/31.jpg)
31
2nd normal Form
PropertyNo pAddresses
properyNo iDate iTime comments staffNo sName carReg
PROPERTY
INSPECTION/STAFF
![Page 32: Chapter 13 Normalization Transparencies. 2 Chapter 13 - Objectives u How to undertake process of normalization. u How to identify most commonly used normal](https://reader035.vdocuments.us/reader035/viewer/2022062301/5697bf701a28abf838c7da1e/html5/thumbnails/32.jpg)
32
3rd Normal Form
PropertyNo pAddresses
PROPERTY
properyNo iDate iTime comments
staffNo carReg
INSPECTION
staffNo sName
STAFF