{"id":136,"date":"2011-07-02T23:14:44","date_gmt":"2011-07-02T23:14:44","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=136"},"modified":"2019-06-02T18:40:37","modified_gmt":"2019-06-02T18:40:37","slug":"normalization-2nf","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/normalization-2nf\/","title":{"rendered":"Normalization, 2NF"},"content":{"rendered":"<p>Previously, I normalized my table to <a href=\"\/q\/normalization-1nf\/\">First Normal Form (1NF)<\/a>.  Now, I want to continue to normalize such that the tables in my schema are in Second Normal Form (2NF).<\/p>\n<p>2NF requires that all non-key attributes depend on the whole of the key and not a subset of a compound key. The 1NF example can elucidate:<\/p>\n<pre class=\"sh_sql\">-- 1NF\nCREATE TABLE foobar_users (\n    username VARCHAR(20) NOT NULL,\n    domain VARCHAR(20) NOT NULL,\n    role VARCHAR(12) NOT NULL,\n    dept VARCHAR(20), --depends on user (but not domain or role)\n    status VARCHAR(12), --depends on user (but not domain or role) \n    login_id VARCHAR(20), --depends on user\/domain (but not role)\n    login_pw VARCHAR(32), --depends on login_id (transitively to user\/domain)\n    website VARCHAR(255), --does not depend on role\n    partner VARCHAR(20), -- does not depend on role\n    PRIMARY KEY(username, domain, role)\n);\n<\/pre>\n<p>This table is un-normalized and highly inefficient. Indexes would need to be added to keep query performance reasonable, indexes that in a normalized schema would not be necessary. Furthermore, because of the attribute dependencies, update statements will be considerably difficult if you wish to maintain data integrity.<\/p>\n<p>To normalize this table, separate the attributes that are not functionally dependent on the primary key into separate tables, i.e.,<\/p>\n<pre class=\"sh_sql\">CREATE 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\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    PRIMARY KEY(username, domain, website, partner)\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,\n    login_pw VARCHAR(32),\n    PRIMARY KEY(username, domain, login_id)\n);\n<\/pre>\n<p>The tables in the schema are now normalized to 2NF, next, I&#8217;d like to normalize to <a href=\"http:\/\/tech.avant.net\/q\/normalization-3nf\/\">Third Normal Form (3NF)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Previously, I normalized my table to First Normal Form (1NF). Now, I want to continue to normalize such that the tables in my schema are in Second Normal Form (2NF). 2NF requires that all non-key attributes depend on the whole of the key and not a subset of a compound key. The 1NF example can [&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\/136"}],"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=136"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/136\/revisions"}],"predecessor-version":[{"id":1004,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/136\/revisions\/1004"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=136"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=136"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=136"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}