cs411-20110218-2

cs411-20110218-2 - C841 1 Database Systems 05: Relational...

Info iconThis preview shows pages 1–14. Sign up to view the full content.

View Full Document Right Arrow Icon
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 4
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 6
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 8
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 10
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 12
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 14
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: C841 1 Database Systems 05: Relational Schema Design. '7 u... Motivation We have designed ER diagram, and translated it into a relational db schema R = set of R1, R2, Now what? We can do the following — specify all relevant constraints over R — implement R in SQL — start using it, making sure the constraints always remain valid However, R may not be well—designed, thus causing us a lot of problems Q: This a good design? Persons with several p_ a' a 'i' Phone Number .2. I. 1".“ .' . ' "may u — 34 “'1"7“" 9: - - . / J __ fi .94 909—438—44 P5008) 464—0028 909-4 -44 9%212) 555-4000 5W as clam /— Potential Problems - Redundancy ° Update anomalies - Deletion anomalies How do We Obtain a Good Design? JJR . Start with the original db schema Ry Narmme . it until we get a good design R* J, . . . * Des1rable propertles for R9,; — must preserve the information of R W — must have WM _ _ must WW FEW“) - if R is associated with a set of constraints C, then it should be easy to also check C over Rf“ — (must also give good query performance) OK, But 0 How do we recognize a good design R*? 0 How do we transform R into R*? 0 What we need is the “theory” of Normal Forms DB gurus have developed many normal forms Most important ones — Boyce-Codd, 3rd, and 4th normal forms If R* is in one of these forms, then R* is guaranteed to achieve certain good properties — e.g., if R* is in Boyce-Codd NF, it is guaranteed to not have certain types of redundancy DB gurus have also developed algorithms to transform R into R* that is in some of these normal forms Normal Forms (cont.) 0 DB gurus have also discussed trade—offs among normal forms - Thus, all we have to do is — learn these forms — transform R into R* in one of these forms — carefully evaluate the trade-offs 0 Many of these normal forms are defined based on various constraints — functional dependencies and keys Behind'lthe Scene: Know Whom we should blame? t Nunnal form Defined by Brief definition First normal form (1 NF) Two version: E, F, God: “may, C,J, Dale (2003)“2' . a; Tab 9 faithfully reareeenie a relalicn and has no “repeath groups" Second normal fcn'n {ENFl Ne nen-p’ime allritule in the table is functionally dependenl on e per. {ereoer '13. E'F' Goad “gm euhael] of a candimte key E.F. Cedd [l Eli'l )fi‘. see also Carlo Zeniele’e equivalent but difierenlly- Every nen-prirre allrihute ie nen-lrareflively dependenl on every key of the Third normal form SNF . l J avoieeeed definition {1932)“91 table Every non-iriviel funcljonal dependency in the table is a dependency on a euperkey Beym-Codd nornal lcrm {Erma Raymond F Boyce end E.F. Cod-d :1 denim] Every nen-lriviel mullivalued dependency in the table is a dependency on a mum normal form i4NFi Ronald Fagin [19mm super“? [13- h -' '-_- Every non-lrivial join dependency in the table is implied by me euoerlieya oi Fillh normal form (Si-lFi Ronald Fagin [19313) the tame Every mnelrainl or the table is a logical comequence of ihe table's cemein cenallainfe and key constraints Denaini‘key normal ‘erm . no (DKHF) Ronald Fagin [1951) [20] Tab e feeiuree nc non-irivial join dependencie at all (with reference to 2‘ genereflze-c join operator} Sixth nern‘el form [SNFJ Shrie Dalel Hugh Darwen, and Nikos Le'enlzoe (EDGE 10 Our Attack Plan Motivation Functional dependencies & keys Reasoning with FDs and keys Desirable properties of schema refinement Various normal forms and the trade-offs — BCNF, 3rd normal form, 4th normal form, etc. Putting all together: how to design DB schema ll Functional Dependencies and Keys 12 Better Designs Exist Break the relation into two: SSN Address flhpfi123-321-99 Sdl 909—43344 10 Green 431 Purple Phone Number (201) 555-1234 (206) 572—4312 (903) 464-0028 ' (212) 555-4000 123—321-959 123—321—99 909—433—414 a I - -433-44 l3 Functional Dependencies - A form of constraint (hence, part of the schema) - Finding them is part of the database design - Used heavily in schema refinement Definition: M AW # fl If two tuples agree on the attributes 1? 4w ~ — - , ,...A iii-#4“- Tz. Alw-- then they must also agree on the attributes AW ],.,,...13 Formally: A], A), An —' B1, B2, B ._ m “I. I4 ...
View Full Document

Page1 / 14

cs411-20110218-2 - C841 1 Database Systems 05: Relational...

This preview shows document pages 1 - 14. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online