Functional Dependency

1 / 16

# Functional Dependency - PowerPoint PPT Presentation

Functional Dependency. Presenter Usman Saeed. Definition. Definition: constraints on relations() characteristic of an attribute where values are determined by another attribute’s values A  B if “for every valid instance of A, that value of A uniquely determines the value of B”

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

## PowerPoint Slideshow about 'Functional Dependency' - diem

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

### Functional Dependency

Presenter

Usman Saeed

Definition

Definition:

• constraints on relations()
• characteristic of an attribute where values are determined by another attribute’s values

A  B if “for every valid instance of A, that value of A uniquely determines the value of B”

Notation:

• α→β (α determines β)
• (α→β may take the form AB→C, A→BC, etc.)
Uses of Functional Dependencies
• To determine if a relation is in a Normal Form.
• To specify constraints on the set of legal relations (functional dependencies to focus on)
• To determine if a decomposition would cause data loss (R decomposed to R1 and R2 but, R1 |X| R2 ≠ R)
Normalization
• Database normalization is a process of removing redundant data from tables, to improve storage efficiency and data integrity.
• We can measure the efficiency of the databases using classifications called normal forms (or NF).
• Normalization generally involves splitting existing tables into multiple ones, which must be re-joined or linked each time a query is issued.
First Normal Form(1NF)
• Atomic Form: A domain is Atomic if

elements of that domain are indivisible

Example: A set of names is a non atomic value.

• A relation schema ‘S’ is said to be in the first normal form if the domains of all the attributes of ‘S’ are atomic.
Example Table (Problems)
• In the Table, we have two violations of First Normal Form:
• First, we have more than one Actor field,
• Second, our Genre field is multivalued (non atomic). With more than one value in a single field, it would be very difficult to search for all the movies on a given Genre.
Second Normal Form

A relation is in second normal form if it is in first normal form AND every nonkey attribute is fully functionally dependant on the primary key.

Hence the table in my example is in violation of this rule because we have two rows for the same movie.

This can be rectified by making separate tables for

Genre and Actors.

Summary
• In general the 1nf is used to get rid of redundancies in the columns. As seen in the example.
• Secondly 2nf deals with redundancies in the rows.
Third Normal Form
• The relation has to be 2NF
• Third normal form (3NF) requires that there are no functional dependencies of non-key attributes on something other than a candidate key.
BCNF
• A relation R is said to be in BCNF if whenever X -> A holds in R, and A is not in X, then X is a candidate key for R.
• The BCNF differs from the 3NF only when there are more than one candidate keys and the keys are composite and overlapping.
Example
• Schema: enrol (sno, sname, cno, cname, date-enrolled)
• Let us assume that the relation has the following candidate keys:
• (sno, cno) (sno, cname) (sname, cno) (sname, cname)
• sno -> snamecno -> cname
Bibliography
• http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/bcnf.html
• Professor Lee’s Notes
• Wikipedia