1 / 15

Database Design

Database Design. Dr. M.E. Fayad, Professor Computer Engineering Department, Room #283I College of Engineering San José State University One Washington Square San José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad. Lesson 8: Normalization. 2. Lesson Objectives.

leoma
Télécharger la présentation

Database Design

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. Database Design Dr. M.E. Fayad, Professor Computer Engineering Department, Room #283I College of Engineering San José State University One Washington Square San José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad SJSU -- CmpE

  2. Lesson 8: Normalization 2 SJSU – CmpE --- M.E. Fayad

  3. Lesson Objectives • Understand the goals of normalization • Explore the problems of DB bad design • Understand normalization • Learn how to deal with normalization 3 SJSU – CmpE --- M.E. Fayad

  4. Goals: 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 constraints Relational Database Design 4 SJSU – CmpE --- M.E. Fayad

  5. Data replication: extra storage, update anomalies Anomaly: costly & data inconsistency Loss of information: poor decomposition Un-enforced dependency constraints: dependencies are lost Problems of Bad DB Design 5 SJSU – CmpE --- M.E. Fayad

  6. A db schemedesign tool A process of replacing associations among attributes in a relation scheme An approximation of the relation schemes that should be created Objectives: accomplish the goals of relational database design Two approaches: decomposition & synthesis Normalization 6 SJSU – CmpE --- M.E. Fayad

  7. A 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. Decomposition 7 SJSU – CmpE --- M.E. Fayad

  8. Normal Forms (NFs) 1NF 2NF 3NF BCNF 4NF PJNF 8 SJSU – CmpE --- M.E. Fayad

  9. restrictions 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 if R is in 4NF if R is in BCNF if R is in 3NF if R is in 2NF if R is in 1NF Normal Forms (NFs) 9 SJSU – CmpE --- M.E. Fayad

  10. Definition.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. Normal Forms (NFs) 10 SJSU – CmpE --- M.E. Fayad

  11. Boyce-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. Normal Forms (NFs) 11 SJSU – CmpE --- M.E. Fayad

  12. Third Normal Form (3NF).A relation scheme R is in 3NF if R is in 1NF, and For 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. Normal Forms (NFs) 12 SJSU – CmpE --- M.E. Fayad

  13. Lossy 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) Normal Forms (NFs) 13 SJSU – CmpE --- M.E. Fayad

  14. 2-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 if R1 R2  R1  F+ or R1 R2  R2  F+ 2-Relational lossless-join decomposition 14 SJSU – CmpE --- M.E. Fayad

  15. a decomposition is dependency preserving if nodependency 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..nFi. If F’+ = F+, then the decomposition is dependencypreserving. Dependency preserving 15 SJSU – CmpE --- M.E. Fayad

More Related