Two tables, namely, OrderItem and Item are depictedbelow in which all attributes mentioned therein
respectively, refer to column heading labels.
If the ItemName attribute is moved from the Item table to the OrderItem table, then will it violate any
normalization form? If yes, then which
of the following normalization forms will be violated?
A.
It will not violate any normal form (NF).
B.
1NF
C.
3NF
D.
2NF
Explanation:
It will violate 2NF, as ItemName is functionally dependent on ItemNumber, which is the primary key of the Item
table.
ItemNumber is the foreign key of the OrderItem table, and OrderNumber is the primary key of the OrderItem
table. According to 2NF, all the
non-key attributes in a table must be functionally dependent on the primary key column. Migrating ItemName to
the OrderItem table will
violate 2NF, since ItemName (a non-key attribute) is not functionally dependent on OrderNumber, which is the
primary key of the OrderItem
table.
This answer should be D:
Third Normal Form
Eliminate fields that do not depend on the key.
Values in a record that are not part of that record’s key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.
For example, in an Employee Recruitment table, a candidate’s university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.
EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or exceed open file and memory capacities.
It may be more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed.
https://support.microsoft.com/en-us/kb/283878
James your suggestion in my opinion is correct. But the correct answer should be “C” i.e. -> 3NF not “D”.
2NF violation is when a non-key attribute is not functionally dependent on primary key.
Is this correct? Or should it be 3NF violation??
I think this violates 2NF and 3NF.