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