y.st./source/pages/en/coursework/CS2203/Library_database_relations~_continued.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 relations, continued',
	'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-06-21',
	'copyright year' => '2017',
	'body' => <<<END
<p>
	The entity integrity constraints are simple.
	First, every relation must have a primary key.
	This primary key must not be null and must be unique to each tuple.
	Semantic and domain constraints in the form of data types are listed in the lists below.
	With a more-complex language, we could also add constraints that, for example, required that the $a[ISBN] check sum be accurate and that the telephone number be in an acceptable format.
	Null and unique constraints are in the lists below as well.
	Remember that the telephone number of a librarian cannot be null, because the librarians are using the library&apos;s internal extension system, not their own telephones.
	However, a member might have a null telephone number because they might not have telephone service.
</p>
<p>
	The only foreign keys in the database are in the borrowed books relation.
	The borrower of a book cannot be null.
	If an attempt is made to remove a borrower from the database, it will be rejected if that borrower has any books checked out.
	The books must be turned in or removed from the library database before the member can be removed.
	If a book is removed from the database and it&apos;s checked out, it&apos;s tuple in the lent books table is removed by cascade too.
	A librarian may quit or be fired.
	In that case, they may be removed from the database.
	For keeping records straight, we should wait to remove the librarian until after any books they lent are brought back.
	However, we don&apos;t have to wait if we don&apos;t want to.
	If a librarian is removed, any books they lent out will have their librarian field nullified.
</p>
<h2><code>Book</code>:</h2>
<p>
	The <code>Book</code> relation will include the following attributes in its tuples:
</p>
<ul>
	<li>
		<code>id</code> (integer, unique, primary key, auto-increment, not null, candidate key)
	</li>
	<li>
		<code>title</code> (string, not null)
	</li>
	<li>
		<code>author</code> (string, not null)
	</li>
	<li>
		<code>isbn</code> (string, not null)
	</li>
	<li>
		<code>publication_date</code> (string, not null)
	</li>
	<li>
		<code>cost</code> (float, not null)
	</li>
</ul>
<h2><code>Borrower</code>:</h2>
<p>
	The <code>Borrower</code> relation will have the following attributes in its tuples:
</p>
<ul>
	<li>
		<code>card_number</code> (integer, unique, primary key, not null, candidate key)
	</li>
	<li>
		<code>name</code> (string, not null)
	</li>
	<li>
		<code>address</code> (string, not null)
	</li>
	<li>
		<code>postal_code</code> (integer, not null)
	</li>
	<li>
		<code>telephone_number</code> (string)
	</li>
	<li>
		<code>join_date</code> (string, not null)
	</li>
</ul>
<h2><code>BookLended</code>:</h2>
<p>
	The tuples of the <code>BookLended</code> relation will have these attributes:
</p>
<ul>
	<li>
		<code>book</code> (integer, unique, primary key, foreign key, not null, candidate key)
	</li>
	<li>
		<code>checked_out</code> (string, not null)
	</li>
	<li>
		<code>due_back</code> (string, not null)
	</li>
	<li>
		<code>borrower</code> (integer, foreign key, not null)
	</li>
	<li>
		<code>librarian</code> (integer, foreign key)
	</li>
</ul>
<h2><code>Librarian</code>:</h2>
<p>
	The attributes of the <code>Librarian</code> relation are as follows:
</p>
<ul>
	<li>
		<code>id</code> (integer, unique, primary key, not null, candidate key)
	</li>
	<li>
		<code>name</code> (string, not null)
	</li>
	<li>
		<code>extension</code> (integer, unique, alternate key, not null, candidate key)
	</li>
</ul>
END
);