Question in Functional depondences in the Relational Databases

acad2012 used Ask the Experts™
Bovce-Codd normal form (BCNF) was proposed as a simpler form of 3NF, but it was found
to be stricter than 3NF. That is, every relation in BCNF is also in 3NF; however, a relation
in 3NF is not necessarily in BCNF. Intuitively, we can see the need for a stronger normal
form than 3NF by going back to the LOTS relation schema of Figure 1O.11a with its four
functional dependencies FD1 through FD4. Suppose that we have thousands of lots in the
relation but the lots are from only two counties: Dekalb and Fulton. Suppose also that lot
sizes in Dekalb County are only 0.5, 0.6, 0.7, 0.8, 0.9, and 1.0 acres, whereas lot sizes in
Fulton County are restricted to 1.1, 1.2, ... , 1.9, and 2.0 acres. In such a situation we
would have the additional functional dependency FD5: AREA --> COUNTY_NAME. If we add this to the other dependencies, the relation schema LOTs1A still is in 3NF because COUNTY_NAME is a prime attribute.

The area of a lot that determines the county, as specified by FD5, can be represented
by 16 tuples in a separate relation R(AREA, COUNTY_NAME), since there are only 16 possible AREA values. This representation reduces the redundancy of repeating the same information in the thousands of LOTs1A tuples. BCNF is a stronger normal form that would disallow LOTs1A and suggest the need for decomposing it.

My Question why the FD5 is AREA --> COUNTY_NAME not COUNTY_NAME --> AREA ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The AREA is not defined by COUNTY_NAME. In your example, only the upper and lower limit of AREA would be defined by COUNTY_NAME. To put it another way: if you know the COUNTY_NAME, you can't infer AREA.


If you know the COUNTY_NAME, you can't infer AREA why ?
Because for each COUNTY_NAME there are several different AREAs.

E.g. in Dekalb County you wrote that the AREA can be 0.5, 0.6, 0.7, 0.8, 0.9, or1.0 --> if you know that the PLOT is in Dekalb County, you can't tell what the AREA is because it could be any of the afore mentioned sizes.


If I know the COUNTY_NAME that can infer that area size is 1 or less ??? I think like that
Yes, but that does not mean that you can infer the exact area. If you have an additional attribute called area range, then it would be different. You could indeed infer that the area range for e.g. Dekalb county is 0 ... 1.0 and you could say that there is a functional dependency between county and area range.

But as long as we are talking about an exact number which can have many different values for any given county, then you don't have a functional dependency between the county and area.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial