{"id":94,"date":"2016-01-24T13:50:00","date_gmt":"2016-01-24T13:50:00","guid":{"rendered":"http:\/\/mr-west.uk\/sql\/?page_id=94"},"modified":"2019-11-19T23:29:45","modified_gmt":"2019-11-19T23:29:45","slug":"creating-a-database-part-2","status":"publish","type":"page","link":"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/","title":{"rendered":"Creating a database &#8211; part 2"},"content":{"rendered":"\n<div class=\"arconix-tabs-horizontal\"><ul class=\"arconix-tabs\"><li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Starter\"><a class=\"\" href=\"#tab-Starter\">Starter<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Primarykeys\"><a class=\"\" href=\"#tab-Primarykeys\">Primary keys<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-ForeignKeys\"><a class=\"\" href=\"#tab-ForeignKeys\">Foreign Keys<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Linktableformanytomanyrelationship\"><a class=\"\" href=\"#tab-Linktableformanytomanyrelationship\">Link table for many to many relationship<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Challenges\"><a class=\"\" href=\"#tab-Challenges\">Challenges<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Resources\"><a class=\"\" href=\"#tab-Resources\">Resources<\/a><\/li><\/ul>\n<div class=\"arconix-panes\"><div class=\"arconix-pane pane-Starter\">\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<\/div>\n<div class=\"arconix-pane pane-Primarykeys\">\n<h3 class=\"prettyprint notranslate prettyprinted\"><span class=\"pln\"><span style=\"color: #0000ff;\">column_name<\/span> <span style=\"color: #0000ff;\">data_type<\/span> PRIMARY KEY AUTOINCREMENT<\/span><\/h3>\n<p>In order to create relational databases, you need to be able to identify each individual entity in a table, and the way we do this is through a PRIMARY KEY.<\/p>\n<p>A table must have a primary key for each entity(row)<\/p>\n<p>The primary key must be unique in that table.<\/p>\n<p>For example, the primary key in the table below is the ID column:<\/p>\n<p>Racers Table<\/p>\n<p>NameAgeTeam<\/p>\n<p>IDNameAgeTeam<\/p>\n<table width=\"60%\">\n<thead><\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>Bob Jones<\/td>\n<td>25<\/td>\n<td>Pirelli<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>Each racer is given an ID number and that number is unique to the driver. This means that even if there are 2 driver, who are both 25 and both work for Pirelli, they won&#8217;t get confused by the database.<\/p>\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-bullseye'><\/i><div class=\"arconix-box-content\">\n<p><strong>Challenge 17 &#8211; Create a new database<\/strong><\/p>\n<p>Database name<strong> &#8211;\u00a0<\/strong>racing2.db<\/p>\n<\/div><\/div>\n<p>&nbsp;<\/p>\n<\/div>\n<div class=\"arconix-pane pane-ForeignKeys\">\n<h3><span style=\"color: #0000ff;\">column_name DATA_TYPE<\/span> REFERENCES <span style=\"color: #0000ff;\">table_name<\/span>(<span style=\"color: #0000ff;\">primary_key_column_name<\/span>)<\/h3>\n<p>So why have we created a primary key in the first place? It&#8217;s more effort than before? Well, the answer is that we can now use the primary in other tables to refer to that racer name. It connects the two tables together and allows us to do more clever things with the database than we did previously.<\/p>\n<h3>Flat File Database Example<\/h3>\n<p><a href=\"http:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/flat-file-tables.png\" rel=\"attachment wp-att-125\"><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-125\" src=\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/flat-file-tables.png\" alt=\"flat file tables\" width=\"545\" height=\"309\" srcset=\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/flat-file-tables.png 545w, https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/flat-file-tables-300x170.png 300w\" sizes=\"(max-width: 545px) 100vw, 545px\" \/><\/a><\/p>\n<p>Here is a simple flat file database. It looks pretty sound, but as you can see there is quite a bit of duplication between the two tables ( racerName, team). The more clever way of doing things would be to use a relational database setup instead, which would save duplication of data.<\/p>\n<h3>Relational Database Example<\/h3>\n<p><a href=\"http:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/relational-tables.png\" rel=\"attachment wp-att-126\"><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-126\" src=\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/relational-tables.png\" alt=\"relational tables\" width=\"623\" height=\"464\" srcset=\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/relational-tables.png 623w, https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/relational-tables-300x223.png 300w\" sizes=\"(max-width: 623px) 100vw, 623px\" \/><\/a><\/p>\n<p>Not only does this save on duplication, but it also avoids storing some data altogether. For instance we can calculate how many times a racer has won a race by doing a clever JOIN lookup on both the<strong> racers<\/strong> and <strong>fixtures<\/strong>\u00a0tables.<\/p>\n<\/div>\n<div class=\"arconix-pane pane-Linktableformanytomanyrelationship\">\n<p>CREATE TABLE Pupil(id integer PRIMARY KEY,name text);<\/p>\n<p>sqlite&gt; CREATE TABLE Allergy(id integer PRIMARY KEY, name text, action text);<\/p>\n<p>&nbsp;<\/p>\n<p>CREATE TABLE Pupil_Allergy(<br \/>\nid integer PRIMARY_key,<br \/>\nPupilID integer,<br \/>\nAllergyID integer,<br \/>\nFOREIGN KEY (PupilID) REFERENCES Pupil(id),<br \/>\nFOREIGN KEY (AllergyID) REFERENCES Allergy(id)<br \/>\n);<\/p>\n<\/div>\n<div class=\"arconix-pane pane-Challenges\">\n<div class=\"arconix-accordions arconix-accordions-vertical arconix-accordions-1\"><div class=\"arconix-accordion-title accordion-Challenge18-Racers\"><i class=\"fa\"><\/i><p>Challenge 18 - Racers<\/p><\/div><div class=\"arconix-accordion-content\">\n<p><strong>Challenge 18 &#8211; Racers<\/strong><\/p>\n<p>In your racing2.db database create the following table:<\/p>\n<p><strong>Table name<\/strong><\/p>\n<p>racers<\/p>\n<p><strong>Column names<\/strong><\/p>\n<ul>\n<li>RacerId integer, auto incrementing<\/li>\n<li>FirstName text<\/li>\n<li>Lastname text<\/li>\n<li>Age integer<\/li>\n<li>TeamID foreign key integer<\/li>\n<\/ul>\n<\/div><div class=\"arconix-accordion-title accordion-Challenge19-Fixtures\"><i class=\"fa\"><\/i><p>Challenge 19 - Fixtures<\/p><\/div><div class=\"arconix-accordion-content\">\n<p><strong>Challenge 19 &#8211; Fixtures<\/strong><\/p>\n<p>In your racing2.db database create the following table:<\/p>\n<p><strong>Table name<\/strong><\/p>\n<p>fixtures<\/p>\n<p><strong>Column names<\/strong><\/p>\n<ul>\n<li>date<\/li>\n<li>time<\/li>\n<li>location<\/li>\n<li>name<\/li>\n<\/ul>\n<\/div><div class=\"arconix-accordion-title accordion-Challenge20-Teams\"><i class=\"fa\"><\/i><p>Challenge 20 -  Teams<\/p><\/div><div class=\"arconix-accordion-content\">\n<p><strong>Challenge 20 &#8211; \u00a0Teams<\/strong><\/p>\n<p>In your racing2.db database create the following table:<\/p>\n<p><strong>Table name<\/strong><\/p>\n<p>teams<\/p>\n<p><strong>Column names<\/strong><\/p>\n<ul>\n<li>name<\/li>\n<li>country<\/li>\n<li>manager<\/li>\n<\/ul>\n<\/div><div class=\"arconix-accordion-title accordion-Challenge21-Results\"><i class=\"fa\"><\/i><p>Challenge 21 - Results<\/p><\/div><div class=\"arconix-accordion-content\">\n<p><strong>Challenge 21 &#8211; Results<\/strong><\/p>\n<p>In your racing2.db database create the following table:<\/p>\n<p><strong>Table name<\/strong><\/p>\n<p>results<\/p>\n<p><strong>Column names<\/strong><\/p>\n<ul>\n<li>fixture<\/li>\n<li>racer<\/li>\n<li>position<\/li>\n<li>time<\/li>\n<\/ul>\n<\/div><\/div>\n<p>&nbsp;<\/p>\n<\/div>\n<div class=\"arconix-pane pane-Resources\">\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<\/div><\/div><\/div>\n\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":70,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":""},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Creating a database - part 2 - SQL<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating a database - part 2 - SQL\" \/>\n<meta property=\"og:description\" content=\"&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL\" \/>\n<meta property=\"article:modified_time\" content=\"2019-11-19T23:29:45+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/flat-file-tables.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/\",\"url\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/\",\"name\":\"Creating a database - part 2 - SQL\",\"isPartOf\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#website\"},\"datePublished\":\"2016-01-24T13:50:00+00:00\",\"dateModified\":\"2019-11-19T23:29:45+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/learnlearn.uk\/sql\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating a database &#8211; part 2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#website\",\"url\":\"https:\/\/learnlearn.uk\/sql\/\",\"name\":\"SQL\",\"description\":\"Databases\",\"publisher\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/learnlearn.uk\/sql\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#organization\",\"name\":\"SQL\",\"url\":\"https:\/\/learnlearn.uk\/sql\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png\",\"contentUrl\":\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png\",\"width\":710,\"height\":98,\"caption\":\"SQL\"},\"image\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating a database - part 2 - SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/","og_locale":"en_GB","og_type":"article","og_title":"Creating a database - part 2 - SQL","og_description":"&nbsp;","og_url":"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/","og_site_name":"SQL","article_modified_time":"2019-11-19T23:29:45+00:00","og_image":[{"url":"http:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/flat-file-tables.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Estimated reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/","url":"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/","name":"Creating a database - part 2 - SQL","isPartOf":{"@id":"https:\/\/learnlearn.uk\/sql\/#website"},"datePublished":"2016-01-24T13:50:00+00:00","dateModified":"2019-11-19T23:29:45+00:00","breadcrumb":{"@id":"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/learnlearn.uk\/sql\/creating-a-database-part-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/learnlearn.uk\/sql\/"},{"@type":"ListItem","position":2,"name":"Creating a database &#8211; part 2"}]},{"@type":"WebSite","@id":"https:\/\/learnlearn.uk\/sql\/#website","url":"https:\/\/learnlearn.uk\/sql\/","name":"SQL","description":"Databases","publisher":{"@id":"https:\/\/learnlearn.uk\/sql\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/learnlearn.uk\/sql\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":"Organization","@id":"https:\/\/learnlearn.uk\/sql\/#organization","name":"SQL","url":"https:\/\/learnlearn.uk\/sql\/","logo":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/","url":"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png","contentUrl":"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png","width":710,"height":98,"caption":"SQL"},"image":{"@id":"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/"}}]}},"rttpg_featured_image_url":null,"rttpg_author":{"display_name":"learnlearnadmin","author_link":"https:\/\/learnlearn.uk\/sql\/author\/learnlearnadmin\/"},"rttpg_comment":0,"rttpg_category":null,"rttpg_excerpt":"&nbsp;","_links":{"self":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages\/94"}],"collection":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/comments?post=94"}],"version-history":[{"count":1,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages\/94\/revisions"}],"predecessor-version":[{"id":209,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages\/94\/revisions\/209"}],"wp:attachment":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/media?parent=94"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}