DOC PREVIEW
SJSU CS 157A - Functional Dependency

This preview shows page 1-2-3-4-5 out of 16 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 16 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 16 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 16 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 16 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 16 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 16 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Functional DependencyDefinitionUses of Functional DependenciesNormalizationFirst Normal Form(1NF)Example TableExample Table (Problems)Corrected Table(1 NF)Second Normal FormSummaryThird Normal FormBCNFExampleSlide 14BibliographyThank youFunctional DependencyFunctional DependencyPresenterPresenterUsman SaeedUsman SaeedDefinitionDefinitionDefinition:–constraints on relations()–characteristic of an attribute where values are determined by another attribute’s valuesA  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 DependenciesUses 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)NormalizationNormalization•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)First Normal Form(1NF)•Atomic Form: A domain is Atomic ifelements of that domain are indivisibleExample: 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 Example Table Title Actor Actor1 Genre Year StudioSpeed Keanu ReaveSandraBullockAction,Drama1999 UniversalStudiosKill Bill Uma ThurmanLucyLiuAction 2003 LionsGateExample Table (Problems)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.Corrected Table(1 NF)Corrected Table(1 NF)Tile Actor Genre Year StudioSpeed Keanu ReaveDrama 1999 UniversalStudiosSpeed Sandra BullockAction 1999 UniversalStudiosKill Bill Lucy Liu Action 2003 LionsGateKill Bill Uma ThurmanAction 2003 LionsGateSecond Normal FormSecond Normal FormA 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.SummarySummary•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 FormThird 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.BCNFBCNF•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.ExampleExample•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)•The relation is in 3NF but not in BCNF because there are dependencies •sno -> snamecno -> cnameBibliographyBibliography•http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/bcnf.html•Professor Lee’s Notes•WikipediaThank youThank


View Full Document

SJSU CS 157A - Functional Dependency

Documents in this Course
SQL

SQL

18 pages

Lecture

Lecture

44 pages

Chapter 1

Chapter 1

56 pages

E-R Model

E-R Model

16 pages

Lecture

Lecture

48 pages

SQL

SQL

15 pages

SQL

SQL

26 pages

Lossless

Lossless

26 pages

SQL

SQL

16 pages

Final 3

Final 3

90 pages

Lecture 3

Lecture 3

22 pages

SQL

SQL

25 pages

Load more
Download Functional Dependency
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Functional Dependency and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Functional Dependency 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?