csh
/
y.st.
forked from y.st./y.st.


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234
							<?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~_continued.xhtml" />
		<title>Library database relations, continued &lt;https://y.st./en/coursework/CS2203/Library_database_relations~_continued.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~_continued.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, continued</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>
<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 <abbr title="International Standard Book Number">ISBN</abbr> 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>
		<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%7E_continued.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%7E_continued.xhtml"><abbr title="Cascading Style Sheets">CSS</abbr>3</a> specification.
		</p>
	</body>
</html>