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.

Suggestion:

• ·         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.

Assumptions:

• 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.