{"id":149,"date":"2011-07-03T00:38:28","date_gmt":"2011-07-03T00:38:28","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=149"},"modified":"2019-06-02T19:02:22","modified_gmt":"2019-06-02T19:02:22","slug":"normalization-5nf","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/normalization-5nf\/","title":{"rendered":"Normalization, 5NF"},"content":{"rendered":"<p>Previously, I normalized my tables to <a href=\"\/q\/normalization-4nf\/\">Fourth Normal Form (4NF)<\/a>.  Now, I want to continue to normalize such that the tables in my schema are in Fifth Normal Form (5NF).<\/p>\n<p>5NF requires that every join dependency in a table is implied by the candidate keys.<\/p>\n<p>All of our 4NF tables meet the 5NF requirement, but when we consider the implicit functional requirements, one table fails 5NF:<\/p>\n<pre class=\"sh_sql\">CREATE 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>This table is in 4NF since there are no non-trivial multivalued dependencies. However, there is a join dependency not implied by the candidate keys, i.e., {{username, domain} {username, role} {domain, role}}.  In other words, if a user is granted access to a role in a domain, then the user has that role in all domains that support that role&#8211; not all roles are supported by all domains. It is this particular caveat that causes an otherwise normalized structure to fail 5NF.<\/p>\n<p>A 5NF version would look like this (note that the <em>foobar_domain_map<\/em> in the 4NF article covers the {username, domain} join dependency):<\/p>\n<pre class=\"sh_sql\">CREATE 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<\/pre>\n<p>Putting this all together, a 5NF 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    dept VARCHAR(20) NOT NULL,\n    status VARCHAR(12) 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 5NF, next, I&#8217;d like to normalize to <a href=\"\/q\/normalization-6nf\/\">Sixth Normal Form (6NF)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Previously, I normalized my tables to Fourth Normal Form (4NF). Now, I want to continue to normalize such that the tables in my schema are in Fifth Normal Form (5NF). 5NF requires that every join dependency in a table is implied by the candidate keys. All of our 4NF tables meet the 5NF requirement, but [&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\/149"}],"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=149"}],"version-history":[{"count":7,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/149\/revisions"}],"predecessor-version":[{"id":1007,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/149\/revisions\/1007"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}