1 / 34

course: Database Systems (A7B36DBS, Y36DBS, X36DBS)

course: Database Systems (A7B36DBS, Y36DBS, X36DBS). Lecturers: RNDr. Irena Mlynkova, Ph.D. & Mgr. Martin Necasky, Ph.D. Acknowledgement: The slides were kindly lent by Doc. RNDr. Tomas Skopal, Ph.D., Department of Software Engineering, Charles University in Prague.

knox
Télécharger la présentation

course: Database Systems (A7B36DBS, Y36DBS, X36DBS)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. course: Database Systems (A7B36DBS, Y36DBS, X36DBS) Lecturers: RNDr. Irena Mlynkova, Ph.D. & Mgr. Martin Necasky, Ph.D. Acknowledgement: The slides were kindly lent by Doc. RNDr. Tomas Skopal, Ph.D., Department of Software Engineering, Charles University in Prague

  2. Today’s lecture outline • motivation • data redundancy and update/insertion/deletion anomalies • functional dependencies • Armstrong’s axioms • attribute and dependency closures • normal forms • 3NF • BCNF Relational design – normal forms (Lect. 8)

  3. Motivation • result of relational design – a set of relational schemas • problems • data redundancy • unnecessary multiple storage of the same data – increased space cost • insert/update/deletion anomalies • insertions and updates must preserve redundant data storage • deletion might cause loss of some data • solution • relational schema normalization Relational design – normal forms (Lect. 8)

  4. Example of “abnormal” schema 1) From functional analysis we know that position determines hourly salary. However, hourly salary data is stored multiple times – redundancy. 2) If we delete employee 6, we lose the information on lecturer salary. 3) If we change the accountant hourly salary, we must do that in three places. Relational design – normal forms (Lect. 8)

  5. How could this even happen? • simply • during “manual” design of relation schemas • badly designed conceptual model • e.g., too many attributes in a class +is owned by 1 +owns 0..* Person- id - address - educationetc. Phone- serial nr.- manufacturer - modeletc. the UML diagram results in 2 tables: Person(id, address, education, ...)Mobil(serial nr., manufacturer, model, ..., id) Relational design – normal forms (Lect. 8)

  6. How could this even happen? Redundancy in attributes Manufacturer, Model, Made in, CertificateWhat happened?ClassPhoneincludes also other classes – Manufacturer, Model,... How to fix it? Two options 1) fix the UMLmodel (design of more classes) 2) alter the already created schemas (see next) Relational design – normal forms (Lect. 8)

  7. Functional dependencies • attribute-based integrity constraints defined by the user (e.g., DB application designer) • a kind of alternative to conceptual modeling (ER and UML invented much later) • functional dependency (FD) X  Y over schema R(A) • mapping fi : Xi Yi, where Xi,Yi A (where i = 1..number of FDs in R(A)) • n-tuple from Xideterminesm-tuple from Yi • m-tuple from Yiis determined by (is dependent on)n-tuple from Xi Relational design – normal forms (Lect. 8)

  8. Functional dependencies • simply, for X  Y, values in X together determine the valuesin Y • if X  Y and Y  X, then X and Y arefunctionally equivalent • could be denoted as X  Y • if X  a, where a  A, then X  a is elementary FD • FDs represent a generalizationof the key concept (identifier) • the key is a special case, see next slides Relational design – normal forms (Lect. 8)

  9. Example – wrong interpretation One might observe from the data, that:Position Hourly salaryand alsoHourly salaryPositionEmpId everythingHours completedeverythingNameeverything (but that is nonsense w.r.t. the natural meaning of the attributes) Relational design – normal forms (Lect. 8)

  10. Example – wrong interpretation newly inserted records Position Hourly salaryEmpId everything Hourly salaryPositionHours completedeverythingNameeverything Relational design – normal forms (Lect. 8)

  11. Example – correct interpretation • at first, after the data analysis the FDs are set “forever”, limiting the content of the tables • e.g. Position Hourly salaryEmpId everything • insertion of the last row is not allowed as it violates both the FDs Relational design – normal forms (Lect. 8)

  12. Armstrong’s axioms Let us haveR(A,F). LetX, Y, Z  A and F is the set of FDs 1) ifY  X, thenX  Y (trivial FD, really axiom) 2) ifX  Y andY  Z, thenX  Z (transitivity, rule) 3) ifX  Y andX  Z, thenX  YZ (composition, rule) 4) ifX  YZ, thenX  Y andX  Z (decomposition, rule) Relational design – normal forms (Lect. 8)

  13. Armstrong’s axioms Armstrong’s axioms: • are correct(sound) • what is derived from F is valid for any instance from R • are complete • all FDs valid in all instances in R (w.r.t. F) can be derived using the axioms • 1,2,3(trivial, transitivity, composition) are independent • removal of any axiom 1,2,3 violates the completeness(decomposition could be derived from trivial FD and transitivity) Proof follows from the FD definition (from the properties of mapping, respectively). Relational design – normal forms (Lect. 8)

  14. Example – deriving FDs R(A,F) A = {a,b,c,d,e}F = {ab  c, ac  d, cd  ed, e  f} We could derive, e.g.,:ab  a (trivial)ab  ac (compositionwithab  c) ab  d (transitivity withac  d)ab  cd (compositionwithab  c)ab  ed (transitivity with cd  ed)ab  e (decomposition)ab  f (transitivity) Relational design – normal forms (Lect. 8)

  15. Example – deriving the decomposition rule R(A,F) A = {a,b,c}F = {a  bc} Deriving:a  bc (assumption) bc b (trivial FD) bc c (trivial FD) a  b (transitivity) a  c (transitivity) i.e.,a  bc a  b  a c Relational design – normal forms (Lect. 8)

  16. Closure of set of FDs • closureF+of FDs set F (FDclosure) is the set of all FDsderivable from F using the Armstrong’s axioms • generallyexponential size w.r.t.|F| • a FDf isredundantin F if(F – {f})+ = F+, i.e., f can be derived from the rest of F Relational design – normal forms (Lect. 8)

  17. Example – closure of set of FDs R(A,F), A = {a,b,c,d}, F = {ab c, cd  b, ad  c} F+ = {a  a, b  b, c  c,ab a, ab b, ab c, cd  b, cd  c, cd  d, ad  a, ad  c, ad  d,abd  a, abd  b, abd  c, abd  d,abd  abcd,...} Relational design – normal forms (Lect. 8)

  18. Cover • cover of a set F is any set of FDs G such that F+=G+ • canonical cover = cover consisting of elementary FDs(decompositions are performed to obtain singleton sets on the right side) • non-redundant cover of F = cover of F after removing all redundant FDs • note the order of removing FDs matters – a redundant FD could become non-redundant FD after removing another redundant FD • implies that there may exist multiple non-redundant covers of F Relational design – normal forms (Lect. 8)

  19. Example – cover R1(A,F), R2(A,G), A = {a,b,c,d}, F ={a  c, b  ac, d  abc},G ={a  c, b  a, d  b}For the check ofG+=F+we do not have to establish the whole covers, it is sufficient to derive F from G, and vice versa, i.e.,F’ = {a  c, b  a, d  b}– decompositionG’ = {a  c, b  ac, d  abc }– transitivity and compositionG+=F+ SchemasR1 andR2 areequivalentbecause G is cover of F, while they share the attribute set A. Moreover, G isminimal cover, whileF is not (for minimal cover see next slides). Relational design – normal forms (Lect. 8)

  20. Example – redundant FDs R1(A,F), R2(A,G), A = {a,b,c,d}, F ={a  c, b  a, b  c, d  a, d  b, d  c}FDsb  c, d  a, d  c are redundantafter their removal F+ is not changed, i.e., they could be derived from the remaining FDsb  c derived using transitivitya  c, b  ad  a derived using transitivityd b, b  ad  c derived using transitivityd b, b  a, a  c Relational design – normal forms (Lect. 8)

  21. Attribute closure, key • attribute closure X+ (w.r.t. F) is a subset of attributes from A determined by X (using F) • consequence: if X+=A, then X is asuper-key • if F contains a FD X  Y andthere exist an attribute a in X such that Y  (X – a)+, then ais anattribute redundant in X  Y • reduced FD is such FD that does not contain any redundant attributes • keyisa set K  Asuch that it is a super-key (i.e., K  A) andK  A ismoreover reduced • there could exist multiple keys (at least one) • if there is no FD in F, it trivially holds A  A, i.e.,the key is the entire set A • key attribute = attribute that is in any key Relational design – normal forms (Lect. 8)

  22. Example – attribute closure R(A,F), A = {a,b,c,d}, F = {a  c, cd  b, ad  c} {a}+ = {a,c}it holdsa  c (+ triviala  a) {b}+ = {b} (trivial b b) {c}+ = {c}(trivial c c) {d}+ = {d}(trivial d d) {a,b}+ = {a,b,c}ab c(+ trivial) {a,d}+ = {a,b,c,d}ad bc(+ trivial) {c,d}+ = {b,c,d}cd b(+ trivial) Relational design – normal forms (Lect. 8)

  23. Example – redundant attribute R(A,F), A = {i,j,k,l,m}, F = {m  k, lm  j, ijk  l, j m, l i, l k} Hypothesis: k is redundant in ijk l, i.e., it holdsij  l Proof: • based on thehypothesis let’s construct FDij  ? • ijk  lremainsin F because weADD new FDij  ?so that we can use ijk  lfor construction of the attribute closure{i,j}+ • we obtain{i,j}+ = {i, j, m, k, l}, i.e.,there existsij  l which we add into F (it is the member ofF+) • now forget how ij  l got into F • becauseijk  l could be trivially derived fromij  l, it is redundant FD and we can remove it from F • so, we removed the redundant attribute kinijk  l In other words, we transformed the problem of removing redundant attribute on the problem of removing redundant FD. Relational design – normal forms (Lect. 8)

  24. FDs: can be redundant “we don’t need it” can have a closure “all derivable FDs” can be elementary “single attribute on the right side” can be reduced “no redundancies on the left side” Attributes: can be redundant “we don’t need it” can have a closure “all derivable attributes” can form (super-)keys FDs vs. attributes

  25. Minimal cover • non-redundant canonical cover that consists of only reduced FDs • i.e. no redundant FDs, no redundant attributes, decomposed FDs • is constructed by removing redundant attributes in FDs followed by removing of redundant FDs (i.e., the order matters) Example: abcd e, e  d, a  b, ac  d Correct order of reduction:Wrong order of reduction: 1. b,d are redundant 1. no redundant FDin abcd  e, i.e., removing them 2. redundant b,d in abcd  e 2. ac  d is redundant (now not a minimal cover, because ac  d is redundant) Relational design – normal forms (Lect. 8)

  26. First normal form (1NF) Every attribute in a relation schema is of simple non-structured type. (1NF is the basiccondition on „flat database“ – a table is really two-dimensional array, not a hidden graphortree) Relational design – normal forms (Lect. 8)

  27. Example – 1NF Person(Id: Integer, Name: String, Birth: Date) is in 1NF Employee(Id: Integer, Subordinate: Person[], Boss: Person) not in 1NF (nestedtable of typePersonin attributeSubordinate, and the Boss attribute is structured) Relational design – normal forms (Lect. 8)

  28. 2nd normal form (2NF) • there do not exist partial dependencies of non-key attributeson (any) key, i.e.,it holdsx  NK KK : KK  x (where NK is the set of non-key attributes and KK is subset of some key) key attribute(s) non-key attribute key Relational design – normal forms (Lect. 8)

  29. Example – 2NF  not in 2NF, becauseHQis determined by a part of key (Company) consequence: redundancyofHQ values Company, DB Server everythingCompany HQ both schemasare in 2NF Company HQ Company, DB Server everything Relational design – normal forms (Lect. 8)

  30. Transitive dependency on key • FD A → B such that A → some key (A is not a super-key), i.e., we get transitivity key → A → B • Transitivities point to probable redundancies, because from the definition of FD as a mapping: • unique values of key are mapped to the same or less unique values of A, and those are mapped to the same or less unique values of B Example in 2NF: ZIPcode → City → Country no redundancymedium redundancyhigh redundancy Relational design – normal forms (Lect. 8)

  31. key Y a a key a key X X a X 3rd normal form (3NF) • non-key attributes are not transitively dependent on key • as the 3NF using the above definition cannot be tested without construction of F+, we use a definition that assumes only R(A,F) • at least one condition holds for each FD X a (where X  A, a A): • FD is trivial • X is super-key • a is part of a key (i.e., a key attribute) Relational design – normal forms (Lect. 8)

  32. Example – 3NF CompanyeverythingZIPcodeHQ is in 2NF, not in 3NF(transitive dependency ofHQonkeythroughZIPcode) consequence: redundancyofHQ values CompanyeverythingZIPcodeeverything both schemas arein 3NF Relational design – normal forms (Lect. 8)

  33. Boyce-Codd normal form (BCNF) • every attribute is (non-transitively) dependent on key • more exactly, in a given schema R(A, F) there holds at least one condition for each FD X a (where X  A, a A): • FD is trivial • X is super-key • the same as 3NF without the last option (a is key attribute) a key a X a key X X Relational design – normal forms (Lect. 8)

  34. Example – BCNF Pilot, Day  everythingPlane, Day  everythingDestination  Pilot is in 3NF, not in BCNF (Pilot is determined by Destination, which is not a super-key) consequence: redundancy of Pilot values Destination  PilotPlane, Day  everything both schemasare in BCNF Relational design – normal forms (Lect. 8)

More Related