{"id":153,"date":"2011-07-03T01:59:36","date_gmt":"2011-07-03T01:59:36","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=153"},"modified":"2019-06-02T19:07:13","modified_gmt":"2019-06-02T19:07:13","slug":"normalization-6nf","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/normalization-6nf\/","title":{"rendered":"Normalization, 6NF"},"content":{"rendered":"<p>Previously, I normalized my tables to <a href=\"\/q\/normalization-5nf\/\">Fifth Normal Form (5NF)<\/a>.  Now, I want to continue to normalize such that the tables in my schema are in Sixth Normal Form (6NF).<\/p>\n<p>6NF requires that each table satisfies only trivial join dependencies.<\/p>\n<p>All of the 5NF tables are already in 6NF, except one (it&#8217;s been going unnoticed since 2NF), it&#8217;s the leftovers from the original <em>foobar_users<\/em> table!<\/p>\n<pre class=\"sh_sql\">-- happily in 5NF, but not 6NF\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<\/pre>\n<p>As is, it&#8217;s in 5NF because every join dependency is implied by the candidate key. Specifically, the only join dependencies are {{username, dept}, {username, status}} &#8212; but this is a non-trivial join dependency, so let&#8217;s finish what we started.<\/p>\n<p>A 6NF version would look like this:<\/p>\n<pre class=\"sh_sql\">CREATE TABLE foobar_users (\n    username VARCHAR(20) NOT NULL PRIMARY KEY,\n    status VARCHAR(12) NOT NULL\n);\n\nCREATE TABLE foobar_dept_map (\n    username VARCHAR(20) NOT NULL PRIMARY KEY,\n    dept VARCHAR(20) NOT NULL\n);\n<\/pre>\n<p>The <em>foobar_users<\/em> table has come a long way since 1NF.<\/p>\n<p>Putting this all together, a 6NF compliant schema would look like this:<\/p>\n<pre class=\"sh_sql\">CREATE TABLE foobar_users (\n    username VARCHAR(20) NOT NULL PRIMARY KEY,\n    status VARCHAR(12) NOT NULL\n);\n\nCREATE TABLE foobar_dept_map (\n    username VARCHAR(20) NOT NULL PRIMARY KEY,\n    dept VARCHAR(20) NOT NULL\n);\n\nCREATE TABLE foobar_roles (\n    domain VARCHAR(20) NOT NULL,\n    role VARCHAR(12) NOT NULL,\n    PRIMARY KEY(domain, role)\n);\n\nCREATE TABLE foobar_role_map (\n    username VARCHAR(20) NOT NULL,\n    role VARCHAR(12) NOT NULL,\n    PRIMARY KEY(username, 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_domain_map (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    PRIMARY KEY(username, domain)\n);\n\nCREATE TABLE foobar_partner_map (\n    username VARCHAR(20) NOT NULL,\n    partner VARCHAR(20) NOT NULL,\n    PRIMARY KEY(username, partner)\n);\n<\/pre>\n<p>The tables in the schema are now normalized to 6NF, next, I want to consider <a href=\"\/q\/denormalization\/\">denormalizing<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Previously, I normalized my tables to Fifth Normal Form (5NF). Now, I want to continue to normalize such that the tables in my schema are in Sixth Normal Form (6NF). 6NF requires that each table satisfies only trivial join dependencies. All of the 5NF tables are already in 6NF, except one (it&#8217;s been going unnoticed [&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\/153"}],"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=153"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/153\/revisions"}],"predecessor-version":[{"id":1008,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/153\/revisions\/1008"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}