# UTSA CS 3743 - Logical Database Design (28 pages)

Previewing pages 1, 2, 3, 26, 27, 28 of 28 page document
View Full Document

## Logical Database Design

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

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
##### Intro to Database Systems Documents
• 26 pages

• 27 pages

• 22 pages

• 33 pages

• 27 pages

• 44 pages

• 5 pages

• 42 pages

• 25 pages

• 25 pages

• 2 pages

• 36 pages

• 2 pages

• 6 pages

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

Unlocking...