Friday, September 23, 2016

Third Normal Form

  • ·         The form of database normalization where all non-key fields are dependent on the key, the whole key, and nothing but the key. - Third Normal Form (3NF)
  • ·         A condition that exists when any attribute in an entity is dependent upon any other non-UID attribute in that entity.- Transitive Dependency

1.      Identify the transitive dependency in the model below. State which attributes violate Third Normal Form.

Store address being dependent on store name, is a transitive dependency and violates 3NF.

2. Define the rule of Third Normal Form.
  • ·         1NF is satisfied by resolving multi-valued attributes.
  • ·         2NF is satisfied by - 1NF  + Each non UID attribute is dependent on the whole UID,
  • ·         #NF is satisfied by - 2NF + there should be no transitive dependency present.

3. A color scheme for a car includes specifications for paint color for the body and the interior colors and materials. For example: The “Desert” color scheme includes silver paint and gray leather interior; the “Sunburst” color scheme includes gold paint and cream leather interior. Does the model below follow the rules of Third Normal Form? If you spot a violation, correct it.


  • ·         Model I choose depend on manufacturer (make) – not all manufacturer make every model.
  • ·         Color scheme available really depends on model I choose to buy.
  • ·         Paint color and interior color depends on color scheme I choose.

  • 1.      A model must have at least one color scheme designed for it
  • 2.      A Color scheme must be created with at least one model in mind.
  • 3.      Once car is manufactured, I can’t change scheme and model of car.

4. Assume the following business rules:
• An athlete employs one agent.
• An agent may work for one or more athletes.
• An athlete may play for one team.
• A team may have one or more players
Does the model below contain one or more transitive dependencies? Identify the attributes involved in the transitive dependencies.

The transitive dependencies identified:
  • ·         Team coach is dependent on team. And if player changes team, coach changes for player, he won't drag his coach with him to new team J
  • Agent commission is dependent on what agent player chooses, if he changes agent, commission may also change (unless agents have a labor union who decides there rates J ). Or if agent revises his commission, 'all the players using him' instances will have to be updated. 

5. Now that you have an idea of what makes a good UID, you need to be aware of the controversy, benefits, and difficulty of uniquely identifying someone. Consider the idea of a national ID card. What kinds of problems would the card create and what kinds of problems would it solve? If your country already uses a national ID card, what are the benefits and issues associated with this? In your opinion, could DNA mapping become the national ID card? Why or why not?

I am more satisfied with the concept of single identity of every person than afraid of. 
Why satisfied:
  • ·         In all government transactions I just need to give SSN and done, say no paper work while getting a new credit card.
  • ·         Say I update my name at one place, ideally it should get propagated everywhere ( in reality it’s not so simple, since theoretically possible in future, so putting here under why satisfied)

Why afraid of:
  • ·         If someone has my SSN, he can even get a credit card that is another story that all credit cards have fraud protections but still…
  • ·         It’s bad for me in a way that, only by knowing SSN they know how many payments of credit card I missed ( I didn’t do it intentionally J )

DNA thing is a good idea but we are far way behind as of today. It won’t be an artificial UID like SSN but costly to collect this data as of today.

No comments:

Post a Comment