DOC PREVIEW
SJSU CS 157A - Functional Dependency

This preview shows page 1-2-3-25-26-27 out of 27 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 27 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 27 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 27 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 27 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 27 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 27 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23Slide 24Slide 25Slide 26Slide 27FunctionalDependencyCS157a Sec. 2Koichiro HongoWhat is Functional Dependency?●An attribute or set of attributes X is said to functionally determine another attribute Y (written X  Y) if and only if each X value isassociated with at most one Y value. Customarily we call X determinant set and Y a dependent set.●A functional dependency is a constraint between two sets of attributes in a relation from a database.●So if we are given the value of X we can determine the value of Y.●The determination of functional dependenciesis an important part of designing databases inthe relational model, and in database normalization and denormalization. Why is Fanctional DependencyImportant?●The functional dependencies, along with the attribute domains, are selected so as to generate constraints that would exclude as much data inappropriate to the user domain from the system as possible.Functional DependencyInference RulesReflexivity: If B is a subset of A then A functionally determines B For example: {name, location}  {name}Functional DependencyInference Rules(cont.)Augmentation: If B is a subset of A and C functionally determines D then A and C functionally determine B and D For example: {name, location} and {birthdate, time}  {name} and {age} (as {name} is a subset of {name, location} and {birthdate, time} functionally determines {age})Functional DependencyInference Rules(cont.)Transitivity: If A functionally determines B and B functionally determines C then A functionally determines C For example: {name, location}  {initials} (as {name, location} functionally determines {name} and {name} functionally determines {initials})Functional DependencyInference Rules(cont.)Pseudo transitivity: If A functionally determines B and B and C functionally determine D then A and C functionally determine DFunctional DependencyInference Rules(cont.)Union: If A functionally determines B and A functionally determines C then A functionally determines B and C For example: {name, location, birthdate, time}  {initials, age} (as {name, location, birthdate, time}  {initials} and {name, location, birthdate, time}  {age})Functional DependencyInference Rules(cont.)Decomposition: If A functionally determines B and C then A functionally determines B and A functionally determines C For example: {name, location, birthdate, time}  {initials, age} implies that {name, location, birthdate, time}  {initials} and {name, location, birthdate, time}  {age}Trivial Functional DependenciesSome functional dependencies are said to be trivial because they are satisfied by all relation. For example:A  AX  Y if Y is a subset of XKeys and Functional Dependencies●Keys and, more generally, functional dependencies,are constraints on the database that require relationsto satisfy certain properties. Relations that satisfyall such constraints are legal relations.What is Superkey?(review)A superkey is defined in the relational model as a set of attributes of a relation for which it holds that in all instances of the relation there are no two distinct tuples that have the same values for the attributes in this set. Equivalently a superkey can also be defined as those sets of attributes of a relation upon which allattributes of the relation are functionally dependent.Keys and Functional Dependencies(cont.)Functional dependencies allow us to express constraintsthat we cannot express with superkeys.Let's consider the schema of the example in the textbook(p.265) Figure 7.2. . . L-100 . . .loan_number amount . . . 10000 . . .loan . . . 23-652 15-202 23-521 . . .customer_idloan_number . . . L-100 L-100 L-100 . . .borrower . . . 23-652 15-202 23-521 . . .customer_id loan_number . . . L-100 L-100 L-100 . . .amount . . . 10000 10000 10000 . . .bor_loanFigure 7.2 Partial list of tuples in relations loan, borrower, and bor_loanKeys and Functional Dependencies(cont.)Figure 7.2, we consider the schemabor_loan = (customer_id, loan_number, amount)in which the functional dependency loan_number  amount holds because for each loan (identified by loan_number) there is a unique amount. We denote the fact that the pair of attributes(customer_id, loan_amount) forms a superkey for bor_loan by writing:customer_id, loan_number  customer_id, loan_number, amountor, equivalently,customer_id, loan_number  bor_loanKeys and Functional Dependencies(cont.)We shall use functional dependencies in two ways: 1. To test relations to see whether they are legal under a given set of functional dependencies. If a relation r is a legal under a set F of functional dependencies, we say that r satisfies F. 2. To specify constraints on the set of legal relations. We shall thus concern ourselves with only those relations that satisfy a given set of functional dependencies. If we wish to constrain ourselves to relations on schema R that satisfy a set F of functional dependencies, we say that F holds on R.Database Normalization●Database normalization relates to the level of redundancy in a relational database's structure. The key idea is to reduce the chance of having multiple different versions of the same data, like an address, by storing all potentially duplicated data in different tables and linking to them instead of using a copy. Then updating the address in one place will instantly change all the places where the address is used.●Well-normalized databases have a schema that reflects the true dependencies between tracked quantities. This means that updates can be quickly performed with little risk of data becoming inconsistent.●Database normalization relates to the level of redundancy in a relational database's structure. The key idea is to reduce the chance of having multiple different versions of the same data, like an address, by storing all potentially duplicated data in different tables and linking to them instead of using a copy. Then updating the address in one place will instantly change all the places where the address is


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?