Schema Refinement and Normal Forms ENGI 3675 Database

Schema Refinement and Normal Forms ENGI 3675 Database Systems

2 Overview • Cow textbook chapter 19 • ER models give a good overview of the DB structure and design ▫ Then translated into Conceptual Schema and implemented with SQL commands • However, they do not fully account for Integrity Constraints (IC) ▫ Taking them into account will allow us to refine the conceptual schema, and create better DB ▫ Does so by eliminating redundancy in DB

3 The Evils of Redundancy • Redundancy: storing the same data at several placed and/or under several forms • It’s at the root of several problems associated with relational schemas ▫ Redundant storage, which at the very least is a waste of disk space and I/O operations ▫ Update anomalies: unless all copies of the data are updated at once, there will be discrepancies ▫ Insertion anomalies: inserting some information may require inserting other, unrelated information ▫ Deletion anomalies: deleting information may cause the loss of other, unrelated information

4 The Evils of Redundancy • Salary function of rating: sin name rating salary 123456789 Bob 8 100, 000 234567890 Dave 8 100, 000 345678901 Lisa 5 70, 000 456789012 Bob 5 70, 000 567890123 Jimmy 8 100, 000 • Storage: We store three instances of 8 -100, 000 relationship, and two of 5 -70, 000 • Update: If we increase salary without changing rating, the DB becomes inconsistent • Insert: we cannot add a rated employee without knowing the salary for that rating • Delete: if we delete all employees rated 8, we lose the salary for that rating

5 The Evils of Redundancy • Problem comes from using a schema that forces an association between attributes ▫ Integrity constraints, in particular functional dependencies, can be used to identify schemas with such problems • Solution is decomposition, replacing a large redundant schema by a set of smaller, non-redundant schemas sin name rating 123456789 Bob 8 rating salary 234567890 Dave 8 5 70, 000 345678901 Lisa 5 8 100, 000 456789012 Bob 5 567890123 Jimmy 8

6 Functional Dependencies • A functional dependency (FD) is a kind of integrity constraint defined as: ▫ Given a table T with (sets of) attributes X and Y ▫ There is an FD X → Y if, for all pairs of tuples (t 1, t 2) in T, if t 1. X = t 2. X then t 1. Y = t 2. Y �For all pairs of tuples, if the X values agree, then the Y values must also agree �Must hold for all possible legal instances of the relation schema �Must be identified based on semantics of the real-world relation being modelled (represent real-world relationships) • An FD implies redundancy ▫ If we know the value of X, we automatically know the value of Y ▫ Storing both X and Y is redundant

7 Functional Dependencies rating → salary → rating → salary No FD name rating salary Bob 8 100, 000 Dave 8 100, 000 Dave 8 90, 000 Lisa 5 70, 000 Dave 5 90, 000 Bob 5 70, 000 Bob 6 70, 000 Bob 8 70, 000 Jimmy 8 100, 000 {dept, rating} → salary name dept rating salary rating → {salary, benefits} name rating salary benefits Bob HR 8 90, 000 Bob 8 100, 000 full Dave IT 8 100, 000 Dave 8 100, 000 full Lisa HR 5 60, 000 Lisa 5 70, 000 dental Bob IT 5 70, 000 Bob 5 70, 000 dental Jimmy IT 8 100, 000 Jimmy 8 100, 000 full

8 Functional Dependencies • A key constraint is a special case of FD ▫ Set of attributes X, any and all sets of attributes Y; X → Y holds: X is a key ▫ V X, V → Y holds: X is a superkey �Set of attributes that includes a key ▫ But an FD is not necessarily a key sin name rating salary 123456789 Bob 8 100, 000 234567890 Dave 8 100, 000 345678901 Lisa 5 70, 000 456789012 Bob 5 70, 000 567890123 Jimmy 8 100, 000 sin → salary (key constraint) {sin, name} → salary (superkey constraint) rating → salary (FD but not key constraint)

9 Functional Dependencies • Given some FDs, we can infer additional FDs ▫ X → Y, Y → Z, therefore X → Z �A rating value determines salary, and salary determines benefits, therefore rating determines benefits ▫ X → Y, therefore XZ → YZ ▫ X Y, therefore X → Y ▫ X → YZ, therefore X → Y and X → Z, and vice-versa • An FD f is implied by a set of FDs F if f holds on every and all instances where F holds • The set of all FDs implied by F is the closure set of F, noted F+

10 Closure Set Example • Example: ▫ Table is {rating R, salary S, benefits B} ▫ R → S, and S → B • Closure set ▫ Trivial FDs (from definition of table) �R → R, S → S, B → B, RS → RS, RS → R, RS → S, RB → RB, RB → R, RB → B, SB→ SB, SB → S, SB → B, RSB → RSB, RSB → R, RSB → S, RSB → B, RSB → RS, RSB → RB, RSB → SB ▫ Transitive FDs (from R → S and S → B) �R → B ▫ Reflective FDs (from R → S, S → B, and R → B) �RB → SB, SR → BR, RS → BS

11 Decomposition • We can detect redundancy by detecting FDs • We can eliminate redundancy by decomposition, replacing a large redundant schema by a set of smaller, non-redundant schemas ▫ A decomposition of a relation schema R consists of replacing R by two or more relations such that: �Each new relation schema contains a subset of the attributes of R �Every attribute of R appears as an attribute of at least one of the new relations �No attributes not found in R appear in the new relations • But decomposition cause problems ▫ May not be possible to recover the original tuples ▫ May not be possible to recover the original dependencies ▫ Some queries may become more expensive

12 Lossless-Join Decompositions • R is decomposed into R 1 and R 2 • If we can join R 1 and R 2 to recover R exactly, then it is a lossless-join decomposition ▫ No tuples in R disappear, no new tuples appear from join ▫

13 Lossless-Join Decompositions sin name rating salary sin name rating name salary 123456789 Bob 8 100, 000 123456789 Bob 8 Bob 100, 000 234567890 Dave 8 Dave 100, 000 345678901 Lisa 5 70, 000 345678901 Lisa 5 Lisa 70, 000 456789012 Bob 5 Bob 70, 000 567890123 Jimmy 8 100, 000 567890123 Jimmy 8 Jimmy 100, 000 Join on name = name sin name rating salary 123456789 Bob 8 100, 000 234567890 Dave 8 100, 000 345678901 Lisa 5 70, 000 456789012 Bob 5 70, 000 567890123 Jimmy 8 100, 000 123456789 Bob 8 70, 000 456789012 Bob 5 100, 000

14 Dependency-Preserving Decompositions • R is decomposed into R 1 and R 2 • If we can discover and enforce all the FDs of R using R 1 and R 2, then it is a dependencypreserving decomposition ▫ ▫ We can discover and enforce all the FDs on R using R 1 and R 2 ▫ We can enforce all FDs during insert/update using one of the new relations

15 Dependency-Preserving Decompositions dept rating salary HR 8 100, 000 PR 7 7 70, 000 PR 7 70, 000 IT 5 5 50, 000 IT 5 50, 000 FR 1: dept → rating → dept FR 2: rating → salary → rating F: dept → rating → salary → dept F+: dept → rating → salary → dept (FR 1 FR 2) +: dept → rating → salary → dept → salary rating → dept salary → rating

16 Dependency-Preserving Decompositions sin name rating salary sin name rating name salary 123456789 Bob 8 100, 000 123456789 Bob 8 Bob 100, 000 234567890 Dave 8 Dave 100, 000 345678901 Lisa 5 70, 000 345678901 Lisa 5 Lisa 70, 000 456789012 Bob 5 Bob 70, 000 567890123 Jimmy 8 100, 000 567890123 Jimmy 8 Jimmy 100, 000 rating → salary No Dependencies • Not dependency preserving • No way to link rating and salary No Dependencies

17 Efficient Decompositions • Decomposition breaks a relation into several relations • Queries might now be selecting attributes from multiple relations ▫ Requires join ▫ Required projection in original relation ▫ Increases computational cost of queries • Need to study workload of DB ▫ Do not split attributes often queried together ▫ Even if it adds redundancy

18 Efficient Decompositions sin name rating 123456789 Bob 8 sin name rating salary 234567890 Dave 8 123456789 Bob 8 100, 000 345678901 Lisa 5 234567890 Dave 8 100, 000 456789012 Bob 5 345678901 Lisa 5 70, 000 567890123 Jimmy 8 456789012 Bob 5 70, 000 rating salary 567890123 Jimmy 8 100, 000 5 70, 000 8 100, 000 • Lossless-join, dependency-preserving decomposition that eliminates redundancy • If workload includes a lot of {name, salary} queries • Decomposition makes system less efficient

19 Properties of Decomposition • A decomposition can be lossless-join without being dependency-preserving, and vice-versa rating → salary name dept rating salary name dept Bob HR 8 100, 000 Bob HR Dave IT 8 100, 000 Dave IT Lisa HR 5 70, 000 Lisa HR Bob IT 5 70, 000 Bob IT Jimmy HR 8 100, 000 Jimmy HR • Dependency-preserving, but not lossless join • Creates tuples (Bob, IT, 8, 100000) and (Bob, HR, 5, 70000)

20 Properties of Decomposition • A decomposition can be lossless-join without being dependency-preserving, and vice-versa {rating, dept} → salary {city, interests} → dept rating dept salary city interests dept city interests rating salary city 8 HR 80, 000 TO Admin HR TO Admin 5 50, 000 TO 5 HR 50, 000 TO Admin IT TB Tech 8 80, 000 TO 8 IT 90, 000 TB Tech 8 90, 000 TB • Lossless-join but not dependency preserving • Lost {rating, dept} → salary • Nothing stopping us from inserting (8, 90000, TO)

21 Normal Forms • We want to decompose relations into smaller non-redundant ones • But we saw that decompositions can lead to problems • How to avoid them? • Decompose towards normal forms ▫ If a relation is in a given normal form, it is known that certain kinds of problems are avoided/minimized ▫ This can be used to guide decomposition, and help decide whether decomposition is needed • This is called normalization

22 Normal Forms • Many possible normal forms ▫ ▫ ▫ First Normal Form Second Normal Form Third Normal Form Boyce-Codd Normal Form Fourth Normal Form Fifth Normal Form • Each is more restrictive on what FDs are allowed ▫ Eliminates more redundancy ▫ Creates simples DB schema ▫ May not always be possible

23 First Normal Form • Domain of values of attributes contains only atomic values ▫ Lists and sets of values not allowed ▫ Each attribute can only have a single value • This has been the case in our course so far

24 First Normal Form sin name dept 123456789 Bob HR 234567890 Dave IT HR 345678901 Lisa IT 456789012 Bob Admin HR 567890123 Jimmy IT • Employee table violates 1 NF • Dept allows list of values for employees working in multiple departments

25 First Normal Form sin name dept • Employee table is 1 NF with primary key sin • Works. In table is 1 NF with primary key (sin, dept) • Join needed to recover tuples 123456789 Bob HR sin name sin dept 234567890 Dave IT 123456789 Bob 123456789 HR 234567890 Dave 234567890 IT 345678901 Lisa 234567890 HR 456789012 Bob Admin 456789012 Bob 345678901 IT 456789012 Bob HR 567890123 Jimmy 456789012 Admin 567890123 Jimmy IT 456789012 HR 567890123 IT • Employee table is 1 NF • Loses primary key

26 Second Normal Form • No partial dependencies ▫ Attributes that are dependent on part of a key ▫ X → Z where pair (X, Y) is a key name dept city Bob HR TO Lisa HR TO Bob Admin TO Dave IT TB Jimmy IT TB • • Table is 1 NF Key is pair (name, dept) Partial dependency: dept → city Violates 2 NF Notice update anomaly danger: updating Bob’s location to TB would cause queries “where is HR dept” to give conflicting results!

27 Second Normal Form • Solve by decomposing to isolate partial dependency ▫ X → Z becomes its own table with X as key ▫ Z removed from first table, X remains name dept city Bob HR HR TO Lisa HR Admin TO Bob Admin IT Dave IT Jimmy IT TB Notice update anomaly is impossible: we can only update dept cities, not employees

28 Third Normal Form (3 NF) • A relation schema R is in 3 NF if, for all FDs X→A ▫ A X (it’s a trivial FD) ▫ X is a superkey ▫ A is part of a key of R �Recall: a key is a minimal set of attributes; A is not part of a superkey �Finding all keys of R is a problem: need to study relation to discover all candidate keys

29 Boyce-Codd Normal Form (BCNF) • A relation schema R is in BCNF if, for all FDs X→A ▫ A X (it’s a trivial FD) ▫ X is a superkey • More restrictive than 3 NF • No redundancy in R can be predicted using FDs alone ▫ For two tuples with the same X, either A is part of X and therefore already known ▫ Or X is a key, therefore there cannot be two tuples with the same X

30 3 NF and BCNF • Two keys: sin name dept city salary 123456789 Bob HR TO 50, 000 234567890 Dave HR TO 60, 000 345678901 Lisa HR NY 50, 000 456789012 Bob IT TB 60, 000 567890123 Jimmy IT TB 70, 000 ▫ sin (primary key) ▫ (name, dept) • (city, salary) → city ▫ X → A where A X ▫ Trivial FD • (sin, city) → salary ▫ X → A where X is a superkey ▫ Key (sin) functionally defines everything • city → dept ▫ X → A where A is part of a key ▫ OK for 3 NF but not BCNF

31 Normalization into BCNF • Given a relation schema R with FD set F, which is not BCNF ▫ BCNF violated by FD X → A ▫ Therefore, A X and X not a superkey • Decompose into R 1 = R – A, & R 2 = XA ▫ R 1 does not have A: no violating FD ▫ R 2 has X as key: FD not violating BCNF and dependencypreserving • If either R 1 or R 2 is not BCNF, repeat dept → city ID dept city 1 HR TO 1 HR HR TO 2 IT TB 2 IT IT TB 3 IT

32 Normalization into BCNF • May not be dependency-preserving {city, interests} → dept {rating, dept} → salary {city, interests} → dept rating dept salary city interests 8 HR 80, 000 TO Admin 5 HR 50, 000 TO Admin 8 IT 90, 000 TB Tech dept city interests HR TO Admin IT TB Tech No Dependencies rating salary city interests 8 80, 000 TO Admin 5 50, 000 TO Admin 8 90, 000 TB Tech • Problem won’t occur if we start with {rating, dept} → salary instead • The order in which we pick the dependencies and decompositions matters!

33 Normalization into BCNF • May not be possible {rating, dept} → city → dept rating dept city 5 HR TO 5 IT TB 8 HR NY 8 IT LA Option 1: {rating, dept} → city R 1: {rating, dept} , R 2: {rating, dept, city} R 2 = R Option 2: city → dept R 1: {rating, city} , R 2: {dept, city} Lost a dependency

34 Normalization into BCNF • Requires backtracking tree-searching algorithm ▫ Root: R with list of all BCNF-violating dependencies ▫ Pick a BCNF-violating dependency and decompose tables �If decomposition not dependency-preserving or leading to BCNF tables, backtrack and try different dependency ▫ End when �All tables are BCNF (success) �Tree has been completely searched (impossible)

35 Normalization into 3 NF • Notice: BCNF algo is the same as 2 NF • Works for 3 NF as well ▫ With the same flaw: decomposition does not guaranteed dependency preservation • Simply flag 2 NF and 3 NF decompositions, return if search fails to find BCNF

36 Practice Exercise: 19. 7

37 Practice Exercise: 19. 7

38 Practice Exercise: 19. 7

39 Other Dependencies • FDs are the most common type of dependency ▫ Easiest and most useful to guide schema refinement • Others include ▫ Multivalued Dependencies ▫ Join Dependencies

40 Multivalued Dependencies • This schema is BCNF • There is redundancy ▫ Each physics textbook is stored twice ▫ Each physics prof is stored twice ▫ Each math prof is stored thrice • Problem is that prof & textbook are independent ▫ And that’s not an FD ▫ Multivalued dependency ▫ Course → → Prof • Since prof and book are independent, they should not be in a single ternary relationship Course Prof Textbook Physics Green Mechanics Physics Green Optics Physics Brown Mechanics Physics Brown Optics Math Green Mechanics Math Green Vectors Math Green Geometry

41 Multivalued Dependencies • X→→Y ▫ Each value in X is associated to a set of values in Y, independently of other attributes • The MVD X → → Y over relation R holds if, for all pairs of tuples (t 1, t 2) and given Z = R – XY: ▫ if t 1. X = t 2. X, then there exits a t 3 such as t 1. XY = t 3. XY and t 2. Z = t 3. Z “if there is a tuple t 1 showing a course X being taught by prof Y, and a tuple t 2 showing a course X using textbook Z, then there is a tuple t 3 showing course X being taught by prof Y using textbook Z” Course Prof Textbook t 1 Physics Green Mechanics t 3 Physics Green Optics Physics Brown Mechanics Physics Brown Optics Math Green Mechanics Math Green Vectors Math Green Geometry t 2

42 Fourth Normal Form • More strict version of BCNF • Recall, in BCNF, for all FDs X → A in relation R ▫ A X (it’s a trivial FD) ▫ X is a superkey • We add: for all MVDs X → → Y ▫ Y X or XY = R (it’s a trivial MVD) ▫ X is a superkey

43 Fourth Normal Form • Not in 4 NF ▫ Course → → Prof non-trivial and Course not a key • Decomposing in Course. Prof and Course. Textbook ▫ Both are 4 NF Course Prof Textbook Physics Green Mechanics Physics Green Optics Course Prof Course Textbook Physics Brown Mechanics Physics Green Physics Mechanics Physics Brown Optics Physics Brown Physics Optics Math Green Mechanics Math Green Vectors Math Green Geometry Math Geometry Green

44 Join Dependencies • If a decomposition of R into R 1, …, Rn is a lossless -join, then join dependency holds • Generalization of MVDs ▫ X → → Y in R can be expressed as • Example: ▫ CP & CT are a lossless-join decomposition of CPT ▫ C→→P Course ▫ Physics Course Textbook Physics Mechanics Prof Physics Optics Green Math Mechanics Physics Brown Math Vectors Math Geometry Green

45 Fifth Normal Form • More strict version of 4 NF • Add that for all JD ▫ Ri = R for some i �The entire relation is one of the decompositions �It’s a trivial JD ▫ JD is obtained from a decomposition using FDs where the left side is a key of R �Join is done on key attribute

46 Schema Refinement • Important step in a DB design is building an ER model ▫ Complex & subjective process: different ER models are possible ▫ Constraints and dependencies are not clearly expressed in ER models • Normalisation helps improve the ER model ▫ Higher NF usually leads to better DB designs

47 Constraints on Entity Set ssn name lot rating salary Employees • Two FDs ▫ {ssn} → {ssn, name, lot, rating, salary} ▫ {rating} → {salary} • But the second one doesn’t appear in the ER model!

48 Constraints on Relation Set sid Suppliers pid cid Contract Parts did Departments quantity • Contract C says that department D buys a quantity Q of part P from supplier S ▫ Company policy: a department can only get one part from any given supplier • Dependency DS → P ▫ Split in DSP & CQSD ▫ Not an intuitive relation in the ER model

49 Schema Refinement since name ssn lot Employees dname did Works_In budget Departments • Each employee is assigned to a parking lot • Suppose parking lots are assigned by departments ▫ did → lot • Redundancy problems! ▫ ▫ Redundant storage Update anomalies Insertion anomalies Deletion anomalies

50 Schema Refinement • Solution: decompose {did, lot} into separate relation • Notice: this relation has the same key as Departments • We can further refine by putting lot as attribute of Departments

51 Schema Refinement name since ssn dname did Employees Works_In budget lot Departments • Solves redundancy issues ▫ No redundant storage, lot stored once per did ▫ Can update a did’s lot by updating a single value ▫ Can insert new employees without associating them to a lot ▫ A department with no employees still has its associated lot

52 Summary • Redundancy is evil ▫ Redundant storage, update anomalies, insertion anomalies, deletion anomalies • We can decompose a large redundant schema into a set of smaller, non-redundant schemas • Functional dependencies can guide this decomposition • The closure set F+ of a set of FDs F • A good decomposition has some properties ▫ Lossless-join ▫ Dependency-preserving ▫ Does not make system workload inefficient

53 Summary • We can reduce problems and improve ER design using normal forms ▫ ▫ ▫ First Normal Form (1 NF) Second Normal Form (2 NF) Third Normal Form (3 NF) Boyce-Codd Normal Form (BCNF) Fourth Normal Form (4 NF) Fifth Normal Form (5 NF)

54 Summary • We then learned how to apply these notions to refine ER diagrams and relation schemas since name ssn lot Employees dname did Works_In budget Departments

55 Exercises • • • 19. 2 19. 3 19. 5 19. 6 19. 7 19. 8 19. 10 19. 13 a 19. 18 1 & 4