1

I'll try and keep it as quick and concise as I can: I'm currently a novice developer who's super eager to learn Database Normalization and Optimization so I can up my skill in Full-Stack development, so bare with me please. I'm trying to get good at spotting the different kinds of functional dependencies so I can get better at making optimal/appropriate relational models/schemas, and I'm currently doing an assignment on 3NF and BCNF decomposition (possibly NF4?) that I'm struggling with.

The Assignment has 2 parts and I'm fairly certain that I've properly completed the first Part 1, but I'm completely confused on the Part 2.

Here is the relation we are normalizing called LOTS: Prompt for Relation called LOTS Diagram of LOTS relation

Part One was to break the Relation down into BCNF form if possible, and here is my work for that: My Decomposed Relations of original LOTS in BCNF

The current R1 relation is the LOTS relation for Part 2.

Here is the prompt for Part2: PART 2

It seems to me that, since the AREA attribute has unique sets of values that can determine the COUNTY_NAME that the new introduces functional dependency is AREA -> COUNTY_NAME, which would leave the LOTS relation in 3NF. Thank you for anyone whiling to help a new comer learn. Cheers!

3
  • All your answers are correct.
    – Renzo
    Commented Mar 24, 2020 at 13:20
  • If you don't show the algorithm & your steps of your work, we can't tell you where you went wrong or right. Also, you expect us to redo your work, moreover somehow with an algorithm you didn't give & making the same choices when there are options. Also your learning resource sounds dubious (like many textbooks, sadly). Eg: there are more FDs than those holding in each schema--all the ones implied by Armstrong's axioms; "arising from" is hopelessly vague; to determine a NF we need a cover and the set of all attributes; etc. PS Please use text, not images, for what can be expressed in text.
    – philipxy
    Commented Mar 31, 2020 at 4:07
  • Before posting, look at the formatted version of your post below the edit box. Your links are not showing. Click on 'edit' to edit your post. Use edit functions to insert links & images. Also give a legend with an image. But use text, not images/links, for what can be given in text. Here all content linked should instead be text, not links/images.
    – philipxy
    Commented May 3, 2020 at 2:57

1 Answer 1

1

You solved your assignment, so not much more to add. However, because you mentioned that you are

"super eager to learn database normalization and optimization so I can up my skill in full-stack development,"

and because I am mostly stuck indoors these days -- I will demonstrate a bit different method to achieve the goal. This method will serve you well for your personal and business projects, not so much for db-related exams and textbook assignments.

So here is the task, we have relvar (table) with attributes, and some mythical creature called (probably) database designer has provided us with functional dependencies.

lots { PROPERTY_ID
     , COUNTY_NAME
     , LOT_NO
     , AREA
     , PRICE
     , TAX_RATE
     }

COUNTY_NAME, LOT_NO -> PROPERTY_ID
PROPERTY_ID -> COUNTY_NAME, LOT_NO
PROPERTY_ID -> COUNTY_NAME, LOT_NO, AREA, PRICE, TAX_RATE
COUNTY_NAME -> TAX_RATE
AREA -> PRICE

The idea now is to verbalize this and write down (simple) predicates and associated constraints so that the problem can be reasoned about in a natural language using logic.

A simple predicate can not be decomposed without losing information, its matching relvar is in 6NF.


[p 1] County COUNTY_NAME exists.

(c 1.1) County is identified by COUNTY_NAME.

county {COUNTY_NAME}  -- p 1
   KEY {COUNTY_NAME}  -- c 1.1

[p 2] Lot number LOT_NO in county COUNTY_NAME exists.

(c 2.1) Lot is identified by COUNTY_NAME, LOT_NO pair.

county_lot {COUNTY_NAME, LOT_NO}  -- p 2
       KEY {COUNTY_NAME, LOT_NO}  -- c 2.1

[p 3] Lot number LOT_NO in county COUNTY_NAME is assigned property number PROPERTY_ID.

(c 3.1) For each lot and county pair, that lot lot and county pair is assigned exactly one property number.

(c 3.2) For each property number, that property number is assigned to exactly one lot and county pair.

property {COUNTY_NAME, LOT_NO, PROPERTY_ID} -- p 3
     KEY {COUNTY_NAME, LOT_NO}              -- c 3.1
     KEY {PROPERTY_ID}                      -- c 3.2 

-- this one can be decomposed to
-- {PROPERTY_ID, COUNTY_NAME} {PROPERTY_ID, LOT_NO}
-- but leaving it as is to simplify.

[p 4] County COUNTY_NAME has tax rate of TAX_RATE.

(c 4.1) For each county, that county has exactly one tax rate; for each tax rate, it is possible that more than one county has that tax rate.

county_tax {COUNTY_NAME, TAX_RATE} -- p 4
       KEY {COUNTY_NAME}           -- c 4.1  

[p 5] Lot area of AREA is priced at PRICE.

(c 5.1) For each lot area, that lot area is priced at exactly one price; for each price, more than one lot area may be priced at that price.

area_price {AREA, PRICE} -- p 5
       KEY {AREA}        -- c 5.1

[p 6] Property PROPERTY_ID has area of AREA.

(c 6.1) Each property is of exactly one area; for each area, it is possible that more than one property has that area.

property_area {PROPERTY_ID, AREA} -- p 6
          KEY {PROPERTY_ID}       -- c 6.1

At this point, all these relvars represent simple predicates and are in 6NF (well p3 in 5NF). Now I can decide to leave them as is (logically ok) or to combine some of them based on keys, but staying in 5NF.

county_tax {COUNTY_NAME, TAX_RATE}  -- p 1,   p 4
       KEY {COUNTY_NAME}            -- c 1.1, c 4.1

area_price {AREA, PRICE}  -- p 5
       KEY {AREA}         -- c 5.1

property_ { COUNTY_NAME   -- p 2, p 3, p 6
          , LOT_NO
          , PROPERTY_ID
          , AREA
          }
      KEY {COUNTY_NAME, LOT_NO}  -- c 2.1, c 3.1
      KEY {PROPERTY_ID}          -- c 3.2, c 6.1

First two are in 6NF, the third one in 5NF.

This method requires understanding of: 1NF, 6NF, 5NF; or 1NF only, as long as redundancy and lossless decomposition join is properly understood. In other words, you can have your DB in 5NF even if you do not know what that means, as long as you can clearly define predicates and constraints.

So how can one know that relvar is 5NF, if one does not know what it means? Simple, it is not abut being in this-and-that NF, but about removing redundancy.

The 5NF is final NF as far as removing redundancy is concerned.
A relvar (table) is in 5NF iff decomposing it would not remove any redundancies.

Or (same meaning) if at least one of these holds:

  • There is no redundancy.
  • There is redundancy, but it can not be removed by decomposition.
  • It is not possible to decompose table without losing information.
  • It is not possible to decompose table at all.

Just make sure that when you reason abut redundancy, you reason about predicate and constraints, not about few rows of sample data.

To recap, instead of decomposing it by an algorithm or following functional dependencies, the method "explodes" the predicate of the initial relvar into set of simple predicates (6NF relvars) and then composes them, making sure to stay in 5NF.
It uses logic and natural language, a huge advantage when one needs to communicate with analysts and business people.

4
  • "A relvar (table) is in 5NF iff decomposing it would not remove any redundancies." No, all anomalies are removed as of ETNF between 4NF & 5NF. (Not that you clearly define "redundancies".) 5NF is iff/when all JDs that hold are implied by the CKs, ie there is some order of joining components where each join is on a CK of the original. Similarly "if at least one of these holds" is not correct. Also you seem to be intending not that the 4 bullets mean the same as 5NF but that they imply it.
    – philipxy
    Commented May 12, 2020 at 4:54
  • @philipxy (1/2): I firmly believe that NF obsession, propagated by academia and mass-education machinery, causes great harm. Many developers take a basic course, get confused with with all the terminology, and then simply abandon the idea of the relational model altogether. Hence, in most startups DBs are nothing more than a collection of square-shaped storage containers. Commented May 12, 2020 at 6:36
  • @philipxy (2/2): Once the business takes off startups grow exponentially, there is no time for technical debt, data volumes explode, and things are not fixable any more. It is important to keep things simple and understandable. Probability that a developer (or a practicing dba) can actually apply term like ETNF is about zero. It is important to keep things simple, easy to understand, help when possible, and do no harm. Commented May 12, 2020 at 6:37
  • Wrong is wrong & not helpful.
    – philipxy
    Commented May 12, 2020 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.