Tuesday, June 2, 2015

SQL - Normalization: First Normal Form (1NF)


Normalization is a set of rules that are to be applied while designing the database tables which are to be connected with each other by relationships. Database normalization is a technical term to improve the data accuracy and efficiency and reduces the data redundancy and inconsistent data dependency.
Definition of Normalization
We organize the data into database tables by using normal forms rules or conditions. Normal forms help us to make a good database design. Generally in the real practice, we organize the data up to third normal form. We rarely use the fourth and fifth normal form. These are the Normal forms of the normalization –
4)     Boyce Code Normal Form (BCNF)
5)     Fourth Normal Form (4NF)
6)     Fifth Normal Form (5NF)

For the examples, we’ll use table Reservation Staff Information as shown below UNF as a starting point.  As we pointed out in the last post’s modification anomalies section, there are several issues to keeping the information in this form.  By normalizing the data you see we’ll eliminate duplicate data as well as modification anomalies.

Reservation Staff
Project Code
Project Name
Project Manager
Project Budget
Employee1
Employee2
Employee3
PC001
Reservation
Mike Towery
$125,000
Ryan Arjun
Will Smith
Bill Gray
PC002
Attendance
Chris Gray
$155,000
Tony Towery
Chao Milk
Red Bill
PC003
Human Resources
Kimmy Wang
$225,000
Lucy Gray
Chris Crow
Tom Ramsay

First Normal Form (1NF)
A database table is said to be in 1NF if it contains no repeating fields/columns. Once a table is in first normal form it is easier to search, filter, and sort the information. The process of converting the UNF table into 1NF is as follows:
1)  Separate the repeating fields into new database tables along with the key from unnormalized database table.
2)     The primary key of new database tables may be a composite key.
When a value is atomic, the values cannot be further subdivided.  For example, the value “Reservation” is atomic; whereas “Reservation; Attendance; Human Resources” is not. Related to this requirement is the concept that a table should not contain repeating groups of columns such as Employee1, Employee2 and Employee3.
This example table is transformed to first normal form by placing the repeating customer related columns into their own table.  This is shown below:
The repeating groups of columns now become separate rows in the Reservation Staff table linked by the Emp ID foreign key.  As we know that a foreign key is a value which matches back to another table’s primary key.
In our case, the Reservation Staff table contains the corresponding Emp Id for the Employee Master now.
Employee Master
Emp Id
Employee Name
EmployeeDept
EmployeeRate
E0001
Ryan Arjun
Database
240
E0002
Tony Towery
Testing
230
E0003
Lucy Gray
IT
250
E0004
Will Smith
Database
245
E0005
Chao Milk
Testing
225
E0006
Chris Gyal
IT
210
E0007
Bill Gray
Database
190
E0008
Red Bill
Testing
210
E0009
Tom Ramsay
IT
200
Primary Key for the above table is Emp Id 

Reservation Staff
Project Code
Emp Id
Project Name
Project Manager
Project Budget
PC001
E0001
Reservation
Mike Towery
$125,000
PC002
E0002
Attendance
Chris Gray
$155,000
PC003
E0003
Human Resources
Kimmy Wang
$225,000
PC001
E0004
Reservation
Mike Towery
$125,000
PC002
E0005
Attendance
Chris Gray
$155,000
PC003
E0006
Human Resources
Kimmy Wang
$225,000
PC001
E0007
Reservation
Mike Towery
$125,000
PC002
E0008
Attendance
Chris Gray
$155,000
PC003
E0009
Human Resources
Kimmy Wang
$225,000
Composite Key  ( Unique Key) = Project Code + Emp Id

This design is superior to our original table in several ways:
1. The original design limited each Reservation Staff entry to three employees.  In the new design, the number of employees associated to each design is practically unlimited.
2. It was nearly impossible to Sort the original data by Reservation Staff.  You could, if you used the UNION statement, but it would be cumbersome.  Now, it is simple to sort customers.
3. The same holds true for filtering on the Reservation Staff table. 
Modification anomalies remain in both tables, but these are fixed once we reorganize them as 2nd normal form.

30 comments:

  1. Extraordinary Accounts Payable: Outstanding records payable (A/P) shows the parity of money you as of now owe to your providers.
    Team Collaboration Software

    ReplyDelete
  2. Will you be a competent computer programmer feeling disillusioned with having to go looking over sixty miles each day forwards and backwards through metropolis office? ycombinator startup templates

    ReplyDelete
  3. Attractive, post. I just stumbled upon your weblog and wanted to say that I have liked browsing your blog posts. After all, I will surely subscribe to your feed, and I hope you will write again soon! microsoft office 2016 product key

    ReplyDelete
  4. The entrance to your database can be constrained by a far off machine that can totally control what a client can see.Change Site URL Using MySQL

    ReplyDelete
  5. I like your post. It is good to see you verbalize from the heart and clarity on this important subject can be easily observed... ms office 2016 free download full version with product key

    ReplyDelete
  6. I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post. https://europa-road.eu/hu/foldmunkagep-szallitas-trelerrel.php

    ReplyDelete
  7. Hi! This is my first comment here so I just wanted to give a quick shout out and say I genuinely enjoy reading your blog posts. Can you recommend any other Beauty Guest Post blogs that go over the same topics? Thanks a ton!

    ReplyDelete
  8. This is to a great extent because of the groups included gathering their own objectives however not adjusting to the corporate destinations of an association. nyc budget

    ReplyDelete
  9. I received my license and certification as soon as I finished school for massage therapy. I knew right away that I was going to start building my own practice out of my house as soon as I finished school (which is why a prerequisite of the home I was purchasing had to have an extra room for my business space). I built up my clientele and was quickly recognized as one of the top massage businesses in the area. Business Review

    ReplyDelete
  10. Subsequently, it is imperative to focus on the client care offered to solidify the achievement rate wanted. Press Release Distribution

    ReplyDelete
  11. In this write up, the readers will get information on future technology and learn to what extent modern technology has been developed to assist the growth of human civilization. Technology of the Future would be more sophisticated and user friendly. The rapid technological advancement will make technology more convenient and usable. writing for web design projects

    ReplyDelete
  12. The Home Business industry is a multi-billion dollar industry, but unfortunately the vast majority of people (more than 95%) never achieve any level of success with it. Why? Because they don't know the 6 keys to home business success, and they give up before they see results. مصور مشاريع

    ReplyDelete
  13. On that website page, you'll see your description, why not read through this. SPSS assignment help online

    ReplyDelete
  14. I’m your to get many of the content, I actually unquestionably savored, I will seriously have a preference for extra details pertaining to the following, seeing as it is really excellent., Thank you created for writing. http://www.fokuszpalyazat.hu/

    ReplyDelete
  15. I received my license and certification as soon as I finished school for massage therapy. I knew right away that I was going to start building my own practice out of my house as soon as I finished school (which is why a prerequisite of the home I was purchasing had to have an extra room for my business space). I built up my clientele and was quickly recognized as one of the top massage businesses in the area. climate change

    ReplyDelete
  16. Albeit still an independent set-up of projects, Office 2013 is more cloud-based than past forms. For example, the default save area is the client's online OneDrive (Microsoft's distributed storage administration). help for Excel

    ReplyDelete
  17. your blog is so attractive and informative. I like your post very much. Thanks for sharing this useful blog. If you want to explore or know about purple eyes

    ReplyDelete
  18. "Semi-Automated" tools may put a graphical interface on some Automated usefulness gave by a "manual" tool, yet will in any case require hand-coding or potentially prearranging to test more perplexing develops. targonca szállítás Debrecen Europa-Road Kft.

    ReplyDelete
  19. A great number of small business owners have a constant urge to expand. Of course, the mere idea of expanding and becoming a "big" business known to thousands and millions of people is fascinating. However, expansion is not all about finding a new office space and hiring new employees to fill the vacant seats. best cryptocurrency Reddit

    ReplyDelete
  20. Subsequent to the development of software, companies evaluate the effectiveness of software by carrying out rigorous testing. This is a necessity for the IT industry and companies utilize different types of software testing methodology along with various QA/QC techniques to ensure that the performance requirements of new software and applications meet existing standards. This article discusses some of the common software testing methodology used by companies to ensure viability of newly-developed software/applications. windows 10 activation key

    ReplyDelete
  21. Some deviation from this age previous concept is permissible. For instance, if you have a long term importance of functioning capital, then a permanent capital need might be warranted. Another great finance strategy is having contingency capital on hand for liberating up your functioning money wants and providing maximum flexibility. https://www.accreditloan.com/

    ReplyDelete
  22. Microsoft Corporation definitely has contributed in the development of the IT industry. Microsoft Certifications are provided for the simplest of the software programs to right up the most complicated of the network services. Microsoft Certification are worthy of appreciations, as it bring value to the organizations. Also, these certifications are also packed with the immense recognition that one requires to excel in one's career. It provides the organization with the needed validation of one's skills... windows 10 activation key

    ReplyDelete
  23. whoah this blog is great i love reading your articles. Keep up the great work! You know, a lot of people are hunting around for this info, you could aid them greatly. buy steroids

    ReplyDelete
  24. Nice Post. It’s really a very good article. I noticed all your important points. Thanks!! 꽁머니 홍보방

    ReplyDelete
  25. The maximum not unusualplace manner to fund a small commercial enterprise is to invite your pals or own circle of relatives for a mortgage.Alternative Funding

    ReplyDelete
  26. The mobile phone is a significant and indispensable communication tool for small entrepreneurs. It can be useful when following up with suppliers and customers, or when communicating with employees, and a way to make their small business accessible to their potential customers. Freedom Insurance

    ReplyDelete
  27. Pond have got onto your website although implementing observe simply simply some bit submits. Soothing strategy for forseeable future, I will be bookmarking currently obtain types perform will come apart. https://mytechmore.com/how-to-fix-hard-drive-not-showing-up-problem-in-windows-10/

    ReplyDelete

Popular Posts