Chapter 10: functional dependencies and normalization for relational


CHAPTER 10: FUNCTIONAL DEPENDENCIES AND NORMALIZATION FOR RELATIONAL DATABASES

 

Answers to Selected Exercises

 

15.19 Suppose we bear the subjoined requirements for a university database that is

used to sustain footprint of novices duplicates:

 

(a) The university sustains footprint of each novice's indicate (SNAME), novice estimate

(SNUM), political bond estimate (SSSN), prevalent oration (SCADDR) and phone

(SCPHONE), enduring oration (SPADDR) and phone (SPPHONE), birthdate

(BDATE), sex (SEX), adjust (CLASS) (freshman, sophomore, ..., graduate),

major portion (MAJORDEPTCODE), inferior portion (MINORDEPTCODE)

(if any), and range program (PROG) (B.A., B.S., ..., Ph.D.). Both ssn and

novice estimate bear matchless appreciates for each novice.

 

(b) Each portion is descriptive by a indicate (DEPTNAME), portion principle

(DEPTCODE), service estimate (DEPTOFFICE), service phone (DEPTPHONE), and

college (DEPTCOLLEGE). Both indicate and principle bear matchless appreciates for each

department.

 

(c) Each mode has a mode indicate (CNAME), term (CDESC), principle estimate

(CNUM), estimate of semester hours (CREDIT), plane (LEVEL), and offering

portion (CDEPT). The appreciate of principle estimate is matchless for each mode.

 

(d) Each minority has an instructor (INSTUCTORNAME), semester (SEMESTER), year

(YEAR), mode (SECCOURSE), and minority estimate (SECNUM). Minority estimates

distinguish irrelative minoritys of the corresponding mode that are taught during the corresponding

semester/year; its appreciates are 1, 2, 3, ...; up to the estimate of minoritys taught

during each semester.

 

(e) A duplicate refers to a novice (SSSN), refers to a feature minority, and

grade (GRADE).

 

Design an intellectual database schema for this database collision. First semblance all

the inventoritative dependencies that should continue unmoulded the attributes. Then, design

narration schemas for the database that are each in 3NF or BCNF. Specify the key

attributes of each narration. Note any unspecified requirements, and establish

appropriate assumptions to establish the demonstration full.

 

 

10.18 Prove or rebut the subjoined conclusion governments for inventoritative dependencies. A

Nursing essay can be made either by a Nursing essay reasoning or by using conclusion governments IR1 through IR3. A disNursing essay should be executed by demonstrating a narration exemplification that satisfies the stipulations and inventoritative dependencies in the left artisan interest of the conclusion government but do not

satisfy the stipulations or dependencies in the proper artisan interest.

 

(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}

 

 

10.19 Consider the subjoined two sets of inventoritative dependencies F= {A ->C, AC ->D,

E ->AD, E ->H} and G = {A ->CD, E ->AH}. Obstruct whether or not they are

equivalent.

 

 

10.22 What upconclusion anomalies happen in the EMP_PROJ and EMP_DEPT narrations of

Figure 14.3 and 14.4?

 

 

10.23 In what ordinary construct is the LOTS narration schema in Figure 10.11(a) after a while the

respect to the obligatory interpretations of ordinary construct that accept singly the

important key into totality? Conciliate it be in the corresponding ordinary construct if the open

definitions of ordinary construct were used?

 

Answer:

 

If we singly accept the important key into totality, the LOTS narration schema in Figure 14.11

(a) conciliate be in 2NF past there are no biased dependencies on the important key .

However, it is not in 3NF, past there are the subjoined two projective dependencies on

the important key:

PROPERTY_ID# ->COUNTY_NAME ->TAX_RATE, and

PROPERTY_ID# ->AREA ->PRICE.

Now, if we accept all keys into totality and use the open limitation of 2NF and 3NF, the

LOTS narration schema conciliate singly be in 1NF consequently there is a biased dependency

COUNTY_NAME ->TAX_RATE on the minor key {COUNTY_NAME, LOT#}, which

violates 2NF.

 

10.24 Prove that any narration schema after a while two attributes is in BCNF.

 

 

10.25 Why do ungenuine tuples happen in the consequence of union the EMP_PROJ1 and

EMPLOCS narrations of Figure 14.5 (consequence semblancen in Figure 14.6)?

 

 

10.26 Consider the whole narration R = {A, B, C, D, E, F, G, H, I} and the set of

authoritative dependencies F = { {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} ->

{G, H}, {D} -> {I, J} }. What is the key for R? Individualize R into 2NF, then 3NF

relations.

 

 

10.27 Repeat training 10.26 for the subjoined irrelative set of inventoritative dependencies

G = { {A, B} -> {C}, {B, D} -> {E, F}, {A, D} -> {G, H}, {A} -> {I}, {H} -> {J} }.

 

14.26, starting after a while the subjoined narration R:

R = {A, B, D, C, E, F, G, H, I}

The first-plane biased dependencies on the key (which debauch 2NF) are:

{A, B} -> {C, I}, {B, D} -> {E, F}, {A, D}+ -> {G, H, I, J}

Hence, R is sunk 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 biased dependencies stop in R1 and R3 consequently {A} -> {I}. Hence, we remove

{I} into R5, so the subjoined narrations are the consequence of 2NF dissection:

R1 = {A, B, C}, R2 = {B, D, E, F}, R3 = {A, D, G, H, J}, R4 = {A, B, D}, R5 = {A, I}

Next, we obstruct for projective dependencies in each of the narrations (which debauch 3NF).

Only R3 has a projective dependency {A, D} -> {H} -> {J}, so it is sunk into R31

and R32 as follows:

R31 = {H, J}, R32 = {A, D, G, H}

The latest set of 3NF narrations is {R1, R2, R31, R32, R4, R5}

 

10.28 Solution to come

 

10.29 Ardent narration R(A,B,C,D,E) after a while dependencies

C.AB

E.CD

B.DE

is AB a claimant key?

is ABD a claimant key?

 

 

10.30 Consider the narration R, which has attributes that continue schedules of modes and

sections at a university; R = {CourseNo, SecNo, OfferingDept, CreditHours,

CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo,

NoOfStudents}. Suppose that the subjoined inventoritative dependencies continue 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 enumerate which sets of attributes construct keys of R. How would you

normalize this narration?

 

Answer:

 

10.31 Consider the subjoined narrations for an regulate-processing collision database at ABC, Inc.

 

ORDER (O#, Odate, Cust#, Total_amount)

ORDER-ITEM (O#, I#, Qty_ordered, Total_price, Discount%)

 

Assume that each individual has a irrelative discount. The Total_price refers to one individual, Oconclusion is the conclusion on which the regulate was placed, and the Total_total is the total of the regulate. If we use a original confederate on the narrations Order-Item and Regulate in this database, what does the consequenceing narration schema contemplate relish? What conciliate be its key? Semblance the FDs in this consequenceing narration. Is it in 2NF? Is it in 3NF? Why or why not? (State any assumptions you establish.)

 

O# .Total_amount

It is not in 2NF, as attributes Odate, Cut#, and Total_total are singly biasedly

dependent on the important key, O#I#

Nor is it in 3NF, as a 2NF is a requirement for 3NF.

 

 

10.32 Consider the subjoined narration:

CAR_SALE(Car#, Date_sold, Salesman#, Commision%, Discount_amt

Assume that a car may be sold by multiple salesmen and hereafter {CAR#, SALESMAN#} is the important key. Additional dependencies are:

Date_sold ->Discount_amt

and

Salesman# ->commission%

Based on the ardent important key, is this narration in 1NF, 2NF, or 3NF? Why or why not? How would you successively ordinaryize it fullly?

 

 

10.33 Consider the subjoined narration for published books:

BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)

Author_affil referes  to the blaze of the inventor. Suppose the subjoined dependencies stop:

Book_title -> Publisher, Book_type

Book_type -> Listprice

Author_indicate -> Author-affil

 

(a) What ordinary construct is the narration in? Explain your counterpart.

(b) Use ordinaryization until you cannot individualize the narrations further. State the reasons following each dissection.

 

 

(a)The key for this narration is Book_title,Authorname. This narration is in 1NF and not in

2NF as no attributes are FFD on the key. It is as-well not in 3NF.

 

(b) 2NF dissection:

Book0(Book_title, Authorname)

Book1(Book_title, Publisher, Book_type, Listprice)

Book2(Authorname, Author_affil)

This dissection eliminates the biased dependencies.

3NF dissection:

Book0(Book_title, Authorname)

Book1-1(Book_title, Publisher, Book_type)

Book1-2(Book_type, Listprice)

Book2(Authorname, Author_affil)

This dissection eliminates the projective dependency of Listprice