UTSA CS 3743 - Logical Database Design (28 pages)

Previewing pages 1, 2, 3, 26, 27, 28 of 28 page document View the full content.
View Full Document

Logical Database Design



Previewing pages 1, 2, 3, 26, 27, 28 of actual document.

View the full content.
View Full Document
View Full Document

Logical Database Design

29 views

Lecture Notes


Pages:
28
School:
The University of Texas at San Antonio
Course:
Cs 3743 - Intro to Database Systems

Unformatted text preview:

Logical Database Design 3 of 3 John Ortiz Normalization If a relation is not in BCNF or 3NF we refine it by decomposing it into two or more smaller relation schemas that are in the normal form Decomposition has to be used carefully since there are potential problems What are desirable properties of a decomposition and how to test them How to obtain a decomposition with some desirable properties Lecture 7 Logical Database Design 2 2 Decomposition of a Relation Let R be a relation schema A decomposition of R demoted by D R1 R2 Rn is a set of relation schemas such that R R1 Rn If R1 R2 Rn is a decomposition of R and r is an instance of R then r R1 r R2 r Rn r Information may be lost i e wrong tuples may be added by the natural join due to a decomposition Lecture 7 Logical Database Design 2 3 An Example of Information Loss Before SC SID Name Cno Grade Room 101 Bill 202 Mary After SR Name Room Bill Mary SR Lecture 7 SG 326 326 SID 101 202 101 202 1000 B 2000 A 326 326 SG SID Cno Grade Room Name Bill Mary Mary Bill 101 1000 B 202 2000 A Cno 1000 2000 1000 2000 Grade B A B A Logical Database Design 2 326 326 Room 326 326 326 326 4 Lossless Join Decomposition Let R be a relation schema and D R1 R2 Rn be a decomposition of R D is a lossless non additive join decomposition of R if for every legal instance r of R we have r R1 r R2 r Rn r Theorem Let F be a set of FDs over R and D R1 R2 be a decomposition of R D is a lossless join decomposition if and only if R1 R2 R1 R2 is in F or Lecture 7 Logical Database Design 2 5 R1 R2 R2 R1 is in F Lossless Join An Example Consider F B AH L CAt over Bank Loans Bank Assets Headquarter Loan Customer Amount Let D Banks B A H Loans B L C At Since Banks Loans B AH Banks Loans is in F since it is already in F D is a lossless join decomposition What if the decomposition contains more than two relations Lecture 7 Logical Database Design 2 6 Test for Lossless Join Algorithm TestLJ Chase Input A relation schema R A1 Am a set of FDs F and a



View Full Document

Access the best Study Guides, Lecture Notes and Practice Exams

Loading Unlocking...
Login

Join to view Logical Database Design and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Logical Database Design and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?