Database 1- Basic Concepts of Normalization

Normalization is an important concept in database design, which helps to simplify the overall design and help to extract, update and delete any data from tables in future. Usually if a table contains too many columns then there is possibility that it will create complexity to maintain data in that database and we need to decompose the table based on the functional dependencies between the columns in table and normal forms. 

In this series of post, I will discuss the basic concepts of normalization and provide an example with explanation. I will use some of the references we were provided with in our LIS490 Introduction to Databases course at GSLIS. I give the credit to Professor John Weible for the reading reference and the sample problem. The solution is completely based on my own understanding of normalization. 

We first start with the qualities of a good database design. There are three basic qualities that should be maintained. 

1. Good Semantics, 

2. Avoiding Redundancy, 

3. Minimum Use of Nulls.

To ensure this, we need to make sure that the non-key fields in our tables cannot have different primary keys or are not closely related to the primary key. And this is the key concept of Normalization and the rules are called normal forms. The available rules are:

1. First Normal Form (1NF)

2. Second Normal Form (2NF)

3. Third Normal Form (3NF)

4. Boyce-Codd Normal Form (BCNF or 3.5 NF)

5. Fourth Normal Form (4NF)

6. Fifth Normal Form (5NF)

Usually it is good enough to understand till 3NF or BCNF and the rules higher than this get more complicated and are generally not implemented. In this post we will discuss till 3NF.

1NF: It means that all values in the tables must consist of same number of fields. In general the fields should be single-valued and not atomic. For example, the first row of Name column and second row of Hobby column is violating first normal form.

Name	Hobby
Nushrat, Khan	Painting
Brishti	Cooking, Painting

2NF: This rule means all the non-key elements should depend on the whole key. It needs to be considered if there is more than one key in the table. The important thing to remember here is, 2NF is violated when a non-key field is a fact about a subset of a key. We will see the example of this rule in our main example. 

3NF: Third Normal Form is all non-key fields will tell fact only about the key. In other words, there should not be any dependency between non-key elements. The important thing to remember here is, 3NF is violated when a non-key field is a fact about another non-key field.

Functional Dependency: Functional dependency between columns is another important concept that needs to be considered. If the functional dependency violates any normal form for an existing table then it needs to be further decomposed. 

The definition is, for two columns X and Y, Y is functionally dependent on X if for any combination of X and Y, the value of X will always be unique and cannot be repeated where Y can be repeated.

The following table violates the rule of functional dependency because if we want to update any information about 'Brishti' then it can cause anomaly in both of the rows.

Name	Hobby
Brishti	Painting
Brishti	Photography

But in the table below Hobby is functionally dependent on Name.

Name	Hobby
Barsha	Singing
Brishti	Painting
Barno	Painting

A classic reference to read more about normalization.

W. Kent, "A Simple Guide to Five Normal Forms in Relational Database Theory", Communications of the ACM 26(2), Feb. 1983, 120-125 (bkent.net/Doc/simple5.htm)

Another source from Microsoft: http://support.microsoft.com/en-us/kb/283878

In the following post I will give an example of a table that violates the normal forms and show how to decompose that based on the concept of functional dependency and normal form.

Squirrels - Adorable Faces of UIUC Campus

Squirrels - Who doesn't love these cute animals? They are not afraid of humans nor do they cause any harm. And we have plenty of them at our UIUC campus, specially at the Quad area. With their small bodies and proportionally big fluffy tails, they move swiftly from branches to branches or play on the ground. To me they are the most entertaining creatures on campus, cause during breaks most of the students go home and any human being can be barely seen around. But the squirrels are always here, adding more life to the green nature and entertaining with their adorable faces.

I always enjoy watching the squirrels playing. But I was wondering how did they get here and how did the population of squirrel grow so big at this campus area? According to University of Illinois Archives, there were not many of them here before 1900. On May of 1901, University President Andrew S. Draper along with Professor Geology Charles W. Rolfe took the initiative to increase their number by constructing breeding cage with several compartments on University lawn. And today we definitely feel thankful to them as their step towards securing the number of squirrels resulted into a more lively campus life of today's.

From University of Illinois Champaign-Urbana Photo-Gravures, ca. 1912

(This image is in public domain)

I personally love taking nature photos and animals are part of that. But I did not have much time during weekdays to walk around campus and take photos since I started grad school here. On this Christmas day the weather was really nice and I thought I would take a walk around the quiet campus and enjoy company of cute squirrels. Then I could capture some photos of them. And one of the squirrels looked so curious as I was taking photos that it looked straight at me and gave a pose for a moment. I really adore that photo and it turned to be the first photo of mine to get Explored on Flickr!

Definitely that was quite motivating to take more photos of them :)

My first Explored photo on Flickr 

Mind & Space

Went for a trip to Art Institute of Chicago this weekend and saw this girl at the photography exhibition space. With a lot of classic photos in front of her, she was probably lost in virtual space. She was there physically but her mind was somewhere else when I saw her. Don't know what she was doing or whether she was thinking something else but it's interesting how these days with all smartphones, tablets etc our mind can be divided into multiple places at the same time...whether it's a good thing or bad? not sure..

The Transition-2

Photo taken by me during fall at UIUC

It's never easy to take those big steps in your life, like deciding to change country while you are already in a comfortable place. It wasn't easy for me either to finally take the decision to leave Japan cause after living for five years there, I knew I had a second home in a foreign country, I knew there were people whom I could call my family and there were my friends, seniors, juniors and many more well wishers. And then there was this big decision to make - whether to choose a better academic path for future career though there can be a lot of uncertainties, or to continue living at a place surrounded by people's love with prospective career opportunities?

So, I chose the first route for my life. Leaving my school, favorite campus, friends, scholarship, work, I decided to move to USA. I have joined Graduate School of Library and Information Science at University of Illinois at Urbana-Champaign from mid-August of 2014. In that sense this post was due a long time ago, but it took me some time (actually a semester!) to settle down in this new place, find out some extra time from school and work and finally sit down and think what I have got and what I have not.

At first I will write about some of the difficulties any usual student in Japan might face during this transition.

• In Japan, semesters usually start from April and all the procedures to start at an institution has to be done by March. And the entrance exams take place in August of the previous year. Though some universities like Tokyo University, University of Tsukuba have started Fall semester in October for foreign students at a small scale, but this is not the standard case in Japanese educational system. Now, in my case, I was not sure whether I am going to join any university in USA till June, until my Graduate Assistantship was fixed. So I had to face a huge problem to cancel my admission and scholarship. Though my professors were by my side, but these systems are very rigid in Japan and once someone is in the system, it is very difficult to come out of it without huge pay off. Again, if I decided to stay in Japan then that would not be a problem, I could just follow the rules. But to them I was an exception, and Japanese administrative system are not good with handling exceptions. So I faced a lot of stress and troubles just to get out of the institution and cancel my scholarship.

• Because of the time gap between the starting time of sessions of two countries, someone has to lose 4-5 months doing nothing (or may be doing fun!). In my case, I attended class for the first semester there and submitted my paper at conference and got accepted there, so worked positive for me though I had to go through those administrative complexities later. 

• Now fixing housing from Japan was another difficult task to do. It's already difficult to do that while you live in the country, contact the landlords and find a trustworthy leasing agent. I had good luck and found a very good place from Wampler Apartments. I would recommend them to anyone who is searching for housing in Urbana-Champaign area.

• Once you get your housing fixed, you have to start the electricity, gas and water service at your place. In Japan I found it comparatively easy, just call them and they will do everything for you. But USA is too big and different states have different rules. When I called them for starting service from Japan they asked me for my Social Security Number, then send notarized copy of ID card etc. Any of which I did know how to get done in Japan, also in that short amount of time. So the summary is, I was in trouble. Glad that the person at Wampler Apartment was very understanding and helped me out with this matter by saying that I can take my time and he understands that these are difficult for international students to handle. 

• Closing all the contracts/ services in Japan took a lot of time, effort and money. To close my phone contract with Softbank I had to pay thousands of extra money.

• Packing and sending all the important stuff from Japan - I am still afraid to think about. Buying all those big cardboard packages from post office, packing and sending them cost me huge amount of time, effort and money again. 

• Finally I booked my bus ticket to go to campus from Chicago and booked hotel room for the night cause the landlord's office will close by the time I will reach and I needed a place to stay for the night.

I am usually little reluctant to take help from others as long as I can handle them by myself. So doing all these things by myself was pretty difficult and I was extremely stressed by the time I was done and could not believe I was actually leaving Japan and feel the pain of tearing the bond. But yes, finally I got here and started a completely new life at a new school with new people and of course, with new struggles. It is not all bad but like I said, there is no perfect place to live on earth. So I am always trying to find the best way to enjoy my life no matter where I am.

And here is something I created as a guide for new international students who are here or will be joining UIUC. I created this libguide as my final project for LIS501 course but I hope to expand it in future as my personal project so that I can help future students be well informed before they land in this city and start their new life.
Guide for International Graduate Students at UIUC

I will keep posting about my experience at UIUC, stay tuned! :)

The Transition-1

A question that I am commonly asked by people I meet for the first time is, why did I choose to study in Japan? And I guess I never gave the proper answer to anyone! Well, I came up with a standard answer over time (cause you know you don't always have the power to make up instant stories!). But at this point of transition, when I am taking another big step of life, I guess I have figured out why I left home in 2009. And this story is for those who might be considering moving to/ studying in Japan. It's true that not everyone will have the same experiences but this might give a little insight of how is it to live in a country like Japan for 5 years.

Looking back...
Stable and secured life probably never attracted me. Like many other challenge loving persons I was always enchanted by the mystery of unknown and test my ability and patience by choosing a rather arduous path. And that is one of the reasons along others why I decided to move to Japan.
Back home I had a pretty much perfect life (well, there is nothing called "perfect life" but it was not bad at all), I just got into my dream institute BUET at the Department of Architecture. I was topping in class and already started to dream of future me as an architect. We moved to our new beautiful house and I had my own room where I could work all day and night. It was not easy to take that much of workload, but its never easy for students of Architecture in any country in this world.
But one of the factors that was not under my control was the political turmoil in our country. The university would often close down because of it and that caused session jam. Students could not graduate when they are supposed to, 5 years Architecture course would often take 6-7 years. We were always little concerned about that, and that's when I got to know about MEXT (Monbusho) Scholarship of Japan.
Later I applied for the scholarship, passed the first selection and was accepted to sit for written exam. I also managed to pass the written exam though I thought my exam was terrible and finally went through the interview. And I got chosen as one of the 3 students (in my program) for the scholarship.

But it's never easy to leave everything behind, your home, family, friends... The reasons why I chose to go to Japan were-

• I might lose extra years for session jam if I stay in my country, so even if I have to spend one extra year to learn Japanese its ok, cause I am actually investing my time for learning something new, something I can treasure.
• Since I will be fully supported by scholarship I will learn to live independently. Though it does not cost much (almost zero) to study at public institutions in Bangladesh, I never wanted to depend financially on my parents. 
• Most importantly I will be able to broaden my perspective by mixing with international community, learn about unique cultures and be able to gain more knowledge about Japan and other countries.

So on April 2nd, 2009 I left home for Japan. I still remember my first morning in Tokyo. Chilly breeze with warm sunshine of early spring, high rise buildings at Shinjuku, white and pink cherry trees - I fell in love with Japan at the first place and I loved Japan more each day till August 14th, 2014 when I left.
It's true that there is no perfect place to live on this earth. But I would say Japanese people has taken it pretty close to ultimate perfection. I learnt to appreciate nature and beauty more, learnt to be more punctual and be attentive to details while my stay in Japan.
But there are always compromises, things that you will lose to achieve something. I have those too, but I won't probably regret spending the most of my youth in Japan. And if you are open to learning a new language and culture, you won't regret as well.

I am writing this post from USA where I'm going through my second great transitional period of life, meeting new people, having new experiences everyday. But I will leave those stories for next post.

Before that, here are some (interesting) facts/incidents from my early days in Japan!

• My friends used to think Japanese people eat only raw fish, which is not true. They have varieties of simple and healthy food. And unlike many friends of mine I loved Sushi and Sashimi from the very first time!
• I heard skirt was first invented for male population but I saw a guy wearing skirt for the first time in Tokyo. 
• Japanese people are really kind and generous. Once I went to supermarket and saw an old person dropped the egg box she bought while leaving. But the store assistant came running and exchanged that for a new box of egg for free.
• Japanese people are also very honest. If you lose something somewhere you are more likely to find it back. One of my friend forgot his iPhone at restaurant once but it was right there when he went back after hours.
• Japanese people don't use English or other foreign language much but they will never make fun of your broken Japanese. They will always listen to you carefully, try to understand what you are trying to convey and tell you "Sugoi ne" (Wow! You're brilliant!)

I guess these stories will never end. But this is all for today.
日本大好き！