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