y.st./source/pages/en/coursework/CS2203/Library_database_SQL~_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 $a[SQL], 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-07-26',
	'copyright year' => '2017',
	'body' => <<<END
<h2>$a[SQL] commands</h2>
<blockquote>
<pre><code>create table Book (
	ISBN decimal(13,0) not null primary key,
	Title varchar(64) not null,
	Author varchar(32) not null,
	Cost decimal(5,2) not null
);
create table Bookcopy(
	ISBN decimal(13,0) not null,
	Sequence int not null,
	PublicationDate decimal(8,0) not null,

	unique(ISBN, Sequence),
	primary key(ISBN, Sequence),
	foreign key (ISBN) references Book(ISBN)
);
create table Borrower (
	Library_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 decimal(5,0) not null,
	Telephone_Number int,
	MembershipDate decimal(8,0) not null
);
create table Librarian (
	Librarian_id int not null primary key,
	Name varchar(32) not null,
	Telephone int not null,
	Supervisor int,

	foreign key (Supervisor) references Librarian(Librarian_id)
);
create table BookLended(
	Borrower int not null,
	Checked_Out_Date decimal(8,0) not null,
	Return_Date decimal(8,0) not null,
	ISBN decimal(13,0) not null,
	Sequence int not null,
	LibrarianId int,

	unique(ISBN, Sequence),
	primary key(ISBN, Sequence),
	check (Return_date &gt; Checked_Out_Date),
	foreign key (ISBN, Sequence) references Bookcopy(ISBN, Sequence),
	foreign key (Borrower) references Borrower(Library_Card_Number),
	foreign key (LibrarianId) references Librarian(Librarian_id)
);

insert into Book values (1441438, &apos;Alice in Wonderland&apos;                         , &apos;Lewis Carroll&apos;       ,   7.95);
insert into Book values (6006374, &apos;A First Course in Database Systems (3rd ed.)&apos;, &apos;Jeffrey Ullman&apos;      ,  99.49);
insert into Book values (3523323, &apos;Database System Concepts&apos;                    , &apos;Abraham Silberschatz&apos;, 119.67);
insert into Book values (1429477, &apos;Grimm’s Fairy Tales&apos;                         , &apos;Jacob Grimm&apos;         ,  26.99);
insert into Book values (1486025, &apos;A Tale of Two Cities&apos;                        , &apos;Charles Dickens&apos;     ,   7.95);
insert into Book values (1853602, &apos;War and Peace&apos;                               , &apos;Leo Tolstoy&apos;         ,   7.99);
insert into Book values (1904129, &apos;The Scarlet letter&apos;                          , &apos;Nathaniel Hawthorne&apos; ,   7.95);
insert into Book values (1593832, &apos;Pride and Prejudice&apos;                         , &apos;Jane Austen&apos;         ,   7.95);
insert into Book values (1538243, &apos;Pride and Prejudice&apos;                         , &apos;Jane Austen&apos;         ,   7.95);

insert into Bookcopy values (1441438, 1, 19970501);
insert into Bookcopy values (6006374, 1, 20071006);
insert into Bookcopy values (6006374, 2, 20071006);
insert into Bookcopy values (3523323, 1, 20100127);
insert into Bookcopy values (1429477, 1, 20040201);
insert into Bookcopy values (1429477, 2, 20040201);
insert into Bookcopy values (1429477, 3, 20040201);
insert into Bookcopy values (1429477, 4, 20040201);
insert into Bookcopy values (1486025, 1, 20101201);
insert into Bookcopy values (1853602, 1, 20070901);
insert into Bookcopy values (1853602, 2, 20100901);
insert into Bookcopy values (1904129, 1, 20091001);
insert into Bookcopy values (1593832, 1, 20040920);
insert into Bookcopy values (1538243, 1, 20040920);
insert into Bookcopy values (1538243, 2, 20040920);

insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Samil Shah&apos;  , &apos;123 Home st&apos;   , 62989, 5551212, 20080201);
insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Tim Jones&apos;   , &apos;3435 Main st.&apos; , 54232, 5552934, 20110713);
insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Sue Smith&apos;   , &apos;2176 Baker st.&apos;, 43542, 5556723, 20050510);
insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Jeff Bridges&apos;, &apos;176 Right st.&apos; , 28460, 5551745, 20100620);
insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Steve Smith&apos; , &apos;435 Main St.&apos;  , 28454, 5556565, 20050518);
insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Arun Goel&apos;   , &apos;34 Home St.&apos;   , 56234, 5554889, 20080315);
insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Jane Doe&apos;    , &apos;65 Water St.&apos;  , 42358, 5554581, 20110907);
insert into Borrower (Name, Address, Postal_Code, Telephone_Number, MembershipDate) values (&apos;Jim Jones&apos;   , &apos;23 Hill Drive&apos; , 85423, 5557891, 20101123);

insert into Librarian values (1, &apos;Gertrude Smith&apos;  , 5551212, null);
insert into Librarian values (2, &apos;Mable Markham&apos;   , 5551212, 1   );
insert into Librarian values (3, &apos;Penelope Pretty&apos; , 5551212, 1   );
insert into Librarian values (4, &apos;Olga Brown&apos;      , 5552300, 1   );

insert into BookLended values (1, 20101201, 20131020, 1441438, 1, 1);
insert into BookLended values (4, 20101001, 20101201, 6006374, 2, 2);
insert into BookLended values (1, 20101201, 20120120, 3523323, 1, 2);
insert into BookLended values (7, 20101201, 20150127, 1429477, 1, 3);
insert into BookLended values (5, 20101201, 20150701, 1853602, 2, 4);
insert into BookLended values (2, 20101201, 20150823, 1904129, 1, 2);</code></pre>
</blockquote>
<h2><code>SELECT</code> statements and output</h2>
<blockquote>
	<p>
		<code>select * from BOOK;</code>
	</p>
</blockquote>
<blockquote>
<pre>1429477,Grimm’s Fairy Tales,Jacob Grimm,26.99,
1441438,Alice in Wonderland,Lewis Carroll,7.95,
1486025,A Tale of Two Cities,Charles Dickens,7.95,
1538243,Pride and Prejudice,Jane Austen,7.95,
1593832,Pride and Prejudice,Jane Austen,7.95,
1853602,War and Peace,Leo Tolstoy,7.99,
1904129,The Scarlet letter,Nathaniel Hawthorne,7.95,
3523323,Database System Concepts,Abraham Silberschatz,119.67,
6006374,A First Course in Database Systems (3rd ed.),Jeffrey Ullman,99.49,</pre>
</blockquote>
<blockquote>
	<p>
		<code>select * from BOOKCOPY;</code>
	</p>
</blockquote>
<blockquote>
<pre>1429477,1,20040201,
1429477,2,20040201,
1429477,3,20040201,
1429477,4,20040201,
1441438,1,19970501,
1486025,1,20101201,
1538243,1,20040920,
1538243,2,20040920,
1593832,1,20040920,
1853602,1,20070901,
1853602,2,20100901,
1904129,1,20091001,
3523323,1,20100127,
6006374,1,20071006,
6006374,2,20071006,</pre>
</blockquote>
<blockquote>
	<p>
		<code>select * from BORROWER;</code>
	</p>
</blockquote>
<blockquote>
<pre>0,Samil Shah,123 Home st,62989,5551212,20080201,
1,Tim Jones,3435 Main st.,54232,5552934,20110713,
2,Sue Smith,2176 Baker st.,43542,5556723,20050510,
3,Jeff Bridges,176 Right st.,28460,5551745,20100620,
4,Steve Smith,435 Main St.,28454,5556565,20050518,
5,Arun Goel,34 Home St.,56234,5554889,20080315,
6,Jane Doe,65 Water St.,42358,5554581,20110907,
7,Jim Jones,23 Hill Drive,85423,5557891,20101123,</pre>
</blockquote>
<blockquote>
	<p>
		<code>select * from BOOKLENDED;</code>
	</p>
</blockquote>
<blockquote>
<pre>7,20101201,20150127,1429477,1,3,
1,20101201,20131020,1441438,1,1,
5,20101201,20150701,1853602,2,4,
2,20101201,20150823,1904129,1,2,
1,20101201,20120120,3523323,1,2,
4,20101001,20101201,6006374,2,2,</pre>
</blockquote>
<blockquote>
	<p>
		<code>select * from LIBRARIAN;</code>
	</p>
</blockquote>
<blockquote>
<pre>1,Gertrude Smith,5551212,,
2,Mable Markham,5551212,1,
3,Penelope Pretty,5551212,1,
4,Olga Brown,5552300,1,</pre>
</blockquote>
<h2>Explanations</h2>
<p>
	I tried to begin the database construction with the command <code>CREATE SCHEMA AUTHORIZATION PUBLIC;</code> like the book said we should, but LibreOffice Base refuses to accept the command.
</p>
<p>
	In the example specified in the assignment, the <code>Book</code> table had a <code>ISBN_number</code> key.
	That key name makes no sense.
	An $a[ISBN] is an International Standard Book Number.
	That means that an &quot;$a[ISBN] Number&quot; would be an &quot;International Standard Book Number Number&quot;.
	What would that even be?
	Would it be a number with wich to look up another number?
	Who even knows.
	This is the same mistake made by people that talk about &quot;$a[PIN] numbers&quot; and &quot;$a[ATM] machines&quot;.
	In order to make my copy of the database not look like it was created by an idiot, I simply shortened that key name from <code>ISBN_number</code> to <code>ISBN</code>.
</p>
<p>
	Month, day, year, is a stupid format for dates.
	A month is larger than a day while smaller than a year, yet it doesn&apos;t fall between the two in this format.
	That makes it neither big endian nor small endian, and is difficult to compare to other dates, especially in an automated way.
	Day, month, year would at least put the date elements in a logical order, but a better order would be the international standard: Year, month, day.
	In the order day, month, year, the components are in small endian order, but the numbers composing each component tend to be in big endian format.
	Using the international standard; year, month, day; the components are in big endian order and the numbers composing each component are in big endian order.
	That makes this date format the easiest to read, understand, and compare to other dates.
	Computers can sort these dates or <code>select</code> them using simple operators, such as <code>&gt;</code> and <code>&lt;</code>.
	For that reason, I used the form &quot;yyyymmdd&quot; instead of the &quot;mm/dd/yyyy&quot; suggested by the assignment.
	Additionally, the <code>date</code> data type is non-standard and isn&apos;t a part of the real $a[SQL] standard.
	For that reason, I used a basic <code>decimal(8,0)</code> field to hold this data in all tables that deal with dates.
</p>
<p>
	The data to enter into the <code>BookLended</code> table seemed to assume that the library card numbers were indexed from one.
	We&apos;re not kindergarteners, unaware that the number zero even exists.
	We&apos;re computer science professionals!
	We should know and understand that indexes should <strong>*always*</strong> start at zero unless there&apos;s a very good reason to start from elsewhere.
	In order to make the data entered into the <code>BookLended</code> table conform to the restraints placed on the database and actually make sense, I decremented the <code>Borrower</code> field for each tuple by one.
	I didn&apos;t do the same for the librarian $a[ID]s though, as they weren&apos;t set to automatically increment.
	Librarian $a[ID]s would need to be added by the librarians by hand, and it&apos;s very likely that, as they aren&apos;t computer scientists, they wouldn&apos;t even consider the possibility of indexing from zero.
	The primary key of this table is the combined $a[ISBN] and sequence number, as those two keys represent a single copy of a book.
	It&apos;s not possible for a single copy of a book to be checker out to more than one borrower at a time, so the book will have to be returned (anf the checkout thus cleared from the table) before that copy may be checked out again.
</p>
<p>
	One of the books was supposed to be due back before it was even checked out.
	This makes no sense, and violated one of the integrity checks I set up.
	To enter this data, I swapped the due date and the checkout date, creating more-sensible data.
</p>
<p>
	When selecting data, LibreOffice Base seemed to be unable to find any tables unless their names were in all caps, regardless of whether the name was in all caps when the tables were created or data added to them.
	For this reason, the <code>select</code> statements use all-caps names, even though the statements that set up the database for use did not.
</p>
END
);