cs 564 database management systems: design and implementation discussion session friday, sept 18,...

CS 564Database Management Systems: Design

and Implementation

Discussion SessionFriday, Sept 18, 2015

1

Apul Jain

Topics

Functional Dependencies Closure

Attribute Set Closure

Canonical Cover

BCNF

3NF

2

Functional Dependencies

Constraint between two sets of attributes A → B

Attributes {A1, A2, …., An} uniquely determine set {B1, B2,….., Bk}

Enrollment

StudentID Name Course Credits SSN

111 Alice CS564 4 12345

112 Bob CS532 4 13124

113 Charlie CS564 4 32123

114 Daniel CS760 3 45415

3

Functional Dependencies

FDs:

StudentID → Name

{StudentID, Course} → {Name, Credits, SSN}

SSN → Name

{SSN, Course} → {StudentID, Name, Credits}


111 Alice CS564 4 12345

112 Bob CS532 4 13124

113 Charlie CS564 4 32123

114 Daniel CS760 3 45415

4

Functional Dependencies: Armstrong’s AxiomsReflexivity

Given X {A1, A2, ……, An}⊆

then FD {A1, A2, …. An} → X is a valid FD

5

Functional Dependencies: Armstrong’s Axioms

Given X {StudentID, Name, Course, Credits, SSN} X = {StudentID, Course}⊆

Then {StudentID, Name, Course, Credits, SSN} → {StudentID, Course} is a valid FD formed by applying reflexivity


111 Alice CS564 4 12345

112 Bob CS532 4 13124

113 Charlie CS564 4 32123

114 Daniel CS760 3 45415

6

Reflexivity Example:

Functional Dependencies: Armstrong’s AxiomsAugmentation

Given sets of attributes A, B, C

If A → B

then {A, C} → {B, C}

7


Given StudentID → Name

then {StudentID, Course} → {Name, Course}


111 Alice CS564 4 12345

112 Bob CS532 4 13124

113 Charlie CS564 4 32123

114 Daniel CS760 3 45415

8

Augmentation Example

Functional Dependencies: Armstrong’s AxiomsTransitivity

Given two sets of attributes A, B. If

A → B

B → C

then A → C

9


If StudentID → SSN and SSN → Name

then StudentID → Name


111 Alice CS564 4 12345

112 Bob CS532 4 13124

113 Charlie CS564 4 32123

114 Daniel CS760 3 45415

10

Transitivity Example

Functional Dependencies ClosureIf F is a set of FDs, then closure of F denoted by F+ = set of all FDs logically implied by F

How to compute: Use Armstrong’s axioms

if A B then B → A (reflexivity)⊆

if A → B then {C, A} → {C, B} (augmentation)

if A → B and B → C, then A → C (transitivity)

11

Functional Dependencies Closure: Derived Rules

Derived Rules:

if A → B and A → C then A → BC (union)

if A → BC then A → B and A → C (decomposition)

if A → B and BC → D, then AC → D (pseudo-transitivity)

12


Algorithm

Input: set of attributes X = {A1, …., An}, F (set of FDs)

Result = F

While (F changes) do: if {B1, B2, ……. Bm} → C is an FD and logically implied by FD’s in F then add {B1, …. Bm} → C to F

13


Given FDs:

F = {StudentID → Name, {StudentID, Course} → {Classroom, ISSN}, ISSN → Instructor}

14

StudentID Name Course Instructor Classroom ISSN

111 Alice CS564 Arun 1210 657-989

112 Bob CS532 Rob 1351 878-090

113 Charlie CS564 Paris 1325 667-989

114 Daniel CS532 Marc 1214 456-878


Given FDs:


One iteration of F+ = F U {

{StudentID, Course} → ISSN [decomposition]{StudentID, Course} → Classroom [decomposition]{StudentID, Course} → Instructor [transitivity]{StudentID, Course} → {Instructor, Classroom} [union rule]

} 15


Topics



Canonical Cover

BCNF

3NF

16


Given a set of Attributes {A1, A2, …. , An}

The closure {A1, ….. , An}+ is the set of attributes B such that

{A1, A2, ……, An} → B

17

Attribute Set ClosureAlgorithm:

Input A = set of attributes

Let result = A;

while (result changes) do for each FD H → K in F do if H result⊆ add K to result

18

Attribute Set ClosureExample:

19


111 Alice CS564 Arun 1210 657-989

112 Bob CS532 Rob 1351 878-090

113 Charlie CS564 Paris 1325 667-989

114 Daniel CS532 Marc 1214 456-878


Attribute Set ClosureExample:


Compute {StudentID, Course}+

result = {StudentID, Course}result = {StudentID, Course, Name} [StudentID → Name]result = {StudentID, Course, Name, Classroom, ISSN} [{StudentID, Course} → {Classroom, ISSN}]result = {StudentID, Course, Name, Classroom, ISSN, Instructor} [ISSN → Instructor]

Since the closure is the entire set of attributes we can verify that {StudentID, Course} is a super key

20

Topics



Canonical Cover

BCNF

3NF

21

Canonical CoverAlgorithm:

1. Standardization of RHS:Rewrite all FDs with 1 attribute on RHS

2. Minimization of LHS For each FD, can the LHS be reduced without affecting the closure? 3. Eliminate redundant FDs (implied by other FDs, trivial FDs) 4. Combine FDs with same LHS

22

Canonical CoverExample:


23

This is already a canonical cover!


F = {StudentID → Name, {StudentID, Course, Name} → {Classroom, ISSN}, ISSN → Instructor, {StudentID, Course} → Instructor}

24

Step1StudentID → Name{StudentID, Course, Name} → Classroom{StudentID, Course, Name} → ISSNISSN → Instructor{StudentID, Course} → Instructor

Steps:

1. Standardization of RHS:2. Minimization of LHS3. Eliminate redundant FDs4. Combine FDs with same LHS



25

Step2StudentID → Name{StudentID, Course, Name} → Classroom{StudentID, Course, Name} → ISSNISSN → Instructor{StudentID, Course} → Instructor

Steps:




26

Step3StudentID → Name{StudentID, Course} → Classroom{StudentID, Course} → ISSNISSN → Instructor{StudentID, Course} → Instructor

Steps:




27

Step4StudentID → Name{StudentID, Course} → Classroom{StudentID, Course} → ISSNISSN → Instructor{StudentID, Course} → {Classroom, ISSN}

Steps:


Topics



Canonical Cover

BCNF

3NF

28

BCNF

X → Y is a trivial functional dependency (Y X)⊆

or

X is a super key for schema R

29

Decomposing a table into BCNF

How to decompose a schema into BCNF

1. Find a non trivial FD X → Y that violates BCNF condition (X is not a superkey)

2. Split table into two tables:

a. one with attributes XY (all attributes from the FD)

b. one with X U {R – Y}

30


Non trivial FDs:

Author → Nationality

Book title → {Genre, Pages}

Key is {Author, Book title}

31

Author Nationality Book title Genre Pages

Henry Dutch Serendipity Fiction 400

Jeff English Automata Textbook 300

David American DBMS Textbook 500

John English DBMS Textbook 500

Books


Book title → {Genre, Pages}

violates BCNF

32

Author Nationality Book title

Henry Dutch Serendipity

Jeff English Automata

David American DBMS

John English DBMS

Book title Genre Pages

Serendipity Fiction 400

Automata Textbook 300

DBMS Textbook 500


33

Author Nationality

Henry Dutch

Jeff English

David American

John English

Author Book title

Henry Serendipity

Jeff Automata

David DBMS

John DBMS

Author → Nationality violates

BCNF

Key is {Author, Book title}

Author Nationality Book title

Henry Dutch Serendipity

Jeff English Automata

David American DBMS

John English DBMS


34

Final schema

Author Nationality

Henry Dutch

Jeff English

David American

John English

Author Book title

Henry Serendipity

Jeff Automata

David DBMS

John DBMS

Book title Genre Pages

Serendipity Fiction 400

Automata Textbook 300

DBMS Textbook 500

Topics



Canonical Cover

BCNF

3NF

35

3NFGiven a relation schema R and a set of FDs F, we say R is in 3NF if for every FD X → A in F (X is a subset of R’s attributes and A is a single attribute), we have:

X → A is a trivial FDorX is a superkey for RorA is part of some key for R

36

Decompose a schema into 3NFConsider relation R: Advisor {StudentID, AdvisorID, SDept, ADept}F = {StudentID → SDept, AdvisorID → ADept}

Key is {StudentID, AdvisorID}R is not in 3NF

37

3NF Conditions:X → A is a trivial FDorX is a superkey for RorA is part of some key for R

Decompose a schema into 3NFConsider relation R: Advisor {StudentID, AdvisorID, SDept, ADept}F = {StudentID → SDept, AdvisorID → ADept}

Key is {StudentID, AdvisorID}R is not in 3NF

How to decompose?

Follow the same algorithm as BCNF

R1 = {StudentID, SDept} and R2 = {AdvisorID, ADept}, R3 = {StudentID, AdvisorID}

38

3NF Conditions:X → A is a trivial FDorX is a superkey for RorA is part of some key for R

Decompose a schema into 3NFTennis

Key is {Tournament, Year}Winner → Winner D.O.B.Winner D.O.B. is not a part of Key and Winner is not a super key, so it’s not in 3NF

39

Tournament Year Winner Winner D.O.B

French Open 2010 Rafael Nadal 3 June 1986

Wimbledon 2015 Novak Djokovic 22 May 1987

US Open 2013 Rafael Nadal 3 June 1986

French Open 2009 Roger Federer 8 August 1981

Decompose a schema into 3NF

40

Tournament Year Winner

French Open 2010 Rafael Nadal

Wimbeldeon 2015 Novak Djokovic

US Open 2013 Rafael Nadal

French Open 2009 Roger Federer

Winner Winner D.O.B

Rafael Nadal 3 June 1986

Novak Djokovic 22 May 1987

Roger Federer 8 August 1981

Tournament Year Winner Winner D.O.B

French Open 2010 Rafael Nadal 3 June 1986

Wimbeldeon 2015 Novak Djokovic 22 May 1987

US Open 2013 Rafael Nadal 3 June 1986

French Open 2009 Roger Federer 8 August 1981

Decompose a schema into 3NF

41

Tournament Year Winner

French Open 2010 Rafael Nadal

Wimbeldeon 2015 Novak Djokovic

US Open 2013 Rafael Nadal

French Open 2009 Roger Federer

Winner Winner D.O.B

Rafael Nadal 3 June 1986

Novak Djokovic 22 May 1987

Roger Federer 8 August 1981

Verify this is also in BCNF

3NF but not BCNF Example

Set of attributes = {A, B, C, D}FDs: {A, B, C} → D and D → A

Candidate Keys : {A, B, C} and {B, C, D}

BCNF check:

{A, B, C} → D : {A, B, C} is a candidate key, so it’s fine

D → A : D is not a super key, so it is not in BCNF

42

3NF but not BCNF Example

Set of attributes = {A, B, C, D}FDs: {A, B, C} → D and D → A

Candidate Keys : {A, B, C} and {B, C, D}

3NF check:

{A, B, C} → D : {A, B, C} is a candidate key.

D → A : D is not a super key, but A is a part of super key {A, B, C}, so it is ok.

Since both FDs are fine, this relation is in 3NF

There is no way to decompose it into BCNF with dependency preservation

43

cs 564 database management systems: design and implementation discussion session friday, sept 18,...

Documents

ssn namethen studentid

c transitivity

iteration of f

c decompositionif

f set of fdsresult

armstrongs axiomsgiven

issn instructor

attributes x