{"id":2978,"date":"2024-08-27T12:50:49","date_gmt":"2024-08-27T11:50:49","guid":{"rendered":"https:\/\/contentlabstudy.com\/soft\/?p=2978"},"modified":"2024-08-27T12:50:50","modified_gmt":"2024-08-27T11:50:50","slug":"database-normalization","status":"publish","type":"post","link":"https:\/\/contentlabstudy.com\/soft\/database-normalization\/","title":{"rendered":"Database Normalization"},"content":{"rendered":"\n<p>Database normalization is a process used to organize a database into tables and columns to minimize redundancy and dependency. The first three normalization forms (1NF, 2NF, and 3NF) are foundational in this process:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1st Normal Form (1NF):<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Objective<\/strong>: Ensure that the table structure is simple and clear.<\/li>\n\n\n\n<li><strong>Requirements<\/strong>:\n<ul class=\"wp-block-list\">\n<li>Each table cell contains a single value (no repeating groups or arrays).<\/li>\n\n\n\n<li>Each record (row) must be unique.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>In simple terms, 1NF ensures that the data is stored in a structured format with no duplicate rows and no multiple values in a single cell.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2nd Normal Form (2NF):<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Objective<\/strong>: Eliminate partial dependencies on the primary key.<\/li>\n\n\n\n<li><strong>Requirements<\/strong>:\n<ul class=\"wp-block-list\">\n<li>The table must first meet all the requirements of 1NF.<\/li>\n\n\n\n<li>All non-key attributes (columns that are not part of the primary key) must be fully dependent on the entire primary key, not just part of it.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>In simpler terms, 2NF ensures that each non-key column is dependent on the whole primary key, not just a part of it. This is relevant in tables with a composite primary key (a key made up of more than one column).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3rd Normal Form (3NF):<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Objective<\/strong>: Remove transitive dependencies.<\/li>\n\n\n\n<li><strong>Requirements<\/strong>:\n<ul class=\"wp-block-list\">\n<li>The table must first meet all the requirements of 2NF.<\/li>\n\n\n\n<li>All the attributes must be directly dependent on the primary key. There should be no transitive dependency, where one non-key attribute depends on another non-key attribute.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>In simple terms, 3NF ensures that non-key columns are only dependent on the primary key and not on other non-key columns, thus reducing redundancy and ensuring data integrity.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Examples<\/h3>\n\n\n\n<p>Here are examples to illustrate the first three normalization forms (1NF, 2NF, and 3NF):<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Consider a table containing information about students, courses, and instructors.<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Unnormalized Table (Before 1NF):<\/strong><\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>StudentID<\/th><th>StudentName<\/th><th>Courses<\/th><th>Instructor<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>Alice<\/td><td>Math, Science<\/td><td>Dr. Smith, Dr. Lee<\/td><\/tr><tr><td>2<\/td><td>Bob<\/td><td>English, History<\/td><td>Dr. Johnson, Dr. King<\/td><\/tr><tr><td>3<\/td><td>Charlie<\/td><td>Math, History, Science<\/td><td>Dr. Smith, Dr. King, Dr. Lee<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>In this table, the <code>Courses<\/code> and <code>Instructor<\/code> columns have multiple values, which violates the 1NF rule.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>1st Normal Form (1NF):<\/strong><\/h4>\n\n\n\n<p>To convert to 1NF, we need to ensure that each column contains only atomic values (a single value per cell).<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>StudentID<\/th><th>StudentName<\/th><th>Course<\/th><th>Instructor<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>Alice<\/td><td>Math<\/td><td>Dr. Smith<\/td><\/tr><tr><td>1<\/td><td>Alice<\/td><td>Science<\/td><td>Dr. Lee<\/td><\/tr><tr><td>2<\/td><td>Bob<\/td><td>English<\/td><td>Dr. Johnson<\/td><\/tr><tr><td>2<\/td><td>Bob<\/td><td>History<\/td><td>Dr. King<\/td><\/tr><tr><td>3<\/td><td>Charlie<\/td><td>Math<\/td><td>Dr. Smith<\/td><\/tr><tr><td>3<\/td><td>Charlie<\/td><td>History<\/td><td>Dr. King<\/td><\/tr><tr><td>3<\/td><td>Charlie<\/td><td>Science<\/td><td>Dr. Lee<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Now, each cell contains a single value, and there are no repeating groups. The table is in 1NF.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>2nd Normal Form (2NF):<\/strong><\/h4>\n\n\n\n<p>In 1NF, if the table has a composite primary key (e.g., <code>StudentID<\/code> and <code>Course<\/code>), we must ensure that non-key attributes are fully dependent on the entire primary key.<\/p>\n\n\n\n<p>However, in this example, the table is already structured to avoid partial dependencies because <code>Instructor<\/code> depends on both <code>StudentID<\/code> and <code>Course<\/code>. So, it satisfies 2NF. Let&#8217;s move on to a more common scenario to illustrate 2NF:<\/p>\n\n\n\n<p>Imagine we add <code>StudentAddress<\/code>:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>StudentID<\/th><th>StudentName<\/th><th>StudentAddress<\/th><th>Course<\/th><th>Instructor<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>Alice<\/td><td>123 Apple St.<\/td><td>Math<\/td><td>Dr. Smith<\/td><\/tr><tr><td>1<\/td><td>Alice<\/td><td>123 Apple St.<\/td><td>Science<\/td><td>Dr. Lee<\/td><\/tr><tr><td>2<\/td><td>Bob<\/td><td>456 Orange St.<\/td><td>English<\/td><td>Dr. Johnson<\/td><\/tr><tr><td>2<\/td><td>Bob<\/td><td>456 Orange St.<\/td><td>History<\/td><td>Dr. King<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>In this case, <code>StudentAddress<\/code> depends only on <code>StudentID<\/code>, not on the entire composite key (<code>StudentID<\/code>, <code>Course<\/code>). Therefore, this table violates 2NF.<\/p>\n\n\n\n<p>To convert to 2NF:<\/p>\n\n\n\n<p><strong>Student Table (2NF):<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>StudentID<\/th><th>StudentName<\/th><th>StudentAddress<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>Alice<\/td><td>123 Apple St.<\/td><\/tr><tr><td>2<\/td><td>Bob<\/td><td>456 Orange St.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Enrollment Table (2NF):<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>StudentID<\/th><th>Course<\/th><th>Instructor<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>Math<\/td><td>Dr. Smith<\/td><\/tr><tr><td>1<\/td><td>Science<\/td><td>Dr. Lee<\/td><\/tr><tr><td>2<\/td><td>English<\/td><td>Dr. Johnson<\/td><\/tr><tr><td>2<\/td><td>History<\/td><td>Dr. King<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Now, <code>StudentAddress<\/code> is in a separate table, fully dependent on <code>StudentID<\/code>, ensuring the table meets 2NF.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>3rd Normal Form (3NF):<\/strong><\/h4>\n\n\n\n<p>To convert a table into 3NF, there should be no transitive dependencies (where a non-key attribute depends on another non-key attribute).<\/p>\n\n\n\n<p>Suppose the <code>Instructor<\/code> column can be broken down into <code>InstructorName<\/code> and <code>InstructorDepartment<\/code>:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>StudentID<\/th><th>Course<\/th><th>InstructorName<\/th><th>InstructorDepartment<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>Math<\/td><td>Dr. Smith<\/td><td>Math Department<\/td><\/tr><tr><td>1<\/td><td>Science<\/td><td>Dr. Lee<\/td><td>Science Department<\/td><\/tr><tr><td>2<\/td><td>English<\/td><td>Dr. Johnson<\/td><td>English Department<\/td><\/tr><tr><td>2<\/td><td>History<\/td><td>Dr. King<\/td><td>History Department<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Here, <code>InstructorDepartment<\/code> is dependent on <code>InstructorName<\/code>, not on the primary key (<code>StudentID<\/code> and <code>Course<\/code>), violating 3NF.<\/p>\n\n\n\n<p>To convert this to 3NF, we separate the instructor information:<\/p>\n\n\n\n<p><strong>Instructor Table (3NF):<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>InstructorName<\/th><th>InstructorDepartment<\/th><\/tr><\/thead><tbody><tr><td>Dr. Smith<\/td><td>Math Department<\/td><\/tr><tr><td>Dr. Lee<\/td><td>Science Department<\/td><\/tr><tr><td>Dr. Johnson<\/td><td>English Department<\/td><\/tr><tr><td>Dr. King<\/td><td>History Department<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Enrollment Table (3NF):<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>StudentID<\/th><th>Course<\/th><th>InstructorName<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>Math<\/td><td>Dr. Smith<\/td><\/tr><tr><td>1<\/td><td>Science<\/td><td>Dr. Lee<\/td><\/tr><tr><td>2<\/td><td>English<\/td><td>Dr. Johnson<\/td><\/tr><tr><td>2<\/td><td>History<\/td><td>Dr. King<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Now, all non-key attributes are directly dependent on the primary key, meeting the requirements of 3NF.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Database normalization is a process used to organize a database into tables and columns to minimize redundancy and dependency. The first three normalization forms (1NF, 2NF, and 3NF) are foundational in this process: 1st Normal Form (1NF): In simple terms, 1NF ensures that the data is stored in a structured format with no duplicate rows [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-2978","post","type-post","status-publish","format-standard","hentry","category-software-design"],"_links":{"self":[{"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/posts\/2978","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/comments?post=2978"}],"version-history":[{"count":1,"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/posts\/2978\/revisions"}],"predecessor-version":[{"id":2979,"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/posts\/2978\/revisions\/2979"}],"wp:attachment":[{"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/media?parent=2978"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/categories?post=2978"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/contentlabstudy.com\/soft\/wp-json\/wp\/v2\/tags?post=2978"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}