Relational Database Design and SQL Basics Relational ...

Relational Database Design

and

SQL Basics

CPS 216

Advanced Database Systems

Relational design: a review

?

?

?

?

?

Identifying tuples: keys

Generalizing the key concept: FDs

Non-key FDs: redundancy

Avoiding redundancy: BCNF decomposition

Preserving FDs: 3NF

2

BNCF = no redundancy?

? Student (SID, CID, club)

¨C Suppose your classes have nothing to do with the

clubs you join

¨C FDs?

¨C BNCF?

¨C Redundancies?

SID

142

142

142

142

123

123

¡­

CID

CPS 216

CPS 216

CPS 214

CPS 214

CPS 216

CPS 216

¡­

club

ballet

sumo

ballet

sumo

chess

golf

¡­

3

1

Multi-valued dependencies

? A multi-valued dependency (MVD) has the form

X ¡ú¡ú Y, where X and Y are sets of attributes in a

relation R

? X ¡ú¡ú Y means that whenever two tuples in R

agree on all the attributes of X, then we can swap

their Y components and get two new tuples that

are also in R

4

MVD examples

Student (SID, CID, club)

5

Complete MVD + FD rules

? FD reflexivity, augmentation, and transitivity

? MVD complementation:

If X ¡ú¡ú Y, then X ¡ú¡ú attrs(R) ¨C X ¨C Y Try proving

dependencies

? MVD augmentation:

If X ¡ú¡ú Y and V ? W, then XW ¡ú¡ú YV with these!?

? MVD transitivity:

If X ¡ú¡ú Y and Y ¡ú¡ú Z, then X ¡ú¡ú Z ¨C Y

? Replication (FD is MVD):

If X ¡ú Y, then X ¡ú¡ú Y

? Coalescence:

If X ¡ú¡ú Y and Z ? Y and there is some W disjoint from

Y such that W ¡ú Z, then X ¡ú Z

6

2

An elegant solution: chase

? Given a set of FDs and MVDs D, does another

dependency d (FD or MVD) follow from D?

? Procedure

¨C Start with the hypotheses of d, and treat them as

¡°seed¡± tuples in a relation

¨C Apply the given dependencies in D repeatedly

? If we apply an FD, we infer equality of two symbols

? If we apply an MVD, we infer more tuples

¨C If we infer the conclusion of d, we have a proof

¨C Otherwise, if nothing more can be inferred, we have a

counterexample

7

Proof by chase

? In R (A, B, C, D), does A ¡ú¡ú B and B ¡ú¡ú C

imply A ¡ú¡ú C?

8

Counterexample by chase

? In R (A, B, C, D), does A ¡ú¡ú BC and CD ¡ú B

imply A ¡ú B?

9

3

4NF

? A relation R is in Fourth Normal Form (4NF) if

¨C For every non-trivial MVD X ¡ú¡ú Y in R, X is a

super key

¨C That is, all FDs and MVDs follow from ¡°key ¡ú other

attributes¡±

? 4NF is stronger than BCNF

10

4NF decomposition algorithm

? Find a 4NF violation

¨C A non-trivial MVD X ¡ú¡ú Y in R where X is not a super key

? Decompose R into R1 and R2, where

¨C R1 has attributes X ¡È Y

¨C R2 has attributes X ¡È Z (Z contains attributes not in X or Y)

? Repeat until all relations are in 4NF

? Almost identical to BCNF decomposition algorithm

? Any decomposition on a 4NF violation is lossless

11

4NF decomposition example

Student (SID, CID, club)

SID

142

142

142

142

123

123

¡­

CID

CPS 216

CPS 216

CPS 214

CPS 214

CPS 216

CPS 216

¡­

club

ballet

sumo

ballet

sumo

chess

golf

¡­

12

4

3NF, BCNF, and 4NF

3NF

BCNF

4NF

Preserves FDs?

Redudancy due to FDs?

Redundancy due to MVDs?

13

Recap

? Another source of redundancy: MVDs

? Reasoning about FDs and MVDs: chase

? Avoiding redundancy due to MVDs: 4NF

14

A complete design example

? Information about parts and assemblies for a

manufacturing company; e.g.:

¨C A bicycle consists of one frame and two wheels; the

cost of assembly is $30

¨C A frame is just a basic part

¨C A wheel consists of one tire, one rim, and 48 spokes;

the cost of assembly is $40

¨C Everything has a part ID and a name

15

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download