Normalization Process

I'll refer to the following tables when explaining some concepts Tbl_Staff_Branch Tbl_Staff Tbl_Branch

What are update Anomalies The Problems resulting from data redundancy in an un-normalized database table are collectively known as update anomalies. So any database insertion, deletion or modification that leaves the database in an inconsistent state is said to have caused an update anomaly. They are classified as Insertion anomalies:  To insert the details of a new member of staff located at branch B1 into the Tbl_Staff_Branch Table shown above, we must enter the correct details of branch numner B1 so that the branch details are consistent with the values for branch B1 in other rows.  To insert the details of a new branch that currently has no members of staff into the Tbl_Staff_Branch table, it is necessory to enter nulls for the staff details which is not allowed asstaffID is the primary key. But if you normalize Tbl_Staff_Branch, which is in Second Normal Form (2NF) to Third Normal Dorm (3NF), you end up with Tbl_Staff and Tbl_Branch and you shouldn't have the problems mentioned above.  Deletion anomalies:  If we delete a row from the Tbl_Staff_Branch table that represents the last member of staff located at that branch, (for e.g. row with Branch numbers B",B3 or B4) the detals about that branch are also lost from the Database.  Modification anomalies:   Should we need to change the address of a perticular branch in the Tbl_Staff_Branch table, we must update the rows of all staff located at that branch. If this modification is not carried out on all the relevent rows, the database will become inconsistent. Top What is Functional Dependency? what are the different types of Functional Dependencies? Functional Dependencies are fundamental to the process of Normalization Functional Dependency describes the relationship between attributes(columns) in a table.  For example, if A and B are attributes of a table, B is functionally dependent on A, if each value of A is associated with exactly one value of B (so, you can say, 'A functionally determines B'). Functional dependency between A and B Attribute or group of attributes on the left hand side of the arrow of a functional dependency is refered to as 'determinant' Simple example would be StaffID functionally determines Position in the above tables. Functional Dependency can be classified as follows: Full Functional dependency   Indicates that if A and B are attributes(columns)of a table, B is fully functionally dependent on A if B is functionally dependent on A ,but not on any proper subset of A.  E.g. StaffID---->BranchID  Partial Functional Dependency   Indicates that if A and B are attributes of a table , B is partially dependent on A if there is some attribute that can be removed from A and yet the dependency still holds.  Say for Ex, consider the following functional dependency that exists in the Tbl_Staff table: StaffID,Name -------> BranchID BranchID is functionally dependent on a subset of A (StaffID,Name), namely StaffID. Transitive Functional Dependency:   A condition where A , B and C are attributes of a table such that if A is functionally dependent on B and B is functionally dependent on Cthen C is Transitively dependent on A via B. Say for Ex, consider the following functional dependencies that exists in the Tbl_Staff_Branch table: StaffID---->Name,Sex,Position,Sal,BranchID,Br_Address  BranchID----->Br_Address So, StaffID attribute functionally determines Br_Address via BranchID attribute.  Top What is Database Normalization? Database Normalization is a step wise formal process that allows us to decompose Database Tables in such a way that both Data Redundancy and Update Anomalies(see above for more info on update anomalies) are minimised.  It makes use of Functional Dependencies that exist in a table (relation, more formally) and the primary key or Candidate Keys in analysing the tables.  Three normal forms were initially proposed called First normal Form (1NF), Second normal Form (2NF), and Third normal Form (3NF). Subsequently R.Boyce and E.F.Codd introduced a stronger definition of 3NF called Boyce-Codd Normal Form(BCNF). With the exception of 1NF, all these normal forms are based on Functional dependencies among the attributes of a table. Higher normal forms that go beyond BCNF were introduced later such as Fourth Normal Form (4NF) and Fifth Normal Form (5NF). However these later normal forms deal with situations that are very rare. First Normal Form (1NF)   The only thing that is required for a table to be in 1NF is to contain only atomic values (intersection of each row and column should contain one and only one value).this is sometimes referred to as : Eliminate Repeating groups.  Second Normal Form (2NF)   A Table is said to be in 2NF if it is in 1NF and there are no partial dependencies i.e. every non primary key attribute of the Table is fully functionally dependent on the primary key.  Third Normal Form (3NF)   A Table that is in 1NF and 2NF and in which no non primary key attribute is transitively dependent on primary key. Boyce-codd Normal Form (BCNF)   A Table is in BCNF if and only if every determinant(it is an attribute or a group of attributes on which some other attribute is fully functionally dependent, see functional dependency described above) is a candidate key. BCNF is a stronger form of 3NF.  The difference between 3NF and BCNF is that for a Functional dependency A--->B, 3NF allows this dependency in a table if attribute B is a primary key attribute and attribute A is not a candidate key, where as BCNF insists that for this dependency to remain in a table, attribute A must be a candidate key .  Normalization Process Fourth Normal Form (4NF)   4NF is a stronger normal form than BCNF as it prevents Tables from containing nontrivial Multi-Valued Dependencies (MVDs) and hence data redendancy.  The Normalization of BCNF Tables to 4NF involves the removal of MVDs from the Table by placing the attribute(s) in a new Table along with the copy of the determinant(s).  Fifth Normal Form(5NF)   5NF is also called Project-Join Normal Form(PJRF) and specifies that a 5NF Table has no Join dependency.