Chapter 3 The Relational Model and Normalization Slide

Chapter 3 The Relational Model and Normalization Slide 1

Contents A. Sales Receipt Problem B. Solution Slide 2

A. Sales Receipt Problem DS company wants to store information of receipt for each selling time. Each receipt has following information: Receipt ID Customer Name Customer Address Date of Receipt Each receipt has one or more receipt items which have following information: Product ID Product Name Quantity Price Line Total For more information about a receipt, please refer to next slide: Slide 3

DS Company ID: …… RECEIPT Date: ………. Customer Name: …………………Customer ID: ………… Address: …………………………. . Product ID Name Quantity Price Line Total Let’s design the DB into Normal Form for the above requirement. Next slides are some examples of receipt. Slide 4

Example 1: DS Company ID: PN 001 RECEIPT Date: 04/12/2008. Customer Name: Mr. Johnson. Customer ID: CUS 001 Address: New York Product ID PRO 001 Name Coca Cola Quantity 100 Price 10000 USD Total Slide 5 Line Total 10000 USD

Example 2: DS Company ID: PN 002 RECEIPT Date: 04/14/2008. Customer Name: Mr. Stewart. Customer ID: CUS 002 Address: New York Product ID PRO 001 PRO 002 Name Coca Cola 7 up Quantity 100 Price 100 50 Total Slide 6 Line Total 10000 USD 50000 USD 60000 USD

B. Solutions 1. 2. 3. 4. Slide 7 Logical Design Physical Design Relational Model Terminologies Normal Form

1. Logical Design Base on the requirement of problem, we have the logical design: o There two objects: Receipt and Receipt_Items. o Each Receipt has one or more Receipt_Items. Slide 8

Delete attributes of each object don’t need to store data. Slide 9

In the above design, there is a problem called Data Duplication at Receipts Object. To solve the problems; Attribute Customer. ID, Customer. Name and Customer. Address should be move to new object (Customers). Sample data Receipts Receipt Customer ID ID Customer Name Customer Address Receipt Date 1 C 0001 Nguyen Thi Nga 702 Nguyen Kiem 22/4/2009 8: 20 AM 2 C 0378 Tran The Nhan 22/4/2009 9: 00 AM 3 C 0001 Nguyen Thi Nga 702 Nguyen Kiem Slide 10 103 Tran Hung Dao Customer Object 22/4/2009 7: 20 PM

Logical Diagram (ERD) Slide 11

In the above design, there is a problem called Data Duplication at Receipt_Items Object. To solve the problem; Attribute Product. ID and Product. Name should be move to new object (Products). Sample data Product Object Receipts Receipt ID 1 2 3 Receipt_Items Slide 12 Receipt ID 1 1 2 3 Customer ID 1 2 1 Product ID PRO 001 PRO 002 PRO 001 Receipt Date 22/4/2009 8: 20 AM 22/4/2009 9: 00 AM 22/4/2009 7: 20 PM Product Name Coca Cola 7 UP Coca Cola Product Quantity Product Price 10 100 20 50 5 100 8 100

Logical Diagram(ERD) Slide 13

Another design Slide 14

2. Physical Design Diagram for the first design Slide 15

Diagram for the second design Slide 16

3. Relational Model Terminology 3. 1. Key definition 3. 2. Functional dependency 3. 3. What is determinant? Slide 17

3. 1. Key definition 3. 1. 1. Primary key 3. 1. 2. Compound key 3. 1. 3. Surrogate key 3. 1. 4. Candidate key 3. 1. 5. Foreign key Slide 18

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 ) Slide 19

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 ) Slide 20

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: My. SQL Autonumber: Microsoft Access. . . Examples: In the Receipt_Items table, Receipt. Item_ID is the SURROGATE KEY. ( Slide 16 ) In the Receipts table, Receipt_ID is the SURROGATE KEY. ( Slide 15 ) Slide 21

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, Customer. ID is the CANDIDATE KEY. ( Slide 16 ) In the Products table, Product. ID is the SURROGATE KEY. ( Slide 16 ) Slide 22

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 Example In the Receipt_Items table, Receipt_ID is the FOREIGN KEY. ( Slide 16 ) Slide 23

3. 2. Functional dependency In the Receipts table, Receipt. Date is functionally dependent on Receipt_ID. Given a value of Receipt_ID, you can determine the Receipt. Date. This is the example of Functional Dependency and can be written: • Receipt_ID Receipt. Date A functional dependency occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s). Slide 24

3. 3. What is determinant? The attribute on the left side of the functional dependency is called the determinant Example: Receipt_ID Receipt. Date Slide 25

4. Normal Form 4. 1. First Normal Forms Definition 4. 2. Second Normal Forms Definition 4. 3. Third Normal Forms Definition Slide 26

4. 1. First Normal Forms Definition 1 NF – A table that qualifies as a relation is in 1 NF. 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 Slide 27

4. 2. Second Normal Forms Definition 2 NF- A relation is in 2 NF if all of its non-key attributes are dependent on ALL of the primary key. Example: Slide 11 Slide 28

4. 3. Third Normal Forms Definition 3 NF – A relation is in 3 NF if it is in 2 NF and has NO DETERMINANTS except the primary key. Example: Slide 13, 14 in logical diagram or diagram below in physical diagram. It conforms to 3 NF Slide 29

? Slide 30