SakilaSampleDB
Contents |
[edit] What is Sakila?
Sakila is the official name of the MySQL dolphin (the logo).
The name comes from a competition to name the dolphin. The winning name was submitted by Ambrose Twebaze, an Open Source software developer from Swaziland, Africa. According to Ambrose, the name Sakila has its roots in SiSwati, the local language of Swaziland. Sakila is also the name of a town in Arusha, Tanzania, near Ambrose's country of origin, Uganda.
[edit] Get the Sakila Sample Database
Download the database from the Sakila Download page on the MySQL Documentation Site.
The Sakila Sample database includes documentation for installation and descriptions of database objects. Also included in a MySQL Workbench Schema definition and appropiate image.
[edit] Contributions
[edit] From the talk at the MySQL Users Conference 2006
Notes by Sheeri Kritzer
[edit] Other
Updates by Ronald Bradford
[edit] About the Sakila Sample Database
[edit] Why is this needed?
The documentation current sample “world” database has just 3 tables, cities, countries and langs.
- Not the Intellectual Property of MySQL, so it can’t be bundled with the software
- No table type diversity, not a lot of data type diversity
- No MySQL 5 features
- Not a lot of data
- Not really used in tutorials and articles because it’s not robust enough
[edit] Scenario
Basically, needed a sample database that was new, that allowed for many queries. Used an idea from a Dell Sample DB It’s 2 stores, in 2 different countries.
[edit] Designing the Database
Designed with MySQL Query Browser Normalized Community provided feedback and content Trick was balance between simplicity and completeness Learning db, so people need to understand it After schema was approved, data was loaded Stored procedures, triggers, UDF’s and VIEWS were used
[edit] Schema
The Sample database has 2 files in installation
- one for schema & routines (16 tables, 8 views)
- one for data
This allows you can just look at schema if you want. You can also support modified data sources, for example larger volumes or different languages.
Data is never deleted — it’s marked as deleted, but not actually deleted, for data integrity reasons. Contains most (all?) data types, including SET and ENUM.
[edit] VIEWS
VIEWS used to simplify queries. ie, there’s a table for actors and one for films, so actor_info contains a list of type of movie and name for each actor. So it would show a list of actors with a list of films that they’ve done — 1 row per actor, and a comma-separated list of films they’ve done (sorted by type). There are a lot of good views in there, which show that that’s what you should do for popular queries (like “show me all the movies that are rented, by customer”).
[edit] Stored Procedures
For common procedures — ie, is a film in stock?
[edit] Stored Functions
get_customer_balance — it does what you think it does.
[edit] Triggers
There’s one to sync 2 tables, for example.
[edit] Licensing
PostgreSQL want to do Documentation copyright MySQL BSD licensing
[edit] Goals Going Forward
Make as few changes as possible Implement 5.1 changes if necessary