chapter 4 part 3 - · pdf file... every non-key column must depend on the entire pk third ......

Chapter 4 Part 3

954245 Data Management

Sirikorn Santirojanakul

Agenda

•Normal form

The benefits of normalization

•Retrieval more efficient

• Insert, update, and delete operation more efficient

•Data redundancy is minimized

•Simplifies maintenance and reduces storage

The Seven Normal FormsNormal form Description

First (1NF) The value stored at the intersection of each row and column must be a scalar value, and a table must not contain any repeating columns

Second (2NF) Every non-key column must depend on the entire PK

Third (3NF) Every non-key column must depend only on the PK

Boyce-Codd(BCNF) A non-key column can’t be dependent on another non-key column. This prevents transitive dependencies, where column A depends on column C and column B depends on column C. Since both A and B depend on C, A and B should be moved into another table with C as the key.

The Seven Normal FormsNormal form Description

Fourth (4NF) A table must not have more than one multivalued dependency, where the PK has a one-to-many relationship to non-key columns. This form gets rid of misleading many-to-many relationships.

Fifth (5NF) The data structure is split into smaller and smaller tables until all redundancy has been crim inated. If further s plitti ng woul d result in tables that coul dn’t be jo ined to recreate the original table, the structure is in fifth normal form.

Domain-key (DKNF) or Sixth (6NF) Every constraint on the relationship is dependent only on key constraints and domain constraints, where a domain is the set of allowable values for a column. This form prevents the insertion of any unacceptable data by enforcing constraints at the level of relationship, rather than at the table or column level. DKNF is less a design model than an abstract “ulti m ate” normal form

First Normal Form: Eliminating Repeating Data• Invoice(invoice_number, customer_number, customer_name, customer_address, customer_city,

customer_state, customer_zipcode, customer_phone, terms, ship_via, order_date, product_number, product_description, quantity, unit_price, extended_amount, total_order_amount)

• Find a group of attributes that repeat together, each attribute in the group is multivalued• Called a repeating group• The procedure for moving a multivalued attribute or repeating group to a new relation is as follows:• Create a new relation with a meaningful name. • Copy the primary key form the original relation to the new one. The data depended on this primary key in the

new relation. This copied primary key now becomes a foreign key to the original relation. • Move the repeating group or multivalued attribute to the new relation. (removed from the original relation)• Make the primary key unique by adding attributes from the repeating group to it.

First Normal Form: Eliminating Repeating Data• Invoice (invoice_number,

customer_number, customer_name, customer_address, customer_city, customer_state, customer_zipcode, customer_phone, terms, ship_via, order_date, total_order_amount)

• Invoice_line_item (invoice_number, product_number, product_description, quantity, unit_price, extended_amount,)

• Invoice_line_item(invoice_line_item_number, invoice_number, product_number, product_description, quantity, unit_price, extended_amount,)

• The invoice_number attribute was copied from invoice to invoice_line_item and product_numberwas added to it

• The repeating group was removed from the invoice relation

• Invoice_number is still the primary key in invoice, and it now serves as a foreign key in invoice_line_item as part of the primary key of invoice_line_item

• There are no repeating groups in the relations, they are in first normal form.

Alternative

Second Normal Form: Eliminating Partial Dependencies• Invoice (invoice_number,

customer_number, customer_name, customer_address, customer_city, customer_state, customer_zipcode, customer_phone, terms, ship_via, order_date, total_order_amount)

• The customer_number is dependent on invoice_number because at any point in time, there can be only one value of customer_number associated with a given value of invoice_number• Invoice_number uniquely identifies the

customer_number• Customer_number is functionally

dependent on the invoice_number

invoice_number

customer_number

Second Normal Form: Eliminating Partial Dependencies


• The product_description is functionally dependent on product_number

• There is only one value of product_description associated with the product_number.

• The product_number is only part of the key of the invoice_line_item

Second normal form have the following criteria

• The relation is in first normal form

• All non-key attributes are functionally dependent on the entire primary key

product_description

product_number


•Some of violations in second normal form such as invoice_line_item relation

• The product_description and unit_price depend only on the product_number instead of the combination of invoice_numberand product_number.


product_descriptionunit_price

product_number

PK => invoice_number+product_number


•What about price changes?

• An invoice is an official record that we must maintain for seven years, per current tax laws.

http://www.rd.go.th/publish/seminar/SMEManualDoc_20062559.pdf

Second Normal Form: Eliminating Partial Dependencies• The common dilemma with changing

attributes – prices.

• The system must be able to recall the price at any point in time or we must store the price with the invoice.

• The system can reproduce the invoice as needed.

• System store the price in two places, one is the current selling price and the other is the price at the time the sale was made.

• Invoice (invoice_number, customer_number, customer_name, customer_address, customer_city, customer_state, customer_zipcode, customer_phone, terms, ship_via, order_date, total_order_amount)

• Invoice_line_item (invoice_number, product_number,, quantity, sale_unit_price, extended_amount,)

• Product (product_number, product_description, list_unit_price)

The second normal form

Third Normal Form: Eliminating Transitive Dependencies

• Transitive dependency?

•An attribute that depends on another attribute that is not the primary key of the relation.

• The problem is attributes of another entity of customer included in invoice relation

invoice_number

customer_name

customer_number

Invoice (invoice_number, customer_number, customer_name, customer_address, customer_city, customer_state, customer_zipcode, customer_phone, terms, ship_via, order_date, total_order_amount)


• Third normal form have the following criteria• The relation is in second normal

form• There is no transitive

dependence (all the non-key attributes depend only on the primary key)

• The simply way to transform the second normal form into third normal form move any transitively dependent attributes to relations that depend only on the primary key.•Next step is reconstruct the

original user view via a join.


• A good database designer will make a note in the documentation specifying formula for the calculated attribute.

• The calculated attributes such as extended_amount is calculated by multiplying sale_unit_price by quantity.

• So extended_amount is transitive dependent on those two attributes.

• And the similar logic, we removed the total_order_amount from the invoice relation.

invoice (invoice_number, customer_number, terms, ship_via, order_date)invoice_line_item (invoice_number, product_number,, quantity, sale_unit_price)product (product_number, product_description, list_unit_price)customer (customer_number, customer_name, customer_address, customer_city, customer_state, customer_zipcode, customer_phone)

The third normal form

BCNF: Boyce-Codd Normal Form• A stronger version of third normal form.• Addresses anomalies that occur when a non-

key attribute is a determinant of an attribute that is part of PK

• Example: industries assigns multiple product support specialists to each customer, and each support specialist handles only one particular product line. And use customer_number and employee_numberinstead of the customer and employee names, but their names are used here for better illustration of issue

• Support specialists are employee

• Many support specialists (employee) each customer

• Each support specialists (employee) one product line

Customer Name Product Line Support Specialist (employee)

John Helmets Brown

Armstrong Helmets Peter

John Springs Jo

John Rockets Jenny

BCNF: Boyce-Codd Normal Form

• BCNF has two requirements• The relation must be in third

normal form• No determinants exist that are not

either the primary key or a candidate key for table. A non-key attribute may not uniquely identify (determine) and other attribute, including one that participates in the primary key.

• Support specialist assignment (customer_number, support_specialist_number)• Support specialist specialty

(support_specialist_number, product_line)

BCNF: Boyce-Codd Normal Form

• In tabular view, names have been substituted for the IDs to make the data easier to visualize

Customer Name Support Specialist (employee)

John Brown

John Jo

John Jenny

Armstrong Peter

Support Specialist (employee)

Product Line

Brown Helmets

Peter Helmets

Jo Springs

Jenny Rockets

Fourth Normal Form

• Two or more multivalued attributes are included in the same relation.

•Example, company need to record both office skills and language skills for employees.

Employee_number Office_skill Language_skill

L001 Typing, 40 wpm French

L001 Spreadsheets German

L002 Spreadsheets French

Fourth Normal Form

•Both alternatives in third normal form •Put each multivalued attribute in

a separate relation

• Employee_skill (employee_number, office_skill, language_skill)

• Employee_skill (employee_number, language_skill, office_skill)

• Employee_office_skill(employee_number, office_skill)• Employee_language_skill

(employee_number, language_skill)

Fifth Normal Form

•Some authors suggest the need of fifth normal form. Others described fifth normal form exactly the way fourth normal form is described here

• There is no clear standard definition of fifth normal form in the industry

Self Reviewสวนของการทบทวนดวยตนเองของนกศกษาจากตวอยางดงตอไปน

Example 1:

First Normal Form (1NF)ทก Attribute ในแ ตละ record จะเปน single value ไมม คา

ของกลม ขอมลทซ ากน (Repeating Group)

ขอมลทกแถว (Tuple) ตองมคาไมซ ากน

รหสนกศกษา ชอ นามสกล รหสวชาทลงทะเบยน001 สมชาย สมใจนก 204-101

204-204204-205

002 ธรชาย บญมาศ 204-102204-204

ตารางทมลกษณะขอมลเปน Repeating group

Repeating Group

Second Normal Form (2NF)

• 1. ตองเปน First Normal Form (1NF) มากอน

•2. ตองไมม Partial Dependency (การขนตอกนบางสวน)

•สรปกคอ นอรมลไลเซชนระดบท 2 (Second normal form : 2NF) เปนการขจดแอตตรบวท ไมขนกบทงสวนของคยหลก ออกไป เพอใหแอตตรบวอนทงหมดขนตรงกบสวนทเปนคยหลกทงหมดเทานน

26

ตวอยางตารางท Partial Dependency (การขนตอกนบางสวน)รหสนกศกษา รหสวชา เกรด ชอวชา

534267001 F01 A การเขยนโปรแกรม

534267001 F02 B การออกแบบฐานขอมล

534267002 F01 D การเขยนโปรแกรม

534267002 F02 A การออกแบบฐานขอมล


534267003 F02 C การออกแบบฐานขอมล

รหสนกศกษา รหสวชา เกร ด ชอวชาPartial Dependency

รหสนกศกษา, รหสวชา เกรด ,ชอวชารหสวชา ชอวชา

Second Normal Form (2NF)

วธขจดปญหา• ตองสราง ตารางเ พม

• นาค อลมยทมปญหาไปใสในต ารางทสราง เพม

• กาหน ดคยหลกใหกบตารางทสราง ใหม

• แอททรบวตใดในตารา งเดม เมอนาไปใสในต ารางใ หม ใ หตดออกจากตารางเดม ยกเวน สวนข องคยหลก คง ไวในต ารางเ ดม

28

29

ตารางนเมอท าใหอยในรป 2 NF จะได 2

ตารางดงน

รหสนกศกษา รหสวชา เกรด ชอวชา


534267001 F02 B การออกแบบฐานขอมล

534267002 F01 D การเขยนโปรแกรม

534267002 F02 A การออกแบบฐานขอมล


534267003 F02 C การออกแบบฐานขอมล

รหสนกศกษา รหสวชา เกรด

534267001 F01 A

534267001 F02 B

534267002 F01 D

534267002 F02 A

534267003 F01 A

534267003 F02 C

รหสวชา ชอวชา

F01 การเขยนโปรแกรม

F02 การออกแบบฐานขอมล

ตารางผลการเรยน

ตารางผลการเรยน ตารางวชา

3 0

รหสผเขาอบรม

รหสครอสอบรม

ชอผเขาอบรม ชอครอสอบรม ผลการทดสอบ

0001 TR01 นายเอ ใจด การซอมไฟฟา ผาน

0001 TR05 นายเอ ใจด การซอมตเยน ผาน

0002 TR03 นางบ ใจกลา การท าอาหาร ไมผาน

0002 TR09 นางบ ใจกลา การเลยงเดก ผาน

0003 TR01 นายรวย มเงน การซอมไฟฟา ผาน

0003 TR05 นายรวย มเงน การซอมตเยน ไมผาน

ตารางผลการอบรม

ใหนกศกษานอมลไลซตารางนใหอยในรปแบบ 2NF

Third Normal Form (3NF)

• 1.Relation น นจะตองมคณสมบต 2NF

• 2.ตองไมมความสมพนธระห วาง Non-key Attribute หรอ

ไมม Transitive Dependency

สรป : แอททรบวตทไมใชคยหลก ตองไมข นตอกนเ อง

31

Third Normal Form (3NF)

วธขจดปญหา

• สราง ตาราง เพม

• นาแ อททรบวตทมปญหามาใสในตารา งให ม

• กาห นดคยหลก

• แอททรบวตทยายจากตารางเดมไปใสในตา รางใหมใหตดออกจากตาราง เดม

• นาคยหลกในขอ 3 ไปใสในต ารางเ ดม

32

33

คยหลกของตารางน คอ รหสพนกงาน

จากตารางยงมฟงกชนการข นตอกนแบบ Transitive Dependency อย คอ

รหสแผนก ซงไมใชคยหลกข องตาราง แตสามารถระบคา ชอแผ นก ได คอ ถารรหสแผนก กจะรชอแผนก

จากตารางขางบน ท าใหอยในรป 3 NF จะได 2 ตารางขางลางน

รหสพนกงาน ชอสกล รหสแผนก ชอแผนก เงนเดอนP001 นพเกศ แกวใส A001 บญช 25000P002 วารณ รวดเรว F001 การเงน 30000

รหสแผนก ชอแผนก

A001 บญชF001 การเงน

รหสพนกงาน ชอสกล เงนเดอนP001 นพเกศ แกวใส 25000

P002 วารณ รวดเรว 30000

รหสแผนกA001

F001

สรป Normalization

• 1NF ทกแอททรบวตในแตละแถวมคาของขอมล เพยงคาเดยว

•2NF รเลชน น นตองไมมความส มพ นธระหวางแอททรบวตแบบบางสวน (แอททรบวตทกตวตองข นกบคยหลกทกตว ไมข นอยกบตวใดตวหนง)

•3NF ทกแอททรบวตทไมใชคยหลก ไมมคณส มบตในการ กาหนด คาของ แอททรบวตอน

34

Example 2:

How to apply the first normal form

•A table in first normal form often has repeating values in its rows. This can be resolved by applying the second normal form.

How to apply the second normal form• Every non-key column must depend on

the entire primary key. • To apply second normal form, you move

colu mns th at don’t depend on th e ent ire primary key to another table and then establish a relationship between the two tables

• Second normal form helps remove redundant row data, which can save storage space, make maintenance easier, and reduce the chance of storing inconsistent data

How to apply the third normal form

• For a table to be in third normal form, every non-key column must depend only on the primary key.• If a column doesn’t depend only

on the primary key. It implies that the column is assigned to the wrong table or that it can be computed from other columns in the table.

Example 3:

Example 4:

45

First Normal Form (1NF)•A database schema is in First Normal Form if all tables are flat

Name GPA Courses

Alice 3.8

Bob 3.7

Carol 3.9

Math

DB

OS

DB

OS

Math

OS

Student Name GPA

Alice 3.8

Bob 3.7

Carol 3.9

Student

Course

Math

DB

OS

Student Course

Alice Math

Carol Math

Alice DB

Bob DB

Alice OS

Carol OS

Takes Course

May need

to add keys

46

Relational Schema Design

PersonbuysProduct

name

price name ssn

Conceptual Model:

Relational Model:plus FD’s

Normalization:Eliminates anomaliesขจดความผดปกต

FD: Functional dependencies

47

Data AnomaliesWhen a database is poorly designed we get anomalies:

Redundancy: data is repeated

Update anomalies: need to change in several places

Delete anomalies: may lose data when we don’t want

48

Relational Schema Design

Anomalies: ความผดปก ต• Redundancy = repeated data• Update anom alies = Fred m oves to “Bellevue”• Deletion anomalies = Joe deletes his phone number:

what is his city ?

Recall set attributes (persons with several phones):

Name SSN PhoneNumber City

Fred 123-45-6789 206-555-1234 Seattle

Fred 123-45-6789 206-555-6543 Seattle

Joe 987-65-4321 908-555-2121 Westfield

One person may have multiple phones, but lives in only one city

49

Relation DecompositionBreak the relation into two:

Name SSN City

Fred 123-45-6789 Seattle

Joe 987-65-4321 Westfield

SSN PhoneNumber

123-45-6789 206-555-1234

123-45-6789 206-555-6543

987-65-4321 908-555-2121Anomalies are gone:• No more repeated data• Ea sy to m ove Fred to “Bellevue” (ho w?)• Easy to delete all Joe’s phone numbers (ho w?)


Fred 123-45-6789 206-555-1234 Seattle

Fred 123-45-6789 206-555-6543 Seattle

Joe 987-65-4321 908-555-2121 Westfield

50

Relational Schema Design(or Logical Design)Main idea:

•Start with some relational schema

• Find out its functional dependencies

•Use them to design a better relational schema

51

Functional Dependencies

•A form of constraint• hence, part of the schema

• Finding them is part of the database design

•Also used in normalizing the relations

52

Examples

EmpID Name, Phone, Position

Position Phone

but not Phone Position

An FD holds, or does not hold on an instance: ตวอยาง

EmpID Name Phone Position

E0045 Smith 1234 Clerk

E3542 Mike 9876 Salesrep

E1111 Smith 9876 Salesrep

E9999 Mary 1234 Lawyer

53

Example

Position Phone






54

Example






but not Phone Position

55

Example

What the key?{SSN, PhoneNumber}


Fred 123-45-6789 206-555-1234 Seattle

Fred 123-45-6789 206-555-6543 Seattle

Joe 987-65-4321 908-555-2121 Westfield

Joe 987-65-4321 908-555-1234 Westfield

SSN Name, City

Hence SSN Name, Cityis a “bad” dependency

BCNF (Boyce/Codd Normal Form) นยามตองเปน 3NF และไม ม attribute อนในรเลชนทสาม ารถระบค าของ attribute ทเปนคย

หลก หรอสวนหนงส วนใดของคยหลกในกรณทคย หลกเป นคย ผสมโดยทว ไปรป แบบ BCNF จะอย ในรปแบบ 3NF แต ไ ม จาเปนเสมอไปทรปแบบ 3NF จะอยในรป

แบบ BCNF ท งน เนองจากรปแบบ น เป นการขยายขอบ เ ขตของรปแบ บ 3NF ใ ห เหมา ะสมยงข น โดยรป แบบ ทตองทาให เปน BCNF มกจะมคณ สมบต ดงน

• เปนรเลชนทมคย ค แข งหลายคย (Multiple Candidate Key)

• โดยทคยค แข งเปนคย ผสม (Composite Key)

• และคย ค แข งน นมบางส วนซ าซ อนกน (Overlapped) ม attribute บางตวร วมกนอย

Question & Answer

References

•รฐสทธ สขะหต. (2555). ฐานขอมลเ บ องตน

• Murach, J. (2015). Murach’s MySQL.

•Oppel, A. (2004). Databases Demystified. Chapter 6. McGraw-Hill.

chapter 4 part 3 - · pdf file... every non-key column must depend on the entire pk third ......

Documents