Link to home
Start Free TrialLog in
Avatar of acad2012
acad2012

asked on

Question in Functional depondences in the Relational Databases

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 ?
Figure-10.11.tif
Avatar of Christoffer Swanström
Christoffer Swanström
Flag of Switzerland image

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.
Avatar of acad2012
acad2012

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Christoffer Swanström
Christoffer Swanström
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial