{"id":146,"date":"2011-07-03T00:36:55","date_gmt":"2011-07-03T00:36:55","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=146"},"modified":"2019-06-02T18:58:56","modified_gmt":"2019-06-02T18:58:56","slug":"normalization-4nf","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/normalization-4nf\/","title":{"rendered":"Normalization, 4NF"},"content":{"rendered":"<p>Previously, I normalized my tables to <a href=\"\/q\/normalization-3nf\/\">Third Normal Form (3NF)<\/a> and Boyce-Codd Normal Form (BCNF).  Now, I want to continue to normalize such that the tables in my schema are in Fourth Normal Form (4NF).<\/p>\n<p>4NF requires for any non-trivial multivalued dependencies X-&gt;Y, that X is a superkey. In other words, there are not multiple many-to-many relationships stored in a single table.<\/p>\n<p>Most of our BCNF tables are already in 4NF, one table is not, i.e.,<\/p>\n<pre class=\"sh_sql\">-- BCNF, but not 4NF\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>There is no relationship between domain and partner, yet there is a many-to-many relationship between username and partner, and another many-to-many relationship between username and domain. A 4NF version of this table would like this:<\/p>\n<pre class=\"sh_sql\">CREATE 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>Putting this all together, a 4NF 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_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_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 4NF, next, I&#8217;d like to normalize to <a href=\"\/q\/normalization-5nf\/\">Fifth Normal Form (5NF)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Previously, I normalized my tables to Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF). Now, I want to continue to normalize such that the tables in my schema are in Fourth Normal Form (4NF). 4NF requires for any non-trivial multivalued dependencies X-&gt;Y, that X is a superkey. In other words, there are not multiple [&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\/146"}],"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=146"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/146\/revisions"}],"predecessor-version":[{"id":1006,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/146\/revisions\/1006"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}