Homework #2 Solutions
Earl Wagner


3.2.1
Customers (_SSN_, name, addr, phone)
Flights (_number_, _day_, aircraft)
Bookings (row, seat, _customerSSN_, _flightNumber_, _flightDay_)


3.2.4
a) Fig 2.22/3.10
Contracts (_starName_, _studioName_, _title_, _year_, salary)
Stars (_name_, addr)
Studios (_name_, addr)
Movies (_title_, _year_, length, filmType)

b) Ex 2.4.1
Students (_studentID_, name)
Courses (_number_, _dept_)
Enrollments (_studentID_, _courseNumber_, _courseDept_, grade)

c) Ex 2.4.4.a
Departments (_name_)
Courses (_number_, _deptName_)


3.3.1
a) ER method
Depts (_name_, chair)
Courses (_number_, _deptName_, room)
LabCourses (_number_, _deptName_, computerAlloc)

b) OO method
Depts (_name_, chair)
Courses (_number_, _deptName_, room)
CoursesL (_number_, _deptName_, room, computerAlloc)

c) nulls method
Depts (_name_, chair)
Courses (_number_, _deptName_, room, computerAlloc)


3.4.4, 3.4.5 (a,b,c) - see keys underlined above


3.5.2
ii)
compute closures
no fd has a single attribute on the left side so no interesting single attribute closures
    ab+ = abcd, ac+ = ac, ad+ = abcd
    bc+ = abcd, bd+ = bd, cd+ = abcd
    abc+ = abcd, abd+ = abcd, acd+ = abcd, bcd+ = abcd

a) ab -> d, ad -> c, bc -> a, cd -> b
b) ab, ad, bc, cd
c) abc, abd, acd, bcd, abcd

iii)
compute closures
    a+ = abcd, b+ = abcd, c+ = abcd, d+ = abcd

a) a -> cd, b -> da, c -> ab, d -> bc (not including the given FDs)
b) a, b, c, d
c) ab, ac, ad, bc, bd, cd, abc, abd, acd, bcd, abcd


3.5.4
a)
A   B
------
0   2
1   2

b)
A   B   C
---------
0   0   3
1   2   4
1   0   4

c)
A   B   C
---------
0   0   3
1   2   4
0   2   0

3.5.10
b)
a -> d, bd -> e, ac -> e, de -> b
compute closures:
    a+ = ad, b+ = b, c+ = c
    ab+ = abde, ac+ = abcde, ad+ = ad
    bc+ = bc, bd+ = bde, cd+ = cd

the only nontrivial FD that holds in S is ac->b
the others do not hold because d and e are not in S

c)
ab -> d, ac -> e, bc -> d, d -> a, e -> b
compute closures:
    a+ = a, b+ , b, c+ = c, d+ = ad, e+ = be
    ab+ = abd, ac+ = abcde, ad+ = ad
    bc+ = abcde, bd+ = abd, cd+ = abcde

projecting out d and e, we get the nontrivial FDs:
    ac -> b, bc -> a

d)
a -> b, b -> c, c -> d, d -> e, e -> a
compute closures:
    a+ = abcde, b+ = abcde, c+ = abcde, d+ abcde
    ab+ = abcde, ac+ = abcde, bc+ = abcde

projecting out d and e, we get the nontrivial FDs:
    a -> b, a -> c, b -> a, b -> c, c -> a, c -> b
    also true, but logically follow from the ones above: ab -> c, ac -> b, bc -> a

3.6.1
c)
compute closures:
    a+ = a, b+ = b, c+ = c, d+ = d
    ab+ = abcd, ac+ = ac, ad+ = abcd
    bc+ = abcd, bd+ = bd, cd+ = abcd
    abc+ = abcd, abd+ = abcd, acd+ = abcd, bcd+ = abcd

keys:
    ab, ad, bc, cd

new non-trivial fds:
    ab -> cd, bc -> ad, ad -> bc, cd -> ab

i) all FDs have a key on the left side, so no BCNF or 3NF violations
ii) no decomposition is necessary
iii) no 3NF violations
iv) no decomposition is necessary

d)
compute closures:
    a+ = abcd, b+ = abcd, c+ = abcd, d+ abcd
keys:
    a, b, c, d
new non-trivial fds
    a -> bcd, b -> acd, c -> abd, d -> abc
i) all attributes are keys, so again no BCNF or 3NF violations
ii) no decomposition necessary
iii) no 3NF violations
iv) no decomposition necessary

f)
compute closures:
    a+ -> a, b+ -> b, c+ -> bcde, d+ -> bde, e+ -> e
    ab+ -> abcde, ac+ -> abcde, ad+ -> abcde, ae -> ae,
    bc+ -> bcde, bd+ -> bde, be+ -> be
    cd+ -> bcde, ce+ -> bcde, de+ -> bde
    abc+ -> abcde, abd+ -> abcde, abe+ -> abcde, acd+ -> abcde, ace+ -> abcde, ade+ -> abcde
    bcd+ -> bcde, bce+ -> bcde, cde+ -> bcde

keys:
    ab, ac, ad

new non-trivial fds
    c -> be, d -> be, ab -> de, ac -> bde, ad -> bce
    bc -> de, bd -> e, cd -> be, ce -> bd, de -> b
    bcd -> e, bce -> d, cde -> b

i) BCNF violations:
    c -> be, d -> be
    bc -> de, bd -> e
    cd -> be, ce -> bd, de -> b
    bcd -> e, bce -> d, cde -> b

ii) 
take FD d -> e, decompose abcde into S(a,b,c,d) and T(d,e)
T is now in BCNF since all FDs that hold in T are not BCNF violations
S is not in BCNF, since c -> d is in violation of BCNF, so decompose into S1(a,b,c) and S2(c,d)
S2 is in BCNF
S1 is not in BCNF since c - > d is in violation of BCNF, so decompose into S1a(a,c) and S1b(b,c)
now all relations are in BCNF

iii) 3NF violations:
    c - > e, d -> e, bc -> e, bd -> e
    cd -> e, bcd -> e

iv)
we can decompose using d -> e to get S(a,b,c,d) and T(d,e)
T is in 3NF as it was in part (ii)
S is now also in 3NF because of the relaxed requirements of 3NF

3.6.4
suppose we have:
A   B   C
---------
1   2   3
2   2   4

if we split into S(A,B) and T(B,C) we get:

  S       T
A   B   B   C
-------------
1   2   2   3
2   2   2   4

after joining, we get:

A   B   C
---------
1   2   2
1   2   4
2   2   3
2   2   4

which is different from the original relation