Normal Forms Functional Dependencies: Fill & Download for Free

First Normal Form (1NF): The relation has a Primary Key, which uniquely identifies each row in the relation. Every column stores Atomic Values, and there are no Repeating Groups.Atomic means the column only stores one thing. A column called, FullName, which stores the customer’s first & last name is not atomic. You should have 2 atomic columns, one for FirstName, and another for LastName. Columns storing comma-separated values are also non-atomic.If you’ve ever seen Phone1, Phone2, and Phone3 in your Customers table (each phone column more sparse than the prior), then this table contains a Repeating Group, and your junior data architect should have his DDL permissions revoked. But chances are, you don’t have a data architect, and the table was created as an afterthought by your application code developers (who think you’d be better off using MongoDB anyway). Congratulations, your Customers table doesn’t even meet first normal form.Second Normal Form (2NF): The relation meets 1NF, and there are No Partial Functional Dependencies. That is, each non-key attribute is Functionally Dependent on the full Primary Key. For a Partial Functional Dependency to occur, the Primary Key must be a Composite Primary Key. A Composite Primary Key is a Primary Key that consists of 2 or more columns.Suppose your OrderLines table has a Composite Primary Key of OrderID + ProductID. Any other columns in this table that describe Products are only Functionally Dependent on the ProductID, and have nothing to do with the OrderID. These columns are Partially Functionally Dependent on the Primary Key, and should be removed from the OrderLines table and placed in the Products table.Third Normal Form (3NF): The relation meets 2NF, and there are No Transitive Functional Dependencies. A Transitive Functional Dependency is when a non-key column is Functionally Dependent on another non-key column, which is Functionally Dependent on the Primary Key.Suppose your Employees table has StateCode, a CHAR(2) column that stores the state the employee lives in. The StateCode column is not part of the Employees table Primary Key. Suppose this table also has columns describing the state, such as the full state name, and information about the state’s geography. Any columns describing the state, beyond StateCode, are Transitively Dependent on the Primary Key (via StateCode), and should be removed from the Employees table and added to the States table.A common saying people use to remember the first 3 normal forms is,“Every non-key attribute must provide a fact about the key (1NF), the whole key (2NF), and nothing but the key (3NF).”And then, for humor,“so help me Codd,”as a homage to Edgar Codd, who’s famous papers in the 1970’s are the foundation of modern relational database theory.

Lets start with the definitionDatabase normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy.NormalizationIf a database design is not perfect it may contain anomalies, which are like a bad dream for database itself. Managing a database with anomalies is next to impossible.Normalization is a method to remove all these anomalies and bring database to consistent state and free from any kinds of anomalies.First Normal Form:This is defined in the definition of relations (tables) itself. This rule defines that all the attributes in a relation must have atomic domains. Values in atomic domain are indivisible units.We re-arrange the relation (table) as below, to convert it to First Normal FormEach attribute must contain only single value from its pre-defined domain.Second Normal Form:Before we learn about second normal form, we need to understand the following:Prime attribute: an attribute, which is part of prime-key, is prime attribute.Non-prime attribute: an attribute, which is not a part of prime-key, is said to be a non-prime attribute.Second normal form says, that every non-prime attribute should be fully functionally dependent on prime key attribute. That is, if X → A holds, then there should not be any proper subset Y of X, for that Y → A also holds.We see here in Student_Project relation that the prime key attributes are Stu_ID and Proj_ID. According to the rule, non-key attributes, i.e. Stu_Name and Proj_Name must be dependent upon both and not on any of the prime key attribute individually. But we find that Stu_Name can be identified by Stu_ID and Proj_Name can be identified by Proj_ID independently. This is called partial dependency, which is not allowed in Second Normal Form.We broke the relation in two as depicted in the above picture. So there exists no partial dependency.Third Normal Form:For a relation to be in Third Normal Form, it must be in Second Normal form and the following must satisfy:No non-prime attribute is transitively dependent on prime key attributeFor any non-trivial functional dependency, X → A, then eitherX is a superkey or,A is prime attribute.We find that in above depicted Student_detail relation, Stu_ID is key and only prime key attribute. We find that City can be identified by Stu_ID as well as Zip itself. Neither Zip is a superkey nor City is a prime attribute. Additionally, Stu_ID → Zip → City, so there exists transitive dependency.We broke the relation as above depicted two relations to bring it into 3NF.Boyce-Codd Normal Form:BCNF is an extension of Third Normal Form in strict way. BCNF states thatFor any non-trivial functional dependency, X → A, then X must be a super-key.In the above depicted picture, Stu_ID is super-key in Student_Detail relation and Zip is super-key in ZipCodes relation. So,Confirms, that both relations are in BCNF.

Normalization of DatabaseDatabase Normalization is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies.Normalization is used for mainly two purpose,Eliminating redundant(useless) data.Ensuring data dependencies make sense i.e data is logically stored.Problem Without NormalizationWithout Normalization, it becomes difficult to handle and update the database, without facing data loss. Insertion, Update and Delete Anomalies are very frequent if Database is not Normalized.Normalization RuleNormalization rule are divided into following normal form.First Normal FormSecond Normal FormThird Normal FormBCNFFirst Normal Form (1NF)As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key.In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.Using the First Normal Form, data redundancy increases, as there will be many columns with same data in multiple rows but each row as a whole will be unique.Second Normal Form (2NF)As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.Third Normal Form (3NF)Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form.Boyce and Codd Normal Form (BCNF)Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:R must be in 3rd Normal Formand, for each functional dependency ( X -> Y ), X should be a super Key.