Module 5 Overview of Database Design Normalization Database

Module 5: Overview of Database Design -- Normalization Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Outline Decomposition Functional Dependency Decomposition Using Functional Dependency Database System Concepts 5. 2 ©Silberschatz, Korth and Sudarshan

Bad Schema Consider a relation inst_dept, which represents the result of a natural join on the relations corresponding to instructor and department This relation is in “bad form” since there is a possibility of repetition of information and we may need to use null values to represent information (department with no instructors). Database System Concepts 5. 3 ©Silberschatz, Korth and Sudarshan

How to Deal with a Bad Schema? The only way to deal with a bad schema is to split the scheme (decompose) into a number of smaller schemas. Hopefully, each of the smaller schemes is in a “good form”; that is, there is no repetition of information and there is no need to use null values. In the case of the inst_dept schema , we would decompose it into two relation schemes – instructor and department. We know that both instructor and department are in good form (how do we know this)? Database System Concepts 5. 4 ©Silberschatz, Korth and Sudarshan

Lossless-Join Decomposition When we decompose a relation r into two relations r 1 and r 2 we must make sure that we can reconstruct the information that r originally contained. Reconstruction is achieved by using the natural-join operation Decomposition of r = (A, B, C) into: r 1 = (A, B) and r 2 = (B, C) r 1 (r) Database System Concepts A B C A B B C 1 A 2 B 1 2 1 2 A B r A, B(r) r 2 (r) A B C 1 A 2 B 5. 5 B, C(r) ©Silberschatz, Korth and Sudarshan

Decomposition does not always work Not all compositions are lossless-join decomposition Suppose we decompose employee(ID, name, street, city, salary) into: employee 1 (ID, name) employee 2 (name, street, city, salary) The next slide shows how we lose information -- we cannot reconstruct the original employee relation using employee 1 and employee 2 -- and so, this is a lossy decomposition. Database System Concepts 5. 6 ©Silberschatz, Korth and Sudarshan

A Lossy Decomposition Database System Concepts 5. 7 ©Silberschatz, Korth and Sudarshan

Lossless-Join Decomposition Definition Let R be a relation schema, and let R 1 and R 2 form a decomposition of R. We say that the decomposition is a lossless decomposition if there is no loss of information by replacing R with two relation schemas R 1 and R 2. More precisely, we say the decomposition is lossless if, for all legal database instances (this is, all possible relations r on schema R) r = R 1 (r ) R 2 (r ) We will define “legal database instances” later on. We will show shortly how one can test to see if a decomposition is lossless. Database System Concepts 5. 8 ©Silberschatz, Korth and Sudarshan

Decomposition Theory Goal — Devise a Theory for the Following: Decide whether a particular relation R is in “good” form. In the case that a relation R is not in “good” form, decompose it into a set of relations {R 1, R 2, . . . , Rn} such that each relation is in good form the decomposition is a lossless-join decomposition Our theory is based on: functional dependencies multivalued dependencies Database System Concepts 5. 9 ©Silberschatz, Korth and Sudarshan

Functional Dependencies Database System Concepts 5. 10 ©Silberschatz, Korth and Sudarshan

Functional Dependencies Constraints on the set of legal relations. Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. A functional dependency is a generalization of the notion of a key. Database System Concepts 5. 11 ©Silberschatz, Korth and Sudarshan

Functional Dependencies (Cont. ) Let R be a relation schema, and let: R and R The functional dependency holds on R if and only if for any legal relations r(R), whenever any two tuples t 1 and t 2 of r agree on the attributes , they also agree on the attributes . That is, t 1[ ] = t 2 [ ] Example: Consider r(A, B ) with the following instance of r. A B 1 4 1 5 3 7 On this instance, A B does NOT hold, but B A does hold. Database System Concepts 5. 12 ©Silberschatz, Korth and Sudarshan

Functional Dependencies (Cont. ) K is a superkey for relation schema R if and only if K R K is a candidate key for R if and only if K R, and for no K, R Functional dependencies allow us to express constraints that cannot be expressed using superkeys. Consider the schema: inst_dept (ID, name, salary, dept_name, building, budget ). We expect these two functional dependencies to hold: dept_name building ID building but would not expect the following to hold: dept_name salary Database System Concepts 5. 13 ©Silberschatz, Korth and Sudarshan

Use of Functional Dependencies We use functional dependencies to: Test relations to see if they are legal under a given set of functional dependencies. 4 If a relation r is legal under a set F of functional dependencies, we say that r satisfies F. specify constraints on the set of legal relations 4 We say that F holds on R if all legal relations on R satisfy the set of functional dependencies F. Note: A specific instance of a relation schema may satisfy a functional dependency even if the functional dependency does not hold on all legal instances. For example, a specific instance of instructor may, by chance, satisfy name ID. Database System Concepts 5. 14 ©Silberschatz, Korth and Sudarshan

Trivial Functional Dependencies A functional dependency is trivial if it is satisfied by all instances of a relation Example: 4 ID, name ID 4 name In general, is trivial if Database System Concepts 5. 15 ©Silberschatz, Korth and Sudarshan

Closure of a Set of Functional Dependencies Given a set F of functional dependencies, there are certain other functional dependencies that are logically implied by F. For example: If A B and B C, then we can infer that A C The set of all functional dependencies logically implied by F is the closure of F. We denote the closure of F by F+. F+ is a superset of F. Database System Concepts 5. 16 ©Silberschatz, Korth and Sudarshan

Computing F+ We can compute F+, the closure of F, by repeatedly applying Armstrong’s Axioms: if , then (reflexivity) if , then (augmentation) if , and , then (transitivity) These rules are sound (generate only functional dependencies that actually hold), and complete (generate all functional dependencies that hold). Database System Concepts 5. 17 ©Silberschatz, Korth and Sudarshan

Example of Armstrong’s Axioms R = (A, B, C, G, H, I) F={ A B A C CG H CG I B H} some members of F+ A H 4 AG I 4 by transitivity from A B and B H by augmenting A C with G, to get AG CG and then transitivity with CG I CG HI 4 by augmenting CG I with CG, to infer CG CGI, and augmenting of CG H with I, to infer CGI HI, and then transitivity Database System Concepts 5. 18 ©Silberschatz, Korth and Sudarshan

Procedure for Computing F+ F+=F repeat for each functional dependency f in F+ apply reflexivity and augmentation rules on f add the resulting functional dependencies to F + for each pair of functional dependencies f 1 and f 2 in F + if f 1 and f 2 can be combined using transitivity then add the resulting functional dependency to F + until F + does not change any further NOTE: We shall see an alternative procedure for this task later Database System Concepts 5. 19 ©Silberschatz, Korth and Sudarshan

Canonical Cover Sets of functional dependencies may have redundant dependencies that can be inferred from the others For example: A C is redundant in: {A B, B C, A C} Parts of a functional dependency may be redundant 4 E. g. : to on RHS: {A B, B C, A CD} can be simplified {A B, B C, A D} – A C, A D 4 E. g. : to on LHS: {A B, B C, AC D} can be simplified {A B, B C, A D} – AC CD, CD D Intuitively, a canonical cover of F, denoted by Fc is a “minimal” set of functional dependencies equivalent to F, having no redundant dependencies or redundant parts of dependencies. We show to compute Fc later on in Module 8. Database System Concepts 5. 20 ©Silberschatz, Korth and Sudarshan

Decomposition Using Functional Dependency Database System Concepts 5. 21 ©Silberschatz, Korth and Sudarshan

Lossless-join Decomposition We can now formally define lossless-join decomposition. Let R be a relation schema, and let R 1 and R 2 form a decomposition of R. For the case of R = (R 1, R 2), we require that for all possible relations r on schema R r = R 1 (r ) R 2 (r ) A decomposition of R into R 1 and R 2 is lossless join if at least one of the following dependencies is in F+: R 1 R 2 Database System Concepts 5. 22 ©Silberschatz, Korth and Sudarshan

Example R = (A, B, C) F = {A B, B C) R 1 = (A, B), R 2 = (B, C) Lossless-join decomposition: R 1 R 2 = {B} and B BC R 1 = (A, B), R 2 = (A, C) Lossless-join decomposition: R 1 R 2 = {A} and A AB Database System Concepts 5. 23 ©Silberschatz, Korth and Sudarshan

Dependency Persevering Decomposition Constraints, including functional dependencies, are costly to check (when one updates or inserts new tuples) unless they pertain to only one relation It is desirable to be able to test only those dependencies on each individual relation of a decomposition in order to ensure that all functional dependencies hold. A decomposition is dependency preserving If it is sufficient to test only those dependencies on each individual relation of a decomposition in order to ensure that all functional dependencies hold. Database System Concepts 5. 24 ©Silberschatz, Korth and Sudarshan

Example R = (A, B, C) F = {A B, B C) R 1 = (A, B), R 2 = (B, C) Lossless-join decomposition: R 1 R 2 = {B} and B BC Dependency preserving R 1 = (A, B), R 2 = (A, C) Lossless-join decomposition: R 1 R 2 = {A} and A AB Not dependency preserving (cannot check B C without computing R 1 Database System Concepts 5. 25 R 2 ) ©Silberschatz, Korth and Sudarshan

What do Know Thus Far? We know that any acceptable decomposition must be a lossless-join decomposition We know how to test to see if the decompose of a relation into a set of relations a lossless-join decomposition. We know that is desirable to have a lossless-join decomposition that is dependency preserving. We do NOT know (yet) is -- how to decide if a given relation is in good form. Database System Concepts 5. 26 ©Silberschatz, Korth and Sudarshan

Boyce-Codd Normal Form A relation schema R is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F+ of the form where R and R, at least one of the following holds: is trivial (i. e. , ) is a superkey for R Example schema not in BCNF: instr_dept (ID, name, salary, dept_name, building, budget ) dept_name building, budget holds on instr_dept, but dept_name is not a superkey Database System Concepts 5. 27 ©Silberschatz, Korth and Sudarshan

Decomposing a Schema into BCNF Suppose we have a schema R and a non-trivial dependency in F+ that causes a violation of BCNF. We decompose R into: ( U ) • • (R-( - )) In our example, R = dept_name, building, budget, ID, name, salary = dept_name = building, budget and inst_dept is replaced by R 1 = ( U ) = ( dept_name, building, budget ) R 2 = ( R - ( - ) ) = ( ID, name, salary, dept_name ) Are R 1 and R 2 in BCNF? Database System Concepts 5. 28 ©Silberschatz, Korth and Sudarshan

BCNF Decomposition Algorithm result : = {R }; done : = false; compute F +; while (not done) do if (there is a schema Ri in result that is not in BCNF) then begin let be a nontrivial functional dependency that holds on Ri such that Ri is not in F + and = ; result : = (result – Ri ) (Ri – ) ( , ); end else done : = true; Note: each Ri is in BCNF, and decomposition is lossless-join. Database System Concepts 5. 29 ©Silberschatz, Korth and Sudarshan

BCNF Decomposition Algorithm Example R = (A, B, C ) Functional dependencies {A B, B C} BCNF Decomposition: B C 4 but B is not a superkey. We replace R by: Database System Concepts 4 R 1 = (B, C) 4 R 2 = (A, B) R 1 and R 2 in BCNF 5. 30 ©Silberschatz, Korth and Sudarshan

BCNF Decomposition Algorithm Example class (course_id, title, dept_name, credits, sec_id, semester, year, building, room_number, capacity, time_slot_id) Functional dependencies: course_id→ title, dept_name, credits building, room_number→capacity course_id, sec_id, semester, year→building, room_number, time_slot_id A candidate key {course_id, sec_id, semester, year}. BCNF Decomposition: course_id→ title, dept_name, credits holds 4 but course_id is not a superkey. We replace class by: 4 course(course_id, title, dept_name, credits) 4 class-1 (course_id, sec_id, semester, year, building, room_number, capacity, time_slot_id) Database System Concepts 5. 31 ©Silberschatz, Korth and Sudarshan

BCNF Decomposition Algorithm Example (Cont. ) course is in BCNF How do we know this? building, room_number→capacity holds on class-1 But {building, room_number} is not a superkey for class-1. We replace class-1 by: 4 classroom (building, room_number, capacity) 4 section (course_id, sec_id, semester, year, building, room_number, time_slot_id) classroom and section are in BCNF. Database System Concepts 5. 32 ©Silberschatz, Korth and Sudarshan

BCNF and Dependency Preservation It is not always possible to get a BCNF decomposition that is dependency preserving R = (J, K, L ) F = {JK L L K} Two candidate keys = JK and JL R is not in BCNF Any decomposition of R will fail to preserve JK L This implies that testing for JK L requires a join Because it is not always possible to achieve both BCNF and dependency preservation, we consider a weaker normal form, known as third normal form. Database System Concepts 5. 33 ©Silberschatz, Korth and Sudarshan

Third Normal Form A relation schema R is in third normal form (3 NF) if for all: in F+ at least one of the following holds: is trivial (i. e. , ) is a superkey for R Each attribute A in { – } is contained in a candidate key for R. (NOTE: each attribute may be in a different candidate key) If a relation is in BCNF it is in 3 NF (since in BCNF one of the first two conditions above must hold). Third condition is a minimal relaxation of BCNF to ensure dependency preservation (we will see why later). Database System Concepts 5. 34 ©Silberschatz, Korth and Sudarshan

3 NF Example R = (J, K, L ) F = {JK L L K} Two candidate keys = JK and JL We have seen before that R is not in BCNF R, however, is in 3 NF JK is a superkey L K and L is NOT a superkey, but: 4{ K } – { L } = { K } and 4 K Database System Concepts is contained in the candidate key JK 5. 35 ©Silberschatz, Korth and Sudarshan

3 NF Example Using University schema Relation dept_advisor: dept_advisor (s_ID, i_ID, dept_name) F = {s_ID, dept_name i_ID, i_ID dept_name} Two candidate keys: {s_ID, dept_name} and {i_ID, s_ID} R is in 3 NF 4 s_ID, dept_name i_ID – {s_ID, dept_name} is a superkey 4 i_ID dept_name – dept_name is contained in a candidate key Database System Concepts 5. 36 ©Silberschatz, Korth and Sudarshan

3 NF Decomposition Algorithm Let Fc be a canonical cover for F; i : = 0; for each functional dependency in Fc do if none of the schemas Rj, 1 j i contains then begin i : = i + 1; Ri : = end if none of the schemas Rj, 1 j i contains a candidate key for R then begin i : = i + 1; Ri : = any candidate key for R; end /* Optionally, remove redundant relations */ repeat if any schema Rj is contained in another schema Rk then /* delete Rj */ Rj = R; ; i=i-1; return (R 1, R 2, . . . , Ri) Database System Concepts 5. 37 ©Silberschatz, Korth and Sudarshan

3 NF Decomposition Algorithm (Cont. ) Above algorithm ensures: each relation schema Ri is in 3 NF decomposition is dependency preserving and lossless-join Proof of correctness is at end of this presentation Database System Concepts 5. 38 ©Silberschatz, Korth and Sudarshan

Redundancy in 3 NF Consider the relation R R = (J, K, L) F = {JK L, L K } And an instance table: J j 1 j 2 L I 1 K k 1 j 3 I 1 k 1 null I 2 k 2 What is wrong with the table? Repetition of information Need to use null values (e. g. , to represent the relationship l 2, k 2 where there is no corresponding value for J) Database System Concepts 5. 39 ©Silberschatz, Korth and Sudarshan

Design Goals Goal for a relational database design is: BCNF. Lossless join. Dependency preservation. If we cannot achieve this and dependency preservation is important, we use 3 NF. Interestingly, SQL does not provide a direct way of specifying functional dependencies other than superkeys. Can specify FDs using assertions, but they are expensive to test, (and currently not supported by any of the widely used databases!) Even if we had a dependency preserving decomposition, using SQL we would not be able to efficiently test a functional dependency whose left hand side is not a key. Database System Concepts 5. 40 ©Silberschatz, Korth and Sudarshan

How Good is BCNF? There are database schemas in BCNF that do not seem to be sufficiently normalized Consider a relation inst_info (ID, child_name, phone) where an instructor may have more than one phone and can have multiple children ID child_name phone 99999 David William 512 -555 -1234 512 -555 -4321 inst_info Database System Concepts 5. 41 ©Silberschatz, Korth and Sudarshan

How good is BCNF? (Cont. ) There are no non-trivial functional dependencies and therefore the relation is in BCNF Insertion anomalies – i. e. , if we add a phone 981 -992 -3443 to 99999, we need to add two tuples (99999, David, 981 -992 -3443) (99999, William, 981 -992 -3443) Database System Concepts 5. 42 ©Silberschatz, Korth and Sudarshan

Fourth Normal Form (4 NF) It is better to decompose inst_info into: inst_child inst_phone ID child_name 99999 David William ID phone 99999 512 -555 -1234 512 -555 -4321 This suggests the need for higher normal forms, such as Fourth Normal Form (4 NF), which we shall see later. Database System Concepts 5. 43 ©Silberschatz, Korth and Sudarshan

End of Module 5 Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Lossless-Join Decomposition A decomposition that does not result in loss of information is termed a Lossless join decomposition. Whenever we decompose a relation into two relations, we will insist that the decomposition will be a lossless-join. Decomposition of R = (A, B, C) R 1 = (A, B) A B C A B B C 1 2 1 2 A B A, B(r) r A (r) Database System Concepts R 2 = (B, C) B (r) A B C 1 2 B, C(r) A B 5. 45 ©Silberschatz, Korth and Sudarshan

Lossless-Join Decomposition When we decompose a relation scheme R into two relation schemes R 1 and R 2 we must make sure that we can always reconstruct the information that any database instance of R contained. Reconstruction is achieved by using the natural-join operation Decomposition of R = (A, B, C) R 1 = (A, B) A B C A B B C 1 2 1 2 A B A, B(r) r A (r) Database System Concepts R 2 = (B, C B (r) A B C 1 2 B, C(r) A B 5. 46 ©Silberschatz, Korth and Sudarshan

Chapter E: Overview of Database Design Features of Good Relational Design Normalization Database System Concepts 5. 47 ©Silberschatz, Korth and Sudarshan

3 NF Decomposition Algorithm Example Relation schema: cust_banker_branch = (customer_id, employee_id, branch_name, type ) The functional dependencies for this relation schema are: 1. customer_id, employee_id branch_name, type 2. employee_id branch_name 3. customer_id, branch_name employee_id We first compute a canonical cover branch_name is extraneous in the r. h. s. of the 1 st dependency No other attribute is extraneous, so we get FC = customer_id, employee_id type employee_id branch_name customer_id, branch_name employee_id Database System Concepts 5. 48 ©Silberschatz, Korth and Sudarshan

3 NF Decomposition Algorithm Example (Cont. ) The for loop generates following 3 NF schema: (customer_id, employee_id, type ) (employee_id, branch_name) (customer_id, branch_name, employee_id) Observe that (customer_id, employee_id, type ) contains a candidate key of the original schema, so no further relation schema needs be added At end of for loop, detect and delete schemas, such as (employee_id, branch_name), which are subsets of other schemas result will not depend on the order in which FDs are considered The resultant simplified 3 NF schema is: (customer_id, employee_id, type) (customer_id, branch_name, employee_id) Database System Concepts 5. 49 ©Silberschatz, Korth and Sudarshan

Redundancy in 3 NF (Cont. ) The above example corresponds to: dept_advisor (s_ID, i_ID, dept_name) F = {s_ID, dept_name i_ID, i_ID dept_name} Repetition of information (e. g. , the relationship l 1, k 1) (i_ID, dept_name) Need to use null values (e. g. , to represent the relationship i_ID, dept_name where there is no corresponding value for s_ID). (i_ID, dept_name) if there is no separate relation mapping instructors to departments Database System Concepts 5. 50 ©Silberschatz, Korth and Sudarshan