📄 index.html
字号:
<!-- -*-html-*-
$Source: /usr/local/cvsroot/BigC++/28/index.html,v $
$Revision: 1.4 $
Big C++, chptr 28
editor: cols=80, tabstop=2
Kurt Schmidt, kschmidt@cs.drexel.edu
NOTES
- 3 spaces are used for each indent in examples
REVISIONS:
$Log: index.html,v $
Revision 1.4 2004/04/20 22:03:05 kurt
Made slides smaller (view %150, on 1024x768 display)
Revision 1.3 2004/04/12 05:01:06 kurt
Not sure
Revision 1.2 2004/02/27 02:44:57 kurt
moved links to sutils
Revision 1.1 2004/02/27 02:39:46 kurt
creation
Revision 1.1 2004/02/27 02:27:37 kurt
Creation
-->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" src="./config.js"></script>
<script language="JavaScript" src="./pageFormat.js"></script>
<script><!-- // Set title on page
title()
//--></script>
</head>
<body>
<hr><h2><font color="#009999" size="+3">Chapter 28 - Relational Databases
</font></h2>
<font size="+1">
<script><!--
image( "cover.png" )
//--></script>
</font>
<hr><h2><font color="#009999" size="+2">Chapter Goals</font></h2>
</font>
<hr noshade size="4" color="#009999">
<font size="+1">
<ul>
<li>To understand how relational databases store information</li>
<li>To learn how to query a database with the Structured Query Language (SQL)
</li>
<li>To connect to a database with an application programming interface
(API)</li>
<li>To write database programs that insert and query data in a relational
database</li>
</ul>
</font>
<hr><h2><font color="#009999">Chapter 28 - Relational Databases
</font></h2>
<font size="+1">
<ul>
<li>Store, add, remove, change, and find data</li>
<li>Efficiently</li>
<li><i>Database management systems</i> (DBMS) allow the programmer to focus
on the data</li>
<li><i>Relational</i> database (RDB) is the most common type</li>
<li>Allows you to use a <i>structured query language</i>
<ul>
<li>Formulate queries, updates, etc. naturally</li>
</ul>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1 Organising Database Information</font></h2>
<h2><font color="#009999">28.1.1 Database Tables</font></h2>
<font size="+1">
<ul>
<li>RDB stores information in <i>tables</i></li>
<li>E.g., a Product Table in an RDBMS</li>
</ul>
<table border='1' cellpadding='4'>
<tr bgcolor='#00ffff'>
<th>Product_Code</th>
<th>Description</th>
<th>Unit_Price</th>
</tr>
<tr>
<td align='center'>116-064</td>
<td align='center'>Toaster</td>
<td align='center'>24.95</td>
</tr>
<tr>
<td align='center'>257-535</td>
<td align='center'>Hair dryer</td>
<td align='center'>29.95</td>
</tr>
<tr>
<td align='center'>643-119</td>
<td align='center'>Car vacuum</td>
<td align='center'>19.99</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.1.1 Database Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Each <i>row</i> describes a product
<ul>
<li>Contains <i>fields</i>, one per column</li>
</ul>
</li>
<li><i>Column headers</i> are product attributes</li>
<li>All items in the same column have the same type</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.1 Database Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Allowable column types differ somewhat</li>
<li>SQL-compliant RDBs commonly support:</li>
</ul>
<table border='1' cellpadding='4'>
<tr bgcolor='#00ffff'>
<th>SQL Data Type</th>
<th>C++ Data Type</th>
</tr>
<tr>
<td align='center'>INTEGER or INT</th>
<td align='center'>int</td>
</tr>
<tr>
<td align='center'>REAL</td>
<td align='center'>float</td>
</tr>
<tr>
<td align='center'>DOUBLE</td>
<td align='center'>double</td>
</tr>
<tr>
<td align='center'>DECIMAL(n, x)</td>
<td align='center'>Fixed-point decimal numbers with n total digits and
x digits after the decimal point</td>
</tr>
<tr>
<td align='center'>BOOLEAN</td>
<td align='center'>bool</td>
</tr>
<tr>
<td align='center'>CHARACTER(x) or CHAR(x)</td>
<td align='center'>Fixed-length string of length x. Similar to
string</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.1.1 Database Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Use SQL to interact w/the DB</li>
<li>E.g., to create a product table:
<blockquote>
<pre>CREATE TABLE Products
(
Product_Code CHAR(11),
Description CHAR(40),
Unit_Price DECIMAL(10, 2)
)</pre>
</blockquote>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.1 Database Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>SQL <b>not</b> case sensitive</li>
<li>By convention, CAPS for keywords, mixed case for table and column
names</li>
<li>Uses single quotes to delimit strings</li>
<ul>
<li>Use two single quotes together to represent the single quote:
<blockquote><tt>
'Sam''s Small Appliances'
</tt></blockquote>
</li>
</ul>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.1 Database Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>To add a row:
<blockquote><tt>
INSERT INTO Products<br>
VALUES ('257-535', 'Hair dryer', 29.95)
</tt></blockquote>
</li>
<li>Values are in order (or can be named)</li>
<li>To remove (not clear) a table:
<blockquote><tt>
DROP TABLE Test
</tt></blockquote>
</li>
<li><b><font color="#009999">Note:</font></b> Avoid proprietary SQL
extensions</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables</font></h2>
<font size="+1">
<ul>
<li>Store a simple object's data...
<blockquote>
<pre>class Customer
{
...
private:
string name;
string address;
string city;
string state;
string zip;
};</pre>
</blockquote>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>as a row in a table (Customer table):
<table border='1' cellpadding='4'>
<tr bgcolor='#00ffff'>
<th>Name</th>
<th>Address</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
</tr>
<tr>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(30)</tt></td>
<td align='center'><tt>CHAR(2)</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
</tr>
<tr>
<td align='center'>Sam's Small Appliances</td>
<td align='center'>100 Main St.</td>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
<td align='center'>98765</td>
</tr>
</table>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Problems if an object references another object:
<blockquote>
<pre>class Invoice
{
...
private:
Customer* cust;
...
};</pre>
</blockquote>
</li>
<li>Can't store a customer in a column (no ptrs. in SQL)</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Store customer info in invoice table?</li>
</ul>
<table border='1' cellpadding='4'>
<tr bgcolor='#00ffff'>
<th>Invoice_Number</th>
<th>Customer_<br>Name</th>
<th>Customer_<br>Address</th>
<th>Customer_<br>City</th>
<th>Customer_<br>State</th>
<th>Customer_<br>Zip</th>
<th>...</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(30)</tt></td>
<td align='center'><tt>CHAR(2)</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
<td align='center'><tt>...</tt></td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>Sam's Small Appliances</td>
<td align='center'>100 Main St.</td>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
<td align='center'>98765</td>
<td align='center'>...</td>
</tr>
<tr>
<td align='center'>11732</td>
<td align='center'>Electronics Unlimited</td>
<td align='center'>1175 Liberty Ave.</td>
<td align='center'>Pleasantville</td>
<td align='center'>MI</td>
<td align='center'>45066</td>
<td align='center'>...</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>Sam's Small Appliances</td>
<td align='center'>100 Main St.</td>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
<td align='center'>98765</td>
<td align='center'>...</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Bad idea - replicate data</li>
<ul>
<li>Wastes space</li>
<li>Updates become unmanageable, inefficient, error-prone</li>
</ul>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables (cont.)</font></h2>
<font size="+1">
<table cellpadding='12'>
<tr>
<td valign='top'><font size='+1'>
<ul>
<li>In C++, many objects can refer to a single object</li>
<li>To accomplish this in SQL:
<ul>
<li>Break data into 2 tables</li>
<li>Solves replication problem</li>
<li>Cust# <i>primary key</i> for Customer table
<ul>
<li>A <i>foreign key</i> in the Invoice table</li>
<li>Links tables</li>
</ul>
</li>
</ul>
</li>
</ul>
</font>
</td>
<td>
<table border='1' cellpadding='4'>
<tr>
<tr><th colspan='3'>Invoice</th></tr>
</tr>
<tr bgcolor='#00ffff'>
<th>Invoice_<br>Number</th>
<th>Customer_<br>Number</th>
<th>Customer_<br>Payment</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>DECIMAL(10,2)</tt></td>
</tr>
<tr>
<td align='center'>11731</td>
<td align='center'>3175</td>
<td align='center'>0</td>
</tr>
<tr>
<td align='center'>11732</td>
<td align='center'>3176</td>
<td align='center'>249.95</td>
</tr>
<tr>
<td align='center'>11733</td>
<td align='center'>3175</td>
<td align='center'>0</td>
</tr>
</table>
</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Add a unique field - <i>primary key</i></li>
</ul>
<table border='1' cellpadding='4'>
<tr><th colspan='6'>Customer</th></tr>
<tr bgcolor='#00ffff'>
<th>Customer_<br>Number</th>
<th>Name</th>
<th>Address</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(40)</tt></td>
<td align='center'><tt>CHAR(30)</tt></td>
<td align='center'><tt>CHAR(2)</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
</tr>
<tr>
<td align='center'>3175</td>
<td align='center'>Sam's Small Appliances</td>
<td align='center'>100 Main St.</td>
<td align='center'>Anytown</td>
<td align='center'>CA</td>
<td align='center'>98765</td>
</tr>
<tr>
<td align='center'>3176</td>
<td align='center'>Electronics Unlimited</td>
<td align='center'>1175 Liberty Ave.</td>
<td align='center'>Pleasantville</td>
<td align='center'>MI</td>
<td align='center'>45066</td>
</tr>
</table>
</font>
<hr><h2><font color="#009999">28.1.2 Linking Tables (cont.)</font></h2>
<font size="+1">
<ul>
<li>Primary key:
<ul>
<li>Field (or fields)</li>
<li>Unique</li>
<li>Used to establish links</li>
<li>Not required</li>
</ul>
</li>
<li>Foreign key:
<ul>
<li>Field(s) matched by primary key from another table</li>
<li>Not unique</li>
</ul>
</li>
<li>Relationship <b>not</b> maintained by DB</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.3 Implementing One-to-Many Relationships
</font></h2>
<font size="+1">
<ul>
<li>Each invoice linked to exactly one customer</li>
<li>An invoice has many items</li>
<li>In C++, <tt>Item</tt>s stored in container:
<blockquote>
<pre>class Invoice
{
...
private:
Customer* cust;
vector<Item> items;
double payment;
};</pre>
</blockquote>
</li>
</ul>
</font>
<hr><h2><font color="#009999">28.1.3 Implementing One-to-Many Relationships
(cont.)</font></h2>
<font size="+1">
<ul>
<li>Novice programmers often replicate columns:
<ul>
<li>Wastes space (1 or 2 items)</li>
<li>Doesn't solve problem (4 or 12 items)</li>
</ul>
</li>
</ul>
<table border='1' cellpadding='4'>
<tr><th colspan='9'>Poor design for Invoice Table</th></tr>
<tr bgcolor='#00ffff'>
<th>Invoice_<br>Number</th>
<th>Customer_<br>Number</th>
<th>Product_<br>Code1</th>
<th>Quantity1</th>
<th>Product_<br>Code2</th>
<th>Quantity2</th>
<th>Product_<br>Code3</th>
<th>Quantity3</th>
<th>Payment</th>
</tr>
<tr>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
<td align='center'><tt>INTEGER</tt></td>
<td align='center'><tt>CHAR(10)</tt></td>
<td align='center'><tt>INTEGER</tt></td>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -