Logical Database Design and the Relational Model

CHAPTER 4: PART C LOGICAL DATABASE DESIGN AND THE RELATIONAL MODEL Modern Database Management 12th Edition Jeff Hoffer, Ramesh Venkataraman, Heikki Topi Copyright 2016 Pearson Education, Inc. DATA NORMALIZATION Primarily a tool to validate and

improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data The process of decomposing relations with anomalies to produce smaller, wellChapter 4 Copyright 2016 relations Pearson Education, Inc. structured 4-2

FUNCTIONAL DEPENDENCIES Functional Dependency: The value of one set of attributes (the determinant) determines the value of another attribute. X functionally determines Y: X Y, where X is called a determinant. Problem 4-33 (Fig. 4-33), p.195, Copyright 196. Chapter 4 2016 Pearson Education, Inc. 4-3

3 FIGURE 4-2 Chapter 4 Copyright 2016 Pearson Education, Inc. 4-4 4 FIGURE 4-23 Chapter 4 Copyright 2016 Pearson Education, Inc.

4-5 5 CANDIDATE KEYS Candidate Key: Attribute or a set of attributes that uniquely identifies a row. Each non-key field is functionally dependent on every candidate key One of the candidate keys will become the primary key

Chapter 4 E.g., perhaps there is both credit card number and SS# in a tablein this case both are candidate keys. Primary vs. candidate key: Fig. 4-16, p.171 Copyright 2016 Pearson Education, Inc. 4-6 6

Figure 4.22 Steps in normalization 3rd normal form is generally considered sufficient Chapter 4 Copyright 2016 Pearson Education, Inc. 4-7 FIRST NORMAL FORM A relation is in 1NF if it contains no multivalued attributes; i.e., every attribute

value is atomic. Fig. 4-25 is not in 1st Normal Form (multivalued attributes) it is not a relation. Fig. 4-26 is in 1st Normal form. All relations are in 1st Chapter 4 Copyright 2016 Pearson Education, Inc. 4-8 Normal Form. 8

ANOMALIES IN EMPLOYEE2 (FIG. 4-2B) Insertioncant enter a new employee without having the employee take a class (or at least empty fields of class information) Deletionif we remove employee 140, we lose information about the existence of a Tax Acc class Modificationgiving a salary increase to employee 100 forces us to update multiple

Why do these anomalies exist? records Because there are two themes (entity types) in this one relation. This results in data duplication and an unnecessary dependency between the entities. Chapter 4 Copyright 2016 Pearson Education, Inc. 4-9 9 SECOND NORMAL FORM

A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key. No partial functional dependencies (i.e., every non-key attribute must be defined by the entire key, not by only part of the key). A B C D: A B C, A D. 2NF? Is Fig. 4-23b (p. 181) in 2NF?

Chapter 4 Copyright 2016 Pearson Education, Inc. 4-10 10 11 SECOND NORMAL FORM (2NF) A relation is in 2NF if Primary key consists of a

single attribute; or Primary key consists of all attributes. Fig. 4-2b Fig. 4-1 + Fig. 4-7 Chapter 4 Copyright 2016 Pearson Education, Inc. 4-11 FIGURE 4-1 Chapter 4 Copyright 2016 Pearson Education, Inc.

4-1212 FIGURE 4-7 Chapter 4 Copyright 2016 Pearson Education, Inc. 4-1313 14 EMPLOYEE-COURSE RELATIONS IN 2NF EmpID

Name DeptName Salary EmpID Chapter 4 Both are full functional dependencies CourseTitle DateCompleted Copyright 2016 Pearson Education, Inc. 4-14

THIRD NORMAL FORM A relation is in 3NF if it is in 2NF and no transitive dependencies exists, where a transitive dependency is a functional dependency between non-key attributes. A B C D: A B C, C D. 2NF? 3NF? Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the Chapter 4 table.

Copyright 2016 Pearson Education, Inc. 4-15 old 15 16 Relation with transitive dependency (a) SALES relation with simple data Chapter 4 Copyright 2016 Pearson Education, Inc.

4-16 17 Relation with transitive dependency CustID Name CustID Salesperson CustID Region Chapter 4 BUT Salesperson Region Transitive dependency (not 3rd NF)

Copyright 2016 Pearson Education, Inc. 4-17 18 Removing a transitive dependency (a) Decomposing the SALES relation Chapter 4 Copyright 2016 Pearson Education, Inc. 4-18

Relations in 3NF 19 Salesperson Region CustID Name CustID Salesperson Now, there are no transitive dependencies Both relations are in 3rd NF Chapter 4 Copyright 2016 Pearson Education, Inc. 4-19

Table with multivalued attributes, not in 1st normal form Note: This is NOT a relation. Chapter 4 Copyright 2016 Pearson Education, Inc. 4-20 Table with no multivalued attributes and unique rows, in 1st normal form Note: This is a relation, but not a well-structured one. Chapter 4 Copyright 2016 Pearson Education, Inc.

4-21 ANOMALIES IN THIS TABLE Insertionif new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication Deletionif we delete the Dining Table from Order 1006, we lose information concerning this items finish and price

Updatechanging the price of product ID 4 requires update in multiple records Why do these anomalies exist? Because there are multiple themes (entity types) in one relation. This results in duplication and an unnecessary dependency between the entities. Chapter 4 Copyright 2016 Pearson Education, Inc. 4-22 Figure 4-27 Functional dependency diagram for INVOICE OrderID OrderDate, CustomerID, CustomerName, CustomerAddress CustomerID CustomerName, CustomerAddress

ProductID ProductDescription, ProductFinish, ProductStandardPrice OrderID, ProductID OrderQuantity Therefore, NOT in 2nd Normal Form Chapter 4 Copyright 2016 Pearson Education, Inc. 4-23 Figure 4-28 Removing partial dependencies Getting it into Second Normal Form

Partial dependencies are removed, but there are still transitive dependencies Chapter 4 Copyright 2016 Pearson Education, Inc. 4-24 Figure 4-29 Removing partial dependencies Getting it into Third Normal Form Transitive dependencies are removed.

Chapter 4 Copyright 2016 Pearson Education, Inc. 4-25 25 Chapter 4 Copyright 2016 Pearson Education, Inc. 4-26

Recently Viewed Presentations

• Construct from a regular grammar such that Any regular language is generated by some regular grammar Example: For any transition: Add production: variable terminal variable For any final state: Add production: * Grammar : or or Languages Generated by Regular...
• If the RTSP messages were to use the same port numbers as the media stream, then RTSP messages would be said to be "interleaved" with the media stream. RTSP initiates and controls delivery Client obtains a description of the multimedia...
• 3.76 x 105 = 3.76 = 37,600 {move your decimal 5 places to the right and fill in empty spaces with zeroes} 5.1 x 10−3= 5.1 = .0051 {move your decimal 3 place to the left and fill in empty...
• Real lives, imagined futures: Stories of participation and progression through the Open University professional qualification in Youth Work Sheila Curran and Tyrrell Golding
• Note tympanogram peak pressure introduce pressure into canal Have patient swallow water recheck tympanogram peak pressure Resonant Freq. Resonant Freq. Gradient = hp/ht R L SOC CN 8th IE ME OE SOC CN 8th IE ME OE 7th CN 7th...
• Suzanne, 36yr with abdominal pain. Onset abd pain 29yr, diverticulosis with abcess, sigmoid colectomy. 8/11 stabbed in RLQ by unknown man outside her apartment with superficial abd wound, bruises
• rx. tx2. Jamming signal. Antidote. tx1. tx2. rx. Full-Duplex Without Antenna Separation. Shield. Antidote signal cancels out jamming signal . Shield can simultaneously jam and receive. Design is small and portable. So we need a full-duplex design that does not...
• [VLDBJ'12] Rui Zhang, JianzhongQi, Dan Lin, Wei Wang, Raymond Chi-Wing Wong. A Highly Optimized Algorithm for Continuous Intersection Join Queries over Moving Objects. VLDB Journal, 21(4): 561-586, 2012.