slide 1 chapter 03 the relational model and normalization
TRANSCRIPT
![Page 1: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/1.jpg)
Slide 1
Chapter 03
The Relational Model and Normalization
![Page 2: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/2.jpg)
Slide 2
Content
A. Sales Receipt ProblemB. Solution
![Page 3: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/3.jpg)
Slide 3
A. Sales Receipt Problem
DS company wants to store information of receipt for each selling time.
Each receipt has following information: Receipt ID Customer ID Customer Name Customer Address
Date of Receipt Each receipt has one or more receipt items which have
following information: Product ID
Product NameQuantityPrice Line Total
For more information about a receipt, please refer to next slide:
![Page 4: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/4.jpg)
Slide 4
Let’s design the DB into Normal Form for the above requirement.
Next slides are some examples of receipt.
Line TotalPriceQuantityNameProduct ID
DS Company
ID: …… RECEIPTDate: ……….
Customer Name: …………………Customer ID: …………Address: ……………………………………………………..
Total
![Page 5: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/5.jpg)
Slide 5
Example 1:
Line TotalPriceQuantityNameProduct ID
DS Company
ID: PN001 RECEIPTDate: 04/12/2008.
Customer Name: Mr. Johnson. Customer ID: CUS001Address: New York
Coca Cola 100 100 10000 USD
PRO001
Total 10000 USD
![Page 6: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/6.jpg)
Slide 6
Example 2:
PRO002
Line TotalPriceQuantityNameProduct ID
DS Company
ID: PN002 RECEIPTDate: 04/14/2008.
Customer Name: Mr. Stewart. Customer ID: CUS002Address: New York
Coca Cola 100 100 10000 USD
PRO001
7 up 100 5000 USD50
Total 15000 USD
![Page 7: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/7.jpg)
Slide 7
B. Solution
1. Logical Design2. Physical Design3. Relational Model Terminologies4. Normal Form
![Page 8: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/8.jpg)
Slide 8
1. Logical Design
Base on the requirement of problem, we have :o There are two objects: Receipt and Receipt_Items.o Each Receipt has one or more Receipt_Items.
![Page 9: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/9.jpg)
Slide 9
Delete attributes of each object don’t need to store data.
![Page 10: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/10.jpg)
Slide 10
In the above design, there is a problem called Data Duplication at Receipts Object.
To solve the problems: Attributes CustomerID, CustomerName and CustomerAddress should be move to new object (Customers).
Sample data
ReceiptsRecei
pt ID
Customer ID
Customer Name
Customer Address
Receipt Date
1 C0001Nguyen Thi Nga 702 Nguyen Kiem
22/4/2009 8:20 AM
2 C0378Tran The Nhan
103 Tran Hung Dao
22/4/2009 9:00 AM
3 C0001Nguyen Thi Nga 702 Nguyen Kiem
22/4/2009 7:20 PM
Customer Object
![Page 11: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/11.jpg)
Slide 11
Logical Diagram (ERD)
![Page 12: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/12.jpg)
Slide 12
Receipt_ItemsReceipt
IDProduct
IDProduct Name
Product Quantity
Product Price
1 PRO001 Coca Cola 10 1001 PRO002 7 UP 20 502 PRO001 Coca Cola 5 1003 PRO001 Coca Cola 8 100
In the above design, there is aslo a problem called Data Duplication at Receipt_Items Object.
To solve the problem: Attributes ProductID and ProductName should be move to new object (Products).
Sample data
ReceiptsReceipt
IDCustomer
ID Receipt Date
1 122/4/2009 8:20
AM
2 222/4/2009 9:00
AM
3 122/4/2009 7:20
PM
Product Object
![Page 13: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/13.jpg)
Slide 13
Logical Diagram(ERD)
![Page 14: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/14.jpg)
Slide 14
Another design
![Page 15: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/15.jpg)
Slide 15
2. Physical Design
Diagram for the first design
![Page 16: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/16.jpg)
Slide 16
Diagram for the second design
![Page 17: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/17.jpg)
Slide 17
3. Relational Model Terminology
3.1. Key definition3.2. Functional dependency3.3. What is determinant?
![Page 18: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/18.jpg)
Slide 18
3.1. Key definition
3.1.1. Primary key3.1.2. Compound key3.1.3. Surrogate key3.1.4. Candidate key3.1.5. Foreign key
![Page 19: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/19.jpg)
Slide 19
3.1.1. Primary key
PRIMARY KEY: the primary means of identifying rows in a relation:
• There is one and only one primary key per relation• The primary key may be a composite key• The ideal primary key is short, numeric and never
changes
Example: In the Receipts table, Receipt_ID is the PRIMARY KEY. (
)
Slide 15
![Page 20: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/20.jpg)
Slide 20
3.1.2. Compound key
COMPOUND KEY: is a primary key that consists of two or more columns.
Example: In the Receipt_Items table, Receipt_ID and Product_ID
combine together to become the COMPOUND KEY. ( )
Slide 15
![Page 21: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/21.jpg)
Slide 21
3.1.3. Surrogate key
SURROGATE KEY: as an artificial column added to a relation to serve as a primary key.
• A key with no business meaning• Short, numeric and never changes • Normally hidden in forms and reports• Frequently a sequential number
Identity: SQLServer Sequence: Oracle Auto_increment: MySQL
Autonumber: Microsoft Access ...
Examples: In the Receipt_Items table, ReceiptItem_ID is the
SURROGATE KEY . ( )
In the Receipts table, Receipt_ID is the SURROGATE KEY. ( )
Slide 16 Slide
15
![Page 22: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/22.jpg)
Slide 22
3.1.4. Candidate key
CANDIDATE KEY: is a key that determines all of the other columns in a relation.
Examples: In the Customers table, CustomerID is the CANDIDATE
KEY . ( )
In the Products table, ProductID is the SURROGATE KEY. ( )
Slide 16 Slide
16
![Page 23: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/23.jpg)
Slide 23
3.1.5. Foreign key
FOREIGN KEY: is the primary key of one relation (table) that is placed in another relation (table) to form a link between the relations:
A foreign key can be a single column or a composite key
The term refers to the fact that key values are foreign to the relation in which they appear as foreign key values
ExampleIn the Receipt_Items table, Receipt_ID is the FOREIGN KEY. ( )
Slide 16
![Page 24: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/24.jpg)
Slide 24
3.2. Functional dependency
In the Receipts table, ReceiptDate is functionally dependent on Receipt_ID. Given a value of Receipt_ID, you can determine the ReceiptDate. This is the example of Functional Dependency and can be written:
• Receipt_ID ReceiptDate
A functional dependency occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s).
![Page 25: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/25.jpg)
Slide 25
3.3. What is determinant?
The attribute on the left side of the functional dependency is called the determinant.
Example:Receipt_ID ReceiptDate
![Page 26: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/26.jpg)
Slide 26
4. Normal Form
4.1. First Normal Forms Definition4.2. Second Normal Forms Definition4.3. Third Normal Forms Definition
![Page 27: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/27.jpg)
Slide 27
4.1. First Normal Forms Definition
1NF – A table that qualifies as a relation is in 1NF. It means the table reaches the characteristics of relation
Characteristics of Relation: Rows contain data about an entity Columns contain data about attributes of the entities All entries in a column are of the same kind Each column has unique name Cells of the tables hold a single value The orders of the columns is unimportant The orders of the rows is unimportant No two rows may be identical
Example: Slide 8, slide 9
![Page 28: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/28.jpg)
Slide 28
4.2. Second Normal Forms Definition
2NF- A relation is in 2NF if all of its non-key attributes are dependent on ALL of the primary key.
Example: Slide 11
![Page 29: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/29.jpg)
Slide 29
4.3. Third Normal Forms Definition
3NF – A relation is in 3NF if it is in 2NF and has NO DETERMINANTS except the primary key.
Example: Slide 13, 14 in logical diagram or diagram below in physical diagram. It conforms to 3NF.
![Page 30: Slide 1 Chapter 03 The Relational Model and Normalization](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f115503460f94c237e4/html5/thumbnails/30.jpg)
Slide 30
?