| List: | Commits | « Previous MessageNext Message » | |
| From: | mhillyer | Date: | April 18 2006 6:22pm |
| Subject: | svn commit - mysqldoc@docsrva: r1856 - trunk/sample-data/sakila | ||
| View as plain text | |||
Author: mhillyer Date: 2006-04-18 18:22:07 +0200 (Tue, 18 Apr 2006) New Revision: 1856 Log: Quick edits to Sakila docs. Modified: trunk/sample-data/sakila/sakila.xml Modified: trunk/sample-data/sakila/sakila.xml =================================================================== --- trunk/sample-data/sakila/sakila.xml 2006-04-18 16:18:20 UTC (rev 1855) +++ trunk/sample-data/sakila/sakila.xml 2006-04-18 16:22:07 UTC (rev 1856) @@ -1,2227 +1,2241 @@ -<?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, with the exception of the - <literal>film_text</literal> table, 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> - - <para> - This section describes the views that are included with the - &sampdb; sample database, in alphabetical order. - </para> - - <section id="sakila-structure-views-actor_info"> - - <title>The actor_info View</title> - - <para> - The <literal>actor_info</literal> view provides a list of all - actors, including the films they have performed in, broken - down by category. - </para> - - <para> - The staff_list view incorporates data from the film, actor, - category, film_actor and film_category tables. - </para> - - </section> - - <section id="sakila-structure-views-customer_list"> - - <title>The customer_list View</title> - - <para> - The <literal>customer_list</literal> view provides a list of - customers, with first name and last name concatenated together - and address information combined into a single view. - </para> - - <para> - The customer_list view incorporates data from the customer, - address, city and country tables. - </para> - - </section> - - <section id="sakila-structure-views-film_list"> - - <title>The film_list View</title> - - <para> - The <literal>film_list</literal> view contains a formatted - view of the film table, with a comma-separated list of the - film's actors. - </para> - - <para> - The film_list view incorporates data from the film, category, - film_category, actor and film_actor tables. - </para> - - </section> - - <section id="sakila-structure-views-nicer_film_list"> - - <title>The nicer_but_slower_film_list View</title> - - <para> - The <literal>nicer_but_slower_film_list</literal> view - contains a formatted view of the film table, with a - comma-separated list of the film's actors. - </para> - - <para> - The nicer_but_slower_film_list view differs from the film_list - view in the list of actors. The lettercase of the actor names - is adjusted so that the first letter of each name is - capitalized rather than have the name in all-caps. - </para> - - <para> - As indicated in its name, the nicer_but_slower_film_list - performs additional processing and therefore takes longer to - return data than the film_list view. - </para> - - <para> - The nicer_but_slower_film_list view incorporates data from the - film, category, film_category, actor and film_actor tables. - </para> - - </section> - - <section id="sakila-structure-views-sales_by_film_category"> - - <title>The sales_by_film_category View</title> - - <para> - The <literal>sales_by_film_category</literal> view provides a - list of total sales, broken down by individual film category. - </para> - - <para> - Because a film can be listed in multiple categories, it is not - advisable to calculate aggregate sales by totalling the rows - of this view. - </para> - - <para> - The sales_by_film_category view incorporates data from the - category, payment, rental, inventory, film, film_category and - category tables. - </para> - - </section> - - <section id="sakila-structure-views-sales_by_store"> - - <title>The sales_by_store View</title> - - <para> - The <literal>sales_by_store</literal> view provides a list of - total sales, broken down by store. - </para> - - <para> - The view returns the store location, manager name, and total - sales. - </para> - - <para> - The sales_by_store view incorporates data from the city, - country, payment, rental, inventory, store, address and staff - tables. - </para> - - </section> - - <section id="sakila-structure-views-staff-list"> - - <title>The staff_list View</title> - - <para> - The <literal>staff_list</literal> view provides a list of all - staff members, including address and store information. - </para> - - <para> - The staff_list view incorporates data from the staff and - address tables. - </para> - - </section> - - </section> - - <section id="sakila-structure-procedures"> - - <title>Stored Procedures</title> - - <para> - This is a list of stored procedures included with the &sampdb; - sample database, in alphabetical order. - </para> - - <para> - All parameters listed are IN parameters unless listed otherwise. - </para> - - <section id="sakila-structure-procedures-film_in_stock"> - - <title>The film_in_stock Stored Procedure</title> - - <bridgehead>Description</bridgehead> - - <para> - The <literal>film_in_stock</literal> stored procedure is used - to determine if there are any copies of a given film in stock - at a given store. - </para> - - <bridgehead>Parameters</bridgehead> - - <itemizedlist> - - <listitem> - <para> - <literal>p_film_id</literal> - The id of the film to be - checked, from the <literal>film_id</literal> column of the - <literal>film</literal> table. - </para> - </listitem> - - <listitem> - <para> - <literal>p_store_id</literal> - The id of the store to - check for, from the <literal>store_id</literal> column of - the <literal>store</literal> table. - </para> - </listitem> - - <listitem> - <para> - <literal>p_film_count</literal> - An OUT parameter that - returns a count of the copies of the film in stock. - </para> - </listitem> - - </itemizedlist> - - <bridgehead>Return Values</bridgehead> - - <para> - This function returns a table of inventory id numbers for the - copies of the film in stock. - </para> - - <bridgehead>Sample Usage</bridgehead> - -<programlisting> -mysql> CALL film_in_stock(1,1,@count); -+--------------+ -| inventory_id | -+--------------+ -| 1 | -| 2 | -| 3 | -| 4 | -+--------------+ -4 rows in set (0.06 sec) - -Query OK, 0 rows affected (0.06 sec) - -mysql> SELECT @count; -+--------+ -| @count | -+--------+ -| 4 | -+--------+ -1 row in set (0.00 sec) - -mysql> -</programlisting> - - </section> - - <section id="sakila-structure-procedures-film_not_in_stock"> - - <title>The film_not_in_stock Stored Procedure</title> - - <bridgehead>Description</bridgehead> - - <para> - The <literal>film_not_in_stock</literal> stored procedure is - used to determine if there are any copies of a given film not - in stock (rented out) at a given store. - </para> - - <bridgehead>Parameters</bridgehead> - - <itemizedlist> - - <listitem> - <para> - <literal>p_film_id</literal> - The id of the film to be - checked, from the <literal>film_id</literal> column of the - <literal>film</literal> table. - </para> - </listitem> - - <listitem> - <para> - <literal>p_store_id</literal> - The id of the store to - check for, from the <literal>store_id</literal> column of - the <literal>store</literal> table. - </para> - </listitem> - - <listitem> - <para> - <literal>p_film_count</literal> - An OUT parameter that - returns a count of the copies of the film not in stock. - </para> - </listitem> - - </itemizedlist> - - <bridgehead>Return Values</bridgehead> - - <para> - This function returns a table of inventory id numbers for the - copies of the film not in stock. - </para> - - <bridgehead>Sample Usage</bridgehead> - -<programlisting> -mysql> CALL film_not_in_stock(2,2,@count); -+--------------+ -| inventory_id | -+--------------+ -| 9 | -+--------------+ -1 row in set (0.02 sec) - -Query OK, 0 rows affected (0.02 sec) - -mysql> SELECT @count; -+--------+ -| @count | -+--------+ -| 1 | -+--------+ -1 row in set (0.00 sec) -</programlisting> - - </section> - - <section id="sakila-structure-procedures-rewards_report"> - - <title>The rewards_report Stored Procedure</title> - - <bridgehead>Description</bridgehead> - - <para> - The <literal>rewards_report</literal> stored procedure - generates a customizable list of the top customers for the - previous month. - </para> - - <bridgehead>Parameters</bridgehead> - - <itemizedlist> - - <listitem> - <para> - <literal>min_monthly_purchases</literal> - The minimum - number of purchases/rentals a customer needed to make in - the last month to qualify. - </para> - </listitem> - - <listitem> - <para> - <literal>min_dollar_amount_purchased</literal> - The - minimum dollar amount a customer needed to spend in the - last month to qualify. - </para> - </listitem> - - <listitem> - <para> - <literal>count_rewardees</literal> - An OUT parameter that - returns a count of the customers who met the - qualifications specified. - </para> - </listitem> - - </itemizedlist> - - <bridgehead>Return Values</bridgehead> - - <para> - This function returns a table of customers who met the - qualifications specified. The table returned is of the same - structure as the - <link - linkend="sakila-structure-tables-customer">customer</link> - table. - </para> - - <bridgehead>Sample Usage</bridgehead> - -<programlisting> -mysql> CALL rewards_report(7,20.00,@count); - -... -| 598 | 1 | WADE | DELVALLE | WADE.DELVALLE@stripped | 604 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 | -| 599 | 2 | AUSTIN | CINTRON | AUSTIN.CINTRON@stripped | 605 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 | -... - -42 rows in set (0.11 sec) - -Query OK, 0 rows affected (0.67 sec) - -mysql> SELECT @count; -+--------+ -| @count | -+--------+ -| 42 | -+--------+ -1 row in set (0.00 sec) -</programlisting> - - </section> - - </section> - - <section id="sakila-structure-functions"> - - <title>Stored Functions</title> - - <para> - This section lists the stored functions included with the - &sampdb; sample database. - </para> - - <section id="sakila-structure-functions-get_customer_balance"> - - <title>The get_customer_balance Function</title> - - <para> - The <literal>get_customer_balance</literal> function returns - the current amount owing on a specified customer's account. - </para> - - <bridgehead>Parameters</bridgehead> - - <itemizedlist> - - <listitem> - <para> - <literal>p_customer_id</literal> - The id of the customer - to check, from the <literal>customer_id</literal> column - of the <literal>customer</literal> table. - </para> - </listitem> - - <listitem> - <para> - <literal>p_effective_date</literal> - The cutoff date for - items that will be applied to the balance. Any - rentals/payments/etc after this date are not counted. - </para> - </listitem> - - </itemizedlist> - - <bridgehead>Return Values</bridgehead> - - <para> - This functions returns the amount owing on the customer's - account. - </para> - - <bridgehead>Sample Usage</bridgehead> - -<programlisting> -mysql> SELECT get_customer_balance(298,NOW()); -+---------------------------------+ -| get_customer_balance(298,NOW()) | -+---------------------------------+ -| 22.00 | -+---------------------------------+ -1 row in set (0.00 sec) -</programlisting> - - </section> - - <section id="sakila-structure-functions-inventory_held_by_customer"> - - <title>The inventory_held_by_customer Function</title> - - <para> - The <literal>inventory_held_by_customer</literal> function - returns the customer_id of the customer who has rented out the - specified inventory item. - </para> - - <bridgehead>Parameters</bridgehead> - - <itemizedlist> - - <listitem> - <para> - <literal>p_inventory_id</literal> - The id of the - inventory item to be checked. - </para> - </listitem> - - </itemizedlist> - - <bridgehead>Return Values</bridgehead> - - <para> - This functions returns the customer_id of the customer who is - currently renting the item, or NULL if the item is in stock. - </para> - - <bridgehead>Sample Usage</bridgehead> - -<programlisting> -mysql> SELECT inventory_held_by_customer(8); -+-------------------------------+ -| inventory_held_by_customer(8) | -+-------------------------------+ -| NULL | -+-------------------------------+ -1 row in set (0.14 sec) - -mysql> SELECT inventory_held_by_customer(9); -+-------------------------------+ -| inventory_held_by_customer(9) | -+-------------------------------+ -| 366 | -+-------------------------------+ -1 row in set (0.00 sec) - -mysql> -</programlisting> - - </section> - - <section id="sakila-structure-functions-inventory_in_stock"> - - <title>The inventory_in_stock Function</title> - - <para> - The <literal>inventory_function</literal> function returns a - boolean value indicating whether the inventory item specified - is in stock. - </para> - - <bridgehead>Parameters</bridgehead> - - <itemizedlist> - - <listitem> - <para> - <literal>p_inventory_id</literal> - The id of the - inventory item to be checked. - </para> - </listitem> - - </itemizedlist> - - <bridgehead>Return Values</bridgehead> - - <para> - This function returns TRUE or FALSE depending on whether the - item specified is in stock. - </para> - - <bridgehead>Sample Usage</bridgehead> - -<programlisting> -mysql> SELECT inventory_in_stock(9); -+-----------------------+ -| inventory_in_stock(9) | -+-----------------------+ -| 0 | -+-----------------------+ -1 row in set (0.03 sec) - -mysql> SELECT inventory_in_stock(8); -+-----------------------+ -| inventory_in_stock(8) | -+-----------------------+ -| 1 | -+-----------------------+ -1 row in set (0.00 sec) -</programlisting> - - </section> - - </section> - - <section id="sakila-structure-triggers"> - - <title>Triggers</title> - - <para> - This section lists the triggers in the &sampdb; sample database. - </para> - - <section id="sakila-structure-triggers-customer_create_date"> - - <title>The customer_create_date Trigger</title> - - <para> - The <literal>customer_create_date</literal> trigger sets the - create_date column of the customer table to the current time - and date as rows are inserted. - </para> - - </section> - - <section id="sakila-structure-triggers-payment_date"> - - <title>The payment_date Trigger</title> - - <para> - The <literal>payment_date</literal> trigger sets the - payment_date column of the payment table to the current time - and date as rows are inserted. - </para> - - </section> - - <section id="sakila-structure-triggers-rental_date"> - - <title>The rental_date Trigger</title> - - <para> - The <literal>rental_date</literal> trigger sets the - rental_date column of the rental table to the current time and - date as rows are inserted. - </para> - - </section> - - <section id="sakila-structure-triggers-ins_film"> - - <title>The ins_film Trigger</title> - - <para> - The <literal>ins_film</literal> trigger duplicates all INSERT - operations on the film table to the film_text table. - </para> - - </section> - - <section id="sakila-structure-triggers-upd_film"> - - <title>The upd_film Trigger</title> - - <para> - The <literal>upd_film</literal> trigger duplicates all UPDATE - operations on the film table to the film_text table. - </para> - - </section> - - <section id="sakila-structure-triggers-del_film"> - - <title>The del_film Trigger</title> - - <para> - The <literal>del_film</literal> trigger duplicates all DELETE - operations on the film table to the film_text table. - </para> - - </section> - - </section> - - </section> - - <section id="sakila-usage"> - - <title>Usage Examples</title> - - <para> - These are a few usage examples of how to perform common operations - using the &sampdb; sample database. While these operations are - good candidates for stored procedures and views, such - implementation is intentionally left as an - exercise to the user. - </para> - - <bridgehead>Rent a DVD</bridgehead> - - <para> - To rent a DVD, first confirm that the given inventory item is in - stock, then insert a row into the rental table. After the rental - is created, insert a row into the payment table. Depending on - business rules, you may also need to check if the customer has an - outstanding balance before processing the rental. - </para> - -<programlisting> -mysql> SELECT INVENTORY_IN_STOCK(10); -+------------------------+ -| INVENTORY_IN_STOCK(10) | -+------------------------+ -| 1 | -+------------------------+ -1 row in set (0.00 sec) - -mysql> INSERT INTO rental(rental_date, inventory_id, customer_id, staff_id) - -> VALUES(NOW(), 10, 3, 1); -Query OK, 1 row affected (0.00 sec) - -mysql> SELECT @balance := get_customer_balance(3, NOW()); -+--------------------------------------------+ -| @balance := get_customer_balance(3, NOW()) | -+--------------------------------------------+ -| 4.99 | -+--------------------------------------------+ -1 row in set (0.01 sec) - -mysql> INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date) - -> VALUES(3,1,LAST_INSERT_ID(), @balance, NOW()); -Query OK, 1 row affected (0.00 sec) -</programlisting> - - <bridgehead>Return a DVD</bridgehead> - - <para> - To return a DVD, we update the rental table and set the return - date. To do this, we first need to identify the rental_id to - update based on the inventory_id of the item being returned. Depending on the situation we may then need to check the - customer balance and perhaps process a payment for overdue fees by - inserting a row into the payment table. - </para> - -<programlisting> -mysql> SELECT rental_id - -> FROM rental - -> WHERE inventory_id = 10 - -> AND customer_id = 3 - -> AND return_date IS NULL; -+-----------+ -| rental_id | -+-----------+ -| 16050 | -+-----------+ -1 row in set (0.00 sec) - -mysql> UPDATE rental - -> SET return_date = NOW() - -> WHERE rental_id = @rentID; -Query OK, 1 row affected (0.00 sec) -Rows matched: 1 Changed: 1 Warnings: 0 - -mysql> SELECT get_customer_balance(3, NOW()); -+--------------------------------+ -| get_customer_balance(3, NOW()) | -+--------------------------------+ -| 0.00 | -+--------------------------------+ -1 row in set (0.09 sec) -</programlisting> - - <bridgehead>Find Overdue DVDs</bridgehead> - - <para> - Many DVD stores produce a daily list of overdue rentals so that - customers can be contacted and asked to return their overdue DVDs. - </para> - - <para> - To create such a list, we search the rental table for films with a - return date that is NULL and where the rental - date is further in the past than the rental duration specified in - the film table. If so, the film is overdue and we should return - the name of the film along with the customer name and phone - number. - </para> - -<programlisting> -mysql> SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, - - -> address.phone, film.title - -> FROM rental INNER JOIN customer ON rental.customer_id = customer.customer -_id - -> INNER JOIN address ON customer.address_id = address.address_id - -> INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id - -> INNER JOIN film ON inventory.film_id = film.film_id - -> WHERE rental.return_date IS NULL - -> AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE() - -> LIMIT 5; -+------------------+--------------+------------------+ -| customer | phone | title | -+------------------+--------------+------------------+ -| OLVERA, DWAYNE | 62127829280 | ACADEMY DINOSAUR | -| HUEY, BRANDON | 99883471275 | ACE GOLDFINGER | -| BROWN, ELIZABETH | 10655648674 | AFFAIR PREJUDICE | -| OWENS, CARMEN | 272234298332 | AFFAIR PREJUDICE | -| HANNON, SETH | 864392582257 | AFRICAN EGG | -+------------------+--------------+------------------+ -5 rows in set (0.02 sec) -</programlisting> - - </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, - contributed sample views and stored procedures. - </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 the &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> - - <section id="sakila-license"> - - <title>License for the &sampdb; Sample Database</title> - - <para> - The contents of the <filename>sakila-schema.sql</filename> and - <filename>sakila-data.sql</filename> files are licensed under the - New BSD license. - </para> - - <para> - Information on the New BSD license can be found at - <ulink - url="http://www.opensource.org/licenses/bsd-license.php">http://www.opensource.org/licenses/bsd-license.php</ulink> - and - <ulink - url="http://en.wikipedia.org/wiki/BSD_License">http://en.wikipedia.org/wiki/BSD_License</ulink>. - </para> - - <para> - The additional materials included in the &sampdb; distribution, - including this documentation, are not licensed under an open - license. Use of this documentation is subject to the following - terms: - </para> - - <itemizedlist> - - <listitem> - <para> - Conversion to other formats is allowed, but the actual content - may not be altered or edited in any way. - </para> - </listitem> - - <listitem> - <para> - You may create a printed copy for your own personal use. - </para> - </listitem> - - <listitem> - <para> - For all other uses, such as selling printed copies or using - (parts of) the manual in another publication, prior written - agreement from MySQL AB is required. - </para> - </listitem> - - </itemizedlist> - - <para> - Please email <email>docs@stripped</email> for more information or - if you are interested in doing a translation. - </para> - - </section> - - <section id="sakila-authors-note"> - - <title>Note for Authors</title> - - <para> - When using the &sampdb; sample database for articles and books, it - is strongly recommended that you explicitly list the version of - the &sampdb; sample database that is used in your examples. This - way readers will download the same version for their use and not - encounter any differences in their results that may occur from - upgrades to the data or schema. - </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> + + <para> + Feedback, bug reports, and requests can be directed to the MySQL + AB documentation team at <email>docs@stripped</email>. + </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, with the exception of the + <literal>film_text</literal> table, 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> + + <para> + This section describes the views that are included with the + &sampdb; sample database, in alphabetical order. + </para> + + <section id="sakila-structure-views-actor_info"> + + <title>The actor_info View</title> + + <para> + The <literal>actor_info</literal> view provides a list of all + actors, including the films they have performed in, broken + down by category. + </para> + + <para> + The staff_list view incorporates data from the film, actor, + category, film_actor and film_category tables. + </para> + + </section> + + <section id="sakila-structure-views-customer_list"> + + <title>The customer_list View</title> + + <para> + The <literal>customer_list</literal> view provides a list of + customers, with first name and last name concatenated together + and address information combined into a single view. + </para> + + <para> + The customer_list view incorporates data from the customer, + address, city and country tables. + </para> + + </section> + + <section id="sakila-structure-views-film_list"> + + <title>The film_list View</title> + + <para> + The <literal>film_list</literal> view contains a formatted + view of the film table, with a comma-separated list of the + film's actors. + </para> + + <para> + The film_list view incorporates data from the film, category, + film_category, actor and film_actor tables. + </para> + + </section> + + <section id="sakila-structure-views-nicer_film_list"> + + <title>The nicer_but_slower_film_list View</title> + + <para> + The <literal>nicer_but_slower_film_list</literal> view + contains a formatted view of the film table, with a + comma-separated list of the film's actors. + </para> + + <para> + The nicer_but_slower_film_list view differs from the film_list + view in the list of actors. The lettercase of the actor names + is adjusted so that the first letter of each name is + capitalized rather than have the name in all-caps. + </para> + + <para> + As indicated in its name, the nicer_but_slower_film_list + performs additional processing and therefore takes longer to + return data than the film_list view. + </para> + + <para> + The nicer_but_slower_film_list view incorporates data from the + film, category, film_category, actor and film_actor tables. + </para> + + </section> + + <section id="sakila-structure-views-sales_by_film_category"> + + <title>The sales_by_film_category View</title> + + <para> + The <literal>sales_by_film_category</literal> view provides a + list of total sales, broken down by individual film category. + </para> + + <para> + Because a film can be listed in multiple categories, it is not + advisable to calculate aggregate sales by totalling the rows + of this view. + </para> + + <para> + The sales_by_film_category view incorporates data from the + category, payment, rental, inventory, film, film_category and + category tables. + </para> + + </section> + + <section id="sakila-structure-views-sales_by_store"> + + <title>The sales_by_store View</title> + + <para> + The <literal>sales_by_store</literal> view provides a list of + total sales, broken down by store. + </para> + + <para> + The view returns the store location, manager name, and total + sales. + </para> + + <para> + The sales_by_store view incorporates data from the city, + country, payment, rental, inventory, store, address and staff + tables. + </para> + + </section> + + <section id="sakila-structure-views-staff-list"> + + <title>The staff_list View</title> + + <para> + The <literal>staff_list</literal> view provides a list of all + staff members, including address and store information. + </para> + + <para> + The staff_list view incorporates data from the staff and + address tables. + </para> + + </section> + + </section> + + <section id="sakila-structure-procedures"> + + <title>Stored Procedures</title> + + <para> + This is a list of stored procedures included with the &sampdb; + sample database, in alphabetical order. + </para> + + <para> + All parameters listed are IN parameters unless listed otherwise. + </para> + + <section id="sakila-structure-procedures-film_in_stock"> + + <title>The film_in_stock Stored Procedure</title> + + <bridgehead>Description</bridgehead> + + <para> + The <literal>film_in_stock</literal> stored procedure is used + to determine if there are any copies of a given film in stock + at a given store. + </para> + + <bridgehead>Parameters</bridgehead> + + <itemizedlist> + + <listitem> + <para> + <literal>p_film_id</literal> - The id of the film to be + checked, from the <literal>film_id</literal> column of the + <literal>film</literal> table. + </para> + </listitem> + + <listitem> + <para> + <literal>p_store_id</literal> - The id of the store to + check for, from the <literal>store_id</literal> column of + the <literal>store</literal> table. + </para> + </listitem> + + <listitem> + <para> + <literal>p_film_count</literal> - An OUT parameter that + returns a count of the copies of the film in stock. + </para> + </listitem> + + </itemizedlist> + + <bridgehead>Return Values</bridgehead> + + <para> + This function returns a table of inventory id numbers for the + copies of the film in stock. + </para> + + <bridgehead>Sample Usage</bridgehead> + +<programlisting> +mysql> CALL film_in_stock(1,1,@count); ++--------------+ +| inventory_id | ++--------------+ +| 1 | +| 2 | +| 3 | +| 4 | ++--------------+ +4 rows in set (0.06 sec) + +Query OK, 0 rows affected (0.06 sec) + +mysql> SELECT @count; ++--------+ +| @count | ++--------+ +| 4 | ++--------+ +1 row in set (0.00 sec) + +mysql> +</programlisting> + + </section> + + <section id="sakila-structure-procedures-film_not_in_stock"> + + <title>The film_not_in_stock Stored Procedure</title> + + <bridgehead>Description</bridgehead> + + <para> + The <literal>film_not_in_stock</literal> stored procedure is + used to determine if there are any copies of a given film not + in stock (rented out) at a given store. + </para> + + <bridgehead>Parameters</bridgehead> + + <itemizedlist> + + <listitem> + <para> + <literal>p_film_id</literal> - The id of the film to be + checked, from the <literal>film_id</literal> column of the + <literal>film</literal> table. + </para> + </listitem> + + <listitem> + <para> + <literal>p_store_id</literal> - The id of the store to + check for, from the <literal>store_id</literal> column of + the <literal>store</literal> table. + </para> + </listitem> + + <listitem> + <para> + <literal>p_film_count</literal> - An OUT parameter that + returns a count of the copies of the film not in stock. + </para> + </listitem> + + </itemizedlist> + + <bridgehead>Return Values</bridgehead> + + <para> + This function returns a table of inventory id numbers for the + copies of the film not in stock. + </para> + + <bridgehead>Sample Usage</bridgehead> + +<programlisting> +mysql> CALL film_not_in_stock(2,2,@count); ++--------------+ +| inventory_id | ++--------------+ +| 9 | ++--------------+ +1 row in set (0.02 sec) + +Query OK, 0 rows affected (0.02 sec) + +mysql> SELECT @count; ++--------+ +| @count | ++--------+ +| 1 | ++--------+ +1 row in set (0.00 sec) +</programlisting> + + </section> + + <section id="sakila-structure-procedures-rewards_report"> + + <title>The rewards_report Stored Procedure</title> + + <bridgehead>Description</bridgehead> + + <para> + The <literal>rewards_report</literal> stored procedure + generates a customizable list of the top customers for the + previous month. + </para> + + <bridgehead>Parameters</bridgehead> + + <itemizedlist> + + <listitem> + <para> + <literal>min_monthly_purchases</literal> - The minimum + number of purchases/rentals a customer needed to make in + the last month to qualify. + </para> + </listitem> + + <listitem> + <para> + <literal>min_dollar_amount_purchased</literal> - The + minimum dollar amount a customer needed to spend in the + last month to qualify. + </para> + </listitem> + + <listitem> + <para> + <literal>count_rewardees</literal> - An OUT parameter that + returns a count of the customers who met the + qualifications specified. + </para> + </listitem> + + </itemizedlist> + + <bridgehead>Return Values</bridgehead> + + <para> + This function returns a table of customers who met the + qualifications specified. The table returned is of the same + structure as the + <link + linkend="sakila-structure-tables-customer">customer</link> + table. + </para> + + <bridgehead>Sample Usage</bridgehead> + +<programlisting> +mysql> CALL rewards_report(7,20.00,@count); + +... +| 598 | 1 | WADE | DELVALLE | WADE.DELVALLE@stripped | 604 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 | +| 599 | 2 | AUSTIN | CINTRON | AUSTIN.CINTRON@stripped | 605 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 | +... + +42 rows in set (0.11 sec) + +Query OK, 0 rows affected (0.67 sec) + +mysql> SELECT @count; ++--------+ +| @count | ++--------+ +| 42 | ++--------+ +1 row in set (0.00 sec) +</programlisting> + + </section> + + </section> + + <section id="sakila-structure-functions"> + + <title>Stored Functions</title> + + <para> + This section lists the stored functions included with the + &sampdb; sample database. + </para> + + <section id="sakila-structure-functions-get_customer_balance"> + + <title>The get_customer_balance Function</title> + + <para> + The <literal>get_customer_balance</literal> function returns + the current amount owing on a specified customer's account. + </para> + + <bridgehead>Parameters</bridgehead> + + <itemizedlist> + + <listitem> + <para> + <literal>p_customer_id</literal> - The id of the customer + to check, from the <literal>customer_id</literal> column + of the <literal>customer</literal> table. + </para> + </listitem> + + <listitem> + <para> + <literal>p_effective_date</literal> - The cutoff date for + items that will be applied to the balance. Any + rentals/payments/etc after this date are not counted. + </para> + </listitem> + + </itemizedlist> + + <bridgehead>Return Values</bridgehead> + + <para> + This functions returns the amount owing on the customer's + account. + </para> + + <bridgehead>Sample Usage</bridgehead> + +<programlisting> +mysql> SELECT get_customer_balance(298,NOW()); ++---------------------------------+ +| get_customer_balance(298,NOW()) | ++---------------------------------+ +| 22.00 | ++---------------------------------+ +1 row in set (0.00 sec) +</programlisting> + + </section> + + <section id="sakila-structure-functions-inventory_held_by_customer"> + + <title>The inventory_held_by_customer Function</title> + + <para> + The <literal>inventory_held_by_customer</literal> function + returns the customer_id of the customer who has rented out the + specified inventory item. + </para> + + <bridgehead>Parameters</bridgehead> + + <itemizedlist> + + <listitem> + <para> + <literal>p_inventory_id</literal> - The id of the + inventory item to be checked. + </para> + </listitem> + + </itemizedlist> + + <bridgehead>Return Values</bridgehead> + + <para> + This functions returns the customer_id of the customer who is + currently renting the item, or NULL if the item is in stock. + </para> + + <bridgehead>Sample Usage</bridgehead> + +<programlisting> +mysql> SELECT inventory_held_by_customer(8); ++-------------------------------+ +| inventory_held_by_customer(8) | ++-------------------------------+ +| NULL | ++-------------------------------+ +1 row in set (0.14 sec) + +mysql> SELECT inventory_held_by_customer(9); ++-------------------------------+ +| inventory_held_by_customer(9) | ++-------------------------------+ +| 366 | ++-------------------------------+ +1 row in set (0.00 sec) + +mysql> +</programlisting> + + </section> + + <section id="sakila-structure-functions-inventory_in_stock"> + + <title>The inventory_in_stock Function</title> + + <para> + The <literal>inventory_function</literal> function returns a + boolean value indicating whether the inventory item specified + is in stock. + </para> + + <bridgehead>Parameters</bridgehead> + + <itemizedlist> + + <listitem> + <para> + <literal>p_inventory_id</literal> - The id of the + inventory item to be checked. + </para> + </listitem> + + </itemizedlist> + + <bridgehead>Return Values</bridgehead> + + <para> + This function returns TRUE or FALSE depending on whether the + item specified is in stock. + </para> + + <bridgehead>Sample Usage</bridgehead> + +<programlisting> +mysql> SELECT inventory_in_stock(9); ++-----------------------+ +| inventory_in_stock(9) | ++-----------------------+ +| 0 | ++-----------------------+ +1 row in set (0.03 sec) + +mysql> SELECT inventory_in_stock(8); ++-----------------------+ +| inventory_in_stock(8) | ++-----------------------+ +| 1 | ++-----------------------+ +1 row in set (0.00 sec) +</programlisting> + + </section> + + </section> + + <section id="sakila-structure-triggers"> + + <title>Triggers</title> + + <para> + This section lists the triggers in the &sampdb; sample database. + </para> + + <section id="sakila-structure-triggers-customer_create_date"> + + <title>The customer_create_date Trigger</title> + + <para> + The <literal>customer_create_date</literal> trigger sets the + create_date column of the customer table to the current time + and date as rows are inserted. + </para> + + </section> + + <section id="sakila-structure-triggers-payment_date"> + + <title>The payment_date Trigger</title> + + <para> + The <literal>payment_date</literal> trigger sets the + payment_date column of the payment table to the current time + and date as rows are inserted. + </para> + + </section> + + <section id="sakila-structure-triggers-rental_date"> + + <title>The rental_date Trigger</title> + + <para> + The <literal>rental_date</literal> trigger sets the + rental_date column of the rental table to the current time and + date as rows are inserted. + </para> + + </section> + + <section id="sakila-structure-triggers-ins_film"> + + <title>The ins_film Trigger</title> + + <para> + The <literal>ins_film</literal> trigger duplicates all INSERT + operations on the film table to the film_text table. + </para> + + </section> + + <section id="sakila-structure-triggers-upd_film"> + + <title>The upd_film Trigger</title> + + <para> + The <literal>upd_film</literal> trigger duplicates all UPDATE + operations on the film table to the film_text table. + </para> + + </section> + + <section id="sakila-structure-triggers-del_film"> + + <title>The del_film Trigger</title> + + <para> + The <literal>del_film</literal> trigger duplicates all DELETE + operations on the film table to the film_text table. + </para> + + </section> + + </section> + + </section> + + <section id="sakila-usage"> + + <title>Usage Examples</title> + + <para> + These are a few usage examples of how to perform common operations + using the &sampdb; sample database. While these operations are + good candidates for stored procedures and views, such + implementation is intentionally left as an exercise to the user. + </para> + + <bridgehead>Rent a DVD</bridgehead> + + <para> + To rent a DVD, first confirm that the given inventory item is in + stock, then insert a row into the rental table. After the rental + is created, insert a row into the payment table. Depending on + business rules, you may also need to check if the customer has an + outstanding balance before processing the rental. + </para> + +<programlisting> +mysql> SELECT INVENTORY_IN_STOCK(10); ++------------------------+ +| INVENTORY_IN_STOCK(10) | ++------------------------+ +| 1 | ++------------------------+ +1 row in set (0.00 sec) + +mysql> INSERT INTO rental(rental_date, inventory_id, customer_id, staff_id) + -> VALUES(NOW(), 10, 3, 1); +Query OK, 1 row affected (0.00 sec) + +mysql> SELECT @balance := get_customer_balance(3, NOW()); ++--------------------------------------------+ +| @balance := get_customer_balance(3, NOW()) | ++--------------------------------------------+ +| 4.99 | ++--------------------------------------------+ +1 row in set (0.01 sec) + +mysql> INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date) + -> VALUES(3,1,LAST_INSERT_ID(), @balance, NOW()); +Query OK, 1 row affected (0.00 sec) +</programlisting> + + <bridgehead>Return a DVD</bridgehead> + + <para> + To return a DVD, we update the rental table and set the return + date. To do this, we first need to identify the rental_id to + update based on the inventory_id of the item being returned. + Depending on the situation we may then need to check the customer + balance and perhaps process a payment for overdue fees by + inserting a row into the payment table. + </para> + +<programlisting> +mysql> SELECT rental_id + -> FROM rental + -> WHERE inventory_id = 10 + -> AND customer_id = 3 + -> AND return_date IS NULL; ++-----------+ +| rental_id | ++-----------+ +| 16050 | ++-----------+ +1 row in set (0.00 sec) + +mysql> UPDATE rental + -> SET return_date = NOW() + -> WHERE rental_id = @rentID; +Query OK, 1 row affected (0.00 sec) +Rows matched: 1 Changed: 1 Warnings: 0 + +mysql> SELECT get_customer_balance(3, NOW()); ++--------------------------------+ +| get_customer_balance(3, NOW()) | ++--------------------------------+ +| 0.00 | ++--------------------------------+ +1 row in set (0.09 sec) +</programlisting> + + <bridgehead>Find Overdue DVDs</bridgehead> + + <para> + Many DVD stores produce a daily list of overdue rentals so that + customers can be contacted and asked to return their overdue DVDs. + </para> + + <para> + To create such a list, we search the rental table for films with a + return date that is NULL and where the rental date is further in + the past than the rental duration specified in the film table. If + so, the film is overdue and we should return the name of the film + along with the customer name and phone number. + </para> + +<programlisting> +mysql> SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, + + -> address.phone, film.title + -> FROM rental INNER JOIN customer ON rental.customer_id = customer.customer +_id + -> INNER JOIN address ON customer.address_id = address.address_id + -> INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id + -> INNER JOIN film ON inventory.film_id = film.film_id + -> WHERE rental.return_date IS NULL + -> AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE() + -> LIMIT 5; ++------------------+--------------+------------------+ +| customer | phone | title | ++------------------+--------------+------------------+ +| OLVERA, DWAYNE | 62127829280 | ACADEMY DINOSAUR | +| HUEY, BRANDON | 99883471275 | ACE GOLDFINGER | +| BROWN, ELIZABETH | 10655648674 | AFFAIR PREJUDICE | +| OWENS, CARMEN | 272234298332 | AFFAIR PREJUDICE | +| HANNON, SETH | 864392582257 | AFRICAN EGG | ++------------------+--------------+------------------+ +5 rows in set (0.02 sec) +</programlisting> + + </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, + contributed sample views and stored procedures. + </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 the &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> + + <listitem> + <para> + <literal>Zak Greant</literal>, + <ulink + url="http://zak.greant.com">Community + Advocate and Author</ulink> - Provided advice and feedback on + licensing. + </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> + + <section id="sakila-license"> + + <title>License for the &sampdb; Sample Database</title> + + <para> + The contents of the <filename>sakila-schema.sql</filename> and + <filename>sakila-data.sql</filename> files are licensed under the + New BSD license. + </para> + + <para> + Information on the New BSD license can be found at + <ulink + url="http://www.opensource.org/licenses/bsd-license.php">http://www.opensource.org/licenses/bsd-license.php</ulink> + and + <ulink + url="http://en.wikipedia.org/wiki/BSD_License">http://en.wikipedia.org/wiki/BSD_License</ulink>. + </para> + + <para> + The additional materials included in the &sampdb; distribution, + including this documentation, are not licensed under an open + license. Use of this documentation is subject to the following + terms: + </para> + + <itemizedlist> + + <listitem> + <para> + Conversion to other formats is allowed, but the actual content + may not be altered or edited in any way. + </para> + </listitem> + + <listitem> + <para> + You may create a printed copy for your own personal use. + </para> + </listitem> + + <listitem> + <para> + For all other uses, such as selling printed copies or using + (parts of) the manual in another publication, prior written + agreement from MySQL AB is required. + </para> + </listitem> + + </itemizedlist> + + <para> + Please email <email>docs@stripped</email> for more information or + if you are interested in doing a translation. + </para> + + </section> + + <section id="sakila-authors-note"> + + <title>Note for Authors</title> + + <para> + When using the &sampdb; sample database for articles and books, it + is strongly recommended that you explicitly list the version of + the &sampdb; sample database that is used in your examples. This + way readers will download the same version for their use and not + encounter any differences in their results that may occur from + upgrades to the data or schema. + </para> + + </section> + + <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="changelog.xml"/> + +</article>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r1856 - trunk/sample-data/sakila | mhillyer | 18 Apr |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.557 sec. using MySQL 5.1.14-beta-log