{"id":141,"date":"2011-07-03T00:18:41","date_gmt":"2011-07-03T00:18:41","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=141"},"modified":"2019-06-02T18:45:37","modified_gmt":"2019-06-02T18:45:37","slug":"normalization-3nf","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/normalization-3nf\/","title":{"rendered":"Normalization, 3NF"},"content":{"rendered":"<p>Previously, I normalized my table to <a href=\"\/q\/normalization-2nf\/\">Second Normal Form (2NF)<\/a>.  Now, I want to continue to normalize such that the tables in my schema are in Third Normal Form (3NF).<\/p>\n<p>3NF requires that every non-prime attribute is directly dependent on every candidate key. Fortunately, normalizing to 2NF accomplished this for all but one table, i.e.,<\/p>\n<pre class=\"sh_sql\">-- 2NF, but not 3NF\nCREATE TABLE foobar_logins (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    login_id VARCHAR(20) NOT NULL,\n    login_pw VARCHAR(32),\n    PRIMARY KEY(username, domain)\n);\n\n-- already 3NF (status and dept directly depend on username)\nCREATE TABLE foobar_users (\n    username VARCHAR(20) NOT NULL,\n    dept VARCHAR(20) NOT NULL,\n    status VARCHAR(12) NOT NULL,\n    PRIMARY KEY(username)\n);\n\n-- already 3NF (there are no non-key attributes)\nCREATE TABLE foobar_partners (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    website VARCHAR(255) NOT NULL,\n    partner VARCHAR(20) NOT NULL,\n    PRIMARY KEY(username, domain, website, partner)\n);\n\n-- already 3NF (there are no non-key attributes)\nCREATE TABLE foobar_role_map (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    role VARCHAR(12) NOT NULL,\n    PRIMARY KEY(username, domain, role)\n);\n<\/pre>\n<p>There exists a transitive dependence on the login_pw, i.e., the login_pw does not depend on the username\/domain primary key except transitively through login_id. This violates 3NF and this table can be normalized as follows:<\/p>\n<pre class=\"sh_sql\">CREATE TABLE foobar_logins (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    login_id VARCHAR(20) NOT NULL PRIMARY KEY\n);\n\nCREATE TABLE foobar_login_pw (\n    login_id VARCHAR(20) NOT NULL PRIMARY KEY,\n    login_pw VARCHAR(32) NOT NULL\n);\n<\/pre>\n<p>The other tables are <em>technically<\/em> in 3NF but one is not in Boyce-Codd Normal Form (BCNF).  BCNF is a slightly stronger version of 3NF. A table in 3NF would need partial overlapping keys to even possibly not be in BCNF.  One of our tables exhibits exactly this issue, and intuitively it doesn&#8217;t look normalized (even though it technically meets the 3NF requirement), i.e.,<\/p>\n<pre class=\"sh_sql\">-- technically 3NF, but not BCNF\nCREATE TABLE foobar_partners (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    website VARCHAR(255),\n    partner VARCHAR(20)\n);\n<\/pre>\n<p>In this case, a website and a partner are slightly related in that a partner is defined by a website registration. For the sake of brevity, a BCNF compliant version of the above table would look like this:<\/p>\n<pre class=\"sh_sql\">CREATE TABLE foobar_partners (\n    partner VARCHAR(20) NOT NULL PRIMARY KEY,\n    website VARCHAR(255) NOT NULL,\n    registration VARCHAR(12) NOT NULL\n);\n\nCREATE TABLE foobar_partner_map (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    partner VARCHAR(20) NULL,\n    PRIMARY KEY(username, domain, partner)\n);\n<\/pre>\n<p>Putting this all together, the BCNF compliant schema looks like this:<\/p>\n<pre class=\"sh_sql\">CREATE TABLE foobar_users (\n    username VARCHAR(20) NOT NULL PRIMARY KEY,\n    dept VARCHAR(20) NOT NULL,\n    status VARCHAR(12) NOT NULL\n);\n\nCREATE TABLE foobar_role_map (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    role VARCHAR(12) NOT NULL,\n    PRIMARY KEY(username, domain, role)\n);\n\nCREATE TABLE foobar_logins (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    login_id VARCHAR(20) NOT NULL PRIMARY KEY\n);\n\nCREATE TABLE foobar_login_pw (\n    login_id VARCHAR(20) NOT NULL PRIMARY KEY,\n    login_pw VARCHAR(32) NOT NULL\n);\n\nCREATE TABLE foobar_partners (\n    partner VARCHAR(20) NOT NULL PRIMARY KEY,\n    website VARCHAR(255) NOT NULL,\n    registration VARCHAR(12) NOT NULL\n);\n\nCREATE TABLE foobar_partner_map (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    partner VARCHAR(20) NULL,\n    PRIMARY KEY(username, domain, partner)\n);\n<\/pre>\n<p>The tables in the schema are now normalized to BCNF, next, I&#8217;d like to normalize to <a href=\"http:\/\/tech.avant.net\/q\/normalization-4nf\/\">Fourth Normal Form<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Previously, I normalized my table to Second Normal Form (2NF). Now, I want to continue to normalize such that the tables in my schema are in Third Normal Form (3NF). 3NF requires that every non-prime attribute is directly dependent on every candidate key. Fortunately, normalizing to 2NF accomplished this for all but one table, i.e., [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[16,15],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/141"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=141"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/141\/revisions"}],"predecessor-version":[{"id":1005,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/141\/revisions\/1005"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}