Can anyone explain me 1NF, 2NF, 3NF, BCNF rules with a proper example?

sql database database-design database-normalization
Vishwanath Dalvi picture Vishwanath Dalvi · Jan 14, 2011 · Viewed 20k times · Source

This is a common interview question. I faced one interview where the interviewer gave me one table and asked me tell him which normal form the table is in? If it's in ##NF, then normalize it to the next NF?

I'm always get confused between these normal forms of database. Can anyone explain to me these normal forms with a proper example of how each NF is modeled into table so it will help in my next interview?

Answer

nvogel picture nvogel · Jan 14, 2011

Boyce Codd Normal Form is really the most important and also the easiest to explain: Every non-trivial determinant is a superkey (just remember "arrows out of superkeys"). The lesser normal forms are only worth mentioning because BCNF is not dependency-preserving in a few cases.

Examples of BCNF and 5NF by Hugh Darwen:

For more mathematical definitions (Abiteboul et al):

Related questions

How to Set Customer Table with Multiple Phone Numbers? - Relational Database Design

CREATE TABLE Phone ( phoneID - PK . . . ); CREATE TABLE PhoneDetail ( phoneDetailID - PK phoneID - FK points to Phone phoneTypeID ... phoneNumber ... . . . ); CREATE TABLE Customer ( customerID - PK firstName phoneID - Unique FK points to Phone . . . ); A customer can have multiple phone …

Read More
How to create a new schema/new user in Oracle Database 11g?

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express …

Read More
SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

If I have two relations in a database, like this: CREATE TABLE Courses ( CourseID int NOT NULL PRIMARY KEY, Course VARCHAR(63) NOT NULL UNIQUE, Code CHAR(4) NOT NULL UNIQUE ); CREATE TABLE BookCourses ( EntryID int NOT NULL PRIMARY KEY, BookID int …

Read More