This preview shows page 1-2-3-4-5-6-7-49-50-51-52-53-54-55-98-99-100-101-102-103-104 out of 104 pages.
Final Exam Revision 4Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Relational Databases Are Most CommonSlide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Problems caused by redundancySlide 20Slide 21DecompositionsSlide 23Functional DependenciesSlide 25Slide 26Slide 27Slide 28Slide 29Slide 30Slide 31Slide 32Slide 33Slide 34Slide 35Slide 36Slide 37Closure of a set of FDsSlide 39Slide 40Proof of Armstrong’s Axioms (soundness)Slide 42Slide 43Additional rulesSlide 45Slide 46Slide 47Attribute ClosureSlide 49Slide 50Slide 51Relational Database DesignSlide 53Slide 54Slide 55Slide 56Slide 57Slide 58Third Normal FormSlide 60Slide 61Slide 62Slide 63Slide 64DecompositionSlide 66Slide 67Slide 68Lossless Join DecompositionSlide 70Slide 71Slide 72Slide 73Slide 74Slide 75Dependency PreservationSlide 77Slide 78Slide 79Slide 80Slide 81Slide 82NormalizationBCNF DecompositionSlide 85Slide 86Slide 87Slide 88Canonical CoverSlide 90Slide 91Slide 92Slide 93Slide 94Slide 95Slide 96Slide 97Slide 98Slide 993NF Synthesis AlgorithmSlide 101Slide 102BCNF VS 3NFDesign GoalsFinal Exam Revision 4Final Exam Revision 4Prof. Sin-Min LeeDepartment of Computer ScienceTerminology•Database – an organized collection of data•Table – data organized in rows and columns•Attribute – a variable or item•Record – a collection of attributes•Domain – the range of values an attribute may take•Index/key – attribute(s) used to identify, organize, or order records in a databaseID AREA Perim Class Code27 39.2 55.4 a 11z14 192.4 77.3 a 119f integerdomain realdomain alpha-numericdomain(a string)Record (or tuple)Attribute (or item or field)Common components of a database:Common Database Models:•Hierarchical• Network• RelationalData organized with parent-child connections in a tree-like structureBranches group successively more similar dataAdvantages:Logical structure, quick searches for related itemsDisadvantages:Significant effort required to create the tree structure.Slow searches across branchesData elements connected in a cross-linked structureAdvantages:Quick searches, reduced (often no) duplication.Disadvantages:Significantly complex structuring – maintenance is difficultRelational Database ModelMinimal row-column structureItems/records with specified domains (possible values)Advantages:Minimum structure, easy programming, flexibleDisadvantages:Relatively slow, a few restrictions on attribute contentRelational Databases Are Most CommonRelational Databases Are Most Common•Flexible•Relatively easy to create and maintain•Computer speeds have overcome slow response in most applications•Low training costs•Inertia – many tools are available for RDBMS, large personnel poolEight Fundamental OperationsRestrict (query) – subset by rowsProject – subset by columnsProduct – all possible combinationsDivide – inverse of productEight Fundamental OperationsUnion – combine top to bottomIntersect – row overlapDifference – row non-overlapJoin (relate) – combine by a key columnMain Operations with Relational TablesQuery / RestrictConditional selectionCalculation and AssignmentSortrank based on attributesRelate/JoinTemporarily combine two tables by an indexQuery / Restrict Operations with Relational TablesSet AlgebraUses operations less than (<), greater than(>), equal to (=), and not equal to (<>).Boolean Algebrauses the conditions OR, AND, and NOT to select features. Boolean expressions are evaluated by assigning an outcome, True or False, to each condition.Query / Restrict Operations with Relational TablesEach record is inspected and is added to the selected set if it meets one to several conditionsAND, OR and NOT may be applied alone or in combinationsAND typically decreases the number of records selected OR typically increases the number of records selectedNOT Is the negation operation and is interpreted as meaning select those that do not meet the condition following the NOT.Query / Restrict – simple, ANDQuery / Restrict – OR, NOTOperation Order is Important in Query(D OR E) AND F may not be the same as D OR (E AND F)NOT (A and B) may not be the same as [ NOT (A) AND NOT (B)]Typically need to clarify order with delimitersRelational TablesRelational tables have many advantages, butIf improperly structured, they may suffer from:Poor performanceInconsistencyRedundancyDifficult maintenanceThis is common because most users do not understand the concepts Normal Forms in relational tables.Relational TablesRelational tables have many advantages, butIf improperly structured, table may suffer from:Poor performanceInconsistencyRedundancyDifficult maintenanceThis is common because most users do not understand the concepts Normal Forms in relational tables.Problems caused by redundancyProblems caused by redundancy•Redundant Storage–Some information is stored repeatedly.•Update Anomalies–If one copy of such repeated data is updated, an inconsistency is created, unless all copies are similarly updated.•Insertion anomalies–It may not be possible to store certain information unless some other unrelated information is stored.•Deletion Anomalies–It may not be possible to delete certain information without losing some other unrelated information.•Redundant Storage–The rating value 8 corresponds to the hourly wage 10, and this association is repeated three times.•Update Anomalies–The hourly_wages in the first tuple could be updated without making a similar change in the second tuple.Id name lot rating Hourly_wages Hours_worked123-22-3666 Attishoo 48 8 10 40231-31-5368 Smiley 22 8 10 30131-24-3650 Smethurst 35 5 7 30434-26-3751 Guldu 35 5 7 32612-67-4134 Madayan 35 8 10 40•Insertion Anomalies–We cannot insert a full tuple for an employee unless we know the hourly wage for the employee’s rating value.•Deletion Anomalies–If we delete all tuples with a given rating value (e.g. tuples of Smethurst and Guldu) we lose the association between the rating value and its hourly_wage value.Id name lot rating Hourly_wages Hours_worked123-22-3666 Attishoo 48 8 10 40231-31-5368 Smiley 22 8 10 30131-24-3650 Smethurst 35 5 7 30434-26-3751 Guldu 35 5 7 32612-67-4134 Madayan 35 8 10 40DecompositionsDecompositions•Intuitively, redundancy arise when a relational schema forces an association between attributes that is not natural.•Functional dependencies can be used to identify such situations and suggest refinements to the schema.•The essential idea is
View Full Document
Unlocking...