Unformatted text preview:

Chapter 7: Relational Database DesignSlide 2First Normal FormFirst Normal Form (Contd.)Pitfalls in Relational Database DesignExampleDecompositionExample of Non Lossless-Join DecompositionGoal — Devise a Theory for the FollowingFunctional DependenciesFunctional Dependencies (Cont.)Slide 12Use of Functional DependenciesSlide 14Closure of a Set of Functional DependenciesSlide 16Procedure for Computing F+Closure of Functional Dependencies (Cont.)Closure of Attribute SetsExample of Attribute Set ClosureUses of Attribute ClosureCanonical CoverExtraneous AttributesTesting if an Attribute is ExtraneousSlide 25Computing a Canonical CoverGoals of NormalizationLossless-join DecompositionSlide 29Dependency PreservationTesting for Dependency PreservationBoyce-Codd Normal FormSlide 33Testing for BCNFBCNF Decomposition AlgorithmExample of BCNF DecompositionSlide 37Testing Decomposition for BCNFBCNF and Dependency PreservationThird Normal Form: MotivationThird Normal Form3NF ExampleRedundancy in 3NFTesting for 3NF3NF Decomposition Algorithm3NF Decomposition Algorithm (Cont.)Slide 47Applying 3NF to Banker-info-schemaComparison of BCNF and 3NFDesign GoalsTesting for FDs Across RelationsHow good is BCNF?How good is BCNF? (Cont.)Slide 54Multivalued Dependencies (MVDs)MVD (Cont.)Slide 57Example (Cont.)Use of Multivalued DependenciesTheory of MVDsFourth Normal FormRestriction of Multivalued Dependencies4NF Decomposition AlgorithmSlide 64Further Normal FormsOverall Database Design ProcessER Model and NormalizationUniversal Relation ApproachSlide 69Universal Relation Approach (Contd.)Denormalization for PerformanceOther Design IssuesProof of Correctness of 3NF Decomposition AlgorithmCorrectness of 3NF Decomposition AlgorithmCorrectness of 3NF Decomposition Algorithm (Contd.)Correctness of 3NF Decomposition (Contd.)Slide 77End of ChapterSample lending RelationSample Relation rThe customer RelationThe loan RelationThe branch RelationThe Relation branch-customerThe Relation customer-loanThe Relation branch-customer customer-loanAn Instance of Banker-schemaTabular Representation of Relation bc: An Example of Reduncy in a BCNF RelationAn Illegal bc RelationDecomposition of loan-infoRelation of Exercise 7.4Example of Lossy-Join DecompositionNormalization Using Functional DependenciesSlide 95Database System Concepts©Silberschatz, Korth and SudarshanSee www.db-book.com for conditions on re-use Chapter 7: Relational Database DesignChapter 7: Relational Database Design©Silberschatz, Korth and Sudarshan7.2Database System Concepts, 5th Ed.Chapter 7: Relational Database DesignChapter 7: Relational Database DesignFirst Normal FormPitfalls in Relational Database DesignFunctional DependenciesDecompositionBoyce-Codd Normal FormThird Normal FormMultivalued Dependencies and Fourth Normal FormOverall Database Design Process©Silberschatz, Korth and Sudarshan7.3Database System Concepts, 5th Ed.First Normal FormFirst Normal FormDomain is atomic if its elements are considered to be indivisible unitsExamples of non-atomic domains:Set of names, composite attributesIdentification numbers like CS101 that can be broken up into partsA relational schema R is in first normal form if the domains of all attributes of R are atomicNon-atomic values complicate storage and encourage redundant (repeated) storage of dataE.g. Set of accounts stored with each customer, and set of owners stored with each accountWe assume all relations are in first normal form (revisit this in Chapter 9 on Object Relational Databases)©Silberschatz, Korth and Sudarshan7.4Database System Concepts, 5th Ed.First Normal Form (Contd.)First Normal Form (Contd.)Atomicity is actually a property of how the elements of the domain are used.E.g. Strings would normally be considered indivisible Suppose that students are given roll numbers which are strings of the form CS0012 or EE1127If the first two characters are extracted to find the department, the domain of roll numbers is not atomic.Doing so is a bad idea: leads to encoding of information in application program rather than in the database.©Silberschatz, Korth and Sudarshan7.5Database System Concepts, 5th Ed.Pitfalls in Relational Database DesignPitfalls in Relational Database DesignRelational database design requires that we find a “good” collection of relation schemas. A bad design may lead to Repetition of Information.Inability to represent certain information.Design Goals:Avoid redundant dataEnsure that relationships among attributes are represented Facilitate the checking of updates for violation of database integrity constraints.©Silberschatz, Korth and Sudarshan7.6Database System Concepts, 5th Ed.ExampleExampleConsider the relation schema: Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount)Redundancy:Data for branch-name, branch-city, assets are repeated for each loan that a branch makesWastes space Complicates updating, introducing possibility of inconsistency of assets valueNull valuesCannot store information about a branch if no loans exist Can use null values, but they are difficult to handle.©Silberschatz, Korth and Sudarshan7.7Database System Concepts, 5th Ed.DecompositionDecompositionDecompose the relation schema Lending-schema into: Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount)All attributes of an original schema (R) must appear in the decomposition (R1, R2):R = R1  R2Lossless-join decomposition.For all possible relations r on schema Rr = R1 (r) R2 (r)©Silberschatz, Korth and Sudarshan7.8Database System Concepts, 5th Ed.Example of Non Lossless-Join Decomposition Example of Non Lossless-Join Decomposition Decomposition of R = (A, B)R1 = (A) R2 = (B)A B121AB12rA(r)B(r)A (r) B (r)A B1212©Silberschatz, Korth and Sudarshan7.9Database System Concepts, 5th Ed.Goal — Devise a Theory for the FollowingGoal — Devise a Theory for the FollowingDecide whether a particular relation R is in “good” form.In the case that a relation R is not in “good” form, decompose it into a set of relations {R1, R2, ..., Rn} such that each relation is in good form the decomposition is a lossless-join decompositionOur theory is based on:functional


View Full Document

UMBC CMSC 461 - Relational Database Design

Download Relational Database Design
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 Relational Database Design 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 Relational Database Design 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?