y.st./source/pages/en/coursework/CS2203/Library_database_SQL.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 $a[SQL]",
	'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-19',
	'copyright year' => '2017',
	'body' => <<<END
<h2>$a[SQL] commands</h2>
<blockquote>
<pre><code>create table Borrower (
	card_number int generated by default as identity (start with 0, increment by 1) not null primary key,
	name varchar(32) not null,
	address varchar(32) not null,
	postal_code int not null,
	telephone_number int,
	join_date date default current_date not null,
	check (postal_code &lt; 100000)
);
create table Librarian (
	id int generated by default as identity (start with 0, increment by 1) not null primary key,
	name varchar(32) not null,
	extension int not null,
	reports_to int,
	foreign key (reports_to) references Librarian(id)
);
create table BookMeta (
	isbn bigint not null primary key,
	title varchar(64) not null,
	author varchar(32) not null,
	publication_date date not null,
	check (isbn &lt; 10000000000000)
);
create table Book(
	id int generated by default as identity (start with 0, increment by 1) not null primary key,
	isbn bigint not null,
	cost decimal(5,2) not null,
	foreign key (isbn) references BookMeta(isbn)
);
create table BookLended(
	book int not null primary key,
	checked_out date default current_date not null,
	due_back date not null,
	borrower int not null,
	librarian int,
	check (due_back &gt; checked_out),
	foreign key (book) references Book(id),
	foreign key (borrower) references Borrower(card_number),
	foreign key (librarian) references Librarian(id)
);</code></pre>
</blockquote>
<h2>Screenshot</h2>
<img src="/img/CC_BY-SA_4.0/y.st./coursework/CS2203/Library_database_SQL.png" alt="Database objects" class="weblog-header-image" width="623" height="356" />
<h2>Explanations</h2>
<p>
	On the <code>Borrower</code> table, the <code>card_number</code> is set to auto-increment so we don&apos;t have to use an external system to assign card numbers.
	The <code>telephone_number</code> is allowed to be null, because some people (such as myself) don&apos;t have telephone service and therefore don&apos;t have a telephone number.
	It&apos;s a pain dealing with companies that insist that you need a telephone number, so in my implementation of the library, I gave people like me a break.
	If a borrower doesn&apos;t have a telephone number, the database can accommodate that.
	The <code>postal_code</code> has a constraint that prevents postal codes with more than five digits from being entered, as postal codes are five digits long.
</p>
<p>
	Like <code>Borrower</code> <code>card_number</code>s, <code>Librarian</code> <code>id</code>s are assigned automatically.
	The telephone <code>extension</code>, unlike <code>Borrower</code> <code>telephone_number</code>, is not allowed to be null.
	This is because all librarians in the building will have an extension using the library&apos;s telephone service.
	We can manually add a <code>join_date</code> if we want, but it defaults to the current date.
	This is because we&apos;re probably adding them to our database as soon as they register an account with the library.
	The unary relationship we defined in class is included.
	The assignment instructions said we don&apos;t need it, but they didn&apos;t explicitly say <strong>*not*</strong> to include it, and including it was actually quite easy.
</p>
<p>
	The <code>BookMeta</code> table was added to remove redundancy in the <code>Book</code> table.
	Each copy of a book needs its own entry in the <code>Book</code> table for a few reasons.
	First, differing copies might have been bought for different prices, so the <code>cost</code> needs to be independent for each copy.
	Second, each copy needs to be tracked separately in the <code>BookLended</code> relation.
	However, all copies of the same book will have the same $a[ISBN], publication date, author, and title.
	A check is performed on the <code>isbn</code> field to ensure it&apos;s not longer than thirteen digits, as while $a[ISBN]s have a few different lengths, none are longer than thirteen digits.
</p>
<p>
	The <code>Book</code> relation references the <code>BookMeta</code> relation for most of its information, but gives a separate <code>id</code> to each copy of a book and holds the <code>cost</code> of that copy.
	<code>id</code>s are mostly meaningless outside the database, so they&apos;re simply assigned automatically.
	Once the <code>id</code>s are assigned, barcodes for them can be generated and put on the library&apos;s books.
</p>
<p>
	The <code>BookLended</code> table ties the three other main tables together.
	The <code>book</code> attribute acts as both a primary key and a foreign key.
	As a foreign key, it tells us which book was lent out.
	It works as a primary key too though because a book cannot be lent out to two people at once.
	When the book is returned, the tuple representing that lending will be removed, and the book can be lent out again.
	<code>book</code> and <code>borrower</code> cannot be null, as there can&apos;t be a lending of a book if there is no book lent and/or no one borrowing it.
	If the book is removed from the collection, the lending information should be dropped too.
	If we want to remove a borrower from the system, we should first have them check the book back in or we should strike any books they haven&apos;t returned from the system.
	As long as we&apos;re keeping the books, even if we disallow that patron from borrowing any more, we need to keep that former patron&apos;s information in the database so we know who has our book.
	<code>librarian</code> <strong>*can*</strong> be null though.
	If we fire a librarian and remove them from the database, the books that they lent out before are still out there, and we need to know which books are missing and who has them.
	When a book is checked out, <code>checked_out</code> is automatically set to the current date, unless we specify otherwise.
	A book cannot be <code>due_back</code> before it is checked out though, so a constraint ensures this basic logic is followed.
	<code>due_back</code> isn&apos;t automatically set though, as the instructions never told us how long a book could be checked out for.
	Additionally, there could be multiple factors.
	A long-time patron may be allowed to check books out for longer or a popular book may be due back sooner.
	By not automatically setting a due date, we allow for flexibility.
</p>
END
);