Lecture9 functional dependencies and normalization for relational databases
Download
1 / 40

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


  • 82 Views
  • Uploaded on

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.

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

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:Functional Dependencies and Normalization for Relational Databases

Lecture9

Ref. Chapter14 - 15

Prepared by L. NoufAlmujally


How to produce a good relation schema
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
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 dependencies1
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 dependencies2
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
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
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 pk1
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
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
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
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 1nf1
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
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
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
Summary : first normal form

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

Lecture9


Unf multivalued 1nf
UNF (multivalued) 1NF

Lecture9



Example first normal form 1nf
Example : First normal form -1NF

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

Lecture9


Table in 1nf
Table in 1NF

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

Lecture9


Second normal form
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 form1
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
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
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
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
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 2nf1
Example 2: Second normal form -2NF

The above relations are now in 2NF

Lecture9


Third normal form 1
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
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
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
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
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 3nf1
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
Review Example

STAFF_PROPERTY_INSPECTION

pAddress

iDate

iTime

sName

Pno

comments

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
UNF 1NF

STAFF_PROPERTY_INSPECTION

pAddress

iDate

iTime

sName

Pno

comments

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
1NF 2NF

STAFF_PROPERTY_INSPECTION

iDate

pAddress

iTime

sName

Pno

comments

StaffNo

CarReg

Lecture9

Partial Dependency : PnopAddress


1nf 2nf1
1NF 2NF

PROPERTY

2NF

pAddress

Pno

PnopAddress

PROPERTY_INSPECTION

iDate

iTime

sName

Pno

comments

StaffNo

CarReg

2NF

Lecture9

Transitive Dependency : StaffNoSname


2nf 3nf
2NF 3NF

PROPERTY

3NF

pAddress

Pno

STAFF

3NF

sName

StaffNo

PROPERTY_INSPECTION

Lecture9

iDate

iTime

3NF

Pno

comments

StaffNo

CarReg

PROPERTY(Pno, pAddres)

STAFF(StaffNo, sName)

PROPERTY_INSPECT(Pno, iDate,iTime, comments, staffNo, CarReg)



References
References

  • “Database Systems: A Practical Approach to Design, Implementation and Management.” Thomas Connolly, Carolyn Begg. 5thEdition, Addison-Wesley, 2009.

Lecture9


ad