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 grade record refers to a
student (Ssn), 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.20
Consider the relation schema EMP_DEPT in Figure 10.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.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.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

10.36
Consider the following relation:

R
(__Doctor#__, __Patient#__, __Date__, Diagnosis, Treat_code, Charge)

In this relation, a tuple describes
a visit of a patient to a doctor along with a treatment code and daily charge. Assume that diagnosis is determined
(uniquely) for each patient by a doctor.
Assume that each treatment code has a fixed charge (regardless of
patient). Is this relation in 2NF? Justify your answer and decompose if
necessary. Then argue whether further
normalization to 3NF is necessary, and if so, perform it.

Answer:

From the question’s text, we can
infer the following functional dependencies:

{Doctor#, Patient#, Date}®{Diagnosis,
Treat_code, Charge}

{Treat_code}®{Charge}

Because there are no partial
dependencies, the given relation is in 2NF already. This however is not 3NF because the Charge is
a nonkey attribute that is determined by another nonkey attribute, Treat_code.
We must decompose further:

R
(__Doctor#__, __Patient#__, __Date__, Diagnosis, Treat_code)

R1
(__Treat_code__, Charge)

We could further infer that the treatment
for a given diagnosis is functionally dependant, but we should be sure to allow
the doctor to have some flexibility when prescribing cures.