Normalisation in DBMS
In clear terms, Normalisation is simply a process of organising data in a database, enhance its integrity by preventing data inconsistency and redundancy. It requires techniques and designs of breaking your complex database tables into bits that are connected to each other. This sort of relational model has been proven to be consistent with RDBMS.
For data to be used or stored efficiently and understandably, it has to be properly linked and distributed. Chaos sets In when data is scattered and are not linked to each other properly.
Normalisation removes all anomalies and brings the database into a consistent state.
Normalisation in any DBMS follows a normal form of rules. The rules are listed into 6 forms with the 6th form being a newly developed form.
Before we proceed, Let’s understand a few things.
SQL Key - Primary Keys and Composite Keys
Primary Key - a primary key is a key uniquely designated to identify each table record in a database. It is very crucial to a consistent and efficient relational database. A database table must have a primary key to insert, update, restore, or delete data from a database table.
A primary key can be set to created manually, serially or automated in random numbers while defining your schema.
It has the following attributes:
1. A Primary Key cannot be NULL
2. A Primary Key must be unique
3. The Primary Keys should rarely be changed
4. The Primary key must be given a specific value once a new record has been inserted
Composite Key - A composite key is a primary key composed of multiple columns used to identify a record uniquely. Imagine a database with closely similar values but little distinction, a composite key would hold multiple columns together.
Now Let’s move into the rules:
The 1NF - **1. Each table cell should contain a single value
2. Each table cell must be unique
**The 2NF - 1. Must be in 1NF
2. Single column primary key is not functionally dependent on the subsets of candidate keys in the database
In 2NF, tables are connected to each other with Foreign Keys. Foreign Keys references the primary key of another table.
- A foreign key can have a different name from the Primary Key
- It ensures rows in one table has corresponding rows in another
- They do not have to be unique
- They can be NULL, Primary Keys cannot.
TRANSITIVE Functional Dependencies - changing a Non-Key column that might affect the other non-keys column to change.
The 3NF - 1. Be in 2NF
2. Have no transitive functional dependency
The Boyce-Cord Normal Form
1. Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.
Sometimes is BCNF is also referred as 3.5 Normal Form.
The 4NF - 1. If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form.
The 5NF - 1. A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.
The 6NF Proposed - 1. 6th Normal Form is not standardsed, yet however, it is being discussed by database experts for some time. Hopefully, we would have a clear & standardised definition for 6th Normal Form in the near future
Database Normalisation and designing are critical to a successful implementation of a good DBMS. Database can be designed further than 3NF, being the standard normal form for any database.
Top comments (0)