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.