1 / 16

Functional Dependency

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”

diem
Download Presentation

Functional Dependency

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Functional Dependency Presenter Usman Saeed

  2. 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.)

  3. 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)

  4. 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.

  5. 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.

  6. Example Table

  7. 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.

  8. Corrected Table(1 NF)

  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. 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)

  14. The relation is in 3NF but not in BCNF because there are dependencies • sno -> snamecno -> cname

  15. Bibliography • http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/bcnf.html • Professor Lee’s Notes • Wikipedia

  16. Thank you

More Related