Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also...
Third normal form (3NF) is the third step in normalizing a database and it builds on the first and second normal forms, 1NF and 2NF.
3NF states that all column reference in referenced data that are not dependent on the primary key should be removed. Another way of putting this is that only foreign key columns should be used to reference another table, and no other columns from the parent table should exist in the referenced table.
Consider a bank’s database, which contains two tables: CUSTOMER_MASTER for storing customer details, and ACCOUNT_MASTER for storing details about bank accounts, including which customer holds which account. In this case, there needs to be a way to link the two tables in order to tie an account to the customer who owns it. The way to do this is via a foreign key. This is a column in the ACCOUNT_MASTER table that points to or references a corresponding column (called the primary key) in the CUSTOMER_MASTER parent table. Let’s call this column CustID.
Suppose that customer Andrew Smith creates an account in the CUSTOMER_MASTER table with CustID 20454. Mr. Smith holds a savings account with the number S-200802-005, whose details are stored in the ACCOUNT_MASTER table. This means that the ACCOUNT_MASTER table will have a column called CustID, which is not an original piece of data. Instead, it also has the value 20454, which simply references the same CustID in the CUSTOMER_MASTER table.
Now, 3NF dictates that in our ACCOUNT_MASTER table, the only information we hold about the customer should be the CustID (20454) as a foreign key, and it refers to and identifies the customer who owns this same CustID in the CUSTOMER_MASTER table (Andrew Smith). No other data about our customer (such as name, date of birth, gender and so on) should be stored in the ACCOUNT_MASTER table, or indeed any other table, because all this data about him is already stored in CUSTOMER_MASTER. By doing this, the only customer data stored outside the CUSTOMER_MASTER table is the CustID. This pays handsome dividends by ensuring there is no data duplication, which in turn makes queries run much more efficiently and reduces the amount of storage required.
Read More »
Join 138,000+ IT pros on our weekly newsletter
Home | Advertising Info | Write for Us | About | Contact Us
2010 - 2015
Partner Sites :