{"id":1242,"date":"2023-04-13T15:37:56","date_gmt":"2023-04-13T14:37:56","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/?page_id=1242"},"modified":"2023-04-14T09:44:37","modified_gmt":"2023-04-14T08:44:37","slug":"database-normalisation","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/data-modelling\/database-normalisation\/","title":{"rendered":"Database normalisation"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>Database normalization is the process of structuring a database, usually a relational database, in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model.<\/p><cite>Wikipedia<\/cite><\/blockquote>\n\n\n\n<p>It\u2019s basically the steps to design a new database or alter an existing one so that it isn\u2019t prone to the anomalies discussed in a previous lesson. You do this by reducing a complex structure into multiple simple structures that are related to one another.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Normal forms<\/h2>\n\n\n\n<p>There are many normal forms. From least normalized to most normalised they are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>UNF: Unnormalised form<\/li><li>1NF: First normal form<\/li><li>2NF: Second normal form<\/li><li>3NF: Third normal form<\/li><li>EKNF: Elementary key normal form<\/li><li>BCNF: Boyce\u2013Codd normal form<\/li><li>4NF: Fourth normal form<\/li><li>ETNF: Essential tuple normal form<\/li><li>5NF: Fifth normal form<\/li><li>DKNF: Domain-key normal form<\/li><li>6NF: Sixth normal form<\/li><\/ul>\n\n\n\n<p>Above 4NF is only really dealt with in theoretical terms rather than practical applications.<\/p>\n\n\n\n<p>When someone describes a database as normalised they are referring to the third form 3NF.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">UNF to 1NF<\/h3>\n\n\n\n<p>To reach 1NF each attribute must be composed of a single value.<\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-9d6595d7 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"144\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154204\/DB24-1024x144-1.png\" alt=\"Example of UNF\" class=\"wp-image-1246\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154204\/DB24-1024x144-1.png 1024w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154204\/DB24-1024x144-1-300x42.png 300w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154204\/DB24-1024x144-1-768x108.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption>Example of UNF<\/figcaption><\/figure><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"156\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154225\/DB25-1024x156-1.png\" alt=\"Example of 1NF\" class=\"wp-image-1247\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154225\/DB25-1024x156-1.png 1024w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154225\/DB25-1024x156-1-300x46.png 300w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154225\/DB25-1024x156-1-768x117.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption>Example of 1NF<\/figcaption><\/figure><\/div>\n<\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\">1NF to 2NF<\/h3>\n\n\n\n<p>Remember the key is the column or columns that uniquely identify the row. To satisfy the 2NF requirements, every non-key column of a table must rely on a single key to be identified. Imagine that there are other games listed in the table below:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"148\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154336\/DB26-1024x148-1.png\" alt=\"Example of games in 1NF table\" class=\"wp-image-1248\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154336\/DB26-1024x148-1.png 1024w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154336\/DB26-1024x148-1-300x43.png 300w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154336\/DB26-1024x148-1-768x111.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption>Example of games in 1NF table<\/figcaption><\/figure><\/div>\n\n\n\n<p>The Price depends on the Platform, so the Game and Platform make a candidate key. But the Release Date and Rating are only dependent on the Game.<\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-9d6595d7 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"760\" height=\"193\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154429\/DB27.png\" alt=\"Example of games price by platform\" class=\"wp-image-1249\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154429\/DB27.png 760w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154429\/DB27-300x76.png 300w\" sizes=\"auto, (max-width: 760px) 100vw, 760px\" \/><figcaption>Example of games price by platform<\/figcaption><\/figure><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"760\" height=\"132\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154433\/DB28.png\" alt=\"Example of games information\" class=\"wp-image-1250\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154433\/DB28.png 760w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154433\/DB28-300x52.png 300w\" sizes=\"auto, (max-width: 760px) 100vw, 760px\" \/><figcaption>Example of games information<\/figcaption><\/figure><\/div>\n<\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\">2NF to 3NF<\/h3>\n\n\n\n<p>To satisfy 3NF you remove any transitive functional dependencies. Transitive functional dependencies are when one attribute gets their value from another one.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"81\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154552\/DB29-1024x81-1.png\" alt=\"Example of games in 2NF\" class=\"wp-image-1251\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154552\/DB29-1024x81-1.png 1024w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154552\/DB29-1024x81-1-300x24.png 300w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154552\/DB29-1024x81-1-768x61.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption>Example of games in 2NF<\/figcaption><\/figure><\/div>\n\n\n\n<p>The country of development field is dependent on the developer field so can be split into their own table. Now all fields depend solely on their primary key.<\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-9d6595d7 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"930\" height=\"126\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154634\/DB30.png\" alt=\"Example of games information in 3NF\" class=\"wp-image-1252\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154634\/DB30.png 930w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154634\/DB30-300x41.png 300w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154634\/DB30-768x104.png 768w\" sizes=\"auto, (max-width: 930px) 100vw, 930px\" \/><figcaption>Example of games information in 3NF<\/figcaption><\/figure><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"673\" height=\"143\" src=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154639\/DB31.png\" alt=\"Example of developer information in 3NF\" class=\"wp-image-1253\" srcset=\"https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154639\/DB31.png 673w, https:\/\/blogs.glowscotland.org.uk\/es\/public\/software\/uploads\/sites\/4063\/2023\/04\/13154639\/DB31-300x64.png 300w\" sizes=\"auto, (max-width: 673px) 100vw, 673px\" \/><figcaption>Example of developer information in 3NF<\/figcaption><\/figure><\/div>\n<\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p>The aim of a normalised database is to avoid data duplication. Any change to data should only have to be done to one row. Everything else should update via the data cascading through foreign keys.<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>One value per attribute<\/li><li>Entity uniquely identified by a single attribute (1 column per key)<\/li><li>No transitive dependencies<\/li><\/ol>\n\n\n\n<p>Un-normalised data should only be used for either tables with multi-value attributes or big data where the performance impact of indexing the data is too high.<\/p>\n\n\n\n<p class=\"nextlink\"><a href=\"https:\/\/blogs.glowscotland.org.uk\/es\/software\/data-modelling\/microsoft-access\/\" data-type=\"page\" data-id=\"1259\">Next: Microsoft Access<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Database normalization is the process of structuring a database, usually a relational database, in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model. Wikipedia It\u2019s basically the steps to design a new&hellip; <a class=\"more-link\" href=\"https:\/\/blogs.glowscotland.org.uk\/es\/software\/data-modelling\/database-normalisation\/\">Continue reading <span class=\"screen-reader-text\">Database normalisation<\/span><\/a><\/p>\n","protected":false},"author":5710,"featured_media":0,"parent":1179,"menu_order":4,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1242","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/users\/5710"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/comments?post=1242"}],"version-history":[{"count":6,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1242\/revisions"}],"predecessor-version":[{"id":1261,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1242\/revisions\/1261"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/pages\/1179"}],"wp:attachment":[{"href":"https:\/\/blogs.glowscotland.org.uk\/es\/software\/wp-json\/wp\/v2\/media?parent=1242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}