y.st./source/pages/en/coursework/CS2203/Library_database_normalisation.xhtml

<?php
/**
 * <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/>.
**/

$xhtml = array(
	'title' => 'Library database normalisation',
	'subtitle' => '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',
	'copyright year' => '2017',
	'body' => <<<END
<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 $a[ISBN], 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 $a[ISBN] 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" />
END
);