Sakila Sample Database

Why is this needed?
“world” database current sample is 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

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.

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

Schema
2 files in installation — one for schema & routines and one for data, so you can just look at schema if you want.
16 tables, 8 views,
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.

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”).

Stored Procedures
For common procedures — ie, is a film in stock?

Stored Functions
get_customer_balance — it does what you think it does.

Triggers
There’s one to sync 2 tables, for example.

Licensing
PostgreSQL want to do
Documentation copyright MySQL
BSD licensing

Goals Going Forward
Make as few changes as possible
Implement 5.1 changes if necessary

Download it at: http://www.openwin.org/mike/download/sakila-0.8.zip

Comments are closed.