{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Chapter52

# Chapter52 - CSIS0278A Introduction to Database Management...

This preview shows pages 1–9. Sign up to view the full content.

Click to edit Master subtitle style CSIS0278A Introduction to Database Management Systems Lecture 5 Database Design Part 2: Database Normalization Dr. Reynold Cheng Based on the Ch. 7 notes of “Database System Concepts” by A. Silberschatz et al., and notes by Dr. Ho Wai Shing and Dr. Nikos Mamoulis

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
2DB060:2 Overview l First Normal Form l Pitfalls in Relational Database Design l Functional Dependencies l Lossless-Join Decomposition l Boyce-Codd Normal Form
3DB060:3 Goals of Normalization l We want to make sure that every relation R is in a “good” form. l If R is not “good”, decompose it into a set of relations {R1, R2, …, Rn} such that l Each relation is good (e.g., BCNF) l They result in a lossless-join decomposition. l These requirements can be explained by using functional dependencies

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
4DB060:4 Lossless-Join Decomposition A B C 1 1 3 1 2 2 2 1 3 R A B 1 1 1 2 2 1 R1 = A, B(R) B C 1 3 2 2 R2 = B, C(R) A B C 1 1 3 1 2 2 2 1 3 l A, B(R) B, C(R) = R
5DB060:5 Lossless-Join Decomposition & FD l Consider a decomposition of R into R1 and R2. l schema of R = schema of R1 } schema of R2. l A decomposition of R into R1 and R2 is lossless-join if and only if at least one of the following dependencies is in F+: l Let schema of R1 ° schema of R2 be R1 and R2’s common attributes l schema of R1 ° schema of R2 ± schema of

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Lossless Join Decomposition & FD Example (1) l Decomposition of R(A,B,C) on P.4: l R1(A, B) l R2(B, C) l To see if (R1, R2) is a lossless join decomposition of R, we do the following: 1. Find common attributes of R1 and R2: B 2. Verify if any of the FD below holds: l B ² 3. Since B ³ R2 (why?), (R1, R2) are lossless 6DB065:6
Lossless Join Decomposition & FD Example (2) l For the decomposition of R on P.4, 7DB065:7 A B C 1 1 3 1 2 2 2 1 3 l A, B(R) B, C(R) R1 R2 B ° R2, so, R1 and R2 form a lossless-join decomposition of R. That is, B is a candidate key of R2.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
8DB060:8 Normalization Goals Again l When we decompose a relation schema R with a set of function dependencies F into R1, R2, …, Rn, the following goals must be met: l Lossless-join Decomposition – otherwise, the decomposition would result in information loss. l Dependency Preserving – So that we can check FDs in local relations only.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}