Author: mhillyer
Date: 2006-03-21 22:04:44 +0100 (Tue, 21 Mar 2006)
New Revision: 1641
Log:
Update IM doc to make it clear that --user and --run-as-service are for UNIX-like systems only.
Also WIP of the sakila DB documentation.
Modified:
trunk/refman-5.0/database-administration.xml
trunk/refman-5.1/database-administration.xml
trunk/sample-data/sakila/sakila.xml
Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml 2006-03-21 20:40:43 UTC (rev 1640)
+++ trunk/refman-5.0/database-administration.xml 2006-03-21 21:04:44 UTC (rev 1641)
@@ -12784,7 +12784,8 @@
</para>
<para>
- Daemonize and start the angel process. The angel process
+ Daemonize and start the angel process (for UNIX-like
+ systems). The angel process
is simple and unlikely to crash. It will restart the
Instance Manager itself in case of a failure.
</para>
@@ -12850,7 +12851,8 @@
<para>
Username to start and run the
- <command>mysqlmanager</command> under. It is recommended
+ <command>mysqlmanager</command> under (for UNIX-like
+ systems). It is recommended
to run <command>mysqlmanager</command> under the same user
account used to run the <command>mysqld</command> server.
(<quote>User</quote> in this context refers to a system
Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml 2006-03-21 20:40:43 UTC (rev 1640)
+++ trunk/refman-5.1/database-administration.xml 2006-03-21 21:04:44 UTC (rev 1641)
@@ -12681,7 +12681,8 @@
</para>
<para>
- Daemonize and start the angel process. The angel process
+ Daemonize and start the angel process (for UNIX-like
+ systems). The angel process
is simple and unlikely to crash. It will restart the
Instance Manager itself in case of a failure.
</para>
@@ -12746,7 +12747,8 @@
<para>
Username to start and run the
- <command>mysqlmanager</command> under. It is recommended
+ <command>mysqlmanager</command> under (for UNIX-like
+ systems). It is recommended
to run <command>mysqlmanager</command> under the same user
account used to run the <command>mysqld</command> server.
(<quote>User</quote> in this context refers to a system
Modified: trunk/sample-data/sakila/sakila.xml
===================================================================
--- trunk/sample-data/sakila/sakila.xml 2006-03-21 20:40:43 UTC (rev 1640)
+++ trunk/sample-data/sakila/sakila.xml 2006-03-21 21:04:44 UTC (rev 1641)
@@ -1,956 +1,1410 @@
-<?xml version="1.0" encoding="UTF-8"?>
-<!DOCTYPE article PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
-"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
-[
- <!ENTITY % sakila.entities SYSTEM "sakila.ent">
- %sakila.entities;
- ]>
-<article id="sakila" lang="en">
-
- <section id="sakila-introduction">
-
- <title>Introduction</title>
-
- <para>
- This document describes the &sampdb; sample database -- its
- history, installation, structure and usage.
- </para>
-
- <para>
- The &sampdb; sample database is developed and maintained by Mike
- Hillyer of the MySQL AB documentation team and is intended to
- provide a standard schema that can be used for examples in books,
- tutorials, articles, samples, etc. The &sampdb; sample database
- also serves to highlight the latest features of MySQL including
- Views, Stored Procedures, Triggers, etc.
- </para>
-
- <para>
- The &sampdb; sample database is the result of support and feedback
- from the MySQL user community and feedback and user input is
- always appreciated. Please direct all feedback to docs@stripped
- </para>
-
- <para>
- Additional information on the &sampdb;ample database and its usage
- can be found through the
- <ulink url="http://forums.mysql.com/list.php?121">MySQL
- forums</ulink>.
- </para>
-
- </section>
-
- <section id="sakila-history">
-
- <title>History</title>
-
- <para>
- The &sampdb; was designed as a replacement to the
- <ulink url="http://downloads.mysql.com/docs/world.sql"><literal>World</literal></ulink>
- sample database, also provided by MySQL AB.
- </para>
-
- <para>
- The World sample database provides a set of tables containing
- information on the countries and cities of the world and is useful
- for basic queries, but lacked structures for testing
- MySQL-specific functionality and new features found in MySQL 5.
- </para>
-
- <para>
- Development of the &sampdb; sample database began in early 2005 .
- Early designs were based on the database used in the Dell
- Whitepaper
- <ulink url="http://www.dell.com/downloads/global/solutions/mysql_apps.pdf">Three
- Approaches to MySQL Applications on Dell PowerEdge
- Servers</ulink>.
- </para>
-
- <para>
- Where Dell's sample database was designed to represent an online
- DVD store, the &sampdb; sample database is designed to represent a
- DVD rental store. The &sampdb; sample database still borrows film
- and actor names from the Dell sample database.
- </para>
-
- <para>
- Development was accomplished using MySQL Query Browser for schema
- design with the tables being populated by a combination of MySQL
- Query Browser and custom scripts, in addition to contributor
- efforts (see
- <xref
- linkend="sakila-acknowledgements"/>).
- </para>
-
- <para>
- After the basic schema was completed, various views, stored
- routines, and triggers were added to the schema, after which the
- sample data was populated. After a series of review versions, the
- first official version of the &sampdb; sample database was
- released in March 2006.
- </para>
-
- </section>
-
- <section id="sakila-installation">
-
- <title>Installation</title>
-
- <para>
- The &sampdb; sample database is divided into two installation
- files: <filename>sakila-schema.sql</filename> and
- <filename>sakila-data.sql</filename>.
- </para>
-
- <para>
- The <filename>sakila-schema.sql</filename> file contains all the
- <literal>CREATE</literal> statements required to create the
- structure of the &sampdb; database including tables, views, stored
- procedures and triggers.
- </para>
-
- <para>
- The <filename>sakila-data.sql</filename> file contains the
- <literal>INSERT</literal> statements required to populate the
- structure created by the <filename>sakila-schema.sql</filename>
- file, along with definitions for triggers that must be created
- after the initial data load.
- </para>
-
- <para>
- To install the &sampdb; sample database, follow these steps:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- Extract the installation archive to a temporary location on
- the MySQL server machine such as <filename>C:\temp\</filename>
- or <filename>/tmp/</filename>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Connect to the MySQL server using the <command>mysql</command>
- command-line client using the following command:
- </para>
-
-<programlisting>
-mysql -u root -p
-</programlisting>
-
- <para>
- Enter your password when prompted. Non-root accounts can be
- used as long as those accounts have privileges to create new
- databases.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Execute the <filename>sakila-schema.sql</filename> script to
- create the database structure with the following command:
- </para>
-
-<programlisting>
-SOURCE <replaceable>C:/temp/</replaceable>sakila-schema.sql;
-</programlisting>
-
- <para>
- Replace <replaceable>C:/temp/</replaceable> with the path to
- the <filename>sakila-schema.sql</filename> on the server.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Execute the <filename>sakila-data.sql</filename> script to
- populate the database structure with the following command:
- </para>
-
-<programlisting>
-SOURCE <replaceable>C:/temp/</replaceable>sakila-data.sql;
-</programlisting>
-
- <para>
- Replace <replaceable>C:/temp/</replaceable> with the path to
- the <filename>sakila-data.sql</filename> on the server.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Confirm the sample database is installed correctly:
- </para>
-
-<programlisting>
-mysql> USE sakila;
-Database changed
-
-mysql> SHOW TABLES;
-+----------------------------+
-| Tables_in_sakila |
-+----------------------------+
-| actor |
-| address |
-| category |
-| city |
-| country |
-| customer |
-| customer_list |
-| film |
-| film_actor |
-| film_category |
-| film_list |
-| film_text |
-| inventory |
-| language |
-| nicer_but_slower_film_list |
-| payment |
-| rental |
-| sales_by_film_category |
-| sales_by_store |
-| staff |
-| staff_list |
-| store |
-+----------------------------+
-22 rows in set (0.00 sec)
-
-mysql> SELECT COUNT(*) FROM film;
-+----------+
-| COUNT(*) |
-+----------+
-| 1000 |
-+----------+
-1 row in set (0.02 sec)
-
-mysql> SELECT COUNT(*) FROM film_text;
-+----------+
-| COUNT(*) |
-+----------+
-| 1000 |
-+----------+
-1 row in set (0.00 sec)
-</programlisting>
- </listitem>
-
- </orderedlist>
-
- </section>
-
- <section id="sakila-structure">
-
- <title>Structure</title>
-
- <para>
- This section provides an overview of the structure of the &sampdb;
- sample database.
- </para>
-
- <para>
- The following diagram provides an overview of the &sampdb;
- structure. The source file (for use with MySQL Workbench) is
- included in the &sampdb; distribution and is named
- <filename>sakila.mwb</filename>.
- </para>
-
- <figure>
- <title>The &sampdb; schema</title>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/sakila-schema.png" format="PNG" lang="en"/>
- </imageobject>
- <textobject>
- <phrase lang="en">The &sampdb; schema</phrase>
- </textobject>
- </mediaobject>
- </figure>
-
- <section id="sakila-structure-tables">
-
- <title>Tables</title>
-
- <para>
- This section describes the tables that make up the &sampdb;
- sample database, in alphabetical order.
- </para>
-
- <note>
-
- <para>
- All tables contain a <literal>last_update</literal> column
- that serves as a timestamp marking the last time each row was
- updated. This column will not be listed for each table in the
- table's column list.
- </para>
-
- </note>
-
- <section id="sakila-structure-tables-actor">
-
- <title>The actor Table</title>
-
- <para>
- The <literal>actor</literal> table lists information for all
- actors.
- </para>
-
- <para>
- The actor table is joined to the
- <link linkend="sakila-structure-tables-film">film</link> table
- by means of the
- <link linkend="sakila-structure-tables-film_actor">film_actor</link>
- table.
- </para>
-
- <bridgehead>Columns:</bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>actor_id</literal> - Surrogate primary key used
- to uniquely identify each actor in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>first_name</literal> - The actor's first name.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>last_name</literal> - The actor's last name.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-address">
-
- <title>The address Table</title>
-
- <para>
- The <literal>address</literal> table contains address
- information for customers, staff, and stores.
- </para>
-
- <para>
- The address table primary key appears as a foreign key in the
- <link
- linkend="sakila-structure-tables-customer">customer</link>,
- <link linkend="sakila-structure-tables-staff">staff</link> and
- <link
- linkend="sakila-structure-tables-store">store</link>
- tables.
- </para>
-
- <bridgehead> Columns:</bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>address_id</literal> - Surrogate primary key used
- to uniquely identify each address in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>address</literal> - First line of an address.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>address2</literal> - Optional second line of an
- address.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>district</literal> - The region of an address,
- this may be a state, province, prefecture, etc.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>city_id</literal> - A foreign key pointing to the
- <link
- linkend="sakila-structure-tables-city">city</link>
- table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>postal_code</literal> - The postal code or zip
- code of the address (where applicable).
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>phone</literal> - The telephone number for the
- address.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-category">
-
- <title>The category Table</title>
-
- <para>
- The <literal>category</literal> table contains a list of
- categories which the various categories that can be assigned
- to a film.
- </para>
-
- <para>
- The category table is joined to the
- <link
- linkend="sakila-structure-tables-film">film</link>
- table by means of the
- <link
- linkend="sakila-structure-tables-film_category">film_category</link>
- tables.
- </para>
-
- <bridgehead>Columns:</bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>category_id</literal> - Surrogate primary key
- used to uniquely identify each category in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>name</literal> - The name of the category.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-city">
-
- <title>The city Table</title>
-
- <para>
- The <literal>City</literal> table contains a list of cities.
- </para>
-
- <para>
- The city table is referred to by a foreign key in the
- <link
- linkend="sakila-structure-tables-address">address</link>
- table and refers to the
- <link
- linkend="sakila-structure-tables-country">country</link>
- table using a foreign key.
- </para>
-
- <bridgehead>Columns:</bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>city_id</literal> - Surrogate primary key used to
- uniquely identify each city in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>city</literal> - The name of the city.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>country_id</literal> - Foreign key identifying
- the country that the city belongs to.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-country">
-
- <title>The country Table</title>
-
- <para>
- The <literal>country</literal> table contains a list of
- countries.
- </para>
-
- <para>
- The country table is referred to by a foreign key in the
- <link
- linkend="sakila-structure-tables-city">city</link>
- table.
- </para>
-
- <bridgehead>Columns:</bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>country_id</literal> - Surrogate primary key used
- to uniquely identify each country in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>country</literal> - The name of the country.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-customer">
-
- <title>The customer Table</title>
-
- <para>
- The <literal>customer</literal> table contains a list of all
- customers.
- </para>
-
- <para>
- The customer table is referred to in the
- <link
- linkend="sakila-structure-tables-payment">payment</link>
- and
- <link linkend="sakila-structure-tables-rental">rental</link>
- tables and refers to the
- <link
- linkend="sakila-structure-tables-address">address</link>
- and <link linkend="sakila-structure-tables-store">store</link>
- tables using foreign keys.
- </para>
-
- <bridgehead>Columns:</bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>customer_id</literal> - Surrogate primary key
- used to uniquely identify each customer in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>store_id</literal> - Foreign key identifying the
- customer's 'home store'. Customers are not limited to
- renting only from this store, but this is the store they
- generally shop at.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>first_name</literal> - The customer's first name.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>last_name</literal> - The customer's last name.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>email</literal> - The customer's email address.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>address_id</literal> - Foreign key identifying
- the customer's address in the
- <link
- linkend="sakila-structure-tables-address">address</link>
- table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>active</literal> - Indicates whether the customer
- is an active customer. Setting this to
- <literal>FALSE</literal> serves as an alternative to
- deleting a customer outright. Most queries should have a
- <literal>WHERE active = TRUE</literal> clause.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>create_date</literal> - The date the customer was
- added to the system. This date is automatically set using
- a trigger during an <literal>INSERT</literal>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-film">
-
- <title>The film Table</title>
-
- <para>
- The <literal>film</literal> table is a list of all films
- potentially in stock in the stores. Actually copies of each
- film and represented in the
- <link
- linkend="sakila-structure-tables-inventory">inventory</link>
- table.
- </para>
-
- <para>
- The film table refers to the
- <link
- linkend="sakila-structure-tables-language">language</link>
- table and is referred to by the
- <link
- linkend="sakila-structure-tables-film_category">film_category</link>,
- <link linkend="sakila-structure-tables-film_actor">film_actor</link>
- and
- <link linkend="sakila-structure-tables-inventory">inventory</link>
- tables.
- </para>
-
- <bridgehead>Columns:</bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>film_id</literal> - Surrogate primary key used to
- uniquely identify each film in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>title</literal> - The title of the film.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>description</literal> - A short description or
- plot summary of the film.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>release_year</literal> - The year in which the
- movie was released.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>language_id</literal> - Foreign key pointing at
- the
- <link
- linkend="sakila-structure-tables-language">language</link>
- table, identified the language of the film.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>original_language_id</literal> - Foreign key
- pointing at the
- <link
- linkend="sakila-structure-tables-language">language</link>
- table, identified the original language of the film. Used
- when a film has been dubbed into a new language.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>rental_duration</literal> - The period in days
- that the film is rented for.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>rental_rate</literal> - The cost to rent the film
- for the period specified in the
- <literal>rental_duration</literal> column.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>length</literal> - The duration of the film, in
- minutes.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>replacement_cost</literal> - The amount charged
- to the customer if the film is not returned or is returned
- in a damaged state.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>rating</literal> - The rating assigned to the
- film. Can be one of: <literal>G</literal>,
- <literal>PG</literal>, <literal>PG-13</literal>,
- <literal>R</literal> or <literal>NC-17</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>special_features</literal> - Lists which common
- special features are included on the DVD. Can be zero or
- more of: <literal>Trailers</literal>,
- <literal>Commentaries</literal>, <literal>Deleted
- Scenes</literal>, <literal>Behind the Scenes</literal>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-film_actor">
-
- <title>The film_actor Table</title>
-
- <para>
- The <literal>film_actor</literal> table is used to support a
- many-to-many relationship between films and actors. For each
- actor in a given film, there will be one row in the film_actor
- table listing the actor and film.
- </para>
-
- <para>
- The film_actor table refers to the
- <link
- linkend="sakila-structure-tables-film">film</link>
- and
- <link
- linkend="sakila-structure-tables-actor">actor</link>
- tables using foreign keys.
- </para>
-
- <bridgehead>
- Columns:
- </bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>actor_id</literal> - Foreign key identifying the
- actor.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>film_id</literal> - Foreign key identifying the
- film.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="sakila-structure-tables-film_category">
-
- <title>The film_category Table</title>
-
- <para>
- The <literal>film_category</literal> table is used to support
- a many-to-many relationship between films and categories. For
- each category applied to a film, there will be one row in the
- film_category table listing the category and film.
- </para>
-
- <para>
- The film_category table refers to the
- <link
- linkend="sakila-structure-tables-film">film</link>
- and
- <link
- linkend="sakila-structure-tables-category">category</link>
- tables using foreign keys.
- </para>
-
- <bridgehead>
- Columns:
- </bridgehead>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>film_id</literal> - Foreign key identifying the
- film.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>category_id</literal> - Foreign key identifying
- the category.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- </section>
-
- <section id="sakila-structure-views">
-
- <title>Views</title>
-
- </section>
-
- <section id="sakila-structure-procedures">
-
- <title>Stored Procedures</title>
-
- </section>
-
- <section id="sakila-structure-functions">
-
- <title>Stored Functions</title>
-
- </section>
-
- <section id="sakila-structure-triggers">
-
- <title>Triggers</title>
-
- </section>
-
- </section>
-
- <section id="sakila-usage">
-
- <title>Usage Examples</title>
-
- </section>
-
- <section id="sakila-acknowledgements">
-
- <title>Acknowledgements</title>
-
- <para>
- The following individuals and organizations have contributed to
- the development of the Sakila sample database.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>Roland Bouman</literal>,
- <ulink url="http://rpbouman.blogspot.com/">Author</ulink> -
- Provided valuable feedback throughout the development process.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>Ronald Bradford</literal>,
- <ulink
- url="http://blog.arabx.com.au/">Developer</ulink>
- - Developed
- <ulink url="http://sakila.arabx.com.au/index.htm">first sample
- application</ulink> for use with the &sampdb; sample database.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>Dave Jaffe</literal>,
- <ulink
- url="http://www.dell.com/mysql">Dell</ulink>
- - Provided schema used in Dell whitepaper and secured
- permission to use parts thereof in &sampdb; sample database.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>Giuseppe Maxia</literal>, CTO of
- <ulink
- url="http://www.stardata.it//index_en.html">Stardata</ulink>
- - Provided valuable feedback throughout the development
- process, populated some of the sample data, provided some of
- the sample views and triggers.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>Jay Pipes</literal>,
- <ulink url="http://www.jpipes.com/">MySQL Community
- Advocate</ulink> - Provided some of the sample stored
- procedures.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- In addition to the individuals mentioned above, there are various
- individuals in MySQL AB and the community that have provided
- feedback during the course of development.
- </para>
-
- </section>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude"
- href="changelog.xml"/>
-
-</article>
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE article PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % sakila.entities SYSTEM "sakila.ent">
+ %sakila.entities;
+ ]>
+<article id="sakila" lang="en">
+
+ <section id="sakila-introduction">
+
+ <title>Introduction</title>
+
+ <para>
+ This document describes the &sampdb; sample database -- its
+ history, installation, structure and usage.
+ </para>
+
+ <para>
+ The &sampdb; sample database is developed and maintained by Mike
+ Hillyer of the MySQL AB documentation team and is intended to
+ provide a standard schema that can be used for examples in books,
+ tutorials, articles, samples, etc. The &sampdb; sample database
+ also serves to highlight the latest features of MySQL including
+ Views, Stored Procedures, Triggers, etc.
+ </para>
+
+ <para>
+ The &sampdb; sample database is the result of support and feedback
+ from the MySQL user community and feedback and user input is
+ always appreciated. Please direct all feedback to docs@stripped
+ </para>
+
+ <para>
+ Additional information on the &sampdb;ample database and its usage
+ can be found through the
+ <ulink url="http://forums.mysql.com/list.php?121">MySQL
+ forums</ulink>.
+ </para>
+
+ </section>
+
+ <section id="sakila-history">
+
+ <title>History</title>
+
+ <para>
+ The &sampdb; was designed as a replacement to the
+ <ulink url="http://downloads.mysql.com/docs/world.sql"><literal>World</literal></ulink>
+ sample database, also provided by MySQL AB.
+ </para>
+
+ <para>
+ The World sample database provides a set of tables containing
+ information on the countries and cities of the world and is useful
+ for basic queries, but lacked structures for testing
+ MySQL-specific functionality and new features found in MySQL 5.
+ </para>
+
+ <para>
+ Development of the &sampdb; sample database began in early 2005 .
+ Early designs were based on the database used in the Dell
+ Whitepaper
+ <ulink url="http://www.dell.com/downloads/global/solutions/mysql_apps.pdf">Three
+ Approaches to MySQL Applications on Dell PowerEdge
+ Servers</ulink>.
+ </para>
+
+ <para>
+ Where Dell's sample database was designed to represent an online
+ DVD store, the &sampdb; sample database is designed to represent a
+ DVD rental store. The &sampdb; sample database still borrows film
+ and actor names from the Dell sample database.
+ </para>
+
+ <para>
+ Development was accomplished using MySQL Query Browser for schema
+ design with the tables being populated by a combination of MySQL
+ Query Browser and custom scripts, in addition to contributor
+ efforts (see
+ <xref
+ linkend="sakila-acknowledgements"/>).
+ </para>
+
+ <para>
+ After the basic schema was completed, various views, stored
+ routines, and triggers were added to the schema, after which the
+ sample data was populated. After a series of review versions, the
+ first official version of the &sampdb; sample database was
+ released in March 2006.
+ </para>
+
+ </section>
+
+ <section id="sakila-installation">
+
+ <title>Installation</title>
+
+ <para>
+ The &sampdb; sample database is divided into two installation
+ files: <filename>sakila-schema.sql</filename> and
+ <filename>sakila-data.sql</filename>.
+ </para>
+
+ <para>
+ The <filename>sakila-schema.sql</filename> file contains all the
+ <literal>CREATE</literal> statements required to create the
+ structure of the &sampdb; database including tables, views, stored
+ procedures and triggers.
+ </para>
+
+ <para>
+ The <filename>sakila-data.sql</filename> file contains the
+ <literal>INSERT</literal> statements required to populate the
+ structure created by the <filename>sakila-schema.sql</filename>
+ file, along with definitions for triggers that must be created
+ after the initial data load.
+ </para>
+
+ <para>
+ To install the &sampdb; sample database, follow these steps:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Extract the installation archive to a temporary location on
+ the MySQL server machine such as <filename>C:\temp\</filename>
+ or <filename>/tmp/</filename>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Connect to the MySQL server using the <command>mysql</command>
+ command-line client using the following command:
+ </para>
+
+<programlisting>
+mysql -u root -p
+</programlisting>
+
+ <para>
+ Enter your password when prompted. Non-root accounts can be
+ used as long as those accounts have privileges to create new
+ databases.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Execute the <filename>sakila-schema.sql</filename> script to
+ create the database structure with the following command:
+ </para>
+
+<programlisting>
+SOURCE <replaceable>C:/temp/</replaceable>sakila-schema.sql;
+</programlisting>
+
+ <para>
+ Replace <replaceable>C:/temp/</replaceable> with the path to
+ the <filename>sakila-schema.sql</filename> on the server.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Execute the <filename>sakila-data.sql</filename> script to
+ populate the database structure with the following command:
+ </para>
+
+<programlisting>
+SOURCE <replaceable>C:/temp/</replaceable>sakila-data.sql;
+</programlisting>
+
+ <para>
+ Replace <replaceable>C:/temp/</replaceable> with the path to
+ the <filename>sakila-data.sql</filename> on the server.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Confirm the sample database is installed correctly:
+ </para>
+
+<programlisting>
+mysql> USE sakila;
+Database changed
+
+mysql> SHOW TABLES;
++----------------------------+
+| Tables_in_sakila |
++----------------------------+
+| actor |
+| address |
+| category |
+| city |
+| country |
+| customer |
+| customer_list |
+| film |
+| film_actor |
+| film_category |
+| film_list |
+| film_text |
+| inventory |
+| language |
+| nicer_but_slower_film_list |
+| payment |
+| rental |
+| sales_by_film_category |
+| sales_by_store |
+| staff |
+| staff_list |
+| store |
++----------------------------+
+22 rows in set (0.00 sec)
+
+mysql> SELECT COUNT(*) FROM film;
++----------+
+| COUNT(*) |
++----------+
+| 1000 |
++----------+
+1 row in set (0.02 sec)
+
+mysql> SELECT COUNT(*) FROM film_text;
++----------+
+| COUNT(*) |
++----------+
+| 1000 |
++----------+
+1 row in set (0.00 sec)
+</programlisting>
+ </listitem>
+
+ </orderedlist>
+
+ </section>
+
+ <section id="sakila-structure">
+
+ <title>Structure</title>
+
+ <para>
+ This section provides an overview of the structure of the &sampdb;
+ sample database.
+ </para>
+
+ <para>
+ The following diagram provides an overview of the &sampdb;
+ structure. The source file (for use with MySQL Workbench) is
+ included in the &sampdb; distribution and is named
+ <filename>sakila.mwb</filename>.
+ </para>
+
+ <figure>
+ <title>The &sampdb; schema</title>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/sakila-schema.png" format="PNG" lang="en"/>
+ </imageobject>
+ <textobject>
+ <phrase lang="en">The &sampdb; schema</phrase>
+ </textobject>
+ </mediaobject>
+ </figure>
+
+ <section id="sakila-structure-tables">
+
+ <title>Tables</title>
+
+ <para>
+ This section describes the tables that make up the &sampdb;
+ sample database, in alphabetical order.
+ </para>
+
+ <note>
+
+ <para>
+ All tables contain a <literal>last_update</literal> column
+ that serves as a timestamp marking the last time each row was
+ updated. This column will not be listed for each table in the
+ table's column list.
+ </para>
+
+ </note>
+
+ <section id="sakila-structure-tables-actor">
+
+ <title>The actor Table</title>
+
+ <para>
+ The <literal>actor</literal> table lists information for all
+ actors.
+ </para>
+
+ <para>
+ The actor table is joined to the
+ <link linkend="sakila-structure-tables-film">film</link> table
+ by means of the
+ <link linkend="sakila-structure-tables-film_actor">film_actor</link>
+ table.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>actor_id</literal> - Surrogate primary key used
+ to uniquely identify each actor in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>first_name</literal> - The actor's first name.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>last_name</literal> - The actor's last name.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-address">
+
+ <title>The address Table</title>
+
+ <para>
+ The <literal>address</literal> table contains address
+ information for customers, staff, and stores.
+ </para>
+
+ <para>
+ The address table primary key appears as a foreign key in the
+ <link
+ linkend="sakila-structure-tables-customer">customer</link>,
+ <link linkend="sakila-structure-tables-staff">staff</link> and
+ <link
+ linkend="sakila-structure-tables-store">store</link>
+ tables.
+ </para>
+
+ <bridgehead> Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>address_id</literal> - Surrogate primary key used
+ to uniquely identify each address in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>address</literal> - First line of an address.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>address2</literal> - Optional second line of an
+ address.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>district</literal> - The region of an address,
+ this may be a state, province, prefecture, etc.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>city_id</literal> - A foreign key pointing to the
+ <link
+ linkend="sakila-structure-tables-city">city</link>
+ table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>postal_code</literal> - The postal code or zip
+ code of the address (where applicable).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>phone</literal> - The telephone number for the
+ address.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-category">
+
+ <title>The category Table</title>
+
+ <para>
+ The <literal>category</literal> table contains a list of
+ categories which the various categories that can be assigned
+ to a film.
+ </para>
+
+ <para>
+ The category table is joined to the
+ <link
+ linkend="sakila-structure-tables-film">film</link>
+ table by means of the
+ <link
+ linkend="sakila-structure-tables-film_category">film_category</link>
+ tables.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>category_id</literal> - Surrogate primary key
+ used to uniquely identify each category in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>name</literal> - The name of the category.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-city">
+
+ <title>The city Table</title>
+
+ <para>
+ The <literal>City</literal> table contains a list of cities.
+ </para>
+
+ <para>
+ The city table is referred to by a foreign key in the
+ <link
+ linkend="sakila-structure-tables-address">address</link>
+ table and refers to the
+ <link
+ linkend="sakila-structure-tables-country">country</link>
+ table using a foreign key.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>city_id</literal> - Surrogate primary key used to
+ uniquely identify each city in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>city</literal> - The name of the city.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>country_id</literal> - Foreign key identifying
+ the country that the city belongs to.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-country">
+
+ <title>The country Table</title>
+
+ <para>
+ The <literal>country</literal> table contains a list of
+ countries.
+ </para>
+
+ <para>
+ The country table is referred to by a foreign key in the
+ <link
+ linkend="sakila-structure-tables-city">city</link>
+ table.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>country_id</literal> - Surrogate primary key used
+ to uniquely identify each country in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>country</literal> - The name of the country.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-customer">
+
+ <title>The customer Table</title>
+
+ <para>
+ The <literal>customer</literal> table contains a list of all
+ customers.
+ </para>
+
+ <para>
+ The customer table is referred to in the
+ <link
+ linkend="sakila-structure-tables-payment">payment</link>
+ and
+ <link linkend="sakila-structure-tables-rental">rental</link>
+ tables and refers to the
+ <link
+ linkend="sakila-structure-tables-address">address</link>
+ and <link linkend="sakila-structure-tables-store">store</link>
+ tables using foreign keys.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>customer_id</literal> - Surrogate primary key
+ used to uniquely identify each customer in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>store_id</literal> - Foreign key identifying the
+ customer's 'home store'. Customers are not limited to
+ renting only from this store, but this is the store they
+ generally shop at.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>first_name</literal> - The customer's first name.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>last_name</literal> - The customer's last name.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>email</literal> - The customer's email address.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>address_id</literal> - Foreign key identifying
+ the customer's address in the
+ <link
+ linkend="sakila-structure-tables-address">address</link>
+ table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>active</literal> - Indicates whether the customer
+ is an active customer. Setting this to
+ <literal>FALSE</literal> serves as an alternative to
+ deleting a customer outright. Most queries should have a
+ <literal>WHERE active = TRUE</literal> clause.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>create_date</literal> - The date the customer was
+ added to the system. This date is automatically set using
+ a trigger during an <literal>INSERT</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-film">
+
+ <title>The film Table</title>
+
+ <para>
+ The <literal>film</literal> table is a list of all films
+ potentially in stock in the stores. Actually copies of each
+ film and represented in the
+ <link
+ linkend="sakila-structure-tables-inventory">inventory</link>
+ table.
+ </para>
+
+ <para>
+ The film table refers to the
+ <link
+ linkend="sakila-structure-tables-language">language</link>
+ table and is referred to by the
+ <link
+ linkend="sakila-structure-tables-film_category">film_category</link>,
+ <link linkend="sakila-structure-tables-film_actor">film_actor</link>
+ and
+ <link linkend="sakila-structure-tables-inventory">inventory</link>
+ tables.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>film_id</literal> - Surrogate primary key used to
+ uniquely identify each film in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>title</literal> - The title of the film.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>description</literal> - A short description or
+ plot summary of the film.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>release_year</literal> - The year in which the
+ movie was released.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>language_id</literal> - Foreign key pointing at
+ the
+ <link
+ linkend="sakila-structure-tables-language">language</link>
+ table, identified the language of the film.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>original_language_id</literal> - Foreign key
+ pointing at the
+ <link
+ linkend="sakila-structure-tables-language">language</link>
+ table, identified the original language of the film. Used
+ when a film has been dubbed into a new language.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>rental_duration</literal> - The period in days
+ that the film is rented for.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>rental_rate</literal> - The cost to rent the film
+ for the period specified in the
+ <literal>rental_duration</literal> column.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>length</literal> - The duration of the film, in
+ minutes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>replacement_cost</literal> - The amount charged
+ to the customer if the film is not returned or is returned
+ in a damaged state.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>rating</literal> - The rating assigned to the
+ film. Can be one of: <literal>G</literal>,
+ <literal>PG</literal>, <literal>PG-13</literal>,
+ <literal>R</literal> or <literal>NC-17</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>special_features</literal> - Lists which common
+ special features are included on the DVD. Can be zero or
+ more of: <literal>Trailers</literal>,
+ <literal>Commentaries</literal>, <literal>Deleted
+ Scenes</literal>, <literal>Behind the Scenes</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-film_actor">
+
+ <title>The film_actor Table</title>
+
+ <para>
+ The <literal>film_actor</literal> table is used to support a
+ many-to-many relationship between films and actors. For each
+ actor in a given film, there will be one row in the film_actor
+ table listing the actor and film.
+ </para>
+
+ <para>
+ The film_actor table refers to the
+ <link
+ linkend="sakila-structure-tables-film">film</link>
+ and
+ <link
+ linkend="sakila-structure-tables-actor">actor</link>
+ tables using foreign keys.
+ </para>
+
+ <bridgehead>
+ Columns:
+ </bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>actor_id</literal> - Foreign key identifying the
+ actor.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film_id</literal> - Foreign key identifying the
+ film.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-film_category">
+
+ <title>The film_category Table</title>
+
+ <para>
+ The <literal>film_category</literal> table is used to support
+ a many-to-many relationship between films and categories. For
+ each category applied to a film, there will be one row in the
+ film_category table listing the category and film.
+ </para>
+
+ <para>
+ The film_category table refers to the
+ <link
+ linkend="sakila-structure-tables-film">film</link>
+ and
+ <link
+ linkend="sakila-structure-tables-category">category</link>
+ tables using foreign keys.
+ </para>
+
+ <bridgehead>
+ Columns:
+ </bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>film_id</literal> - Foreign key identifying the
+ film.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>category_id</literal> - Foreign key identifying
+ the category.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-film_text">
+
+ <title>The film_text Table</title>
+
+ <para>
+ The <literal>film_text</literal> table is the only table in
+ the &sampdb; sample database that uses a
+ <literal>MyISAM</literal> storage engine. This table is
+ provided to allow for fulltext searching of the titles and
+ descriptions of the films listed in the
+ <link
+ linkend="sakila-structure-tables-film">film</link>
+ table.
+ </para>
+
+ <para>
+ The film_text table contains the <literal>film_id</literal>,
+ <literal>title</literal> and <literal>description</literal>
+ columns of the film table, with the contents of the table kept
+ in sync with the film table by means of triggers on the film
+ table's <literal>INSERT</literal>, <literal>UPDATE</literal>
+ and <literal>DELETE</literal> operations (see
+ <xref
+ linkend="sakila-structure-triggers"/>).
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>film_id</literal> - Surrogate primary key used to
+ uniquely identify each film in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>title</literal> - The title of the film.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>description</literal> - A short description or
+ plot summary of the film.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The contents of the film_text table should never be modified
+ directly, all changes should be made to the film table
+ instead.
+ </para>
+
+ </section>
+
+ <section id="sakila-structure-tables-inventory">
+
+ <title>The inventory Table</title>
+
+ <para>
+ The <literal>inventory</literal> table contains one row for
+ each copy of a given film in a given store.
+ </para>
+
+ <para>
+ The inventory table refers to the
+ <link
+ linkend="sakila-structure-tables-film">film</link>
+ and
+ <link
+ linkend="sakila-structure-tables-store">store</link>
+ tables using foreign keys and is referred to by the
+ <link
+ linkend="sakila-structure-tables-rental">rental</link>
+ table.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>inventory_id</literal> - Surrogate primary key
+ used to uniquely identify each item in inventory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>film_id</literal> - Foreign key pointing to the
+ film this item represents.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>store_id</literal> - Foreign key pointing to the
+ store stocking this item.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-language">
+
+ <title>The language Table</title>
+
+ <para>
+ The <literal>language</literal> table is a lookup table
+ listing the possible languages a film can be listed as having
+ for its language and original language.
+ </para>
+
+ <para>
+ The language table is referred to by the
+ <link
+ linkend="sakila-structure-tables-film">film</link>
+ table.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>language_id</literal> - Surrogate primary key
+ used to uniquely identify each language.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>name</literal> - The English name of the
+ language.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-payment">
+
+ <title>The payment Table</title>
+
+ <para>
+ The <literal>payment</literal> table records each payment made
+ by a customer, with information such as the amount and the
+ rental being paid for (when applicable).
+ </para>
+
+ <para>
+ The payment table refers to the
+ <link
+ linkend="sakila-structure-tables-customer">customer</link>,
+ <link linkend="sakila-structure-tables-rental">rental</link>
+ and <link linkend="sakila-structure-tables-staff">staff</link>
+ tables.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>payment_id</literal> - Surrogate primary key used
+ to uniquely identify each payment.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>customer_id</literal> - The customer whose
+ balance the payment is being applied to. Foreign key
+ reference to the <literal>customer</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>staff_id</literal> - The staff member who
+ processed the payment. Foreign key reference to the
+ <literal>staff</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>rental_id</literal> - The rental that the payment
+ is being applied to. This is optional because some
+ payments are for outstanding fees and may not be directly
+ related to a rental.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>amount</literal> - The amount of the payment.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>payment_date</literal> - The date the payment was
+ processed.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-rental">
+
+ <title>The rental Table</title>
+
+ <para>
+ The <literal>rental</literal> table contains one row for each
+ rental of each inventory item with information about who
+ rented what item, when it was rented and when it was returned.
+ </para>
+
+ <para>
+ The rental table refers to the
+ <link
+ linkend="sakila-structure-tables-inventory">inventory</link>,
+ <link linkend="sakila-structure-tables-customer">customer</link>
+ and <link linkend="sakila-structure-tables-staff">staff</link>
+ tables and is referred to by the
+ <link
+ linkend="sakila-structure-tables-payment">payment</link>
+ table.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>rental_id</literal> - Surrogate primary key that
+ uniquely identifies the rental.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>rental_date</literal> - The date/time that the
+ item was rented.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>inventory_id</literal> - The item being rented.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>customer_id</literal> - The customer renting the
+ item.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>return_date</literal> - The date/time the item
+ was returned.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>staff_id</literal> - The staff member who
+ processed the rental.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-staff">
+
+ <title>The staff Table</title>
+
+ <para>
+ The <literal>staff</literal> table lists all staff members,
+ including information on email address, login information, and
+ picture.
+ </para>
+
+ <para>
+ The staff table refers to the
+ <link
+ linkend="sakila-structure-tables-store">store</link>
+ and
+ <link
+ linkend="sakila-structure-tables-address">address</link>
+ tables using foreign keys, and is referred to by the
+ <link
+ linkend="sakila-structure-tables-rental">rental</link>,
+ <link linkend="sakila-structure-tables-payment">payment</link>
+ and store tables.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>staff_id</literal> - Surrogate primary key that
+ uniquely identifies the staff member.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>first_name</literal> - The first name of the
+ staff member.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>last_name</literal> - The last name of the staff
+ member.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>address_id</literal> - Foreign key to the staff
+ member's address in the address table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>picture</literal> - A BLOB containing a
+ photograph of the employee.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>email</literal> - The staff member's email
+ address.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>store_id</literal> - The staff member's 'home
+ store'. The employee can work at other stores but is
+ generally assigned to the store listed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>active</literal> - Whether this is an active
+ employee. If an employee leaves their row is not deleted
+ from this table, instead this column is set to
+ <literal>FALSE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>username</literal> - The username used by the
+ staff member to access the rental system.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>password</literal> - The password used by the
+ staff member to access the rental system. The password
+ should be stored as a hash using the
+ <function>SHA1()</function> function.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="sakila-structure-tables-store">
+
+ <title>The store Table</title>
+
+ <para>
+ The <literal>store</literal> table lists all stores in the
+ system. All inventory is assigned to specific stores, and
+ staff and customers are assigned a 'home store'.
+ </para>
+
+ <para>
+ The store table refers to the
+ <link
+ linkend="sakila-structure-tables-staff">staff</link>
+ and
+ <link
+ linkend="sakila-structure-tables-address">address</link>
+ tables using foreign keys and is referred to by the staff,
+ <link
+ linkend="sakila-structure-tables-customer">customer</link>
+ and
+ <link linkend="sakila-structure-tables-inventory">inventory</link>
+ tables.
+ </para>
+
+ <bridgehead>Columns:</bridgehead>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>store_id</literal> - Surrogate primary key that
+ uniquely identifies the store.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>manager_staff_id</literal> - Foreign key
+ identifying the manager of this store.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>address_id</literal> - Foreign key identifying
+ the address of this store.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ </section>
+
+ <section id="sakila-structure-views">
+
+ <title>Views</title>
+
+ </section>
+
+ <section id="sakila-structure-procedures">
+
+ <title>Stored Procedures</title>
+
+ </section>
+
+ <section id="sakila-structure-functions">
+
+ <title>Stored Functions</title>
+
+ </section>
+
+ <section id="sakila-structure-triggers">
+
+ <title>Triggers</title>
+
+ </section>
+
+ </section>
+
+ <section id="sakila-usage">
+
+ <title>Usage Examples</title>
+
+ </section>
+
+ <section id="sakila-acknowledgements">
+
+ <title>Acknowledgements</title>
+
+ <para>
+ The following individuals and organizations have contributed to
+ the development of the Sakila sample database.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>Roland Bouman</literal>,
+ <ulink url="http://rpbouman.blogspot.com/">Author</ulink> -
+ Provided valuable feedback throughout the development process.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Ronald Bradford</literal>,
+ <ulink
+ url="http://blog.arabx.com.au/">Developer</ulink>
+ - Developed
+ <ulink url="http://sakila.arabx.com.au/index.htm">first sample
+ application</ulink> for use with the &sampdb; sample database.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Dave Jaffe</literal>,
+ <ulink
+ url="http://www.dell.com/mysql">Dell</ulink>
+ - Provided schema used in Dell whitepaper and secured
+ permission to use parts thereof in &sampdb; sample database.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Giuseppe Maxia</literal>, CTO of
+ <ulink
+ url="http://www.stardata.it//index_en.html">Stardata</ulink>
+ - Provided valuable feedback throughout the development
+ process, populated some of the sample data, provided some of
+ the sample views and triggers.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Jay Pipes</literal>,
+ <ulink url="http://www.jpipes.com/">MySQL Community
+ Advocate</ulink> - Provided some of the sample stored
+ procedures.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In addition to the individuals mentioned above, there are various
+ individuals in MySQL AB and the community that have provided
+ feedback during the course of development.
+ </para>
+
+ </section>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude"
+ href="changelog.xml"/>
+
+</article>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1641 - in trunk: refman-5.0 refman-5.1 sample-data/sakila | mhillyer | 21 Mar |
