"cell_type": "markdown",
"metadata": {},
"source": [
- "A [relational database](../gloss.html#relational-database)\n",
+ "A [relational database](../../gloss.html#relational-database)\n",
"is a way to store and manipulate information\n",
- "that is arranged as [tables](../gloss.html#table).\n",
- "Each table has columns (also known as [fields](../gloss.html#field-database)) which describe the data,\n",
- "and rows (also known as [records](../gloss.html#record-database)) which contain the data.\n",
+ "that is arranged as [tables](../../gloss.html#table).\n",
+ "Each table has columns (also known as [fields](../../gloss.html#field-database)) which describe the data,\n",
+ "and rows (also known as [records](../../gloss.html#record-database)) which contain the data.\n",
" \n",
"When we are using a spreadsheet,\n",
"we put formulas into cells to calculate new values based on old ones.\n",
"When we are using a database,\n",
"we send commands\n",
- "(usually called [queries](../gloss.html#query))\n",
- "to a [database manager](../gloss.html#database-manager):\n",
+ "(usually called [queries](../../gloss.html#query))\n",
+ "to a [database manager](../../gloss.html#database-manager):\n",
"a program that manipulates the database for us.\n",
"The database manager does whatever lookups and calculations the query specifies,\n",
"returning the results in a tabular form\n",
"> every database manager can import and export data in a variety of formats,\n",
"> so it *is* possible to move information from one to another.\n",
"\n",
- "Queries are written in a language called [SQL](../gloss.html#sql),\n",
+ "Queries are written in a language called [SQL](../../gloss.html#sql),\n",
"which stands for \"Structured Query Language\".\n",
"SQL provides hundreds of different ways to analyze and recombine data;\n",
"we will only look at a handful,\n",
"and the table name in Title Case,\n",
"but we don't have to:\n",
"as the example below shows,\n",
- "SQL is [case insensitive](../gloss.html#case-insensitive)."
+ "SQL is [case insensitive](../../gloss.html#case-insensitive)."
]
},
{
"metadata": {},
"source": [
"One of the most powerful features of a database is\n",
- "the ability to [filter](../gloss.html#filter) data,\n",
+ "the ability to [filter](../../gloss.html#filter) data,\n",
"i.e.,\n",
"to select only those records that match certain criteria.\n",
"For example,\n",
" What does it actually produce?\n",
"\n",
"1. Some database designers prefer to use\n",
- " a [sentinel value](../gloss.html#sentinel-value)\n",
+ " a [sentinel value](../../gloss.html#sentinel-value)\n",
" to mark missing data rather than `null`.\n",
" For example,\n",
" they will use the date \"0000-00-00\" to mark a missing date,\n",
"metadata": {},
"source": [
"but to combine them,\n",
- "wee must use an [aggregation function](../gloss.html#aggregation-function)\n",
+ "wee must use an [aggregation function](../../gloss.html#aggregation-function)\n",
"such as `min` or `max`.\n",
"Each of these functions takes a set of records as input,\n",
"and produces a single record as output:"
"metadata": {},
"source": [
"`join` creates\n",
- "the [cross product](../gloss.html#cross-product)\n",
+ "the [cross product](../../gloss.html#cross-product)\n",
"of two tables,\n",
"i.e.,\n",
"it joins each record of one with each record of the other\n",
"We can tell which records from `Site`, `Visited`, and `Survey`\n",
"correspond with each other\n",
"because those tables contain\n",
- "[primary keys](../gloss.html#primary-key)\n",
- "and [foreign keys](../gloss.html#foreign-key).\n",
+ "[primary keys](../../gloss.html#primary-key)\n",
+ "and [foreign keys](../../gloss.html#foreign-key).\n",
"A primary key is a value,\n",
"or combination of values,\n",
"that uniquely identifies each record in a table.\n",
"Now that we have seen how joins work,\n",
"we can see why the relational model is so useful\n",
"and how best to use it.\n",
- "The first rule is that every value should be [atomic](../gloss.html#atomic-value),\n",
+ "The first rule is that every value should be [atomic](../../gloss.html#atomic-value),\n",
"i.e.,\n",
"not contain parts that we might want to work with separately.\n",
"We store personal and family names in separate columns instead of putting the entire name in one column\n",
"and all our queries assume there will be a row in the latter\n",
"matching every value in the former.\n",
" \n",
- "This problem is called [referential integrity](../gloss.html#referential-integrity):\n",
+ "This problem is called [referential integrity](../../gloss.html#referential-integrity):\n",
"we need to ensure that all references between tables can always be resolved correctly.\n",
"One way to do this is to delete all the records\n",
"that use `'lake'` as a foreign key\n",
"before deleting the record that uses it as a primary key.\n",
"If our database manager supports it,\n",
"we can automate this\n",
- "using [cascading delete](../gloss.html#cascading-delete).\n",
+ "using [cascading delete](../../gloss.html#cascading-delete).\n",
"However,\n",
"this technique is outside the scope of this chapter.\n",
"\n",
" in `Survey.person`\n",
" with the string `'unknown'`.\n",
"\n",
- "2. One of our colleagues has sent us a [CSV](../gloss.html#csv) file\n",
+ "2. One of our colleagues has sent us a [CSV](../../gloss.html#csv) file\n",
" containing temperature readings by Robert Olmstead,\n",
" which is formatted like this:\n",
"\n",
"all we need to specify is the name of the database file.\n",
"Other systems may require us to provide a username and password as well.\n",
"Line 3 then uses this connection to create\n",
- "a [cursor](../gloss.html#cursor);\n",
+ "a [cursor](../../gloss.html#cursor);\n",
"just like the cursor in an editor,\n",
"its role is to keep track of where we are in the database.\n",
"\n",
"If we execute this,\n",
"it will erase one of the tables in our database.\n",
" \n",
- "This technique is called [SQL injection](../gloss.html#sql-injection),\n",
+ "This technique is called [SQL injection](../../gloss.html#sql-injection),\n",
"and it has been used to attack thousands of programs over the years.\n",
"In particular,\n",
"many web sites that take data from users insert values directly into queries\n",
"the safest way to deal with this threat is\n",
"to replace characters like quotes with their escaped equivalents,\n",
"so that we can safely put whatever the user gives us inside a string.\n",
- "We can do this by using a [prepared statement](../gloss.html#prepared-statement)\n",
+ "We can do this by using a [prepared statement](../../gloss.html#prepared-statement)\n",
"instead of formatting our statements as strings.\n",
"Here's what our example program looks like if we do this:"
]
projects / swc-sql.git / commitdiff