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

STEPS:

• Start with a set of relation.

• Define the functional dependencies for the relation to specify the PK.

• Transform relations to normal form.

Lecture9

• 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

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

• 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

• 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

• 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

• 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

• 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

• 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

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)

• 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

• 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

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

Lecture9

UNF (multivalued) 1NF

Lecture9

Lecture9

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

Lecture9

• all attribute values are atomic because there are no repeating group and no composite attributes.

Lecture9

• 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

• Meet all the requirements of the 1NF

• Remove columns that are not fully dependent upon the primary key.

Lecture9

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

• 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

• 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

• 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

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: 2NF 3NF

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

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

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

STAFF_PROPERTY_INSPECTION

iDate

iTime

sName

Pno

StaffNo

CarReg

Lecture9

Partial Dependency : PnopAddress

PROPERTY

2NF

Pno

PROPERTY_INSPECTION

iDate

iTime

sName

Pno

StaffNo

CarReg

2NF

Lecture9

Transitive Dependency : StaffNoSname

PROPERTY

3NF

Pno

STAFF

3NF

sName

StaffNo

PROPERTY_INSPECTION

Lecture9

iDate

iTime

3NF

Pno

StaffNo

CarReg