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.

107 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. 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
  8. 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
  9. Subsequently, it is imperative to focus on the client care offered to solidify the achievement rate wanted. Press Release Distribution

    ReplyDelete
  10. 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
  11. 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
  12. On that website page, you'll see your description, why not read through this. SPSS assignment help online

    ReplyDelete
  13. 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
  14. 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
  15. 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
  16. 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
  17. "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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. Nice Post. It’s really a very good article. I noticed all your important points. Thanks!! 꽁머니 홍보방

    ReplyDelete
  24. 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
  25. 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
  26. 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
  27. 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
  28. Here we discuss the reality of attaining business funding for small businesses.  click here for info

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

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

    ReplyDelete
  31. 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
  32. 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
  33. 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
  34. 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
  35. 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
  36. 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
  37. 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
  38. 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
  39. 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
  40. 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
  41. Once the Stimulus Bill, you think that whether any bank or financial institution is making loans to small businesses. extra resources

    ReplyDelete
  42. 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
  43. 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
  44. You can borrow money in accordance to your financial status and repayment capacity. 작업대출

    ReplyDelete
  45. 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
  46. There's also a large number of identification thefts which happen every day. md credit repair

    ReplyDelete
  47. This comment has been removed by the author.

    ReplyDelete
  48. This comment has been removed by the author.

    ReplyDelete
  49. Dubbed as one of the fastest growing social media network, TikTok has gained fame at a massive rate with more than 1 Billion monthly active user.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  50. Thanks to its addictive and engaging video sharing template, it continues to gain popularity at a massive yet consistent rate.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  51. Despite being banned in several countries, it seems that this growth will not stop any time soon.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  52. The app has been growing in popularity recently, with users of all ages flocking to the platform to create and consume content.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  53. One of the big questions people have about TikTok is how many people you can follow.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  54. Mass following can cause major issues in the ecosystem with irregular distribution of followers among the creators, resulting in the negative impact on user experience.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  55. Another reason for the limit is to prevent users from being overloaded with content.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  56. TikTok can help ensure that users have a more positive experience on the app.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  57. If you’re using the app TikTok, you might wonder how many accounts you can follow in an hour.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  58. The answer is that you can safely follow up to 30 accounts in an hour. Any more than that, and you risk getting a warning from TikTok that you are following too fast.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  59. TikTok has a limit of 200 accounts that a user can follow in a day. This is to prevent spammers from following a large number of people in a short period.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  60. If you try to follow more than 200 people in a day, you will receive an error message telling you that you’ve reached the limit.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  61. This is done to combat any kind of unfairness and “follower bots” on the platform.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  62. If you see this message, it doesn’t mean that your account has been banned. TikTok is just limiting the number of creators you can follow in a short time to prevent any kind of spam and abuse.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  63. Whenever this happens, try to take a break from following people, or do so slowly, this is to ensure that you don’t get the warning again and again which can lead to bans in extreme cases.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  64. On top of all that, as the network of small independent businesses expands, so will the ability of every Loyalty member to shop in any of these outlets, and get INSTANT GRATIFICATION, as any of these small businesses who are also Loyalty merchants, will have the ability to enable shoppers to have the choice of money or Reward Points (or a combination) to pay for their shopping. WHERE TO BUY ORAL AND INJECTABLE STEROIDS ONLINE

    ReplyDelete
  65. You possess lifted an essential offspring..Blesss for using..I would want to study better latest transactions from this blog..preserve posting.. Data recovery

    ReplyDelete
  66. Websites are similar to AnimeTake where you may watch animation online.
    Blogging
    Blogging
    Blogging
    Blogging
    Blogging

    ReplyDelete
  67. This comment has been removed by the author.

    ReplyDelete
  68. Considering all the data above, you might feel that beginning a gold business in Dubai isn't excessively mind boggling.
    GRND

    ReplyDelete
  69. You want a business plan to make your business thought a reality. In the event that you hope to look for money related help from a financial backer or monetary association, a formal composed business plan is an unquestionable necessity. concrete

    ReplyDelete
  70. Albeit many individuals adventure into a home candle making business because of benefit, it is irrefutable that many do this for the sake of entertainment. https://www.lakelandflconcrete.com/

    ReplyDelete
  71. When composed, the business plan will act as an inner apparatus to survey the administration of the organization and its deviations from the arranged situation.
    it asset disposition provider

    ReplyDelete
  72. The essential of your web busniess is giving helpful data on fishing procedures or fly fishing: to show individuals how to do, and so on.
    tow near me

    ReplyDelete
  73. I value the article post. Thanks Again. Much obliged.
    chen zhi in cambodia

    ReplyDelete
  74. Thanks for sharing; this is a fantastic blog article. Much thanks again. Fantastic.
    neak oknha chen zhi

    ReplyDelete
  75. This would frame what occurs in the event that an accomplice wishes to leave the business.
    Auto Glass Shop

    ReplyDelete
  76. I received my license and certification as soon as I finished school for massage therapy.I read a lot of stuff and i found that the way of writing to clearifing that exactly want to say was very good so i am impressed and ilike to come again in future. Really looking forward to read more. Awesome. <a href="https://www.careuk.com/>Home care bussiness</a>

    ReplyDelete
  77. This will incorporate readiness of the necessary changes and cycles to welcome the business in the groove again.
    learn more

    ReplyDelete
  78. Really enjoyed this post. Thanks Again. Great. chen zhi in cambodia

    ReplyDelete
  79. Industrial merchants increment the worth of this supply chain by offering one single resource for end-clients that end up being obtaining various industrial items.
    Concrete formwork

    ReplyDelete
  80. You've introduced WordPress. You've gone through hours looking for the right subject. You've redone your establishment with gadgets, modules and custom settings.
    www.wordpressctapro.com

    ReplyDelete
  81. With regards to family wealth management, it's in every case better to have the mastery of a few experts each functioning out the particulars of a family trust or investments.
    Exponent

    ReplyDelete
  82. USDA mortgage financing costs - USDA mortgage loan fees are additionally connected to Ginnie Mae got securities (similarly as FHA and VA mortgage rates are). Of the three, nonetheless Sean Rankin

    ReplyDelete
  83. Today, online PR has been integrated inside a more extensive SEM setting that incorporates SEO, PPC, and online standing administration (ORM). For instance, an official statement can be dispersed web-based to build up some decent momentum inside the regular postings. RyanCameron

    ReplyDelete
  84. A Hole Channel Film framework is applied to the inner essence of the earth holding structure where it can acknowledge the entrance of water depressurise it and oversee it to a protected departure point. This is alluded to as type C type of storm cellar waterproofing. https://ecfoundations.com

    ReplyDelete
  85. Try not to be tricked only by the material that certain individuals think holds wealth.
    Exponent

    ReplyDelete
  86. Supply chain management is essential for businesses to ensure that they have access to the resources and materials needed to operate and meet customer demand. photo booths columbus ohio

    ReplyDelete
  87. This is one awesome blog. Really looking forward to read more. Really Great. cambodia chen zhi prince group

    ReplyDelete
  88. SEO can help businesses build brand awareness and establish themselves as an authority in their industry. RyanCameron

    ReplyDelete
  89. I cannot thank you enough for the article. Thanks Again. Fantastic.
    cambodia prince chen zhi

    ReplyDelete
  90. This is wonderful and quite informative blog I have learnt so many things from here.
    streameast.live

    ReplyDelete
  91. Appreciate you sharing great article post. Really thank you! Great.
    neak oknha chen zhi

    ReplyDelete
  92. This comment has been removed by a blog administrator.

    ReplyDelete
  93. This comment has been removed by a blog administrator.

    ReplyDelete
  94. This comment has been removed by a blog administrator.

    ReplyDelete

Popular Posts