Why normal forms were defined
Explain why normal forms were defined. What problems are solved by using them?
Provide an example table that violates at least one normal form and explain how you would rebuild this table to make it comply with it. (Hint: you can use any online tool and take screen shots.)
Foreign Keys:
Explain why foreign keys are used. What problems can be solved by using them? (25 points)
Identify a foreign key from the “classic models” tutorial and:
explain how it works, i.e., which tables does it connect, etc. (12 1/2 points)
what would be the practical implication of removing this foreign key relationship that you chose. Things to consider include: data duplication, data inconsistency, orphaned data, etc. (12 1/2 points)
Sample Solution
Normal forms are guidelines used to design relational databases which help ensure that data is stored efficiently, accurately, and securely. The most common normal forms (1NF, 2NF and 3NF) were first developed by Edgar F. Codd in the 1970s with each successively more complex form addressing specific issues such as redundancy or inconsistencies (Navathe et al.).
The 1st Normal Form (1FN) requires all columns in a table to have unique identifiers while also preventing multiple values from being stored within a single column. This ensures that there is no redundant data entry which can lead to discrepancies between records—such as one record using ‘Mr Smith’ for a name and another using ‘Smith Mr’ —and helps protect against data loss due to accidental deletions.
The 2nd Normal Form (2NF) builds upon this by removing any partial dependencies between two tables —so that if an attribute from table A depends on an attribute from table B , then it should be deleted from A . This prevents unnecessary duplication of information and ensures accurate representation of relationships between different elements of the database.
Finally, the 3rd Normal Form (3FN) focuses on ensuring that every non-key field only refers back to the primary key —preventing orphaned records where changes made in one place do not propagate correctly throughout other parts of the database.
To illustrate these concepts let us consider an example table called StudentCourse mapped below. This table violates 1st normal form because Name is included twice—once for FirstName and once for LastName—meaning we cannot rely on its uniqueness when searching for records or making updates . To make this compliant with 1 FN , we could separate out Name into three distinct fields : StudentID , FirstName and LastName . We would also need to add additional columns like CourseID so that our primary keys are properly defined . Our revised version might look like this: