csh
/
y.st.
rozštěpen z y.st./y.st.


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273
							<?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_relations.xhtml" />
		<title>Library database relations &lt;https://y.st./en/coursework/CS2203/Library_database_relations.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_relations.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 relations</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-06-21</p>
		</header>
<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>
<p>
	This relation has a relation degree of six.
</p>
<p>
	<code>id</code> will be the primary key, and will auto-increment as books are added.
	This attribute will be an integer.
	It&apos;s <strong>*extremely*</strong> tempting to use the <code>isbn</code> attribute as the primary key, but there could be multiple copies of the same book in the library&apos;s collection.
	If there are multiple copies of any book, no field will be unique unless we add this artificial key.
</p>
<p>
	<code>title</code> will be a string.
	Obviously, it&apos;ll be the title of the book.
	<code>author</code> too will be a string, the author of the book.
	This will be in &quot;{given name} {surname}&quot; format, <strong>*not*</strong> &quot;{surname}, {given name}&quot; format.
	The proper format of an <abbr title="International Standard Book Number">ISBN</abbr> includes three hyphens, so to accommodate this, the <code>isbn</code> attribute is a string, not an integer.
	<code>publication_date</code> is the date of the book&apos;s publication in the <code>*international standard format*</code>, which happens to be big endian.
	That is to say, if the book were published today, the <code>publication_date</code> would be set to <code>2017-06-21</code>, not something goofy such as <code>06-21-2017</code>.
	This field is a string to accommodate use of hyphens as well.
	The cost of the book wil be stored in the <code>cost</code> attribute.
	This field is a floating-point decimal to allow for partial currency units.
	In the United States, dollars would be the whole units and cents would be represented by hundredths of units.
	As this is a floating-point decimal and not a string, a currency sign such as a dollar sign is not included.
</p>
<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>
<p>
	This relation has a relation degree of seven.
</p>
<p>
	<code>card_number</code> will be an integer set to the cardholder&apos;s library card number.
	These numbers will likely not be sequential, but they will be unique.
	This field will be used as the primary key.
	<code>name</code> will be a string attribute used to store the cardholder&apos;s name.
	<code>address</code> will store their postal address as a string, excluding the postal code.
	<code>postal_code</code> will be an integer storing the postal code of each cardholder.
	<code>telephone_number</code> will be used to store the cardholder&apos;s telephone number.
	Not everyone has telephone service though.
	To allow for this, the <code>telephone_number</code> attribute can be null, unlike other attributes of these relations.
	<code>join_date</code> will be a string, using the international, standard date format.
</p>
<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, not null)
	</li>
</ul>
<p>
	This relation has a relation degree of five.
</p>
<p>
	The <code>book</code> attribute is a foreign key representing a book that is currently checked out.
	It&apos;s an integer that matched the <code>id</code> of a book in the <code>Books</code> relation.
	Because a book cannot be checked out by two people at once, the <code>book</code> also serves as the primary key of the <code>BookLended</code> relation.
	<code>checked_out</code> and <code>due_back</code> are the dates the book was checked out and will be due back, respectively.
	As before, the international, standard, big-endian format is used here.
	<code>borrower</code> is an integer that acts as a foreign key to the <code>Borrower</code> relation.
	This represents the cardholder that currently has the book in their possession.
	The <code>librarian</code> attribute is an integer and a foreign key representing the <abbr title="identification">ID</abbr> number of the librarian that checked out the book.
</p>
<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>
<p>
	This relation has a relation degree of three.
</p>
<p>
	The primary key of this relation is the librarians&apos; <abbr title="identification">ID</abbr> numbers, stored as the <code>id</code> property.
	However, as each librarian has their own extension, the <code>extension</code> field both stores that extension and acts as an alternate key.
	While our borrowers may not all have telephone numbers, all of our librarians are reachable by telephone, so this attribute cannot be null.
	This is because we&apos;re using the library&apos;s internal telephone setup, not relying on the librarians to be subscribed to telephone service.
	The librarian&apos;s name is stored using the <code>name</code> key.
</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_relations.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_relations.xhtml"><abbr title="Cascading Style Sheets">CSS</abbr>3</a> specification.
		</p>
	</body>
</html>