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 modifications for a university database that is

used to practise mark of wards relishnesss:

 

(a) The university practises mark of each ward's call (SNAME), ward calculate

(SNUM), political pledge calculate (SSSN), ordinary harangue (SCADDR) and phone

(SCPHONE), beaming harangue (SPADDR) and phone (SPPHONE), birthdate

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

major province (MAJORDEPTCODE), junior province (MINORDEPTCODE)

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

ward calculate bear uncommon treasures for each ward.

 

(b) Each province is vivid by a call (DEPTNAME), province command

(DEPTCODE), business-post calculate (DEPTOFFICE), business-post phone (DEPTPHONE), and

college (DEPTCOLLEGE). Both call and command bear uncommon treasures for each

department.

 

(c) Each arrange has a arrange call (CNAME), description (CDESC), command calculate

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

province (CDEPT). The treasure of command calculate is uncommon for each arrange.

 

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

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

distinguish incongruous individualitys of the similar arrange that are taught during the similar

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

during each semester.

 

(e) A relishness refers to a ward (SSSN), refers to a point individuality, and

grade (GRADE).

 

Design an abstruse database schema for this database impression. First profession all

the causeitative dependencies that should arrest unformed the attributes. Then, design

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

attributes of each kinsman. Note any unspecified modifications, and construct

appropriate assumptions to construct the demonstration exhaustive.

 

 

10.18 Prove or rebut the subjoined conclusion administrations for causeitative dependencies. A

evidence can be made either by a evidence controversy or by using conclusion administrations IR1 through IR3. A disevidence should be done by demonstrating a kinsman illustration that satisfies the stipulations and causeitative dependencies in the left workman laterality of the conclusion administration but do not

satisfy the stipulations or dependencies in the just workman laterality.

 

(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 causeitative dependencies F= {A ->C, AC ->D,

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

equivalent.

 

 

10.22 What upend anomalies arise in the EMP_PROJ and EMP_DEPT kinsmans of

Figure 14.3 and 14.4?

 

 

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

respect to the styptic interpretations of eventual construct that catch singly the

earliest key into representation? Achieve it be in the similar eventual construct if the public

definitions of eventual construct were used?

 

Answer:

 

If we singly catch the earliest key into representation, the LOTS kinsman schema in Figure 14.11

(a) achieve be in 2NF past there are no inequitable dependencies on the earliest key .

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

the earliest key:

PROPERTY_ID# ->COUNTY_NAME ->TAX_RATE, and

PROPERTY_ID# ->AREA ->PRICE.

Now, if we catch all keys into representation and use the public limitation of 2NF and 3NF, the

LOTS kinsman schema achieve singly be in 1NF accordingly there is a inequitable dependency

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

violates 2NF.

 

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

 

 

10.25 Why do bastard tuples arise in the termination of connection the EMP_PROJ1 and

EMPLOCS kinsmans of Figure 14.5 (termination professionn in Figure 14.6)?

 

 

10.26 Consider the exhaustive kinsman 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 use 10.26 for the subjoined incongruous set of causeitative 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 kinsman R:

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

The first-flatten inequitable 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 corrupt 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 inequitable dependencies depend in R1 and R3 accordingly {A} -> {I}. Hence, we remove

{I} into R5, so the subjoined kinsmans are the termination of 2NF analysis:

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

Next, we curb for phenomenal dependencies in each of the kinsmans (which outrage 3NF).

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

and R32 as follows:

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

The ultimate set of 3NF kinsmans is {R1, R2, R31, R32, R4, R5}

 

10.28 Solution to come

 

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

C.AB

E.CD

B.DE

is AB a canvasser key?

is ABD a canvasser key?

 

 

10.30 Consider the kinsman R, which has attributes that arrest schedules of arranges and

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

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

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

normalize this kinsman?

 

Answer:

 

10.31 Consider the subjoined kinsmans for an arrange-processing impression database at ABC, Inc.

 

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

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

 

Assume that each ace has a incongruous abatement. The Total_price refers to one ace, Oend is the end on which the arrange was placed, and the Total_total is the total of the arrange. If we use a eventual adadjoin on the kinsmans Order-Item and Arrange in this database, what does the terminationing kinsman schema face relish? What achieve be its key? Profession the FDs in this terminationing kinsman. Is it in 2NF? Is it in 3NF? Why or why not? (State any assumptions you construct.)

 

O# .Total_amount

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

dependent on the earliest key, O#I#

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

 

 

10.32 Consider the subjoined kinsman:

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

Date_sold ->Discount_amt

and

Salesman# ->commission%

Based on the ardent earliest key, is this kinsman in 1NF, 2NF, or 3NF? Why or why not? How would you successively eventualize it exhaustively?

 

 

10.33 Consider the subjoined kinsman for published books:

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

Author_affil referes  to the breeze of the cause. Suppose the subjoined dependencies depend:

Book_title -> Publisher, Book_type

Book_type -> Listprice

Author_call -> Author-affil

 

(a) What eventual construct is the kinsman in? Explain your counter-argument.

(b) Use eventualization until you cannot analyze the kinsmans prefer. State the reasons following each analysis.

 

 

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

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

 

(b) 2NF analysis:

Book0(Book_title, Authorname)

Book1(Book_title, Publisher, Book_type, Listprice)

Book2(Authorname, Author_affil)

This analysis eliminates the inequitable dependencies.

3NF analysis:

Book0(Book_title, Authorname)

Book1-1(Book_title, Publisher, Book_type)

Book1-2(Book_type, Listprice)

Book2(Authorname, Author_affil)

This analysis eliminates the phenomenal dependency of Listprice