Friday, September 23, 2016

Second Normal Form

  • The outcome of the second step of database normalization – Second Normal Form (2NF)
1. The UID of a library book includes its BOOK number and SHELF number. Does this ERD follow the rules of Second Normal Form? If you spot a violation, correct it.


Sugession: 2NF on shelf. [Assume that a book won’t have multiple author, even if it has we only track major one so, 1NF is not violated their, problem specifically targets 2NF here.]
2. .      Class Enrollment is the intersection entity that resolves the M:M between STUDENT and CLASS. Does the ERD follow the rules of Second Normal Form? If you spot a violation, correct it.

Suggestion:

3. A store can be located in several shopping malls, and a shopping mall may house several stores. To locate a particular store in a specific neighborhood, you will need to know the name and address of the nearby shopping mall, plus the name of the store. Does the ERD follow the rules of Second Normal Form? If you spot a violation, correct it.

Answer:
To locate a store, name and address of mall + store name is needed, then why location is present in store listing entity. Ok, let’s be realistic here, inside mall, where is store? In shop no 100. This is what here location means. I say no harm in storing a location value. Some may argue why location is not in store entity, what if this store has another branch in another mall and it is located in shop no 200 there?
 Now we come to store specialty, this may be the reason why I have this problem here. Store franchises follow exactly same recipes’ and if there is a specialty, then it’s of the whole chain. Since specialty is of store not only the one located in specific mall, I want to move it to Store Entity. 2NF wants specialty to be dependent on Store UID.



No comments:

Post a Comment