(Adam)Data dependency diagrams are great at helping illustrate data dependencies in a table. When you are creating a table in Microsoft SQL or Access, it can be difficult to difficult to perceive all of the relationships and dependencies in your tables by looking at the table itself. Creating the dependency diagrams separately gives you an easy way to see all of that information at a glance. These diagrams show you partial dependencies (when the attribute is dependent on a portion of the primary key) and transitive dependencies (when an attribute is dependent on a separate attribute that isn’t a part of the primary key) and you can realize the form of the data right away from looking at them (Coronel & Morris, 2018).
1NF is a table format where your entity has a primary key, gives each row only one value, and the attributes depend on the primary key. If a table has a row where employee “101” listed as “Engineer, Manager” as job function, they would be violating 1NF since they listed “Engineer” and “Manager” in the same row. The proper way to go about that would be to have two separate rows where you list employee “101” as “Engineer” and employee “101” as “Manager”. Since there is the possibility of employee ID’s being listed more than once, you wouldn’t be able to use that attribute as your primary key in that table.
2NF is an extension of 1NF and therefore has the initial requirement that the table be in 1NF. 2NF also requires that you eliminate partial dependencies. As I highlighted above, that means you remove attributes that are dependent on only a portion of the primary key from your table. Sometimes you may need to remove them completely, sometimes you may need to relocate them to another table in your database. A violation of 2NF would be a table with “Employee_#”, “Salary”, and “Employee_Name”. This would be a violation because “Employee_#” and “Employee_Name” are dependent, but “Employee_Name” and “Salary” are not. You would resolve this by having a separate table tying “Employee_Name” and “Employee_#” together.
Like the previous relationship, 3NF is an extension of 2NF. It requires all of the rules of 2NF and requires you to remove transitive dependencies. As I highlighted in the first paragraph, that means you remove dependencies that are not part of the primary key. A violation of 3NF would be when Column 3 is dependent on Column 2 and Column 2 is dependent on Column 1, which happens to be the primary key. To fix this, you would create another table where Column 2 is the primary key for Column 3.
Reference:
Coronel, C., & Morris, S. (2018). Database Principles: Fundamentals of Design, Implementation, and Management. Cengage
