DOC PREVIEW
SJSU CS 157A - Keys and Functional Dependency

This preview shows page 1-2-3-4-28-29-30-31-57-58-59-60 out of 60 pages.

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

Unformatted text preview:

Keys and Functional DependencyData NormalizationFunctional Dependency and KeysFunctional dependencySlide 5Functional DependenciesSlide 7… functional dependencyCandidate Keys… candidate keykeys and dependenciesSlide 12determinants & candidate keysIntroductionSlide 15Slide 16Slide 17Slide 18Slide 19Normal Forms provide database designers with:KeysSlide 22Slide 23Slide 24Slide 25Slide 26Slide 27What is Normalization?Normal FormsSlide 30Steps in Normalization1NFFirst Normal Form ( 1NF )Slide 34Slide 352NFSlide 37Second Normal Form ( 2NF )Second Normal FormSlide 40Slide 41Slide 42Slide 43Slide 44Slide 451NF 2NF3NFtransitive dependency… transitive dependencySlide 50Slide 51Slide 52Slide 53Slide 54Slide 55Third Normal FormThird Normal Form ( 3 NF )Third Normal Form ( 3 NF )General Definitions of Second and Third Normal FormsSlide 6019/1/14 1Keys and Functional DependencyProf. Sin-Min LeeDepartment of Computer ScienceSan Jose State University19/1/14 2Data NormalizationPrimarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data.The process of decomposing relations with anomalies to produce smaller, well-structured relations.Primary Objective: Reduce Redundancy,Reduce nulls,Improve “modify” activities:insert, update, delete, but not readPrice: degraded query, display, reporting19/1/14 3Functional Dependency and KeysFunctional Dependency: The value of one attribute (the determinant) determines the value of another attribute.Candidate Key: Each non-key field is functionally dependent on every candidate key.19/1/14 4Functional dependencya constraint between two attributes (columns) or two sets of columnsA  B if “for every valid instance of A, that value of A uniquely determines the value of B”or …A B if “there exists at most one value of B for every value of A”19/1/14 519/1/14 6Functional DependenciesFDs defined over two sets of attributes: X, Y  RNotation: X  Y reads as “X determines Y”If X  Y, then all tuples that agree on X must also agree on YX Y Z1 2 32 4 51 2 41 2 72 4 83 7 9R19/1/14 7X Y Z1 2 32 4 51 2 41 2 72 4 83 7 9X Y ZFunctional Dependencies (example)19/1/14 8… functional dependencysome examplesSSN  Name, Address, BirthdateVIN  Make, Model, Colornote: the LHS is the determinantso functional dependency is the technical term for determines19/1/14 9Candidate Keysan attribute (or set of attributes) that uniquely identifies a rowprimary key is a special candidate keyvalues cannot be nulle.g. ENROLL (Student_ID, Name, Address, …)PK = Student_IDcandidate key = Name, Address19/1/14 10… candidate keya candidate key must satisfy:unique identification. implies that each nonkey attribute is functionally dependent on the key (for not(A  B) to be true, A must occur more than once (with a different B), or A must map to more than one B in a given row)nonredundancy no attribute in the key can be deleted and still be uniqueminimal set of columns (Simsion)19/1/14 11keys and dependenciesEMPLOYEE1 (Emp_ID, Name, Dept_Name, Salary) Emp_ID Name Dept_Name Salaryfunctional dependencydeterminant19/1/14 12EMPLOYEE2 (Emp_ID, Course_Title, Name, Dept_Name, Salary, Date_Completed)Emp_IDCourse_TitleName Dept_ Name SalaryDate_Comp.not fully functionally dependant on the primary key19/1/14 13determinants & candidate keyscandidate key is always a determinant (one way to find a determinant)determinant may or may not be a candidate key candidate key is a determinant that uniquely identifies the remaining (nonkey) attributesdeterminant may bea candidate keypart of a composite candidate keynonkey attribute19/1/14 14IntroductionData integrity maintained by various constraints on dataFunctional dependencies are application constraints that help DB model real-world entityJoin dependencies are a further constraint that help resolve some FD constraint limitations19/1/14 1519/1/14 1619/1/14 1719/1/14 1819/1/14 1919/1/14 20Normal Forms provide database designers with:A formal framework for analyzing relation schemas based on their keys and on the functional dependencies among their attributes.A series of tests that can be carried out on individual relation schemas so that the relational database can be normalized to any degree.19/1/14 21Keyssuperkey:a superkey is a set of attributes S  R={A1,A2,….An} with the property that no two tuples t1 and t2 in any relation state r of R will have t1[S] = t2[S].A key K is a superkey with the additional property that removal of any attribute from K will cause K not to be a superkey anymore.19/1/14 22KeysThe difference between a key and a superkey is that a key has to be “minimal”.Example:{SSN} is a key for EMPLOYEE, whereas {SSN}, {SSN,ENAME}, {SSN, ENAME, BDATE} are all superkeys.19/1/14 23KeysIf a relation schema has more than one “minimal” key, each is called a candidate key.19/1/14 24Keysone of the candidate keys is designated to be the primary key.Each relation schema must have a primary key.For example, {SSN} is the only candidate key for EMPLOYEE, so it is also the primary key.19/1/14 25R(A B C D E)FD1. A -> CFD2. BC ->DFD3. E ->AB result = ABy FD1. A -> C A resultresult = {A, C}By FD2. BC -> D BC resultresult = {A, C}By FD3. E ->AB E resultresult = {A, C} {A}+ = {A, C}19/1/14 26Similarly {B}+ = {B} {C}+ = {C} {D}+ = {D} {E}+ = {E,A,B,C,D}E is a candidate keyNow, we see{AB}+ = {ABCD} {AC}+ = {AC} {AD}+ = {ACD}{BC}+ = {BCD} {BD}+ = {BD} {CD}+ = {CD}{ABC}+ = {ABCD} {ABD}+ = {ABCD} {BCD}+ = {BCD}{ACD}+ = {ACD}19/1/14 27What is the largest normal form of this table?R(A B C D E)FD1. A ->CFD2. BC ->DFD3. E ->ABAnswer: {E} is the only candidate key of RThe non-prime attributes are: A, B, C, DAs FD!. A->C, we have transitive dependency.Thus R(ABCD) is 2NF but not 3NF19/1/14 28What is Normalization?The purpose of normalization is to produce a stable set of relations that is a faithful model of the operations of the enterprise. By following the principles of normalization, we can achieve a design that is highly flexible, allowing the model to be extended when needed to account for new


View Full Document

SJSU CS 157A - Keys and 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 Keys and 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 Keys and 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 Keys and 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?