Functional Dependency Graphs and SQLData NormalizationFunctional Dependency and KeysFunctional dependencySlide 5Functional DependenciesSlide 7Slide 8… functional dependencyCandidate Keys… candidate keykeys and dependenciesSlide 13determinants & candidate keysIntroductionSlide 16Slide 17Slide 18Slide 19Slide 20What is SQL?HistoryTable BasicsSlide 24Basic StructureSelecting DataSelecting Data cont…Slide 28The where ClauseThe from ClauseCreating TablesMost Common Data TypesCreating Tables cont…Inserting into a TableUpdating RecordsDeleting RecordsDrop a TableOrdering the Display of TuplesAggregatesSlide 40Aggregates: Proper and Improper UsageGroupingGROUP BYGROUP BY - ExampleHAVING ClauseEvaluation of GroupBy with HavingExampleORDER BY ClauseQuery Evaluation StrategyNested QueriesCorrelated Nested QueriesCorrelated Nested Queries (con’t)Division – SQL SolutionDivisionSlide 55Slide 56Slide 57Slide 58Slide 59Slide 60SQL and Relational AlgebraConstructing SQLSlide 63Slide 64Aggregate FunctionsEmbedded SQLEmbedded SQL cont…Dynamic SQLSlide 6919/1/14 1Functional Dependency Graphs and SQLProf. 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 Graph(example)19/1/14 8R( A B C D) 1 1 2 1 2 1 3 2 3 1 2 3 2 1 2 3 1 2 1 2 3 1 1 2A B C D*A B C D*Two Candidate Keys {AC}, {AD}Super Keys {AC}, {AD} {ABC}, {ACD} {ABD}, {ABCD}19/1/14 9… functional dependencysome examplesSSN Name, Address, BirthdateVIN Make, Model, Colornote: the LHS is the determinantso functional dependency is the technical term for determines19/1/14 10Candidate 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 11… 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 12keys and dependenciesEMPLOYEE1 (Emp_ID, Name, Dept_Name, Salary) Emp_ID Name Dept_Name Salaryfunctional dependencydeterminant19/1/14 13EMPLOYEE2 (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 14determinants & 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 15IntroductionData 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 1619/1/14 1719/1/14 1819/1/14 1919/1/14 2019/1/14 21What is SQL?It is a language used to communicate with a database.SQL statements are used to perform tasks such as update or retrieve data from a database.Standard SQL commands: “Select”, “Create”, “Insert”, “Update”, “Delete”, and “Drop” can be used to accomplish almost everything that a database needs to do.19/1/14 22HistoryOriginally developed by IBM at San Jose Research Laboratory (a.k.a. Almaden Research Center)Oracle, Sybase, Microsoft SQL server are some common relational database management systems that use SQL19/1/14 23Table BasicsData or information for the database are stored in tables.Tables are uniquely identified by their names and are comprised of columns and rows.Rows contain data for the columns.City State High LowPhoenix Arizona105 90Tucson Arizona101 92Flagstaff Arizona88 69San DiegoCalifornia77 60San Jose California80 6519/1/14 2419/1/14 25Basic StructureThe basic structure of an SQL expression consists of three clauses: select, from, and where.The select clause corresponds to the projection operation of the relational algebra. It is used to list attributes desired in the result.The from clause corresponds to the Cartesian-product operation of the relational algebra. It lists the relations to be scanned in the evaluation of the expression.The where clause corresponds to the selection predicate of the relational algebra. It consists of a predicate involving attributes of the relations that appear in the from clause.19/1/14 26Selecting DataThe Select statement is used to query the database and retrieve selected data that match the criteria that you specifyExample: select “column1”[,”column2”, etc]from “tablename”[where “condition”];[ ] = optional continued…19/1/14 27Selecting Data
View Full Document
Unlocking...