Chapter 3

1. The Relational Model and Normalization Chapter 3

2. Contents A. Sales Receipt Problem B. Solution

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 Name Quantity Price Line Total For more information about a receipt, please refer to next slide:

4. 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. Total

5. DS Company ID: PN001 RECEIPT Date: 04/12/2008. Customer Name: Mr. Johnson. Customer ID: CUS001 Address: New York Product ID Name Quantity Price Line Total PRO001 Coca Cola 100 100 10000 USD • Example 1: Total 10000 USD

6. DS Company ID: PN002 RECEIPT Date: 04/14/2008. Customer Name: Mr. Stewart. Customer ID: CUS002 Address: New York Product ID Name Quantity Price Line Total PRO001 Coca Cola 100 100 10000 USD PRO002 7 up 100 50 50000 USD • Example 2: Total 60000 USD

7. B. Solutions • Logical Design • Physical Design • Relational Model Terminologies • Normal Form

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

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

10. In the above design, there is a problem called Data Duplication at Receipts Object. • To solve the problems; Attribute CustomerID, CustomerName and CustomerAddress should be move to new object (Customers). • Sample data Customer Object

11. Logical Diagram (ERD)

12. In the above design, there is a problem called Data Duplication at Receipt_Items Object. • To solve the problem; Attribute ProductID and ProductName should be move to new object (Products). • Sample data ProductObject

13. Logical Diagram(ERD)

14. Another design

15. 2. Physical Design • Diagram for the first design

16. Diagram for the second design

17. 3. Relational Model Terminology 3.1. Key definition 3.2. Functional dependency 3.3. What is determinant?

18. 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

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

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

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

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

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

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).

25. 3.3. What is determinant? • The attribute on the left side of the functional dependency is called the determinant • Example: • Receipt_ID  ReceiptDate

26. 4. Normal Form 4.1. First Normal Forms Definition 4.2. Second Normal Forms Definition 4.3. Third Normal Forms Definition

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

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

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

30. ?