Table is the basic building block of database design

Normalization is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies

• the process involves assigning attributes to tables based on the concept of determination in Relational Database Modeling

Denormalization is a process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed. Denormalization potentially yields data anomalies - Read more on this

The objective of normalization is to ensure that each table conforms to the concept of well- formed relations or tables

When designing a new database structure

• Normalization is used to analyze the relationships among the attributes within each entity and determine if the structure can be improved through normalization

When modifying existing data structures

• Normalization is used to analyze relationships among the attributes or fields in the data structure

Each table represents a single subject

• For example, a COURSE table will contain only data that directly pertain to courses. Similarly, a STUDENT table will contain only student data

No data item will be unnecessarily stored in more than one table

• Ensures that the data is updated in only one place

All nonprime attributes in a table are dependent on the primary key - the entire primary key and nothing but the primary key

• Ensures that the data is uniquely identifiable by a primary key value

Each table is void of insertion, update, or deletion anomalies

• Ensures the integrity and consistency of the data

Read more on Higher-Level Normal Forms;

• Boyce-Codd Normal Form (BCNF)
• A table is in Boyce-Codd normal form (BCNF) when every determinant in the table is a candidate key

• Fourth Normal Form (4NF)
• A table is in fourth normal form (4NF) when it is in 3NF and has no multivalued dependencies

Steps;

• Eliminate the repeating groups For example, a car can have multiple colors for its top, interior, bottom, trim, and so on
• Identify the primary key
• Identify all dependencies

Properties of 1NF;

• No repeating groups in the table i.e., each row/column intersection contains one and only one value, not a set of values
• All of the key attributes are defined
• All attributes are dependent on the primary key

Conversion to 2NF occurs only

• when the 1NF has a composite primary key; If the 1NF has a single-attribute primary key, then the table is automatically in 2NF

Steps;

• Make new tables to eliminate partial dependencies
• Re-assign corresponding dependent attributes

Properties of 2NF;

• It includes no partial dependencies; i.e., no attribute is dependent on only a portion of the primary key

Steps;

• Make new tables to eliminate transitive dependencies
• Reassign corresponding dependent attributes

Properties in 3NF;

• It is in 2NF
• It contains no transitive dependencies i.e. no non-key attribute is functionally dependent on another non-key attribute

Queries about this Lesson, please send them to: jmwaura@jkuat.ac.ke

*References*

• Database Systems: Design, Implementation, and Management, 12th ed. Carlos Coronel & Steven Morris
• Database Modeling and Design; Logical Design, 5th ed. Taby Teorey et.al
• Fundamentals of database systems, 6th ed. Ramez Elmasri & Shamkant B. Navathe