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 feel the forthcoming accomplishments for a university database that is

used to repress vestige of wards lovenesss:

 

(a) The university represss vestige of each ward's spectry (SNAME), ward calculate

(SNUM), gregarious protection calculate (SSSN), open oration (SCADDR) and phone

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

(BDATE), sex (SEX), dispose (CLASS) (freshman, sophomore, ..., disequalize),

major individuality (MAJORDEPTCODE), less individuality (MINORDEPTCODE)

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

ward calculate feel choice appreciates for each ward.

 

(b) Each individuality is forcible by a spectry (DEPTNAME), individuality decree

(DEPTCODE), appointment calculate (DEPTOFFICE), appointment phone (DEPTPHONE), and

college (DEPTCOLLEGE). Both spectry and decree feel choice appreciates for each

department.

 

(c) Each progress has a progress spectry (CNAME), name (CDESC), decree calculate

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

individuality (CDEPT). The appreciate of decree calculate is choice for each progress.

 

(d) Each individuality has an schoolmistress (INSTUCTORNAME), semester (SEMESTER), year

(YEAR), progress (SECCOURSE), and individuality calculate (SECNUM). Individuality calculates

distinguish opposed individualitys of the identical progress that are taught during the identical

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

during each semester.

 

(e) A loveness refers to a ward (SSSN), refers to a feature individuality, and

grade (GRADE).

 

Design an mental database schema for this database impression. First parade all

the constructoritative dependencies that should continue inthorough the attributes. Then, design

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

attributes of each affinity. Note any unspecified accomplishments, and bring-about

appropriate assumptions to bring-about the mention thorough.

 

 

10.18 Prove or misadduce the forthcoming deduction administrations for constructoritative dependencies. A

testimony can be made either by a testimony topic or by using deduction administrations IR1 through IR3. A distestimony should be performed by demonstrating a affinity befallrence that satisfies the provisions and constructoritative dependencies in the left index badjust of the deduction administration but do not

satisfy the provisions or dependencies in the fair index border.

 

(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 forthcoming two sets of constructoritative dependencies F= {A ->C, AC ->D,

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

equivalent.

 

 

10.22 What update anomalies befall in the EMP_PROJ and EMP_DEPT affinitys of

Figure 14.3 and 14.4?

 

 

10.23 In what probable arrange is the LOTS affinity schema in Figure 10.11(a) after a while the

respect to the stringent interpretations of probable arrange that use simply the

first key into statement? Allure it be in the identical probable arrange if the open

definitions of probable arrange were used?

 

Answer:

 

If we simply use the first key into statement, the LOTS affinity schema in Figure 14.11

(a) allure be in 2NF since there are no peculiar dependencies on the first key .

However, it is not in 3NF, since there are the forthcoming two developed dependencies on

the first key:

PROPERTY_ID# ->COUNTY_NAME ->TAX_RATE, and

PROPERTY_ID# ->AREA ->PRICE.

Now, if we use all keys into statement and use the open restriction of 2NF and 3NF, the

LOTS affinity schema allure simply be in 1NF owing there is a peculiar dependency

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

violates 2NF.

 

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

 

 

10.25 Why do counterfeit tuples befall in the effect of confederacy the EMP_PROJ1 and

EMPLOCS affinitys of Figure 14.5 (effect paraden in Figure 14.6)?

 

 

10.26 Consider the exhaustive affinity 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? Analyze R into 2NF, then 3NF

relations.

 

 

10.27 Repeat collision 10.26 for the forthcoming opposed set of constructoritative dependencies

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

 

14.26, starting after a while the forthcoming affinity R:

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

The first-plane peculiar dependencies on the key (which outrage 2NF) are:

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

Hence, R is faded 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 peculiar dependencies insist in R1 and R3 owing {A} -> {I}. Hence, we remove

{I} into R5, so the forthcoming affinitys are the effect 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 stay for developed dependencies in each of the affinitys (which outrage 3NF).

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

and R32 as follows:

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

The terminal set of 3NF affinitys is {R1, R2, R31, R32, R4, R5}

 

10.28 Solution to come

 

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

C.AB

E.CD

B.DE

is AB a petitioner key?

is ABD a petitioner key?

 

 

10.30 Consider the affinity R, which has attributes that continue schedules of progresss and

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

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

NoOfStudents}. Suppose that the forthcoming constructoritative 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 determine which sets of attributes arrange keys of R. How would you

normalize this affinity?

 

Answer:

 

10.31 Consider the forthcoming affinitys for an adjust-processing impression database at ABC, Inc.

 

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

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

 

Assume that each part has a opposed remittance. The Total_price refers to one part, Odate is the date on which the adjust was placed, and the Total_totality is the totality of the adjust. If we use a probable annex on the affinitys Order-Item and Adjust in this database, what does the effecting affinity schema seem love? What allure be its key? Parade the FDs in this effecting affinity. Is it in 2NF? Is it in 3NF? Why or why not? (State any assumptions you bring-about.)

 

O# .Total_amount

It is not in 2NF, as attributes Odate, Cut#, and Total_totality are simply peculiarly

dependent on the first key, O#I#

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

 

 

10.32 Consider the forthcoming affinity:

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 first key. Additional dependencies are:

Date_sold ->Discount_amt

and

Salesman# ->commission%

Based on the ardent first key, is this affinity in 1NF, 2NF, or 3NF? Why or why not? How would you successively probableize it thoroughly?

 

 

10.33 Consider the forthcoming affinity for published books:

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

Author_affil referes  to the blaze of the constructor. Suppose the forthcoming dependencies insist:

Book_title -> Publisher, Book_type

Book_type -> Listprice

Author_spectry -> Author-affil

 

(a) What probable arrange is the affinity in? Explain your retort.

(b) Use probableization until you cannot analyze the affinitys raise. State the reasons after each dissection.

 

 

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

2NF as no attributes are FFD on the key. It is to-boot 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 peculiar 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 developed dependency of Listprice