Exam 1 Solutions - University of New Mexicomueen/Teaching/CS_464_564/Lectures/Exam1.pdf · Property...

Exam 1 Solutions

Spring 2016

CS564 - Exam 1 Solutions (spring 2016)

Problem 1

1.

R1 := σcolor=′red′ OR color=′green′(Parts)

Result := Πsid(R1 ./ Catalog)

2.

R1 := σsname=′Y osemiteSham′(Suppliers)

R2 := Πpid,cost(R1 ./ Catalog)

R3(pid1, cost1) := R2

R4(pid2, cost2) := R2

R5 := Πpid1,cost1(R3 ./ pid1=pid2 AND cost1<cost2 R4)

R6 := R3 − R5

Result := Πpid(R6)

3.

R1 := Πsid,pid(Catalog)

R2(sid1, pid1) := R1

R3(sid2, pid2) := R1

Result := Πpid1(R2 ./ sid1<>sid2 AND pid1=pid2 R3)

of 11


Problem 2

1.

A B E F

1 2 3 31 2 3 31 2 3 31 2 3 3

2.

A B Count(D) AVG(C)

1 2 2 23 3 2 44 1 1 31 6 1 41 3 1 6

We don’t count NULL in COUNT and AVG.

of 11


Problem 3

According to FD’s the key is ID, AdvisorID, so all the given FD’s violatesBCNF because on the left side of them, we don’t have the superkey. Inorder to decompose, we take one of them that violate BCNF. For exampleID→Name.

{ID}+ = {ID, Name, FavoriteAdvisorID}

So we compose the table to:Students1 (ID,Name,FavoriteAdvisorID)Students2 (ID,AdvisorID,AdvisorName)But we are not done here! We should consider all relevant FD’s for eachtable to see that there is still any violation of BCNF or not. For Studens1there is no violation of BCNF because the key is ID and all relevant FD’shave the key on their left side. For Students2 the key is ID, AdvisorID butthe relevant FD that is {AdvisorID→AdvisorName} don’t have supper keyon the left side. So we have BCNF violation and we should decompose thistable.{AdvisorID}+ = {AdvisorID, AdvisorName}

So we have:

Students3 (AdvisorID,AdvisorName)Students4 (AdvisorID,ID)

Because they are two-attribute relations so there are in BCNF. Thereforeat the end we will have three relations that are the final decomposition:

Students1 (ID,Name,FavoriteAdvisorID)Students3 (AdvisorID,AdvisorName)Students4 (AdvisorID,ID)

of 11


Problem 4

a.

Πname

./ssn=buyer−ssn

Person ./

Purchase ./maker−sid=sid

Πpid,maker−cid

Product

Πcid

σcountry=′USA′

Company

b.

1)The correct answer is:R := Person2× Purchase× Person1Answer := ΠP2.ssn(σP1.ssn=buyers−ssn AND P2.ssn=seller−ssn AND P2.phone number=P1.phone number(R))

The common mistake was:

R1 := Purchase ./ buyer−ssn=ssn PersonR2 := Purchase ./ seller−ssn=ssn PersonResult := R1 ./ R1.phone number=R2.phone number R2

The reason why that is not correct because the result contains inappropriatetuples and does not cover all correct answers.

of 11


2)Π seller−ssn(σ buyer−ssn=seller−ssn(Purchase))

3)R = (Purchase1× Purchase2)Πbuyer−ssn(σP1.buyer−ssn=P2.seller−ssn AND P1.store=P2.store(R))

of 11


Problem 5

Property 3NF BCNF 4NF

Eliminates redundancy due to FD’s No Yes YesEliminates redundancy due to MVD’s No No Yes

Preserve FD’s Yes No noPreserve MVD’s No No no

3NF

BCNF

4NF

of 11


Problem 6

[464]

a) A→ D does not hold.

b)Step 1. Initialize

A B C D E

a b1 c1 d e1a b c d2 e

Step 2. Apply A −→→ BC

A B C D E

a b1 c1 d e1a b1 c1 d2 ea b c d2 ea b c d e1

Step 3. Apply B → D

A B C D E

a b1 c1 d e1a b1 c1 d ea b c d ea b c d e1

Step 4. Apply C −→→ E

A B C D E

a b1 c1 d e1a b1 c1 d ea b1 c1 d ea b1 c1 d e1a b c d ea b c d e1a b c d e1a b c d e

of 11


A −→→ D hold in that relation.

[564]

Step 1. Initialize

A B C D E

a b c d1 e1a2 b c d e2a b3 c d3 e

Step 2. Apply A→ D

A B C D E

a b c d1 e1a2 b c d e2a b3 c d1 e

Step 3. Apply D → E

A B C D E

a b c d1 ea2 b c d e2a b3 c d1 e

Step 4. Apply B → D

A B C D E

a b c d ea2 b c d e2a b3 c d1 e

Decomposition is lossless.

of 11


Problem 7

[464]

Studios (name, addr)Crews (number, studioName, crewChief)

When weak entity relationship does not have attributes, there is no needto convert it to a relation.

[564]

Flights (FlightNumber, Day, Aircraft)Customers (SSN, Name, Address)Booking (SSN, FlightNumber, Day, Row, Seat)

FD’s for ”Flights” is:FlightNumber,Day → AricraftIt does not violate BCNF because left side has the keys (super key).

FD’s for ”Customers” is:SSN → NameSSN → AddressIt does not violate BCNF because left side has the keys (super key).

FD’s for ”Booking” is:SSN,F lightNumbers,Day → RowSSN,F lightNumbers,Day → SeatsIt does not violate BCNF because left side has the keys (super key).

of 11


Problem 8

We can use contrapositive in order to prove this. So, we are going to showthat if X+ is not a subset of Y+, then it must be that X is not a subset ofY.

Imagine we have A1A1...An attributes in X+ that is not in Y+ (Becausewe said that (X+ ⊆ Y +) ). If any of these attributes were originally in Xthen we are done because Y doesn’t have any of this attributes but if theseattributes were added by the closure, then we must see the case further. As-sume that there was sum FD’s like C1C2...Cm → A1A1...Ai where A1A1...Ai

is some subset of A1A1...An. So, it must be C1C2...Cm or some subset ofC1C2...Cm is in X. But because the attribute C1C2...Cm cannot be in Y be-cause we assumed that attributes A1A1...An are only in X+ therefore X isnot a subset of Y.

By proving the contrapositive we have also proved if X ⊆ Y then X+ ⊆ Y +.

of 11

Exam 1 Solutions - University of New Mexicomueen/Teaching/CS_464_564/Lectures/Exam1.pdf · Property...

Documents

Transcript of Exam 1 Solutions - University of New Mexicomueen/Teaching/CS_464_564/Lectures/Exam1.pdf · Property...