《山东大学数据库系统英语课件08关系数据库设计》由会员分享，可在线阅读，更多相关《山东大学数据库系统英语课件08关系数据库设计（92页珍藏版）》请在金锄头文库上搜索。
1、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 Domain
2、s 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 Con
3、cepts - 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 RepetitionnCon
4、sider 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 Edition
5、What 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 bu
6、ilding, 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, na
7、me)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
8、 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.8Databas
9、e 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
10、 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
11、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
12、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, Kort
13、h 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 l
14、the 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 certai
15、n 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 functi
16、onal 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
