Avatar of 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 ?
DatabasesOracle DatabaseMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Christoffer Swanström

8/22/2022 - Mon
Christoffer Swanström

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 ?
Christoffer Swanström

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.
Your help has saved me hundreds of hours of internet surfing.

If I know the COUNTY_NAME that can infer that area size is 1 or less ??? I think like that
Christoffer Swanström

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question