csh
/
y.st.
разклонено от y.st./y.st.


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309
							<?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_SQL_select.xhtml" />
		<title>Library database SQL select &lt;https://y.st./en/coursework/CS2203/Library_database_SQL_select.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_SQL_select.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 <abbr title="Structured Query Language">SQL</abbr> <code>select</code></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-08-02</p>
		</header>
<h2><abbr title="Structured Query Language">SQL</abbr> 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> statement and output</h2>
<blockquote>
<pre><code>select BORROWER.NAME, BORROWER.ADDRESS, BORROWER.POSTAL_CODE, BORROWER.TELEPHONE_NUMBER, LIBRARIAN.NAME, LIBRARIAN.TELEPHONE, BOOKLENDED.RETURN_DATE, BOOK.TITLE, BOOK.COST, BOOK.ISBN
	from BOOKLENDED, BOOK, BORROWER, LIBRARIAN
	where BOOKLENDED.RETURN_DATE &lt; 20170802
	and BOOK.COST &gt; 10
	and BOOKLENDED.ISBN = BOOK.ISBN
	and BOOKLENDED.LIBRARIANID = LIBRARIAN.LIBRARIAN_ID
	order by BOOKLENDED.RETURN_DATE desc
;</code></pre>
</blockquote>
<blockquote>
<pre>Jane Doe,65 Water St.,42358,5554581,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Jim Jones,23 Hill Drive,85423,5557891,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Steve Smith,435 Main St.,28454,5556565,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Arun Goel,34 Home St.,56234,5554889,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Sue Smith,2176 Baker st.,43542,5556723,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Jeff Bridges,176 Right st.,28460,5551745,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Samil Shah,123 Home st,62989,5551212,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Tim Jones,3435 Main st.,54232,5552934,Penelope Pretty,5551212,20150127,Grimm’s Fairy Tales,26.99,1429477,20150127,
Jane Doe,65 Water St.,42358,5554581,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Jim Jones,23 Hill Drive,85423,5557891,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Steve Smith,435 Main St.,28454,5556565,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Arun Goel,34 Home St.,56234,5554889,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Sue Smith,2176 Baker st.,43542,5556723,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Jeff Bridges,176 Right st.,28460,5551745,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Samil Shah,123 Home st,62989,5551212,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Tim Jones,3435 Main st.,54232,5552934,Mable Markham,5551212,20120120,Database System Concepts,119.67,3523323,20120120,
Jane Doe,65 Water St.,42358,5554581,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,
Jim Jones,23 Hill Drive,85423,5557891,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,
Steve Smith,435 Main St.,28454,5556565,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,
Arun Goel,34 Home St.,56234,5554889,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,
Sue Smith,2176 Baker st.,43542,5556723,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,
Jeff Bridges,176 Right st.,28460,5551745,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,
Samil Shah,123 Home st,62989,5551212,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,
Tim Jones,3435 Main st.,54232,5552934,Mable Markham,5551212,20101201,A First Course in Database Systems (3rd ed.),99.49,6006374,20101201,</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 <abbr title="International Standard Book Number">ISBN</abbr> is an International Standard Book Number.
	That means that an &quot;<abbr title="International Standard Book Number">ISBN</abbr> 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;<abbr title="personal identification number">PIN</abbr> numbers&quot; and &quot;<abbr title="automated teller machine">ATM</abbr> 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 <abbr title="Structured Query Language">SQL</abbr> 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 <abbr title="identification">ID</abbr>s though, as they weren&apos;t set to automatically increment.
	Librarian <abbr title="identification">ID</abbr>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 <abbr title="International Standard Book Number">ISBN</abbr> 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>
		<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_SQL_select.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_SQL_select.xhtml"><abbr title="Cascading Style Sheets">CSS</abbr>3</a> specification.
		</p>
	</body>
</html>