Second Normal Form

After talkling about the first normal form earlier, its time for second normal form.

But, before we start, first go through this and this article serially if you haven't.

Now, to understand it, take a look on this statement
Second Normal Form = First Normal Form + [ ADDITIONAL PROPERTY ]
From this, we can conclude that for a table to have second normal form, it must have first normal form plus some additional property.

We already have discussed first normal form. So, In this post, I will discuss this additional property for a table (already having first normal form) to qualify for second normal form.
And this addtional characteristic is that every column in the table depends on the whole candidate key (column suitable to be primary key), NOT on just a subset column of candidate key in case of composite candidate key.

Let's continue the example of table EMP containg employee recordswe took to understand first normal form. Now we have the table having first normal form as shown. Here the primary key is "ID".

Does this have second normal form? YES. Why? because, here every column other than primary key column (ID) is funtionally dependent on the primary key. So, we can say a first normal table with non-composite candiidate key have second normal form already.

Now, we see the case of a composite primary key in a first normal table. We take other example here as shown.

This table have first normal form and it has composite primary key. i.e. "Bike_brand - colors".
But it doesn't have second normal form. because the values of column "Price" are dependent only on "Bike_brand" column, not on whole primary key.
So, if we want to make it into second normal form we will separate this column to a new table (with the same column as primary key on which these value s are dependent) as shown below.

Look at the structure of these new tables.

The main table has second normal form now as all non-candiadate columns (here "Discount_percentage") are dependent on whole primary key ( "Bike_brand - colors"). While the new table contains the columns "Bike_brand" (primary key in new table) and "Price" wih primary-foreign key relationship with main table.

Now, The way I remember it is that to make a first normal form (1-N) table to second normal form (2-N), the column with redundant values are taken in new table. And there is many-to-one relationship between main table and new table. We have done same in second example.

I hope this article was some help on the topic. The next post will be discussing the third normal form. bye !

Isolation in Database

This post is to discuss the Isolation in database. It took me a little difficulty to understand this concept as I couldn't find any suitable example over internet. It was explained as a theoretical concept.

So, We'll try to discuss it using an example. But before that let's have a look on a statement on Isolation in my earlier post, "Any two or more transactions on a database are isolated from each other. i.e. they don’t interfere with each other."

Now let's take an example of table named EMP in a database which supports isolation. And we have two users: user X and user Y who frequently access/use this table.

The table contains data as shown in picture above.
In a situation, where user X logins into database and change the Age of Michelle from 19 to 23 as shown in picture below. And this change in the EMP table is uncommitted yet.

Now, consider three scenarios.
scenario(I): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user.
scenario(II): User X commits the change made by him on table EMP. After that User Y logins into database.
scenario(III): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user. After that user X commits the change.

Here is a task for you to guess how the data of table EMP would appear to user Y for each of three scenarios.

Here are the answers.
scenario(I): The data of table EMP would appear unchanged as in first picture to user Y.
scenario(II) and (III): The data of table EMP would appear changed as in the second picture.

Now, hopefully you would have figured out the reason why. Still, here is the explanation.

Only committed data is visible across the sessions. Since the data change was uncommitted for session(I), the last committed data of table EMP (as in first picture) would appear to user Y.

Now after going through this example, we conclude that any database changes/operations/transaction are isolated from each other across different sessions untill and unless those are committed. This is the property of isolation in the database.

Hope you liked my post. Here is more on this topic on Wikipedia

Relational Database: a basic understanding

What is a Relational Database?

It’s a database based on Relational Model. Which was proposed by E.F. Codd

Now in a relational model, the data is stored in the form of tables which look something like this example.

So, the catch here is "the data is stored logically in tabular form".

Table shown in above picture gives the details (i.e. Name, age and city) of three persons and it contains the rows and columns where

• Each Row represents a record having information about a person. e.g. The last row contains Name, Age and City of Katie.


• Each Column represents a category of information. e.g. The second column contains age of all the persons in the example.

So, Basically a Relational database is a collection of such enties which contain data in tabular form as explained in above example.

Now, to use and manage such relational databases (like Oracle, SQL server, etc.), the standard language is SQL (Structured Query language)