csh
/
y.st.
forked from y.st./y.st.


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160
							<?xml version="1.0" encoding="utf-8"?>
<!--
                                                                                     
 h       t     t                ::       /     /                     t             / 
 h       t     t                ::      //    //                     t            // 
 h     ttttt ttttt ppppp sssss         //    //  y   y       sssss ttttt         //  
 hhhh    t     t   p   p s            //    //   y   y       s       t          //   
 h  hh   t     t   ppppp sssss       //    //    yyyyy       sssss   t         //    
 h   h   t     t   p         s  ::   /     /         y  ..       s   t    ..   /     
 h   h   t     t   p     sssss  ::   /     /     yyyyy  ..   sssss   t    ..   /     
                                                                                     
	<https://y.st./>
	Copyright © 2017 Alex Yst <mailto:copyright@y.st>

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program. If not, see <https://www.gnu.org./licenses/>.
-->
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<base href="https://y.st./en/coursework/CS2203/Library_database_normalisation.xhtml" />
		<title>Library database normalisation &lt;https://y.st./en/coursework/CS2203/Library_database_normalisation.xhtml&gt;</title>
		<link rel="icon" type="image/png" href="/link/CC_BY-SA_4.0/y.st./icon.png" />
		<link rel="stylesheet" type="text/css" href="/link/basic.css" />
		<link rel="stylesheet" type="text/css" href="/link/site-specific.css" />
		<script type="text/javascript" src="/script/javascript.js" />
		<meta name="viewport" content="width=device-width" />
	</head>
	<body>
		<nav>
			<p>
				<a href="/en/">Home</a> |
				<a href="/en/a/about.xhtml">About</a> |
				<a href="/en/a/contact.xhtml">Contact</a> |
				<a href="/a/canary.txt">Canary</a> |
				<a href="/en/URI_research/"><abbr title="Uniform Resource Identifier">URI</abbr> research</a> |
				<a href="/en/opinion/">Opinions</a> |
				<a href="/en/coursework/">Coursework</a> |
				<a href="/en/law/">Law</a> |
				<a href="/en/a/links.xhtml">Links</a> |
				<a href="/en/coursework/CS2203/Library_database_normalisation.xhtml.asc">{this page}.asc</a>
			</p>
			<hr/>
			<p>
				<a href="/en/coursework/BUS1101/" title="Principles of Business Management">BUS 1101</a> |
				<span class="hyperlink_unavailable" title="Basic Accounting">BUS 1102</span> |
				<span class="hyperlink_unavailable" title="Principles of Marketing">BUS 2201</span> |
				<span class="hyperlink_unavailable" title="Multinational Management">BUS 2207</span> |
				<span class="hyperlink_unavailable" title="Business and Society">BUS 3306</span> |
				<a href="/en/coursework/CS1101/" title="Programming Fundamentals">CS 1101</a> |
				<a href="/en/coursework/CS1102/" title="Programming 1">CS 1102</a> |
				<a href="/en/coursework/CS1103/" title="Programming 2">CS 1103</a> |
				<span class="hyperlink_unavailable" title="Computer Systems">CS 1104</span> |
				<a href="/en/coursework/CS2203/" title="Databases 1">CS 2203</a> |
				<span class="hyperlink_unavailable" title="Communications and Networking">CS 2204</span> |
				<a href="/en/coursework/CS2205/" title="Web Programming 1">CS 2205</a> |
				<a href="/en/coursework/CS2301/" title="Operating Systems 1">CS 2301</a> |
				<span class="hyperlink_unavailable" title="Software Engineering 1">CS 2401</span> |
				<span class="hyperlink_unavailable" title="Data Structures">CS 3303</span> |
				<span class="hyperlink_unavailable" title="Analysis of Algorithms">CS 3304</span> |
				<span class="hyperlink_unavailable" title="Web Programming 2">CS 3305</span> |
				<span class="hyperlink_unavailable" title="Databases 2">CS 3306</span> |
				<span class="hyperlink_unavailable" title="Operating Systems 2">CS 3307</span> |
				<span class="hyperlink_unavailable" title="Information Retrieval">CS 3308</span> |
				<span class="hyperlink_unavailable" title="Comparative Programming Languages">CS 4402</span> |
				<span class="hyperlink_unavailable" title="Software Engineering 2">CS 4403</span> |
				<span class="hyperlink_unavailable" title="Advanced Networking and Data Security">CS 4404</span> |
				<span class="hyperlink_unavailable" title="Mobile Applications">CS 4405</span> |
				<span class="hyperlink_unavailable" title="Computer Graphics">CS 4406</span> |
				<span class="hyperlink_unavailable" title="Data Mining and Machine Learning">CS 4407</span> |
				<span class="hyperlink_unavailable" title="Artificial Intelligence">CS 4408</span> |
				<span class="hyperlink_unavailable" title="English Composition 2">ENGL 1102</span> |
				<span class="hyperlink_unavailable" title="World Literature">ENGL 1405</span> |
				<span class="hyperlink_unavailable" title="Introduction to Environmental Science">ENVS 1301</span> |
				<a href="/en/coursework/HIST1421/" title="Greek and Roman Civilization">HIST 1421</a> |
				<span class="hyperlink_unavailable" title="College Algebra">MATH 1201</span> |
				<span class="hyperlink_unavailable" title="Calculus">MATH 1211</span> |
				<span class="hyperlink_unavailable" title="Introduction to Statistics">MATH 1280</span> |
				<span class="hyperlink_unavailable" title="Discrete Mathematics">MATH 1302</span> |
				<span class="hyperlink_unavailable" title="Introduction to Philosophy">PHIL 1402</span> |
				<a href="/en/coursework/PHIL1404/" title="Ethics and Social Responsibility">PHIL 1404</a> |
				<a href="/en/coursework/POLS1503/" title="Globalization">POLS 1503</a> |
				<span class="hyperlink_unavailable" title="Introduction to Psychology">PSYC 1504</span> |
				<a href="/en/coursework/UNIV1001/" title="Online Education Strategies">UNIV 1001</a>
			</p>
			<hr/>
			<p>
				<a href="/en/coursework/CS2203/Library_database_relations.xhtml" title="Library database relations">Unit 1</a> |
				<a href="/en/coursework/CS2203/Library_database_relations~_continued.xhtml" title="Library database relations, continued">Unit 2</a> |
				<a href="/en/coursework/CS2203/Library_database_relation_diagram.xhtml" title="Library database relation diagram">Unit 3</a> |
				<a href="/en/coursework/CS2203/Library_database_normalisation.xhtml" title="Library database normalisation">Unit 4</a> |
				<a href="/en/coursework/CS2203/Library_database_SQL.xhtml" title="Library database SQL">Unit 5</a> |
				<a href="/en/coursework/CS2203/Library_database_SQL~_continued.xhtml" title="Library database SQL, continued">Unit 6</a> |
				<a href="/en/coursework/CS2203/Library_database_SQL_select.xhtml" title="Library database SQL select">Unit 7</a>
			</p>
			<hr/>
		</nav>
		<header>
			<h1>Library database normalisation</h1>
			<p>Written in <span title="Databases 1">CS 2203</span> of <a href="http://www.uopeople.edu/">University of the People</a>, finalised on 2017-07-12</p>
		</header>
<h2>First normalised form</h2>
<p>
	For me, the database is already in first normalised form, as I&apos;ve been working with the idea of primary keys and a relational database in mind.
	No duplicate tuples exist and no fields contain multiple values.
	Looking at the design I have from before, it looks like the only change I needed to make to fit the first normalised form, before we even learned about it, was to add an <code>id</code> field to the <code>Book</code> relation.
	It&apos;s highly probable that the library will have multiple copies of the same book.
	In this case, most if not all fields of a given tuple will exactly match those of another.
	They&apos;ll have the same <abbr title="International Standard Book Number">ISBN</abbr>, the same title, the same author ... the price may vary between copies if purchased at different times or from different sources, but everything else about the books will match.
	By adding a surrogate key, <code>id</code>, I ensured that having multiple copies of the book wouldn&apos;t cause a problem.
	Each copy of the book can only be checked out to a single borrower at a time, but each copy can be checked out to a different borrower.
</p>
<img class="weblog-header-image" src="/static/img/CC_BY-SA_4.0/y.st./coursework/CS2203/Library_database_relation_diagram.png" alt="relation diagram" width="1009" height="302" />
<h2>Second normalised form</h2>
<p>
	All of my tables use only a single field as their primary key.
	As a result, my tables automatically qualify as being second-form normalised.
	It&apos;s impossible to break the primary key into components, so no field depends on part of the primary key without depending on all of it.
</p>
<img class="weblog-header-image" src="/static/img/CC_BY-SA_4.0/y.st./coursework/CS2203/Library_database_relation_diagram.png" alt="relation diagram"  width="1009" height="302"/>
<h2>Third normalised form</h2>
<p>
	As stated above, the <code>Book</code> relation was modified to account for the possibility of multiple copies of the same book.
	This <strong>*introduced*</strong> redundancy into the database.
	To get the database into the third normalised form, that redundancy had to be removed once more.
	This was actually a very trivial task.
	The <code>Book</code> relation was split into two smaller relations.
	I moved the <code>title</code>, <code>author</code>, <code>isbn</code>, and <code>publication_date</code> into their own relation, called <code>BookMeta</code>.
	<code>isbn</code> became the primary key of that table; two books can share an author or share a title (usually not both, but it&apos;s possible), but they&apos;ll <strong>*never*</strong> share an <abbr title="International Standard Book Number">ISBN</abbr> unless they&apos;re two copies of the same book.
	The <code>Book</code> relation kept the <code>id</code> and <code>cost</code> fields, and additionally kept a copy of the <code>isbn</code> field to use as a foreign key to the <code>BookMeta</code> relation.
	<code>cost</code> was kept in the <code>Book</code> relation because, as stated before, differing copies of the book could have been bought for different prices, even if they were otherwise the same book.
</p>
<img class="weblog-header-image" src="/static/img/CC_BY-SA_4.0/y.st./coursework/CS2203/Library_database_normalisation.png" alt="relation diagram" width="1362" height="437" />
		<hr/>
		<p>
			Copyright © 2017 Alex Yst;
			You may modify and/or redistribute this document under the terms of the <a rel="license" href="/license/gpl-3.0-standalone.xhtml"><abbr title="GNU&apos;s Not Unix">GNU</abbr> <abbr title="General Public License version Three or later">GPLv3+</abbr></a>.
			If for some reason you would prefer to modify and/or distribute this document under other free copyleft terms, please ask me via email.
			My address is in the source comments near the top of this document.
			This license also applies to embedded content such as images.
			For more information on that, see <a href="/en/a/licensing.xhtml">licensing</a>.
		</p>
		<p>
			<abbr title="World Wide Web Consortium">W3C</abbr> standards are important.
			This document conforms to the <a href="https://validator.w3.org./nu/?doc=https%3A%2F%2Fy.st.%2Fen%2Fcoursework%2FCS2203%2FLibrary_database_normalisation.xhtml"><abbr title="Extensible Hypertext Markup Language">XHTML</abbr> 5.1</a> specification and uses style sheets that conform to the <a href="http://jigsaw.w3.org./css-validator/validator?uri=https%3A%2F%2Fy.st.%2Fen%2Fcoursework%2FCS2203%2FLibrary_database_normalisation.xhtml"><abbr title="Cascading Style Sheets">CSS</abbr>3</a> specification.
		</p>
	</body>
</html>