Chapter52

Chapter52 - CSIS0278A Introduction to Database Management...

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

View Full Document Right Arrow Icon
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
Background image of page 1

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

View Full DocumentRight Arrow Icon
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
Background image of page 2
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
Background image of page 3

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

View Full DocumentRight Arrow Icon
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
Background image of page 4
5DB060:5 Lossless-Join Decomposition 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 be R1 and R2’s common attributes l schema of R1 & schema of R2 ± schema of
Background image of page 5

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

View Full DocumentRight Arrow Icon
Lossless Join Decomposition 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 3. Since B ± R2 (why?), (R1, R2) are lossless 6DB065:6
Background image of page 6
Lossless Join Decomposition 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 a lossless-join decomposition of R. That is, B is a candidate key of R2.
Background image of page 7

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

View Full DocumentRight Arrow Icon
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. l
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/01/2010 for the course CS 0278 taught by Professor Dr during the Spring '10 term at HKU.

Page1 / 27

Chapter52 - CSIS0278A Introduction to Database Management...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online