Normalization & SQL Commands

Jalena Mathew

8 min read

·

Aug 28, 2022

--

Listen

Share

If your database is not organized properly, it gets difficult to extract data from it. If the table have repeating values or anomalies it gets tedious to extract the desired data. Normalization is used to help with such redundancies and anomalies.

Press enter or click to view image in full size

There are different forms in normalization using which we can organize our data in the database.

Table of contents:

• What is normalization in SQL
• First Normal Form
• Second Normal Form
• Third Normal Form
• Boyce Codd Normal Form

Press enter or click to view image in full size

1. Normalization in SQL

Normalization is the process of organizing the data in the database.
By organizing we mean that:

1. Minimize redundancy from a relation or set of relations
2. Eliminate undesirable characteristics such as Insertion, Update and Deletion Anomalies

With this our large table gets divided into smaller tables and they are linked using relationship.

You can learn about insertion, update and deletion anomalies here.

To achieve this normalization there are many normal forms used from which we will only be looking at till BCNF.

2. First Normal Form

A table is said to be in 1NF if the domain of an attribute includes only atomic values.

Conditions:

• Atomicity states that a single cell in the table cannot hold multiple values. Only single-valued attributes are allowed. (1NF disallows the multi-valued attribute, composite attribute, and their combinations.)
• All entries of the column must be of the same type
• Each column must have unique name
• No two rows are identical

Let’s look at an example.

Below is a students’ record table that has information about student roll number, student name, student course, and age of the student.

Here the course field has multiple values. Thus this violates the 1NF condition. To make it in the First Normal Form, we will split the rows having two values for course into two separate rows as shown below,

Now every column contains only single value and no two rows are identical.

3. Second Normal Form

A table is said to be in 2NF if,

• Table is in 1NF
• Every non-prime attribute is fully functionally dependent on prime key attribute

Say, X -> A is fully FD, then after removing attributes from X, FD X-> A should no longer hold.

Prime attribute: An attribute, which is a part of the candidate-key, is known as a prime attribute

Non-prime attribute: An attribute, which is not a part of the prime-key is said to be a non-prime attribute

Partial Dependency: If the proper subset of a candidate key determines non-prime attribute, it is called partial dependency

In simple words,
2NF eliminates partial dependencies on primary keys.

In the below given table,

Candidate keys : {cust_id, storeid}
Non prime attribute: store_location

The store_location should be only dependent on {cust_id, storeid}, but it is also dependent on the subsets on candidate keys cust_id and storeid. Thus partial dependency exists in the table and is a violation of 2NF.

Dependencies that exist in the above table:
store_location -> {cust_id, storeid}
store_location -> cust_id
store_location -> storeid

To remove Partial Dependency and violation on 2NF, we will decompose the above table as,

4. Third Normal Form

A table design is said to be in 3NF if the following conditions hold true:

• Table must be in 2NF
• No non-prime attribute is transitively dependent on prime key attribute
• For any functional dependency, X -> A, then either
  1. X is a superkey or,
  2. A is a prime attribute

Superkey: Super key is a set of over one key that can identify a record uniquely in a table, and the Primary Key is a subset of Super Key.

Transitive dependency: Whenever some indirect relationship happens to cause functional dependency (FC) i.e., if A -> B and B -> C are true, then A -> C happens to be a transitive dependency.

Example,

In the below student table we have student id, student name, subject id, subject name, and address of the student as its columns.

Here, stu_id determines subid, and subid determines sub. Therefore, sub(non prime key) is transitively dependent on stu_id(primary key). The stu_id field isn’t a superkey and sub isn’t a prime key therefore 3NF is violated.

We will divide the table to change the table to 3NF as,

As you can see in both the tables, all the non-key attributes are now fully functional, dependent only on the primary key. In the first table, columns name, subid, and addresses only depend on stu_id. In the second table, the sub only depends on subid.

5. Boyce Codd Normal Form (BCNF)

This is the advance version of 3NF and is stricter than 3NF.

The conditions for a table to be in BCNF is,

• Table should be in 3NF
• For every functional dependency, LHS is super key
  i.e. for every X -> Y,
  X is the super key of the table

Consider the below given subject table,

The subject table follows these conditions:

• Each student can enroll in multiple subjects.
• Multiple professors can teach a particular subject.
• For each subject, it assigns a professor to the student.

What do you think should be the Primary Key?

Well, in the table above student_id, subject together form the primary key, because using student_id and subject, we can find all the columns of the table.

Another important point to be noted here is that one professor teaches only one subject, but one subject may have two professors.

Which exhibit there is a dependency between subject and professor, i.e. subject depends on the professor’s name.

The table is in 1st Normal form as all the column names are unique, all values are atomic, and all the values stored in a particular column are of the same domain.

The table also satisfies the 2nd Normal Form, as there is no Partial Dependency.

And, there is no Transitive Dependency; hence, the table also satisfies the 3rd Normal Form.

This table follows all the Normal forms except the Boyce Codd Normal Form.

As you can see stuid, and subject forms the primary key, which means the subject attribute is a prime attribute.

However, there exists yet another dependency —professor → subject.

BCNF does not follow in the table as a subject is a prime attribute, the professor is a non-prime attribute.

To transform the table into the BCNF, you will divide the table into two parts. One table will hold stuid which already exists and the second table will hold a newly created column profid.

And in the second table will have the columns profid, subject, and professor, which satisfies the BCNF.

There are even more normal forms such as 4NF and 5NF, but we will cover only upto BCNF in this blog.

Basic SQL Commands

In this section we will go through some of the basic SQL commands that you should know.

• SHOW Statement
  It lists the databases present on the MySQL server.
  Syntax:
  SHOW DATABASES;
• USE Statement
  It selects a database on which SQL operations will be performed.
  Syntax:
  USE database_name;
• CREATE TABLE Statement
  It is used to create a new table
  Syntax:
  CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ….
  );
• INSERT INTO Statement
  It is used to add new rows in the table.
  Syntax:
  INSERT INTO table_name (column1, column2, …)
  VALUES (value1, value2, …);
• SELECT Statement
  It is used to select rows and columns from a table in the database. It is also used to print aggregate values.
  Syntax:
  SELECT column1, column2,…
  FROM table_name;
• DROP TABLE Statement
  The DROP Statement is used to delete a particular table. Dropping of the table will drop all the objects (tables, views, procedures, etc.) inside it. The user should have admin privileges for deleting a table.
  Syntax:
  DROP TABLE table_name;
• TRUNCATE TABLE Statement
  It deletes the data inside a table, but not the table itself. The structure of the table will not get deleted.
  Syntax:
  TRUNCATE TABLE table_name;
• ALTER TABLE Statement
  It is used to update the structure of a table by updating the definition of its columns. The ALTER command can perform the following functions:
  1. Add, drop, modify table columns
  2. Add and drop constraints
  3. Enable and Disable constraints
  Syntax:
  ALTER TABLE table_name ADD column_name datatype;
  ALTER TABLE table_name MODIFY column_name datatype;
  ALTER TABLE table_name DROP COLUMN column_name;
• DELETE Statement
  A DELETE statement is used to delete fields from a table.
  Syntax:
  DELETE FROM table_name WHERE condition;
• UPDATE Statement
  It is used to update the existing records in a table.
  Syntax:
  UPDATE table_name
  SET column1 = value1, column2 = value2, …
  WHERE condition;
• DISTINCT Statement
  The SELECT DISTINCT statement in SQL is used to return only unique values from a column.
  Syntax:
  SELECT DISTINCT column1, column2, …
  FROM table_name;

Aggregate Functions in MySQL

MySQL supports the following aggregate functions:

• AVG() Function
  It returns the average of the values in the selected column.
  Syntax:
  SELECT AVG(column_name)
  FROM table_name
  WHERE condition;
• COUNT() Function
  It returns the number of rows returned for a selection.
  Syntax:
  SELECT COUNT(column_name) FROM table_name;
  SELECT COUNT(*) FROM table_name; //prints the number of rows
• MAX() Function
  It returns the largest value of the selected numeric column.
  Syntax:
  SELECT MAX(column_name)
  FROM table_name
  WHERE condition;
• MIN() Function
  The MIN() function returns the smallest value of the selected numeric column in a table.
  Syntax:
  SELECT MIN(column_name)
  FROM table_name
  WHERE condition;
• SUM Function
  The SUM() function gives the total sum of a numeric column.
  Syntax:
  SELECT SUM(column_name)
  FROM table_name
  WHERE condition;

Clauses in SQL

This is a clause, which can be used along with any MySQL query to specify a condition.

• GROUP BY CLAUSE
  The GROUP BY clause is used to return data that is grouped according to one or more columns.
  Syntax:
  SELECT column_name(s)
  FROM table_name
  WHERE condition
  GROUP BY column_name(s);
• HAVING CLAUSE
  The HAVING clause is mostly used with aggregate functions. Having clause filters the records from groups based on specified conditions.
  Syntax:
  SELECT column_name(s)
  FROM table_name
  WHERE condition
  GROUP BY column_name(s)
  HAVING condition
  ORDER BY column_name(s);
• ORDER BY CLAUSE
  The ORDER BY clause is used within a SELECT statement to extract output either in ascending or descending order of columns.
  Syntax:
  SELECT column-list
  FROM table_name [WHERE condition]
  [ORDER BY column1 [, column2, .. columnN] [DESC]];

After you had practiced using the above basic commands you can jump to much more advanced commands such as- LIKE, JOIN, UNION, VIEW etc.

Happy learning!