Keys and Functional DependencyData NormalizationFunctional Dependency and KeysFunctional dependencyPowerPoint PresentationFunctional 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 24What is Normalization?Normal FormsSlide 27Steps in Normalization1NFFirst Normal Form ( 1NF )Slide 31Slide 322NFSlide 34Second Normal Form ( 2NF )Second Normal FormSlide 37Slide 38Slide 39Slide 40Slide 41Slide 421NF 2NF3NFtransitive dependency… transitive dependencySlide 47Slide 48Slide 49Slide 50Slide 51Slide 52Third Normal FormThird Normal Form ( 3 NF )Third Normal Form ( 3 NF )General Definitions of Second and Third Normal FormsSlide 5719/1/14 1Keys and Functional DependencyProf. Sin-Min LeeDepartment of Computer ScienceSan Jose State University19/1/14 2Data NormalizationPrimarily 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 readPrice: degraded query, display, reporting19/1/14 3Functional Dependency and KeysFunctional 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 dependencya constraint between two attributes (columns) or two sets of columnsA 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 DependenciesFDs defined over two sets of attributes: X, Y RNotation: 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 dependencysome examplesSSN Name, Address, BirthdateVIN Make, Model, Colornote: the LHS is the determinantso functional dependency is the technical term for determines19/1/14 9Candidate Keysan attribute (or set of attributes) that uniquely identifies a rowprimary key is a special candidate keyvalues cannot be nulle.g. ENROLL (Student_ID, Name, Address, …)PK = Student_IDcandidate key = Name, Address19/1/14 10… candidate keya 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 uniqueminimal 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 keyscandidate 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) attributesdeterminant may bea candidate keypart of a composite candidate keynonkey attribute19/1/14 14IntroductionData integrity maintained by various constraints on dataFunctional dependencies are application constraints that help DB model real-world entityJoin 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 21Keyssuperkey: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 22KeysThe 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 23KeysIf a relation schema has more than one “minimal” key, each is called a candidate key.19/1/14 24Keysone 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 25What 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 attributes, entity sets, and relationships.19/1/14 26Normal FormsA relation is in specific normal form if it satisfies the set of requirements or constraints for that form. All of the normal forms are nested in that each satisfies the constraints of the previous one but is a "better" form because each eliminates flaws found in the previous19/1/14 2719/1/14 28Steps in Normalization19/1/14 291NFrelation is in first normal form if it contains no multivalued attributesremove repeating groups to a new table as already demonstrated, “carrying” the PK as a FK19/1/14 30First Normal Form ( 1NF )the domains of attributes must include only atomicatomic(simple, indivisible)(simple, indivisible) values values and the value of any attribute in a tuple must be a single valuesingle value from the domain of the attribute.19/1/14
View Full Document