Database Normalization

Topics: Database normalization, Third normal form, Fourth normal form Pages: 7 (1381 words) Published: August 30, 2013

A logical design method which minimizes data redundancy and reduces design flaws. Consists of applying various “normal” forms to the database design. The normal forms break down large tables into smaller subsets.

First Normal Form (1NF)
Each attribute must be atomic
• No repeating columns within a row.
• No multi-valued columns.
1NF simplifies attributes
• Queries become easier.
1NF Employee (unnormalized)

Employee (1NF)

Second Normal Form (2NF)

Each attribute must be functionally dependent on the primary key. • Functional dependence - the property of one or more attributes that uniquely determines the value of other attributes. • Any non-dependent attributes are moved into a smaller (subset) table. 2NF improves data integrity.

Prevents update, insert, and delete anomalies.
Functional Dependence

Employee (1NF)

Name, dept_no, and dept_name are functionally dependent on emp_no. (emp_no -> name, dept_no, dept_name)

Skills is not functionally dependent on emp_no since it is not unique to each emp_no.

Employee (1NF)

Employee (2NF)Skills (2NF)

Third Normal Form (3NF)

Remove transitive dependencies.
• Transitive dependence - two separate entities exist within one table. • Any transitive dependencies are moved into a smaller (subset) table. 3NF further improves data integrity.

Prevents update, insert, and delete anomalies.

Transitive Dependence
Employee (2NF)

Dept_no and dept_name are functionally dependent on emp_no however, department can be considered a separate entity.

Employee (2NF)

Employee (3NF)Department (3NF)

Boyce-Codd Normal Form

A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key. 1. Anomalies can occur in relations in 3NF if there is a composite key in which part of that key has a determinant which is not itself a candidate key. 2. This can be expressed as R(A,B,C), C[pic]A where:

o The relation contains attributes A, B and C.
o A and B form a candidate key.
o C is the determinant for A (A is functionally dependent on C). o C is not part of any key.
3. Anomalies can also occur where a relation contains several candidate keys where: o The keys contain more than one attribute (they are composite keys). o An attribute is common to more than one key.

Take the following table structure as an example:
schedule(campus, course, class, time, room/bldg)
Take the following sample data:
|campus |course |class |time |room/bldg | |East |English 101 |1 |8:00-9:00 |212 AYE | |East |English 101 |2 |10:00-11:00 |305 RFK | |West |English 101 |3 |8:00-9:00 |102 PPR |

Note that no two buildings on any of the university campuses have the same name, thus ROOM/BLDG[pic]CAMPUS. As the determinant is not a candidate key this table is NOT in Boyce-Codd normal form. This table should be decomposed into the following relations: R1(course, class, room/bldg, time)

R2(room/bldg, campus)

As another example take the following structure:
enrol(student#, s_name, course#, c_name, date_enrolled)
This table has the following candidate keys:
• (student#, course#)
• (student#, c_name)
• (s_name, course#) - this...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Essay on Normalization of Database Management Systems
  • Database Normalization Essay
  • Chapter 3: Table Normalization and Windows Azure SQL Database Essay
  • Database Normalisation Essay
  • Essay about Normalizaiton in Database
  • Database Modeling And Design Essay
  • PT2520 Unit4 Assignment1 Homework Essay
  • Database Slides on Normalization Essay

Become a StudyMode Member

Sign Up - It's Free