CS482 – Fall 01

Test 2A
Instructor: Son Cao Tran
________________________________________________________________________
Name:
SSN:
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Directions:

The test has 4 questions, each worth as indicated, there is more than 20 points.
Read the question carefully and answer only what is asked
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

1. (3.5 points) Consider a relation schema R(X,Y,Z,P,Q) with the following functional
dependencies:

XY → P, P → Z, and XP → Q

a) Which of the above functional dependencies violates the 3NF condition?

Why?
b) Based on the results of a) decide whether R is in 3NF or not.

a) {X, Y}+ = {X, Y, P, Z, Q} => {X, Y} is a superkey

{P}+ = {P, Z}
{X, P}+ = {X, P, Q, Z}+
=> {P}, {X, P} are not superkey. Notice also that no key in R contains Q or Z.
=> P→Ζ and XP→Q violate 3NF condition.

b) No, because there are functional dependencies in R that violate the 3NF condition.
2. (3.5 points) Consider the relation schema R(X,Y,Z,P,Q) with the following
multivalued dependencies (MD)

Y →→ X and YX →→ Z

functional dependencies

Y → P and YX → Q.

a) Derive 2 multivalued dependencies from the functional dependencies!

b) Find all MDs from the given MDs and the MDs from (a) that violate the 4NF
condition!
c) Is R in 4NF?
d) Decompose R into 4NF relations!

a) Y →→ P and YX →→ Q

b) There are only 2 functional dependencies in R:

{Y}+ = {Y, P}
{Y, X}+ = {X, Y, P, Q}
=> none of them are superkeys

=> all 4 multivalued dependencies: Y →→ X

YX →→ Z
Y →→ P
YX →→ Q

violate the 4NF condition because all of them are nontrivial and the left hand side is not a
superkey.

c) No

d) Takes YX →→ Q we have
(Y, X, Q) and (Y, X, Z, P)

−> this is not in 4NF.

Because of Y →→ X: (Y, X, Q) can be decomposed into (Y, X), (Y, Q)
(Y, X, Z, P) can be decomposed into (Y, X), (Y, Z, P)
Because of Y →→ P: (Y, Z, P) can be decomposed into (Y, Z), (Y, P)

So, the final decomposition: (Y, X), (Y, Q), (Y, Z), (Y, P).

Notes: There might be different ways to decompose R into 4NF. If you solution is right, it
will be graded correctly!
3. (7 points) Suppose that we have the following relational database schemas

Product(maker, model)
PC(model, speed, ram, hd, cd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

with the assumption that model number are unique over all manufacturer and product
types. This means that the above four relations have model as one of their keys.

Write expression of relational algebra for the following queries:

a) Which manufacturers produce PC and laptop?

b) Which manufactures produce at least two computers (PC or laptop) with the
speeds of at least 133?
c) Find all manufactures that produce printers but do not produce laptop.
d) List the price of all the PC, laptop, and printer.
e) Extral Point: Find all manufactures that produce the fastest PC.

NOTES: R: Product(maker, model)

S: PC(model, speed, ram, hd, cd, price)
T: Laptop(model, speed, ram, hd, screen, price)
U: Printer(model, color, type, price)
( : Join)

a) Πmaker (R
S)  Πmaker (R
T)

b) R1= Πmaker, model ( 

speed >=1.3
(R S) )
R2= Πmaker, model ( 

speed>=1.3
(R T) )

R3 = R1 R2, R4 = R3
R5 = R3.maker = R4.maker and R3.model<>R4.model (R3 x R4)


Πmaker (R5)

c) Πmaker (R
U) − Πmaker (R
T)
d) Πmodel, price (S)  Πmodel, price (T)  Πmodel, price (U)

e) R1= Πmaker, speed (R
S)

R2 = Πspeed (R1) R3 = R2
R4 = Πspeed ( 

R2.speed >R3.speed
(R2 x R3) )  Πspeed ( 

R2.speed =< R3.speed

(R2 x R3) )
4. (7 points) Suppose that we have the following relational database schemas

Product(maker, model)
PC(model, speed, ram, hd, cd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

with the assumption that model number are unique over all manufacturer and product
types. This means that the above four relations have model as one of their keys.

Write SQL queries for the following queries:

a) Which manufacturers produce PC and laptop?

b) Find the average price of PC’s made by manufacturer “A”.
c) Which manufactures produce at least two computers (PC or laptop) with the
speeds of at least 133?
d) Find the manufactures of PC’s with at least four different speeds.
e) Find the manufactures who sell exactly four different models of PC.
f) Find for each manufacturer the maximum price of a PC.

a) select maker
from product, pc
where product.model = pc.model
intersect
select maker
from product, laptop
where product.model = laptop

b) select avg(price)
from pc, product
where pc.model = product.model and maker=’A’;

c) create view pmaker (maker, model) as

select maker, pc.model
from product, pc
where product.model = pc.model and pc.speed >=133
union
select maker, laptop.model
from product, pc
where product.model = laptop.model and laptop.speed >=133

select maker
from pmaker
group by maker
having count (model) >=2
d) select maker
from product p,pc
where p.model=pc.model
group by maker
having count(distinct (speed))>=4;

e) select maker
from product p,pc
where p.model=pc.model
group by maker
having count(distinct (model)) =4;

f) select maker, max(price)

from product, pc
where product.model = pc.model
group by maker;
 CS482 – Fall 01
Test 2A
Instructor: Son Cao Tran
________________________________________________________________________
Name:
SSN:
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Directions:

The test has 4 questions, each worth as indicated, there is more than 20 points.
Read the question carefully and answer only what is asked

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

5. (3.5 points) Consider a relation schema R(P,Q,M,N,E) with the following functional
dependencies:

PQ → N, N → M, and PN → E

a) Which of the above functional dependencies violates the 3NF condition?

Why?
b) Based on the results of a) decide whether R is in 3NF or not.

a) {P,Q}+ = {P, Q, N, M, E} => {P, Q} is a superkey

{N}+ = {N, M}
{P, N}+ = {P, N, E, M}+
=> {N}, {P,N} are not superkey. Notice also that no key in R contains M or E.
=> N→M and PN→E violate 3NF condition.

b) No, because there are functional dependencies in R that violate the 3NF condition.
6. (3.5 points) Consider the relation schema R(P,Q,M,N,E) with the following
multivalued dependencies (MD)

Q →→ P and QP →→ M

functional dependencies

Q → N and PQ → E.

a) Derive 2 multivalued dependencies from the functional dependencies!

b) Find all MDs from the given MDs and the MDs from (a) that violate the 4NF
condition!
c) Is R in 4NF?
d) Decompose R into 4NF relations!

a) Q →→ N and PQ →→ E

b) There are only 2 functional dependencies in R:

{Q}+ = {Q, N}
{P, Q}+ = {P, Q, E, N}
=> none of them are superkeys

=> all 4 multivalued dependencies: Q →→ P

QP →→ M
Q →→ N
PQ →→ E

violate the 4NF condition because all of them are nontrivial and the left hand side is not a
superkey.

c) No

d) Takes PQ →→ E we have
(P, Q, E) and (P, Q, M, N)

−> this is not in 4NF.

Because of Q →→ P: (P, Q, E) can be decomposed into (Q, P), (Q, E)
(P, Q, M, N) can be decomposed into (P, Q), (Q, M, N)
Because of Q →→ N: (Q, M, N) can be decomposed into (Q, N), (Q, M)

So, the final decomposition: (Q, P), (Q, E), (Q, N), (Q, M).

Notes: There might be different ways to decompose R into 4NF. If your solution is right,
it will be graded correctly !
7. (7 points) Suppose that we have the following relational database schemas

Product(maker, model)
PC(model, speed, ram, hd, cd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

with the assumption that model number are unique over all manufacturer and product
types. This means that the above four relations have model as one of their keys.

Write expression of relational algebra for the following queries:

a) Which manufacturers produce printer and laptop?

b) List the price of all the PC, laptop, and printer.
c) Find all manufactures that produce printers but do not produce PC.
d) Which manufactures produce at least two computers (PC or laptop) with the
hard disk of at least 1.3?
e) Extral Point: Find all manufactures that produce the fastest PC.

NOTES: R: Product(maker, model)

S: PC(model, speed, ram, hd, cd, price)
T: Laptop(model, speed, ram, hd, screen, price)
U: Printer(model, color, type, price)
( : Join)

a) Πmaker (R
U)  Πmaker (R
T)

b) Πmodel, price (S)  Πmodel, price (T)  Πmodel, price (U)

c) Πmaker (R
U) − Πmaker (R
S)

d) R1= Πmaker, model ( (R 

hd>=1.3
S) )
R2= Πmaker, model ( hd>=1.3 (R  T) )

R3 = R1 R2, R4 = R3
R5 = R3.maker = R4.maker and R3.model<>R4.model (R3 x R4)


Πmaker (R5)

e) R1= Πmaker, speed (R
S)

R2 = Πspeed (R1) R3 = R2
R4 = Πspeed ( 

R2.speed >R3.speed
(R2 x R3) )  Πspeed ( 

R2.speed =< R3.speed

(R2 x R3) )
8. (7 points) Suppose that we have the following relational database schemas

Product(maker, model)
PC(model, speed, ram, hd, cd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

with the assumption that model number are unique over all manufacturer and product
types. This means that the above four relations have model as one of their keys.

Write SQL queries for the following queries:

a) Which manufacturers produce printer and laptop?

b) Which manufactures produce at least two computers (PC or laptop) with the
hard disk of at least 1.3?
c) Find the average price of PC’s made by manufacturer “C”.
d) Find the manufactures of PC’s with at least four different speeds.
e) Find the manufactures who sell exactly four different models of PC.
f) Find for each manufacturer the maximum price of a PC.

a) select maker
from product, printer
where product.model = printer.model
intersect
select maker
from product, laptop
where product.model = laptop

b) create view pmaker (maker, model) as

select maker, pc.model
from product, pc
where product.model = pc.model and pc.hd >=1.3
union
select maker, laptop.model
from product, laptop
where product.model = laptop.model and laptop.hd >=1.3

select maker
from pmaker
group by maker
having count (distinct model) >=2

c) select avg(price)
from pc, product
where pc.model = product.model and maker=’C’;
d) select maker
from product p,pc
where p.model=pc.model
group by maker
having count(distinct (speed))>=4;

e) select maker
from product p,pc
where p.model=pc.model
group by maker
having count(distinct (model)) =4;

f) select maker, max(price)

from product, pc
where product.model = pc.model
group by maker;