Lecture9: Functional Dependencies and Normalization for Relational Databases

1 / 40

# Lecture9: Functional Dependencies and Normalization for Relational Databases - PowerPoint PPT Presentation

Lecture9: Functional Dependencies and Normalization for Relational Databases. Ref. Chapter14 - 15. Prepared by L. Nouf Almujally. How to produce a good relation schema?. STEPS : Start with a set of relation. Define the functional dependencies for the relation to specify the PK.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Lecture9: Functional Dependencies and Normalization for Relational Databases' - jena-sherman

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Lecture9:Functional Dependencies and Normalization for Relational Databases

Lecture9

Ref. Chapter14 - 15

Prepared by L. NoufAlmujally

How to produce a good relation schema?

STEPS:

• Start with a set of relation.
• Define the functional dependencies for the relation to specify the PK.
• Transform relations to normal form.

Lecture9

Functional Dependencies
• Describes the relationship between attributes in a relation.
• If A and B are attributes of relation R, B is functionally dependent on A, denoted by A B, if each value of A is associated with exactly one value of B. B may have several values of A.

Determinant Dependent

B is functionally

dependent on A

A

B

Lecture9

Functional Dependencies

X Y

• X -> Y holds if whenever two tuples have the same value for X, they must have the same value for Y
• For any two tuples t and u in any relation instance r(R): If t[X]=u[X], thent[Y]=u[Y]

If t & u agree here

Then they must agree here

Lecture9

X

Y

t

u

Functional Dependencies

Example

1:1 or M:1 relationship between attributes in a relation

StaffNo

position

Position is functionally

dependent on Staffno

SL21 Manager

Lecture9

1:M relationship between attributes in a relation

StaffNo is NOT functionally

dependent on position

position

StaffNo

Manager SL21

SG5

Examples of FD constraints
• Social security number determines employee name
• SSN -> ENAME
• Project number determines project name and location
• PNUMBER -> {PNAME, PLOCATION}
• Employee ssn and project number determines the hours per week that the employee works on the project
• {SSN, PNUMBER} -> HOURS

Lecture9

Identifying the PK
• Purpose of functional dependency, specify the set of integrity constraints that must hold on a relation.
• The determinant attribute(s) are candidate of the relation, if:
• 1:1 relationship between determinant & dependent.
• No subset of determinant attribute(s) is a determinant. (nontrivial)

If (A, B) C, then NOT A B, and NOT B A

• All attributes that are not part of the CK should be functionally dependent on the key: CK all attributes of R
• Hold for all time.
• PK is the candidate attribute(s) with the minimal set of functional dependency.

Lecture9

Identifying the PK
• If a relation schema has more than one key, each is called a candidate key.
• One of the candidate keys is arbitrarily designated to be the primary key, and the others are called secondary keys.
• A Prime attribute must be a member of some candidate key
• A Nonprime attribute is not a prime attribute—that is, it is not a member of any candidate key.

Lecture9

The Purpose of Normalization
• Normalization is a bottom-up approach to database design that begins by examining the relationships between attributes. It is performed as a series of tests on a relation to determine whether it satisfies or violates the requirements of a given normal form.
• Purpose:

- Guarantees no redundancy due to FDs

- Guarantees no update anomalies

• Normal Forms:
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-CoddNormal Form (BCNF)

Lecture9

Normal Forms Defined Informally
• 1st normal form
• All attributes depend on the key
• 2nd normal form
• All attributes depend on the whole key
• 3rd normal form
• All attributes depend on nothing but the key

Lecture9

First Normal Form (1NF)
• Unnormalized form (UNF): A relation that contains one or more repeating groups.
• First normal form (1NF): A relation in which the intersection of each row and column contains one & only one value.
• 1NF Disallows:
• composite attributes
• multivalued attributes
• nested relations; attributes whose values for an individual tuple are non-atomic

Lecture9

First Normal Form (1NF)

CLIENT_PROPERTY

Name

ClientNo

PropertyNo

John Key

CR76

PG4

PG16

Aline Stewart

CR56

PG4

PG36

Lecture9

PG16

Unnormalized form (UNF)

• Not in the 1NF because there are Multivalued attribute in the table (PropertyNo)
UNF 1NF Approach 1
• Expand the key so that there will be a separate tuple in the original relation for each repeated attribute(s).
• Primary key becomes the combination of primary key and redundant value (multivalued attribute).

1NF relation

• Disadvantage: introduce redundancy in the relation.

CLIENT_PROPERTY

Name

ClientNo

PropertyNo

John Key

CR76

PG4

John Key

CR76

PG16

Lecture9

Aline Stewart

CR56

PG4

Aline Stewart

CR56

PG36

Aline Stewart

CR56

PG16

UNF 1NF Approach 2
• If the maximum number of values is known for the attribute, replace repeated attribute (PropertyNo) with a number of atomic attributes (PropertyNo1, PropertyNo2, PropertyNo3).

1NF relation

• Disadvantage: introduce NULL values in the relation.

CLIENT_PROPERTY

Name

ClientNo

PropertyNo1

PropertyNo2

PropertyNo3

John Key

PG16

NULL

CR76

PG4

Lecture9

Aline Stewart

PG36

PG16

CR56

PG4

Summary : first normal form
• 1NF : if all attribute values are atomic: no repeating group, no composite attributes.

Lecture9

Example : First normal form -1NF

The following table is not in 1NF because there are nested relations in the table

Lecture9

Table in 1NF
• all attribute values are atomic because there are no repeating group and no composite attributes.

Lecture9

Second Normal Form
• Uses the concepts of FDs, primary key
• Definitions
• Prime attribute: An attribute that is member of the primary key K
• Full functional dependency: a FD Y -> Z where removal of any attribute from Y means the FD does not hold any more
• Examples:
• {SSN, PNUMBER} -> HOURS is a full FD since neither SSN -> HOURS nor PNUMBER -> HOURS hold
• {SSN, PNUMBER} -> ENAME is not a full FD (it is called a partial dependency ) since SSN -> ENAME also holds

Lecture9

Second Normal Form
• Second normal form (2NF) further addresses the concept of removing duplicative data
• A relation R is in 2NF if
• R is 1NF , and
• All non-prime attributes are fully dependent on the candidate keys. Which is creating relationships between these new tables and their predecessors through the use of foreign keys.
• A prime attribute appears in a candidate key.
• There is no partial dependency in 2NF.

Lecture9

Summary : Second Normal Form (2NF)
• Meet all the requirements of the 1NF
• Remove columns that are not fully dependent upon the primary key.

Lecture9

Example1: 1NF 2NF

Lecture9

• Remove partial dependencies by placing the functionally dependent attributes in a new relation along with a copy of their determinants.
Example2: Second normal form -2NF
• There are two non-key fields.  So, here are the questions:
• If I know just Description, can I find out Cost?  No, because we have more than one supplier for the same product.
• If I know just Supplier, and I find out Cost?  No, because I need to know what the Item is as well.
• Therefore, Cost is fully, functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence.

Lecture9

Example 2: Second normal form -2NF
• If I know just Description, can I find out Supplier Address?  No,
• because we have more than one supplier for the same product.
• If I know just Supplier, and I find out Supplier Address?  Yes.
• The Address does not depend upon the description of the item.
• Therefore, Supplier Address is NOT functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence.

Lecture9

Example 2: Second normal form -2NF

The above relations are now in 2NF

Lecture9

Third Normal Form (1)
• Transitive functional dependency

X, Y, Z are attributes of a relation, such that:

• If X Y and Y Z, then Z is transitively dependent on X via Y.
• Provided X is NOT functionally dependent on Y or Z (nontrivial FD).
• Examples:
• SSN -> DMGRSSN is a transitive FD
• Since SSN -> DNUMBER and DNUMBER -> DMGRSSN hold
• SSN -> ENAME is non-transitive
• Since there is no set of attributes X where SSN -> X and X -> ENAME

Lecture9

Third Normal Form (2)
• A relation schema R is in third normal form (3NF) if :
• R in 2NF and
• no non-prime attribute A in R is transitively dependent on the primary key
• R can be decomposed into 3NF relations via the process of 3NF normalization
• NOTE:
• In X -> Y and Y -> Z, with X as the primary key, we consider this a problem only if Y is not a candidate key.
• When Y is a candidate key, there is no problem with the transitive dependency .
• E.g., Consider EMP (SSN, Emp#, Salary ).
• Here, SSN -> Emp# -> Salary and Emp# is a candidate key.

Lecture9

Summary : Third Normal Form (3NF)
• Meet all the requirements of the 1NF
• Meet all the requirements of the 2NF
• Remove columns that are not dependent upon the primary key.

Lecture9

Example: 2NF 3NF

Lecture9

• If transitive dependencies exist, place transitively dependent attributes in a new relation along with a copy of their determinants.
Example : Third normal form -3NF
• describes parcels of land for sale in various counties of a state. Suppose that there are two candidate keys: Property_id# and {County_name, Lot#}
• lot # are unique only within each county
• Property_id# numbers are unique across counties for the entire state.

Lecture9

Example : Third normal form -3NF
• If I know # of Pages, can I find out Author\'s Name?  No.  Can I find out Author\'s Non-de Plume?  No.
• If I know Author\'s Name, can I find out # of Pages?  No.  Can I find out Author\'s Non-de Plume?  YES.
• Therefore, Author\'s Non-de Plume is functionally dependent upon Author\'s Name, not the PK for its existence.

Lecture9

Review Example

STAFF_PROPERTY_INSPECTION

iDate

iTime

sName

Pno

StaffNo

CarReg

Lawrence St,

Glasgow

5 Novar Dr.,

Glasgow

PG4

PG16

18-Oct-00

22-Apr-01

1-Oct-01

22-Apr-01

24-Oct-01

10:00

09:00

12:00

13:00

14:00

Replace crockery

Good order

Damp rot

Replace carpet

Good condition

SG37

SG14

SG14

SG14

SG37

M23JGR

M53HDR

N72HFR

M53HDR

N72HFR

Ann

David

David

David

Ann

Lecture9

Unnormalized relation

UNF 1NF

STAFF_PROPERTY_INSPECTION

iDate

iTime

sName

Pno

StaffNo

CarReg

PG4

PG4

PG4

PG16

PG16

Lawrence St, Glasgow

Lawrence St,Glasgow

5 Novar Dr., Glasgow

5 Novar Dr., Glasgow

5 Novar Dr., Glasgow

18-Oct-00

22-Apr-01

1-Oct-01

22-Apr-01

24-Oct-01

10:00

09:00

12:00

13:00

14:00

Replace crockery

Good order

Damp rot

Replace carpet

Good condition

SG37

SG14

SG14

SG14

SG37

M23JGR

M53HDR

N72HFR

M53HDR

N72HFR

Ann

David

David

David

Ann

Lecture9

1NF

1NF 2NF

STAFF_PROPERTY_INSPECTION

iDate

iTime

sName

Pno

StaffNo

CarReg

Lecture9

Partial Dependency : PnopAddress

1NF 2NF

PROPERTY

2NF

Pno

PROPERTY_INSPECTION

iDate

iTime

sName

Pno

StaffNo

CarReg

2NF

Lecture9

Transitive Dependency : StaffNoSname

2NF 3NF

PROPERTY

3NF

Pno

STAFF

3NF

sName

StaffNo

PROPERTY_INSPECTION

Lecture9

iDate

iTime

3NF

Pno

StaffNo

CarReg