Database DesignPowerPoint PresentationSlide 3Relational Database DesignProblems of Bad DB DesignNormalizationDecompositionNormal Forms (NFs)Slide 9Slide 10Slide 11Slide 12Slide 132-Relational lossless-join decompositionDependency preserving2003SJSU -- CmpE L10-S1 NormalizationDatabase Design Dr. M.E. Fayad, ProfessorComputer Engineering Department, Room #283I College of EngineeringSan José State UniversityOne Washington SquareSan José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad2003SJSU – CmpE --- M.E. Fayad L10-S2 Normalization2Lesson 10:Normalization2003SJSU – CmpE --- M.E. Fayad L10-S3 Normalization Lesson ObjectivesObjectives3 Understand the goals of normalization Explore the problems of DB bad design Understand normalization Learn how to deal with normalization2003SJSU – CmpE --- M.E. Fayad L10-S4 NormalizationGoals:–Reduce data redundancy (undesirable replication of data values)–Minimize anomaly problems (data model is structured in an improper manner)–Maintain (correct) information–Enforce semantic & integrity constraints, e.g., using dependency & domain constraints4Relational Database Design2003SJSU – CmpE --- M.E. Fayad L10-S5 NormalizationData replication: extra storage, update anomalies Anomaly: costly & data inconsistency Loss of information: poor decompositionUn-enforced dependency constraints: dependencies are lost5Problems of Bad DB Design2003SJSU – CmpE --- M.E. Fayad L10-S6 NormalizationA db scheme design toolA process of replacing associations among attributes in a relation schemeAn approximation of the relation schemes that should be createdObjectives: accomplish the goals of relational database design Two approaches: decomposition & synthesis6Normalization2003SJSU – CmpE --- M.E. Fayad L10-S7 NormalizationA process to split or decompose a relation until the resultant relations no longer exhibit the undesirable problems, e.g., data redundancy, data inconsistency, anomaly, etc. Decomposing a relation scheme R means breaking R into a pair of schemes, possibly intersecting+ this process is repeated until all the decomposed relation schemes are in the desired (normal) form.7Decomposition2003SJSU – CmpE --- M.E. Fayad L10-S8 NormalizationNormal Forms (NFs)1NF2NF3NFBCNF4NFPJNF82003SJSU – CmpE --- M.E. Fayad L10-S9 Normalizationrestrictions on the db scheme that preclude certain undesirable properties (data redundancy, update anomaly, loss of information, etc.) from the DB.A relation scheme R is in PJNF ifR is in 4NF ifR is in BCNF ifR is in 3NF ifR is in 2NF if R is in 1NF9Normal Forms (NFs)2003SJSU – CmpE --- M.E. Fayad L10-S10 NormalizationDefinition. A data value v is atomic if v is not a (i) set of values or (ii) composite value; otherwise, v is non-atomic.First Normal Form (1NF). A relation scheme R is in 1NF if for every attribute A in R, the values in the domain of A, i.e., dom(A), are atomic.10Normal Forms (NFs)2003SJSU – CmpE --- M.E. Fayad L10-S11 NormalizationBoyce-Codd Normal Form (BCNF). A relation scheme R is in BCNF if for every non-trivial FD X Y applied to R, X is a superkey for R.Definition. Let A be an attribute in a relation scheme R, and let F be a set of FDs over R. A is a prime attribute in R if A is contained in some candidate key of R; otherwise, A is a non-prime attribute in R.11Normal Forms (NFs)2003SJSU – CmpE --- M.E. Fayad L10-S12 NormalizationThird Normal Form (3NF). A relation scheme R is in 3NF if1R is in 1NF, and2For every non-trivial FD X Y applied to R, either–X is a superkey of R, or–every attribute in Y is an attribute of some candidate key of R, i.e., prime.12Normal Forms (NFs)2003SJSU – CmpE --- M.E. Fayad L10-S13 NormalizationLossy decomposition: a decomposition is lossy if the natural join of all the decomposed relations contain additional tuples and the original relation is lost.Lossless decomposition: a decomposition is lossless if the natural join of all the decomposed relations always yields the original relation without any extra tuples, i.e., –a decomposition {R1, R2, …, Rn} of R is lossless if r(R),r(R) = R1(r) R2(r) … Rn(r)13Normal Forms (NFs)2003SJSU – CmpE --- M.E. Fayad L10-S14 Normalization2-Relational lossless-join decomposition: Let R1 and R2 be decomposed schemes of R. Let F be a set of FDs on R. The decomposition is lossless ifR1 R2 R1 F+ or R1 R2 R2 F+142-Relational lossless-join decomposition2003SJSU – CmpE --- M.E. Fayad L10-S15 Normalizationa decomposition is dependency preserving if no dependency is lost in the process.Let F be the set of FDs on R.Let R1, … , Rn be a decomposition of R.Let Fi, 1 i n, be the set of FDs in F+ which applies to Ri.Let F’ = i=1..n Fi.If F’+ = F+, then the decomposition is dependency preserving.15Dependency
View Full Document
Unlocking...