Database System Concepts, 6th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use Chapter 8: Relational Database DesignSilberschatz, Korth and Sudarshan8.2Database System Concepts - 6th EditionChapter 8: Relational Database DesignnFeatures of Good Relational DesignnAtomic Domains and First Normal FormnDecomposition Using Functional DependenciesnFunctional Dependency TheorynAlgorithms for Functional DependenciesnDecomposition Using Multivalued Dependencies nMore Normal FormnDatabase-Design ProcessnModeling Temporal DataSilberschatz, Korth and Sudarshan8.3Database System Concepts - 6th EditionCombine Schemas?nSuppose we combine instructor and department into inst_deptl(No connection to relationship set inst_dept)nResult is possible repetition of informationSilberschatz, Korth and Sudarshan8.4Database System Concepts - 6th EditionA Combined Schema Without RepetitionnConsider combining relations lsec_class(sec_id, building, room_number) and lsection(course_id, sec_id, semester, year) into one relationlsection(course_id, sec_id, semester, year, building, room_number)nNo repetition in this caseSilberschatz, Korth and Sudarshan8.5Database System Concepts - 6th EditionWhat About Smaller Schemas?nSuppose we had started with inst_dept. How would we know to split up (decompose) it into instructor and department?nWrite a rule “if there were a schema (dept_name, building, budget), then dept_name would be a candidate key”nDenote as a functional dependency: dept_name building, budgetnIn inst_dept, because dept_name is not a candidate key, the building and budget of a department may have to be repeated. lThis indicates the need to decompose inst_deptnNot all decompositions are good. Suppose we decompose employee(ID, name, street, city, salary) intoemployee1 (ID, name)employee2 (name, street, city, salary)nThe next slide shows how we lose information - we cannot reconstruct the original employee relation - and so, this is a lossy decomposition.Silberschatz, Korth and Sudarshan8.6Database System Concepts - 6th EditionA Lossy DecompositionSilberschatz, Korth and Sudarshan8.7Database System Concepts - 6th EditionExample of Lossless-Join DecompositionExample of Lossless-Join Decomposition nLossless join decompositionnDecomposition of R = (A, B, C)R1 = (A, B)R2 = (B, C)AB12AB12rB,C(r)A (r) B (r)AB12CABB12CABCABA,B(r)Silberschatz, Korth and Sudarshan8.8Database System Concepts - 6th EditionFirst Normal FormnDomain is atomic if its elements are considered to be indivisible unitslExamples of non-atomic domains:4Set of names, composite attributes4Identification numbers like CS101 that can be broken up into partsnA relational schema R is in first normal form if the domains of all attributes of R are atomicnNon-atomic values complicate storage and encourage redundant (repeated) storage of datalExample: Set of accounts stored with each customer, and set of owners stored with each accountlWe assume all relations are in first normal form (and revisit this in Chapter 22: Object Based Databases)Silberschatz, Korth and Sudarshan8.9Database System Concepts - 6th EditionFirst Normal Form (Contd)nAtomicity is actually a property of how the elements of the domain are used.lExample: Strings would normally be considered indivisible lSuppose that students are given roll numbers which are strings of the form CS0012 or EE1127lIf the first two characters are extracted to find the department, the domain of roll numbers is not atomic.lDoing so is a bad idea: leads to encoding of information in application program rather than in the database.Silberschatz, Korth and Sudarshan8.10Database System Concepts - 6th EditionGoal Devise a Theory for the FollowingnDecide whether a particular relation R is in “good” form.nIn the case that a relation R is not in “good” form, decompose it into a set of relations R1, R2, ., Rn such that leach relation is in good form lthe decomposition is a lossless-join decompositionnOur theory is based on:lfunctional dependencieslmultivalued dependenciesSilberschatz, Korth and Sudarshan8.11Database System Concepts - 6th EditionFunctional DependenciesnConstraints on the set of legal relations.nRequire that the value for a certain set of attributes determines uniquely the value for another set of attributes.nA functional dependency is a generalization of the notion of a key.Silberschatz, Korth and Sudarshan8.12Database System Concepts - 6th EditionFunctional Dependencies (Cont.)nLet R be a relation schema R and RnThe functional dependency holds on R if and only if for any legal relations r(R), whenever any two tuples t1 and t2 of r agree on the attributes , they also agree on the attributes . That is, t1 = t2 t1 = t2 nExample: Consider r(A,B ) with the following instance of r.nOn this instance, A B does NOT hold, but B A does hold. 141 53 7Silberschatz, Korth and Sudarshan8.13Database System Concepts - 6th EditionFunctional Dependencies (Cont.)nK is a superkey for relation schema R if and only if K RnK is a candid