CHAPTER 10: FUNCTIONAL DEPENDENCIES
AND NORMALIZATION FOR RELATIONAL DATABASES
Answers to Selected Exercises
10.17
Suppose we have the following requirements for a university database that is
used to keep track of students
transcripts:
(a) The university keeps track of
each student's name (SNAME), student number
(SNUM), social security number
(SSSN), current address (SCADDR) and phone
(SCPHONE), permanent address
(SPADDR) and phone (SPPHONE), birthdate
(BDATE), sex (SEX), class (CLASS)
(freshman, sophomore, ..., graduate),
major department (MAJORDEPTCODE),
minor department (MINORDEPTCODE)
(if any), and degree program (PROG)
(B.A., B.S., ..., Ph.D.). Both ssn and
student number have unique values
for each student.
(b) Each department is described by
a name (DEPTNAME), department code
(DEPTCODE), office number
(DEPTOFFICE), office phone (DEPTPHONE), and
college (DEPTCOLLEGE). Both name and
code have unique values for each
department.
(c) Each course has a course name
(CNAME), description (CDESC), code number
(CNUM), number of semester hours
(CREDIT), level (LEVEL), and offering
department (CDEPT). The value of
code number is unique for each course.
(d) Each section has an instructor
(INSTUCTORNAME), semester (SEMESTER), year
(YEAR), course (SECCOURSE), and
section number (SECNUM). Section numbers
distinguish different sections of
the same course that are taught during the same
semester/year; its values are 1, 2,
3, ...; up to the number of sections taught
during each semester.
(e) A transcript refers to a student
(SSSN), refers to a particular section, and
grade (GRADE).
Design an relational database schema
for this database application. First show all
the functional dependencies that
should hold among the attributes. Then, design
relation schemas for the database
that are each in 3NF or BCNF. Specify the key
attributes of each relation. Note
any unspecified requirements, and make
appropriate assumptions to make the
specification complete.
Answer:
From the above description, we can
presume that the following functional dependencies
hold on the attributes:
FD1: {SSSN} -> {SNAME, SNUM,
SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS,
MAJOR,
MINOR, PROG}
FD2: {SNUM} -> {SNAME, SSSN,
SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS,
MAJOR,
MINOR, PROG}
FD3: {DEPTNAME} -> {DEPTCODE,
DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE}
FD4: {DEPTCODE} -> {DEPTNAME,
DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE}
FD5: {CNUM} -> {CNAME, CDESC,
CREDIT, LEVEL, CDEPT}
FD6: {SECCOURSE, SEMESTER, YEAR,
SECNUM} -> {INSTRUCTORNAME}
FD7: {SECCOURSE, SEMESTER, YEAR,
SECNUM, SSSN} -> {GRADE}
These are the basic FDs that we can
define from the given requirements; using inference
rules IR1 to IR3, we can deduce many
others. FD1 and FD2 refer to student attributes;
we can define a relation STUDENT and
choose either SSSN or SNUM as its primary key.
Similarly, FD3 and FD4 refer to
department attributes, with either DEPTNAME or
DEPTCODE as primary key. FD5 defines
COURSE attributes, and FD6 SECTION attributes.
Finally, FD7 defines GRADES
attributes. We can create one relation for each of STUDENT,
DEPARTMENT, COURSE, SECTION, and
GRADES as shown below, where the primary keys are underlined. The COURSE,
SECTION, and GRADES relations are in 3NF and BCNF if no other dependencies
exist. The STUDENT and DEPARTMENT relations are in 3NF and BCNF according to
the general definition given in Sections 14.4 and 14.5, but not according to the
definitions of Section 14.3 since both relations have secondary keys.
The foreign keys will be as follows:
STUDENT.MAJOR
-> DEPARTMENT.DEPTCODE
STUDENT.MINOR
-> DEPARTMENT.DEPTCODE
COURSE.CDEPT ->
DEPARTMENT.DEPTCODE
SECTION.SECCOURSE -> COURSE.CNUM
GRADES.(SECCOURSE, SEMESTER, YEAR,
SECNUM) ->
SECTION.(SECCOURSE, SEMESTER, YEAR,
SECNUM)
GRADES.SNUM
-> STUDENT.SNUM
Note: We arbitrarily chose SNUM over
SSSN for primary key of STUDENT, and
DEPTCODE over DEPTNAME for primary
key of DEPARTMENT.
10.18
Prove or disprove the following inference rules for functional dependencies. A
proof can be made either by a proof
argument or by using inference rules IR1 through IR3. A disproof should be done
by demonstrating a relation instance that satisfies the conditions and functional
dependencies in the left hand side of the inference rule but do not
satisfy the conditions or
dependencies in the right hand side.
(a) {W ->Y, X ->Z} |= {WX
->Y }
(b) {X ->Y} and Z subset-of Y |=
{ X ->Z }
(c) { X ->Y, X ->W, WY ->Z}
|= {X ->Z}
(d) {XY ->Z, Y ->W} |= {XW
->Z}
(e) {X ->Z, Y ->Z} |= {X
->Y}
(f) {X ->Y, XY ->Z} |= {X
->Z}
Answer:
(a) Proof:
(1) W ->Y (given)
(2) X ->Z (given)
(3) WX ->YX (using IR2
(augmentation) to augment (1) with X)
(4) YX ->Y (using IR1
(reflexivity), knowing that Y subset-of YX)
(5) WX ->Y (using IR3
(transitivity) on (3) and (4))
(b) Proof:
(1) X ->Y (given)
(2) Y ->Z (using IR1
(reflexivity), given that Z subset-of Y)
(3) X ->Z (using IR3
(transitivity) on (1) and (2))
(c) Proof:
(1) X ->Y (given)
(2) X ->W (given)
(3) WY ->Z (given)
(4) X ->XY (using IR2
(augmentation) to augment (1) with X)
(5) XY ->WY (using IR2 (augmentation)
to augment (2) with Y)
(6) X ->WY (using IR3
(transitivity) on (4) and (5))
(7) X ->Z (using IR3
(transitivity) on (6) and (3))
(d) Disproof: X Y Z W
t 1 =x 1 y 1 z 1 w 1
t 2 =x 1 y 2 z 2 w 1
The above two tuples satisfy XY
->Z and Y ->W but do not satisfy XW ->Z
(e) Disproof: X Y Z
t 1 =x 1 y 1 z 1
t 2 =x 1 y 2 z 1
The above two tuples satisfy X
->Z and Y ->Z but do not satisfy X ->Y
(f) Proof:
(1) X ->Y (given)
(2) XY ->Z (given)
(3) X ->XY (using IR2
(augmentation) to augment (1) with X)
(4) X ->Z (using IR3
(transitivity) on (3) and (2))
10.19 Consider the following two
sets of functional dependencies F= {A ->C, AC ->D,
E ->AD, E ->H} and G = {A
->CD, E ->AH}. Check whether or not they are
equivalent.
Answer:
To show equivalence, we prove that G
is covered by F and F is covered by G.
Proof that G is covered by F:
{A} + = {A, C, D} (with respect to
F), which covers A ->CD in G
{E} + = {E, A, D, H, C} (with
respect to F), which covers E ->AH in G
Proof that F is covered by G:
{A} + = {A, C, D} (with respect to
G), which covers A ->C in F
{A, C} + = {A, C, D} (with respect
to G), which covers AC ->D in F
{E} + = {E, A, H, C, D} (with
respect to G), which covers E ->AD and E ->H in F
10.20
Consider the relation schema EMP_DEPT in Figure 14.3(a) and the following set
G of functional dependencies on
EMP_DEPT: G = {SSN ->{ENAME, BDATE,
ADDRESS, DNUMBER} , DNUMBER
->{DNAME, DMGRSSN} }. Calculate the
closures {SSN} + and {DNUMBER} +
with respect to G.
Answer:
{SSN} + ={SSN, ENAME, BDATE,
ADDRESS, DNUMBER, DNAME, DMGRSSN}
{DNUMBER} + ={DNUMBER, DNAME,
DMGRSSN}
10.21 Is the set of functional
dependencies G in Exercise 14.20 minimal? If not, try to
find an minimal set of functional
dependencies that is equivalent to G. Prove that
your set is equivalent to G.
Answer:
The set G of functional dependencies
in Exercise 14.20 is not minimal, because it
violates rule 1 of minimality (every
FD has a single attribute for its right hand side).
The set F is an equivalent minimal
set: F= {SSN ->{ENAME},SSN ->{BDATE}, SSN->
{ADDRESS}, SSN ->{DNUMBER} ,
DNUMBER ->{DNAME}, DNUMBER->{DMGRSSN}}
To show equivalence, we prove that G
is covered by F and F is covered by G.
Proof that G is covered by F:
{SSN} + = {SSN, ENAME, BDATE,
ADDRESS, DNUMBER, DNAME, DMGRSSN} (with
respect to F), which covers SSN
->{ENAME, BDATE, ADDRESS, DNUMBER} in G
{DNUMBER} + ={DNUMBER, DNAME,
DMGRSSN} (with respect to F), which covers
DNUMBER ->{DNAME, DMGRSSN} in G
Proof that F is covered by G:
{SSN} + = {SSN, ENAME, BDATE,
ADDRESS, DNUMBER, DNAME, DMGRSSN} (with
respect to G), which covers SSN
->{ENAME}, SSN ->{BDATE}, SSN ->{ADDRESS}, and
SSN ->{DNUMBER} in F
{DNUMBER} + ={DNUMBER, DNAME,
DMGRSSN} (with respect to G), which covers
DNUMBER ->{DNAME} and
DNUMBER->{DMGRSSN} in F
10.22
What update anomalies occur in the EMP_PROJ and EMP_DEPT relations of
Figure 14.3 and 14.4?
Answer:
In EMP_PROJ, the partial
dependencies {SSN}->{ENAME} and {PNUMBER}->{PNAME,
PLOCATION} can cause anomalies. For
example, if a PROJECT temporarily has no
EMPLOYEEs working on it, its
information (PNAME, PNUMBER, PLOCATION) will not be
represented in the database when the
last EMPLOYEE working on it is removed (deletion
anomaly). A new PROJECT cannot be
added unless at least one EMPLOYEE is assigned to
work on it (insertion anomaly).
Inserting a new tuple relating an existing EMPLOYEE to
an existing PROJECT requires
checking both partial dependencies; for example, if a
different value is entered for
PLOCATION than those values in other tuples with the same
value for PNUMBER, we get an update
anomaly. Similar comments apply to EMPLOYEE
information. The reason is that
EMP_PROJ represents the relationship between
EMPLOYEEs and PROJECTs, and at the
same time represents information concerning
EMPLOYEE and PROJECT entities.
In EMP_DEPT, the transitive
dependency {SSN}->{DNUMBER}->{DNAME, DMGRSSN}
can cause anomalies. For example, if
a DEPARTMENT temporarily has no EMPLOYEEs
working for it, its information
(DNAME, DNUMBER, DMGRSSN) will not be represented
in the database when the last
EMPLOYEE working on it is removed (deletion anomaly). A
new DEPARTMENT cannot be added
unless at least one EMPLOYEE is assigned to work on it
(insertion anomaly). Inserting a new
tuple relating a new EMPLOYEE to an existing
DEPARTMENT requires checking the
transitive dependencies; for example, if a different
value is entered for DMGRSSN than
those values in other tuples with the same value for
DNUMBER, we get an update anomaly.
The reason is that EMP_DEPT represents the
relationship between EMPLOYEEs and
DEPARTMENTs, and at the same time represents
information concerning EMPLOYEE and
DEPARTMENT entities.
10.23 In what normal form is the
LOTS relation schema in Figure 10.11(a) with the
respect to the restrictive
interpretations of normal form that take only the
primary key into account? Will it be
in the same normal form if the general
definitions of normal form were
used?
Answer:
If we only take the primary key into
account, the LOTS relation schema in Figure 14.11
(a) will be in 2NF since there are
no partial dependencies on the primary key .
However, it is not in 3NF, since
there are the following two transitive dependencies on
the primary key:
PROPERTY_ID# ->COUNTY_NAME
->TAX_RATE, and
PROPERTY_ID# ->AREA ->PRICE.
Now, if we take all keys into account
and use the general definition of 2NF and 3NF, the
LOTS relation schema will only be in
1NF because there is a partial dependency
COUNTY_NAME ->TAX_RATE on the
secondary key {COUNTY_NAME, LOT#}, which
violates 2NF.
10.24
Prove that any relation schema with two attributes is in BCNF.
Answer:
Consider a relation schema R={A, B}
with two attributes. The only possible (non-trivial)
FDs are {A} ->{B} and {B}
->{A}. There are four possible cases:
(i) No FD holds in R. In this case,
the key is {A, B} and the relation satisfies BCNF.
(ii) Only {A} ->{B} holds. In
this case, the key is {A} and the relation satisfies BCNF.
(iii) Only {B} ->{A} holds. In
this case, the key is {B} and the relation satisfies BCNF.
(iv) Both {A} ->{B} and {B}
->{A} hold. In this case, there are two keys {A} and {B} and
the relation satisfies BCNF.
Hence, any relation with two
attributes is in BCNF.
10.25 Why do spurious tuples occur
in the result of joining the EMP_PROJ1 and
EMPLOCS relations of Figure 14.5
(result shown in Figure 14.6)?
Answer:
In EMP_LOCS, a tuple (e, l)
signifies that employee with name e works on some project
located in location l. In EMP_PROJ1,
a tuple (s, p, h, pn, l) signifies that employee with
social security number s works on
project p that is located at location l. When we join
EMP_LOCS with EMP_PROJ1, a tuple (e,
l) in EMP_LOCS can be joined with a tuple (s,
p, h, pn, l) in EMP_PROJ1 where e is
the name of some employee and s is the social
security number of a different
employee, resulting in spurious tuples. The lossless join
property (see Chapter 13) can
determine whether or not spurious tuples may result
based on the FDs in the two
relations being joined.
10.26
Consider the universal relation R = {A, B, C, D, E, F, G, H, I} and the set of
functional dependencies F = { {A, B}
-> {C}, {A} -> {D, E}, {B} -> {F}, {F} ->
{G, H}, {D} -> {I, J} }. What is
the key for R? Decompose R into 2NF, then 3NF
relations.
Answer:
A minimal set of attributes whose
closure includes all the attributes in R is a key. (One
can also apply algorithm 15.4a (see
chapter 15 in the textbook)). Since the closure of
{A, B}, {A, B}+ = R, one key of R is
{A, B} (in this case, it is the only key).
To normalize R intuitively into 2NF
then 3NF, we take the following steps (alternatively,
we can apply the algorithms
discussed in Chapter 15):
First, identify partial dependencies
that violate 2NF. These are attributes that are
functionally dependent on either
parts of the key, {A} or {B}, alone. We can calculate the
closures {A}+ and {B}+ to determine
partially dependent attributes:
{A}+ = {A, D, E, I, J}. Hence {A}
-> {D, E, I, J} ({A} -> {A} is a trivial dependency)
{B}+ = {B, F, G, H}, hence {A} ->
{F, G, H} ({B} -> {B} is a trivial dependency)
To normalize into 2NF, we remove the
attributes that are functionally dependent on part
of the key (A or B) from R and place
them in separate relations R1 and R2, along with
the part of the key they depend on
(A or B), which are copied into each of these relations
but also remains in the original
relation, which we call R3 below:
R1 = {A,
D, E, I, J}, R2 = {B, F, G, H}, R3 = {A, B, C}
The new keys for R1, R2, R3 are
underlined. Next, we look for transitive dependencies
in R1, R2, R3. The relation R1 has
the transitive dependency {A} -> {D} -> {I, J}, so we
remove the transitively dependent
attributes {I, J} from R1 into a relation R11 and copy
the attribute D they are dependent
on into R11. The remaining attributes are kept in a
relation R12. Hence, R1 is
decomposed into R11 and R12 as follows:
R11 = {D,
I, J}, R12 = {A, D, E}
The relation R2 is similarly
decomposed into R21 and R22 based on the transitive
dependency {B} -> {F} -> {G,
H}:
R2 = {F,
G, H}, R2 = {B, F}
The final
set of relations in 3NF are {R11, R12, R21, R22, R3}
10.27 Repeat exercise 10.26 for the
following different set of functional dependencies
G = { {A, B} -> {C}, {B, D} ->
{E, F}, {A, D} -> {G, H}, {A} -> {I}, {H} -> {J} }.
Answer:
To help in solving this problem
systematically, we can first find the closures of all
single attributes to see if any is a
key on its own as follows:
{A}+
-> {A, I}, {B}+ -> {B}, {C}+ -> {C}, {D}+ -> {D}, {E}+ -> {E},
{F}+ -> {F},
{G}+ -> {G}, {H}+ -> {H, J},
{I}+ -> {I}, {J}+ -> {J}
Since none of the single attributes
is a key, we next calculate the closures of pairs of
attributes that are possible keys:
{A, B}+
-> {A, B, C, I}, {B, D}+ -> {B, D, E, F}, {A, D}+ -> {A, D, G, H, I,
J}
None of these pairs are keys either
since none of the closures includes all attributes. But
the union of the three closures
includes all the attributes:
{A, B,
D}+ -> {A, B, C, D, E, F, G, H, I}
Hence, {A, B, D} is a key. (Note:
Algorithm 15.4a (see chapter 15 in the textbook) can
be used to determine a key).
Based on the above analysis, we
decompose as follows, in a similar manner to problem
14.26, starting with the following
relation R:
R = {A,
B, D, C, E, F, G, H, I}
The first-level partial dependencies
on the key (which violate 2NF) are:
{A, B}
-> {C, I}, {B, D} -> {E, F}, {A, D}+ -> {G, H, I, J}
Hence, R is decomposed into R1, R2,
R3, R4 (keys are underlined):
R1 = {A,
B, C, I}, R2 = {B, D, E, F}, R3 = {A, D, G, H, I, J}, R4 = {A, B, D}
Additional partial dependencies
exist in R1 and R3 because {A} -> {I}. Hence, we remove
{I} into R5, so the following
relations are the result of 2NF decomposition:
R1 = {A,
B, C}, R2 = {B, D, E, F}, R3 = {A, D, G, H, J}, R4 = {A, B, D}, R5 = {A, I}
Next, we check for transitive
dependencies in each of the relations (which violate 3NF).
Only R3 has a transitive dependency
{A, D} -> {H} -> {J}, so it is decomposed into R31
and R32 as follows:
R31 = {H,
J}, R32 = {A, D, G, H}
The final
set of 3NF relations is {R1, R2, R31, R32, R4, R5}
10.28 Solution to come
10.29 Given relation R(A,B,C,D,E)
with dependencies
AB C
CD E
DE B
is AB a candidate key?
is ABD a candidate key?
Answers:
No, AB+ =
{A,B,C},a proper subset of {A,B,C,D,E}
Yes, ABD+ = {A,B,C,D,E}
10.30 Consider the relation R, which
has attributes that hold schedules of courses and
sections at a university; R =
{CourseNo, SecNo, OfferingDept, CreditHours,
CourseLevel, InstructorSSN,
Semester, Year, Days_Hours, RoomNo,
NoOfStudents}. Suppose that the
following functional dependencies hold on R:
{CourseNo} -> {OfferingDept,
CreditHours, CourseLevel}
{CourseNo, SecNo, Semester, Year}
->
{Days_Hours, RoomNo, NoOfStudents,
InstructorSSN}
{RoomNo, Days_Hours, Semester, Year}
-> {InstructorSSN, CourseNo, SecNo}
Try to determine which sets of
attributes form keys of R. How would you
normalize this relation?
Answer:
Let us use the following shorthand
notation:
C = CourseNo, SN = SecNo, OD =
OfferingDept, CH = CreditHours, CL = CourseLevel,
I = InstructorSSN, S = Semester, Y =
Year, D = Days_Hours, RM = RoomNo,
NS = NoOfStudents
Hence, R = {C, SN, OD, CH, CL, I, S,
Y, D, RM, NS}, and the following functional
dependencies hold:
{C} -> {OD, CH, CL}
{C, SN, S, Y} -> {D, RM, NS, I}
{RM, D, S, Y} -> {I, C, SN}
First, we can calculate the closures
for each left hand side of a functional dependency,
since these sets of attributes are
the candidates to be keys:
(1) {C}+ = {C, OD, CH, CL}
(2) Since {C, SN, S, Y} -> {D,
RM, NS, I}, and {C}+ = {C, OD, CH, CL}, we get:
{C, SN, S, Y}+ = {C, SN, S, Y, D,
RM, NS, I, OD, CH, CL} = R
(3) Since {RM, D, S, Y} -> {I, C,
SN}, we know that {RM, D, S, Y}+ contains {RM, D, S,
Y, I, C, SN}. But {C}+ contains {OD,
CH, CL} so these are also contained in {RM, D, S,
Y}+ since C is already there.
Finally, since {C, SN, S, Y} are now all in {RM, D, S, Y}+
and {C, SN, S, Y}+ contains {NS}
(from (2) above), we get:
{RM, D, S, Y}+ = {RM, D, S, Y, I, C,
SN, OD, CH, CL, NS} = R
Hence, both K1 = {C, SN, S, Y} and
applying the general definition of
2NF, we find that the functional dependency {C} ->
{OD, CH, CL} is a partial dependency
for K1 (since C is included in K1). Hence, R is
normalized into R1 and R2 as
follows:
R1 = {C,
OD, CH, CL}
R2 = {RM,
D, S, Y, I, C, SN, NS} with candidate keys K1 and
Since neither R1 nor R2 have
transitive dependencies on either of the candidate keys, R1
and R2 are in 3NF also. They also
both satisfy the definition of BCNF.
10.31 Consider the following
relations for an order-processing application database at ABC, Inc.
ORDER (O#, Odate, Cust#,
Total_amount)
ORDER-ITEM (O#, I#,
Qty_ordered, Total_price, Discount%)
Assume that each item has a
different discount. The Total_price refers to one item, Odate is the date on
which the order was placed, and the Total_amount is the amount of the order. If
we apply a natural join on the relations Order-Item and Order in this database,
what does the resulting relation schema look like? What will be its key? Show
the FDs in this resulting relation. Is it in 2NF? Is it in 3NF? Why or why not?
(State any assumptions you make.)
Answer:
Given relations
Order(O#, Odate, Cust#, Total_amt)
Order_Item(O#, I#, Qty_ordered, Total_price,
Discount%),
the schema of Order * Order_Item looks like
T1(O#,I#,Odate, Cust#,
Total_amount, Qty_ordered, Total_price, Discount%)
and its key is O#,I#.
It has functional dependencies
O#I# . Qty_ordered
O#I# .Total_price
O#I# .Discount%
O# . Odate
O# .Cust#
O# .Total_amount
It is not in 2NF, as attributes Odate, Cut#, and
Total_amount are only partially
dependent on the primary key, O#I#
Nor is it in 3NF, as a 2NF is a requirement for 3NF.
10.32 Consider the following
relation:
CAR_SALE(Car#, Date_sold, Salesman#,
Commision%, Discount_amt
Assume that a car may be sold by
multiple salesmen and hence {CAR#, SALESMAN#} is the primary key. Additional
dependencies are:
Date_sold ->Discount_amt
and
Salesman# ->commission%
Based on the given primary key, is
this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively
normalize it completely?
Answer:
Given the
relation schema
Car_Sale(Car#, Salesman#, Date_sold,
Commission%, Discount_amt)
with the functional dependencies
Date_sold
Discount_amt
Salesman#
Commission%
Car# Date_sold
This relation satisfies 1NF but not
2NF (Car# Date_sold and Car#
Discount_amt
so these two attributes are not FFD
on the primary key) and not 3NF.
To normalize,
2NF:
Car_Sale1(Car#, Date_sold,
Discount_amt)
Car_Sale2(Car#, Salesman#)
Car_Sale3(Salesman#,Commission%)
3NF:
Car_Sale1-1(Car#, Date_sold)
Car_Sale1-2(Date_sold, Discount_amt)
Car_Sale2(Car#, Salesman#)
Car_Sale3(Salesman#,Commission%)
10.33 Consider the following
relation for published books:
BOOK (Book_title, Authorname,
Book_type, Listprice, Author_affil, Publisher)
Author_affil referes to the affiliation of the author. Suppose the
following dependencies exist:
Book_title -> Publisher,
Book_type
Book_type -> Listprice
Author_name -> Author-affil
(a) What normal form is the relation
in? Explain your answer.
(b) Apply normalization until you
cannot decompose the relations further. State the reasons behind each
decomposition.
Answer:
Given the
relation
Book(Book_title, Authorname,
Book_type, Listprice, Author_affil, Publisher)
and the FDs
Book_title
Publisher, Book_type
Book_type Listprice
Authorname Author_affil
(a)The key for this relation is
Book_title,Authorname. This relation is in 1NF and not in
2NF as no attributes are FFD on the
key. It is also not in 3NF.
(b) 2NF decomposition:
Book0(Book_title, Authorname)
Book1(Book_title, Publisher,
Book_type, Listprice)
Book2(Authorname, Author_affil)
This decomposition eliminates the
partial dependencies.
3NF decomposition:
Book0(Book_title, Authorname)
Book1-1(Book_title, Publisher, Book_type)
Book1-2(Book_type, Listprice)
Book2(Authorname, Author_affil)
This decomposition eliminates the
transitive dependency of Listprice