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.

59 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
  28. From career advancement to simply running your business better - there are many reasons why getting certified as a Microsoft Excel MVP can benefit you. Learn more by reading the following article. pdf to word

    ReplyDelete
  29. Here we discuss the reality of attaining business funding for small businesses.  click here for info

    ReplyDelete
  30. Really enjoyed this article. Really looking forward to read more. Awesome.
    Merchant Service Agent

    ReplyDelete
  31. Credit card processing client portfolios are great for building income over the long term, but don’t do much for those that want to get a fast injection of capital to build businesses. If you want to get a lump sum or a loan against your residual portfolio, you can do that. All you have to do is list your credit card processing business for sale and get a buyer or lender to borrow you money or give you money against your portfolio assets. Payment Processing Business for Sale

    ReplyDelete
  32. Thanks for sharing!
    https://windowsactivators.org/microsoft-office-2016-product-keys/

    ReplyDelete
  33. buy twitch followers Time... It's become such a valuable commodity for many of us that we base all of our daily activities around how much time we have to spare. I'm sure we've all had to cut something out of our daily routine just because there's not enough time. When you're a young adult like me, with a full time job and other responsibilities, it's honestly quite hard to find time to play video games, which is a pity because I love video games. This list is designed to show you which game genres take the most time to play through, starting with the least.

    ReplyDelete
  34. I think your blog is getting more and more visitors.,-~;, Casino Online

    ReplyDelete
  35. Spot up for this write-up, I really think this fabulous website needs considerably more consideration. I’ll apt to be once more to read a great deal more, thank you for that info. Federal Champion Ammunition 45 ACP 230 Grain Full Metal Jacket Aluminum Case

    ReplyDelete
  36. https://www.visualaidscentre.com/service/eyes-specialist-delhi/ There is a myth among some people that video games are not 'healthy' for kids, but let's assure you that it is not really the case. As a matter of fact, online games can be rather healthy and beneficial to them, provided that they are enjoyed with certain guidelines. Moreover, family members can also get involved, and enjoy gaming together.

    ReplyDelete
  37. Many doctors suggests a topical steroid for eczema flare ups. But what is a topical steroid? It is any cream, ointment or lotion that contains a steroid. They're employed by diminishing inflammation in the skin and are used for assorted skin rashes including eczema. These topical treatments are often called corticosteroids. see this

    ReplyDelete
  38. Business landlines are soon becoming a thing of the past. But are small businesses really willing to put them into oblivion and welcome VOIP technology and business mobile phones for their small businesses? bulk salt

    ReplyDelete
  39. https://www.visualaidscentre.com/lasik-eye-surgery-in-delhi/ What elements push a game beyond mere goodness and into greatness? To this author, PC games are best when they deliver a transcendent gaming experience that is possible only with the aid of a personal computer: They don't simulate board or card games, reproduce real-world sports, or try to approximate movies. They are an art form unto themselves. To be considered, a game must have achieved most of its prominence on a PC platform. (This explains why Tetris, for example, didn't make the cut: It was clearly the Nintendo Game Boy's killer app). I defined a "PC" as any consumer computer that has a keyboard the user can program with arbitrary code--not just a PC of the IBM variety. If you're into PC games, check out "15 Reasons PC Gaming Beats All," too. And if you want to stay current with the latest games, tune into PC World's ace gaming blog, Game On! Without further ado, here is the Top Tens List for the Greatest PC Games in the world...

    ReplyDelete
  40. https://hostinglelo.in A new wave of fishing has become a favored pastime and it involves the gaming industry. While fishing is an ancient sport, online fishing games are a new fad and this allows a whole new atmosphere to enjoy the thrill of catching fish. The article discusses the new world of online fishing.

    ReplyDelete
  41. https://onohosting.com Does this sound familiar? You're playing your favourite game, and getting pretty good at it too, then suddenly the screen goes blank and that fateful message appears "DISC READ ERROR". All of a sudden you get extremely frustrated really fast and all you can do is let out a scream...

    ReplyDelete
  42. You don't have to be a programmer to make a basic web site for yourself. Whether it is business or personal, your web site does not need to be fancy. If you can try out a basic web site builder, you will know just what you like and how much time and effort it takes to create your web site survey maker

    ReplyDelete
  43. There are many things to think about if you want to be a paintball sniper. These things include going, light, the way you shoot and maneuver, play with a team, and more. It is important to take everything you need with you when you are on the fiel. check that

    ReplyDelete
  44. Many develop trauma symptoms of PTSD - post-traumatic stress syndrome, with painful memories and flashbacks similar to a war veteran. Physical health may be impacted as well. The ACE ("Adverse Childhood Experiences") study found a direct correlation between adult symptoms of negative health and childhood trauma. ACE incidents that they measured included divorce, various forms of abuse, neglect, and also living with an addict or substance abuse in the family. Children of addicts and alcoholics usually experience multiple ACEs. Purple Candy

    ReplyDelete
  45. Once the Stimulus Bill, you think that whether any bank or financial institution is making loans to small businesses. extra resources

    ReplyDelete
  46. It’s hard to search out knowledgeable individuals on this subject, but you sound like you realize what you’re speaking about! Thanks Excel can hack the blast game !!!

    ReplyDelete
  47. Today's business climate is quite challenging for small businesses. It isn't difficult for small company keepers to lose their way momentarily and suddenly become embroiled in negative financial spiral which could threaten the businesses very survival.  look here

    ReplyDelete
  48. You can borrow money in accordance to your financial status and repayment capacity. 작업대출

    ReplyDelete
  49. Tidycal is automated meeting schedule software that helps you schedule meeting efficiently and easily. you have interest visit my website link https://ltddeals.in/tidycal-appsumo-ltd-review/

    ReplyDelete
  50. The word for any guarantee such as this is known as 'file segregation'. No-one can provide you with a brand new interpersonal protection quantity however the Interpersonal Protection Management workplace. md credit repair

    ReplyDelete
  51. There's also a large number of identification thefts which happen every day. md credit repair

    ReplyDelete
  52. Steroids work to promote growth of skeletal muscle and the development of male sexual characteristics in both males and females. Tren-A

    ReplyDelete
  53. Wow, cool post. I'd like to write like this too - taking time and real hard work to make a great article... but I put things off too much and never seem to get started. Thanks though. data archiving
    Wow, cool post. I'd like to write like this too - taking time and real hard work to make a great article... but I put things off too much and never seem to get started. Thanks though. data archiving

    ReplyDelete
  54. I like the valuable info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I’m quite certain I will learn a lot of new stuff right here! Best of luck for the next! Please visit my web site firstaidcourseonline.com.au. Best allens training sunshine coast service provider.

    ReplyDelete

Popular Posts