International Computer Institute Izmir Turkey Database Design and

International Computer Institute, Izmir, Turkey Database Design and Normal Forms Asst. Prof. Dr. İlker Kocabaş UTI 510 at http: //ube. ege. edu. tr/~ikocabas

Database Design and Normal Forms n First Normal Form n Functional Dependencies n Decomposition n Boyce-Codd Normal Form n Database Design Process UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 2 of 56 Modifications & additions by Cengiz Güngör

First Normal Form n A domain is atomic if its elements are considered to be indivisible units § Examples of non-atomic domains: • Set-valued attributes, composite attributes • Identifiers like UBİ 502 that can be broken up into parts n A relational schema R is in first normal form if the domains of all attributes of R are atomic n Non-atomic values § complicate storage § encourage redundancy § interpretation of non-atomic values built into application programs • $cid = substring( $result [ “course-id” ], 1, 3 ); UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 3 of 56 Modifications & additions by Cengiz Güngör

First Normal Form (cont) n Atomicity: not an intrinsic property of the elements of the domain n Atomicity is a property of how the elements of the domain are used § E. g. strings containing a possible delimiter (here: space) • cities = “Melbourne Sydney” (non-atomic: space separated list) • surname = “Fortescue Smythe” (atomic: compound surname) § E. g. strings encoding two separate fields • student_id = CS 1234 • If the first two characters are extracted to find the department, the domain of student identifiers is not atomic • leads to encoding of information in application program rather than in the database UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 4 of 56 Modifications & additions by Cengiz Güngör

Pitfalls (Traps) in Relational Database Design n Relational database design requires that we find a “good” collection of relation schemas n A bad design may lead to § redundant information § difficulty in representing certain information § difficulty in checking integrity constraints n Design Goals: § Avoid redundant data § Ensure that relationships among attributes are represented § Facilitate the checking of updates for violation of integrity constraints UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 5 of 56 Modifications & additions by Cengiz Güngör

Example of Bad Design n Consider the relation schema: Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount) n Redundant Information: § Data for branch-name, branch-city, assets are repeated for each loan that a branch makes § Wastes space and complicates updates, introducing possibility of inconsistency of assets value n Difficulty representing certain information: § Cannot store information about a branch if no loans exist § Can use null values, but they are difficult to handle UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 6 of 56 Modifications & additions by Cengiz Güngör

Solution: Decomposition n Break up such redundant tables into multiple tables § this operation is called decomposition n E. g. consider Lending-schema again: Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount) n now decompose as follows: Branch-schema = (branch-name, branch-city, assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount) n Want to ensure that the original data is recoverable 1. all attributes of the original schema (R) must appear in the decomposition (R 1, R 2), i. e. R = R 1 R 2 2. decomposition must be a lossless-join decomposition UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 7 of 56 Modifications & additions by Cengiz Güngör

Lossless-Join Decomposition: Definition n Let R, R 1, R 2 be schemas and where R = R 1 R 2 n R 1, R 2 is a lossless-join decomposition of R § if, for all possible relations r(R) § r = R 1 ( r ) ⋈ R 2 ( r ) n Here “possible” means “meaningful in the context of the particular database design” § we will formalize this notion using functional dependencies UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 8 of 56 Modifications & additions by Cengiz Güngör

Lossless-Join Decomposition: Example n Example of Non Lossless-Join Decomposition of R = (A, B) R 2 = (A) R 2 = (B) A B A B 1 2 1 1 2 A ( r ) B ( r ) 1 2 r A ( r ) ⋈ B ( r ) Thus, r is different to A (r) ⋈ B (r) and so A, B is not a lossless-join decomposition of R. UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 9 of 56 Modifications & additions by Cengiz Güngör

Goal — Formalize the notion of good design n Process: § 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 n Our theory is based on 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 § generalizes the notion of a key n Functional dependencies allow us to formalize good database design UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 10 of 56 Modifications & additions by Cengiz Güngör

Functional Dependencies: Definition n Let R be a relation schema R and R n The functional dependency (FD) holds on R iff § 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 § i. e. ( t 1 ) = ( t 2 ) n Example: Consider r(A, B) with the following instance of r: 1 1 3 4 5 7 n On this instance, A B does NOT hold, but B A does hold UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 11 of 56 Modifications & additions by Cengiz Güngör

Functional Dependency : Another Definition n A functional dependency occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s): Student. ID Student. Name Student. ID (Dorm. Name, Dorm. Room, Fee) n The attribute on the left side of the functional dependency is called the determinant. n Functional dependencies may be based on equations: Extended. Price = Quantity X Unit. Price (Quantity, Unit. Price) Extended. Price n Function dependencies are not equations! UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 12 of 56 Modifications & additions by Cengiz Güngör

Composite Determinants n Composite determinant = a determinant of a functional dependency that consists of more than one attribute (Student. Name, Class. Name) (Grade) Functional Dependency Rules n If A (B, C), then A B and A C. n If (A, B) C, then neither A nor B determines C by itself. UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 13 of 56 Modifications & additions by Cengiz Güngör

Functional Dependencies: Visualization General form of a FD: A 1. . . An B 1. . . Bm A 1. . . An B 1. . . Bm if t and u agree here then they must also agree here t u UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 14 of 56 Modifications & additions by Cengiz Güngör

Functional Dependencies vs Keys n FDs can express the same constraints we could express using keys: n Superkeys: § K is a superkey for relation schema R if and only if K R n Candidate keys: § K is a candidate key for R if and only if • K R, and • there is no K’ K such that K’ R n However, FDs are more general § i. e. we can express constraints that cannot be expressed using keys UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 15 of 56 Modifications & additions by Cengiz Güngör

Functional Dependencies vs Keys (cont) n Example of FDs that can’t be represented using keys: n Consider the following Loan-info-schema: Loan-info-schema = (customer-name, loan-number, branch-name, amount). We expect these FDs to hold: loan-number amount loan-number branch-name We could try to express this by making loan-number the key, however the following FD does not hold: loan-number customer-name UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 16 of 56 Modifications & additions by Cengiz Güngör

Functional Dependencies (cont) n Movies(title, year, length, studio. Name, star. Name) title year length studio. Name star. Name Star Wars 1977 124 Fox Carrie Fisher Star Wars 1977 124 Fox Harrison Ford Mighty Ducks 1991 104 Disney Emilio Estevez Wayne’s World 1992 95 Paramount Dana Carvey Wayne’s World 1992 95 Paramount Mike Meyers n FD: title, year length, studio. Name n not an FD: title, year star. Name n candidate key, a minimal K such that K R § propose: K = {title, year, star. Name} § check: does K functionally determine R? § to answer this question we’ll need to look at closures UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 17 of 56 Modifications & additions by Cengiz Güngör

Functional Dependencies (cont) n An FD is an assertion about a schema, not an instance n If we only consider an instance, we can’t tell if an FD holds § e. g. inspecting the movies relation, we might suggest that length title, since no two films in the table have the same length § However, we cannot assert this FD for the movies relation, since we know it is not true of the domain in general n Thus, identifying FDs is part of the data modelling process UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 18 of 56 Modifications & additions by Cengiz Güngör

Modification Anomalies n Deletion anomaly n Insertion anomaly n Update anomaly n Movies(title, year, length, studio. Name, star. Name) title year length studio. Name star. Name Star Wars 1977 124 Fox Carrie Fisher Star Wars 1977 124 Fox Harrison Ford Mighty Ducks 1991 104 Disney Emilio Estevez Wayne’s World 1992 95 Paramount Dana Carvey Wayne’s World 1992 95 Paramount Mike Meyers n Update lenght on Row-1 is an anomaly, two different lenghts are recorded. UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 19 of 56 Modifications & additions by Cengiz Güngör

Normal Forms n Relations are categorized as a normal form based on which modification anomalies or other problems they are subject to: UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 20 of 56 Modifications & additions by Cengiz Güngör

Normal Forms n 1 NF—a table that qualifies as a relation is in 1 NF. n 2 NF—a relation is in 2 NF if all of its non-key attributes are dependent on all of the primary keys. n 3 NF—a relation is in 3 NF if it is in 2 NF and has no determinants except the primary key. n Boyce-Codd Normal Form (BCNF)—a relation is in BCNF if every determinant is a candidate key. “I swear to construct my tables so that all non-key columns are dependent on the key, the whole key and nothing but the key, so help me Codd. ” UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 21 of 56 Modifications & additions by Cengiz Güngör

Eliminating Modification Anomalies from Functional Dependencies in Relations: Put All Relations into BCNF UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 22 of 56 Modifications & additions by Cengiz Güngör

Functional Dependencies: Uses n We use FDs to: § test relations to see if they are legal under a given set of FDs • If a relation r is legal under a set F of FDs, we say that r satisfies F § specify constraints on the set of legal relations • We say that F holds on R if all legal relations on R satisfy the set of FDs F n Note: A specific instance of a relation schema may satisfy an FD even if the FD does not hold on all legal instances. § For example, a specific instance of Loan-schema may, by chance, satisfy loan-number customer-name UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 23 of 56 Modifications & additions by Cengiz Güngör

Aside: Trivial Functional Dependencies n An FD is trivial if it is satisfied by all instances of a relation § E. g. • customer-name, loan-number customer-name • customer-name § In general, is trivial if n Permitting such FDs makes certain definitions and algorithms easier to state UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 24 of 56 Modifications & additions by Cengiz Güngör

FD Closure: Definition n Given a set F of fds, there are other FDs logically implied by F § E. g. If A B and B C, then we can infer that A C n The set of all FDs implied by F is the closure of F, written F+ n We can find all of F+ by applying Armstrong’s Axioms: § if , then (reflexivity) § if , then (augmentation) § if , and , then (transitivity) n Additional rules (derivable from Armstrong’s Axioms): § If holds and holds, then holds (union) § If holds, then holds and holds (decomposition) § If holds and holds, then holds (pseudotransitivity) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 25 of 56 Modifications & additions by Cengiz Güngör

FD Closure: Example n R = (A, B, C, G, H, I) F={ A B A C CG H CG I B H} n some members of F+ § A H • by transitivity from A B and B H § AG I • by augmenting A C with G, to get AG CG and then transitivity with CG I § CG HI • by union rule with CG H and CG I UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 26 of 56 Modifications & additions by Cengiz Güngör

Computing FD Closure n To compute the closure of a set of FDs F: F+ = F repeat for each FD f in F+ apply reflexivity and augmentation rules on f add the resulting FDs to F+ for each pair of FDs f 1 and f 2 in F+ if f 1 and f 2 can be combined using transitivity then add the resulting FD to F+ until F+ does not change any further (NOTE: More efficient algorithms exist) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 27 of 56 Modifications & additions by Cengiz Güngör

Minimal Cover of an FD Set n The opposite of closure: what is the “minimal” set of FDs equivalent to F, having no redundant FDs (or extraneous attributes) n Sets of FDs may have redundant FDs that can be inferred from the others § Eg: A C is redundant in: {A B, B C, A C} § Parts of an FD may be redundant • E. g. on RHS: {A B, B C, A CD} can be simplified to {A B, B C, A D} • E. g. on LHS: {A B, B C, AC D} can be simplified to {A B, B C, A D} § (We’ll cover these later under the heading of extraneous attributes) n (NB Textbook calls this “canonical” cover, though there is no guarantee of uniqueness. ) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 28 of 56 Modifications & additions by Cengiz Güngör

Closure of Attribute Sets n Given a set of attributes , define the closure of under F (denoted by +) as the set of attributes that are functionally determined by under F: is in F+ + n Algorithm to compute +, the closure of under F result : = ; while (changes to result) do for each in F do begin if result then result : = result end UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 29 of 56 Modifications & additions by Cengiz Güngör

Closure of Attribute Sets: Example n R = (A, B, C, G, H, I) n F = {A B A C CG H CG I B H} n (AG)+ 1. 2. 3. 4. result = AG result = ABCGHI (A C and A B) (CG H and CG AGBC) (CG I and CG AGBCH) n Is AG a candidate key? 1. Is AG a superkey? 1. Does AG R? == Is (AG)+ R 2. Is any subset of AG a superkey? 1. Does A R? == Is (A)+ R 2. Does G R? == Is (G)+ R UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 30 of 56 Modifications & additions by Cengiz Güngör

Closure of Attribute Sets: Uses n Testing for superkey: § To test if is a superkey, we compute +, and check if + contains all attributes of R n Testing FDs § To check if a FD holds (or, in other words, is in F+), just check if + § i. e. compute + by using attribute closure, and then check if it contains § Is a simple and cheap test, and very useful UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 31 of 56 Modifications & additions by Cengiz Güngör

Extraneous Attributes n Recall that we could have redundant FDs. Parts of FDs can also be redundant n Consider a set F of FDs and the FD in F. § Attribute A is extraneous in if A and F logically implies (F – { }) {( – A) }. § Attribute A is extraneous in if A and the set of functional dependencies (F – { }) { ( – A)} logically implies F. n Example: Given F = {A C, AB C } § B is extraneous in AB C because {A C, AB C} logically implies A C (I. e. the result of dropping B from AB C). n Example: Given F = {A C, AB CD} § C is extraneous in AB CD since AB C can be inferred even after deleting C UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 32 of 56 Modifications & additions by Cengiz Güngör

Decomposition n Decompose the relation schema Lending-schema into: Branch-schema = (branch-name, branch-city, assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount) n All attributes of an original schema (R) must appear in the decomposition (R 1, R 2): R = R 1 R 2 n Lossless-join decomposition. For all possible relations r on schema R r = R 1 (r) ⋈ R 2 (r) n A decomposition of R into R 1 and R 2 is lossless-join if and only if at least one of the following dependencies is in F+: § R 1 R 2 R 1 § R 1 R 2 UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 33 of 56 Modifications & additions by Cengiz Güngör

Decomposition Using Functional Dependencies When we decompose a relation schema R with a set of FDs F into R 1, R 2, . . , Rn we want 1. Lossless-join decomposition: Otherwise decomposition would result in information loss 2. No redundancy: The relations Ri should be in BCNF 3. Dependency preservation: Let Fi be the set of FDs F+ that include only attributes in Ri § Preferably the decomposition should be dependency preserving, that is, (F 1 F 2 … Fn)+ = F+ § Otherwise, checking updates for violation of FDs may require computing joins, which is expensive UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 34 of 56 Modifications & additions by Cengiz Güngör

Example n R = (A, B, C) F = {A B, B C) § Can be decomposed in two different ways n R 1 = (A, B), R 2 = (B, C) § Lossless-join decomposition: R 1 R 2 = {B} and B BC § Dependency preserving n 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 ⋈ R 2) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 35 of 56 Modifications & additions by Cengiz Güngör

Summary n First Normal Form n Functional Dependencies n Decomposition § to eliminate redundancy § lossless-join § dependency preserving n Next Up: n Boyce-Codd Normal Form n Database Design Process UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 36 of 56 Modifications & additions by Cengiz Güngör

Boyce-Codd Normal Form (BCNF) n. A relation schema R is in BCNF with respect to a set F of FDs if n for all FDs in F+ of the form n where R and R nat least one of the following holds: n is trivial (i. e. , ), or n is a superkey for R UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 37 of 56 Modifications & additions by Cengiz Güngör

Example n R = (A, B, C) F = {A B B C} Key = {A} n R is not in BCNF n Decomposition R 1 = (A, B), R 2 = (B, C) § R 1 and R 2 in BCNF § Lossless-join decomposition § Dependency preserving n Question: How do we decompose a schema to get BCNF schemas in the general case? UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 38 of 56 Modifications & additions by Cengiz Güngör

BCNF Decomposition n First, we need a method to check if a non-trivial dependency on R violates BCNF 1. compute + (the attribute closure of ), and 2. verify that it includes all attributes of R n ie. + is a superkey of R 3. if not, then violates BCNF UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 39 of 56 Modifications & additions by Cengiz Güngör

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 UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 40 of 56 Modifications & additions by Cengiz Güngör

Example of BCNF Decomposition n R = (branch-name, branch-city, assets, customer-name, loan-number, amount) F = {branch-name assets branch-city loan-number amount branch-name} Key = {loan-number, customer-name} n Is R in BCNF? § Are there non-trivial FDs in which the LHS is not a superkey? § FD: branch-name assets branch-city • Is branch-name a superkey? (no) § FD: loan-number amount branch-name • Is loan-number a superkey? (no) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 41 of 56 Modifications & additions by Cengiz Güngör

Example of BCNF Decomposition (cont) n R = (branch-name, branch-city, assets, customer-name, loan-number, amount) F = {branch-name assets branch-city loan-number amount branch-name} n BCNF Decomposition § consider FD branch-name assets branch-city • = branch-name, = assets branch-city • result : = (result – Ri ) (Ri – ) ( , ); § Replace R with and R- • R 1: = (branch-name, assets, branch-city) • R 2: R- = (branch-name, customer-name, loan-number, amount) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 42 of 56 Modifications & additions by Cengiz Güngör

Example of BCNF Decomposition (cont) n R 1 = (branch-name, assets, branch-city) R 2 = (branch-name, customer-name, loan-number, amount) F = {branch-name assets branch-city loan-number amount branch-name} n R 1 is in BCNF, R 2 is not in BCNF Decomposition § consider FD loan-number amount branch-name § = loan-number, = amount branch-name § Replace R 2 with and R 2 - • R 3: = (branch-name, loan-number, amount) • R 4: R- = (customer-name, loan-number) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 43 of 56 Modifications & additions by Cengiz Güngör

Example of BCNF Decomposition (cont) n R 1 = (branch-name, assets, branch-city) R 3 = (branch-name, loan-number, amount) R 4 = (customer-name, loan-number) F = {branch-name assets branch-city loan-number amount branch-name} n All relations are now BCNF! n Why does it work – i. e. why is this a lossless-join decomposition? UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 44 of 56 Modifications & additions by Cengiz Güngör

Why are BCNF Decompositions lossless-join? n A 1. . . An B 1. . . Bm n For every combination R B’s R 1 A’s others R 2 UBI 502 Database Management Systems of A’s with others, we repeat the B’s n So put the B’s in a separate table R 1, for which the A’s are keys, and put the remainder in R 2 ©Silberschatz, Korth and Sudarshan 8. 45 of 56 Modifications & additions by Cengiz Güngör

Why are BCNF Decompositions lossless-join? (cont) n r = R 1 (r) ⋈ R 2 (r) ? n Consider R = (A, B, C), FD B C not in BCNF decomposition gives us: R 1 = (B, C), R 2 = (A, B) n Do we lose any tuples in R 1 (r) ⋈ R 2 (r) ? § § Let t = (a, b, c) be a tuple in r t projects as (b, c) for R 1 and (a, b) for R 2 joining these tuples gives us t back again thus, we don’t lose any tuples, and so r is contained in R 1 (r) ⋈ R 2 (r) n Do we gain any tuples in R 1 (r) ⋈ R 2 (r) ? § § § Let t = (a, b, c) and u = (d, b, e) be tuples in r By projecting and joining them, can we create (a, b, e) or (d, b, c)? Since B C we know that c=e So we can’t create any tuple we didn’t already have Thus, the FD ensures r contains R 1 (r) ⋈ R 2 (r) n Therefore r = R 1 (r) ⋈ R 2 (r) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 46 of 56 Modifications & additions by Cengiz Güngör

BCNF and Dependency Preservation It is not always possible to get a BCNF decomposition that is dependency preserving n R = (J, K, L) F = {JK L L K} Two candidate keys = JK and JL n R is not in BCNF n Any decomposition of R will fail to preserve JK L n Two solutions: § test FDs across relations § use Third Normal Form UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 47 of 56 Modifications & additions by Cengiz Güngör

Testing for FDs Across Relations n Suppose that is a dependency not preserved in a decomposition n Create a new materialized view for § The materialized view is defined as a projection on of the join of the relations in the decomposition § Many database systems support materialized views § No extra coding effort for programmer n Declare as a candidate key on the materialized view n Checking for candidate key is cheaper than checking n The down-side: § Space overhead: for storing the materialized view § Time overhead: Need to keep materialized view up to date § Database system may not support key declarations on materialized views UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 48 of 56 Modifications & additions by Cengiz Güngör

Aside 1: Third Normal Form n There are some situations where § BCNF is not dependency preserving, and § efficient checking for FD violations is important n Solution: define a weaker normal form, called Third Normal Form. § Allows some redundancy § FDs can be checked on individual relations without computing any joins § There is always a lossless-join, dependency-preserving decomposition into 3 NF n Details are beyond the scope of this course UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 49 of 56 Modifications & additions by Cengiz Güngör

Aside 2: SQL Support for FDs n SQL does not provide a direct way of specifying functional dependencies other than superkeys n Can specify FDs using assertions § assertions must express the following type of constraint (t 1) = (t 2) § these are expensive to test (especially if LHS of FD not a key) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 50 of 56 Modifications & additions by Cengiz Güngör

Design Goals n Goal for a relational database design is: § eliminate redundancies by decomposing relations § must be able to recover original data using lossless joins n BCNF: § no redundancies § no guarantee of dependency preservation n (3 NF: dependency preservation, but redundancies) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 51 of 56 Modifications & additions by Cengiz Güngör

Overall Database Design Process n We have assumed schema R is given § R could have been generated when converting E-R diagram to a set of tables. § R could have been a single relation containing all attributes that are of interest (called universal relation). § Normalization breaks R into smaller relations. § R could have been the result of some ad hoc design of relations, which we then test/convert to normal form. UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 52 of 56 Modifications & additions by Cengiz Güngör

E-R Model and Normalization n When an E-R diagram is carefully designed, identifying all entities correctly, the tables generated from the E-R diagram should not need further normalization n However, in a real (imperfect) design there can be FDs from non-key attributes of an entity to other attributes of the entity n The keys identified in our E-R diagram might not be minimal (only FDs force us to identify minimal keys) n E. g. employee entity with attributes department-number and department-address, and an FD department-number departmentaddress § Good design would have made department an entity n FDs from non-key attributes of a relationship set are possible, but rare UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 53 of 56 Modifications & additions by Cengiz Güngör

Denormalization for Performance n May want to use non-normalized schema for performance n E. g. displaying customer-name along with account-number and balance requires join of account with depositor n Alternative 1: Use denormalized relation containing attributes of account as well as depositor with all above attributes § faster lookup § extra space and extra execution time for updates § extra coding work for programmer and possibility of error in extra code n Alternative 2: use a materialized view defined as account ⋈ depositor § benefits and drawbacks same as above, except no extra coding work for programmer and avoids possible errors UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 54 of 56 Modifications & additions by Cengiz Güngör

Other Design Issues n Some aspects of database design are not caught by normalization n Examples of bad database design, to be avoided: n E. g suppose that, instead of earnings(company-id, year, amount), we used: § earnings-2000, earnings-2001, earnings-2002, etc. , all on the schema (company-id, earnings) • all are BCNF, but make querying across years difficult • needs a new table each year § company-year(company-id, earnings-2000, earnings-2001, earnings-2002) • in BCNF, but makes querying across years difficult • requires new attribute each year UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 55 of 56 Modifications & additions by Cengiz Güngör

Summary n Functional Dependencies and Decomposition help us achieve our design goals: § Avoid redundant data § Ensure that relationships among attributes are represented § Facilitate the checking of updates for violation of integrity constraints UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 8. 56 of 56 Modifications & additions by Cengiz Güngör