
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Create Table
- SQL - Drop Table
- SQL - Insert Query
- SQL - Select Query
- SQL - Where Clause
- SQL - AND & OR Clauses
- SQL - Update Query
- SQL - Delete Query
- SQL - Like Clause
- SQL - Top Clause
- SQL - Order By
- SQL - Group By
- SQL - Distinct Keyword
- SQL - Sorting Results
- Advanced SQL
- SQL - Constraints
- SQL - Using Joins
- SQL - Unions Clause
- SQL - NULL Values
- SQL - Alias Syntax
- SQL - Indexes
- SQL - Alter Command
- SQL - Truncate Table
- SQL - Using Views
- SQL - Having Clause
- SQL - Transactions
- SQL - Wildcards
- SQL - Date Functions
- SQL - Temporary Tables
- SQL - Clone Tables
- SQL - Sub Queries
- SQL - Using Sequences
- SQL - Handling Duplicates
- SQL - Injection
- SQL Useful Resources
- SQL - Database Tuning
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Database - Third Normal Form (3NF)
A table is in a third normal form when the following conditions are met −
- It is in second normal form.
- All nonprimary fields are dependent on the primary key.
The dependency of these non-primary fields is between the data. For example, in the following table – the street name, city and the state are unbreakably bound to their zip code.
CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, STREET VARCHAR(200), CITY VARCHAR(100), STATE VARCHAR(100), ZIP VARCHAR(12), EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );
The dependency between the zip code and the address is called as a transitive dependency. To comply with the third normal form, all you need to do is to move the Street, City and the State fields into their own table, which you can call as the Zip Code table. −
CREATE TABLE ADDRESS( ZIP VARCHAR(12), STREET VARCHAR(200), CITY VARCHAR(100), STATE VARCHAR(100), PRIMARY KEY (ZIP) );
The next step is to alter the CUSTOMERS table as shown below −
CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, ZIP VARCHAR(12), EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );
The advantages of removing transitive dependencies are mainly two-fold. First, the amount of data duplication is reduced and therefore your database becomes smaller.
The second advantage is data integrity. When duplicated data changes, there is a big risk of updating only some of the data, especially if it is spread out in many different places in the database.
For example, if the address and the zip code data were stored in three or four different tables, then any changes in the zip codes would need to ripple out to every record in those three or four tables.