mysql information

Column Name	Column Type	Null?	Column Comment
actor_id	smallint(5) unsigned	NO
first_name	varchar(45)	NO
last_name	varchar(45)	NO
last_update	timestamp	NO

View Name

actor_info

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_actor_last_name	1	last_name	A	200				BTREE
0	PRIMARY	1	actor_id	A	200				BTREE

Table	Create Table
actor	CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL auto_increment, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

address

Column Name	Column Type	Null?	Column Comment
address_id	smallint(5) unsigned	NO
address	varchar(50)	NO
address2	varchar(50)	YES
district	varchar(20)	NO
city_id	smallint(5) unsigned	NO
postal_code	varchar(10)	YES
phone	varchar(20)	NO
last_update	timestamp	NO

View Name

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_city_id	1	city_id	A	589				BTREE
0	PRIMARY	1	address_id	A	589				BTREE

Table

Create Table

address

CREATE TABLE `address` ( `address_id` smallint(5) unsigned NOT NULL auto_increment, `address` varchar(50) NOT NULL, `address2` varchar(50) default NULL, `district` varchar(20) NOT NULL, `city_id` smallint(5) unsigned NOT NULL, `postal_code` varchar(10) default NULL, `phone` varchar(20) NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`address_id`), KEY `idx_fk_city_id` (`city_id`), CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

category

Column Name	Column Type	Null?	Column Comment
category_id	tinyint(3) unsigned	NO
name	varchar(25)	NO
last_update	timestamp	NO

View Name

actor_info

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
0	PRIMARY	1	category_id	A	16				BTREE

Table	Create Table
category	CREATE TABLE `category` ( `category_id` tinyint(3) unsigned NOT NULL auto_increment, `name` varchar(25) NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

city

Column Name	Column Type	Null?	Column Comment
city_id	smallint(5) unsigned	NO
city	varchar(50)	NO
country_id	smallint(5) unsigned	NO
last_update	timestamp	NO

View Name

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_country_id	1	country_id	A	427				BTREE
0	PRIMARY	1	city_id	A	427				BTREE

Table

Create Table

city

CREATE TABLE `city` ( `city_id` smallint(5) unsigned NOT NULL auto_increment, `city` varchar(50) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (`country_id`), CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

country

Column Name	Column Type	Null?	Column Comment
country_id	smallint(5) unsigned	NO
country	varchar(50)	NO
last_update	timestamp	NO

View Name

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
0	PRIMARY	1	country_id	A	109				BTREE

Table	Create Table
country	CREATE TABLE `country` ( `country_id` smallint(5) unsigned NOT NULL auto_increment, `country` varchar(50) NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

customer

Column Name	Column Type	Null?	Column Comment
customer_id	smallint(5) unsigned	NO
store_id	tinyint(3) unsigned	NO
first_name	varchar(45)	NO
last_name	varchar(45)	NO
email	varchar(50)	YES
address_id	smallint(5) unsigned	NO
active	tinyint(1)	NO
create_date	datetime	NO
last_update	timestamp	NO

View Name

Routine Type	Routine Name
FUNCTION	get_customer_balance

Trigger Name	Event Manipulation	Action Orientation	Action Timing	Action Reference Old Row	Action Reference New Row
customer_create_date	INSERT	ROW	BEFORE	OLD	NEW

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_address_id	1	address_id	A	541				BTREE
1	idx_fk_store_id	1	store_id	A	4				BTREE
1	idx_last_name	1	last_name	A	541				BTREE
0	PRIMARY	1	customer_id	A	541				BTREE

Table

Create Table

customer

CREATE TABLE `customer` ( `customer_id` smallint(5) unsigned NOT NULL auto_increment, `store_id` tinyint(3) unsigned NOT NULL, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `email` varchar(50) default NULL, `address_id` smallint(5) unsigned NOT NULL, `active` tinyint(1) NOT NULL default '1', `create_date` datetime NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`customer_id`), KEY `idx_fk_store_id` (`store_id`), KEY `idx_fk_address_id` (`address_id`), KEY `idx_last_name` (`last_name`), CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE, CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

film

Column Name	Column Type	Null?	Column Comment
film_id	smallint(5) unsigned	NO
title	varchar(255)	NO
description	text	YES
release_year	year(4)	YES
language_id	tinyint(3) unsigned	NO
original_language_id	tinyint(3) unsigned	YES
rental_duration	tinyint(3) unsigned	NO
rental_rate	decimal(4,2)	NO
length	smallint(5) unsigned	YES
replacement_cost	decimal(5,2)	NO
rating	enum('G','PG','PG-13','R','NC-17')	YES
special_features	set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')	YES
last_update	timestamp	NO

View Name

actor_info

Routine Type	Routine Name
FUNCTION	get_customer_balance

Trigger Name	Event Manipulation	Action Orientation	Action Timing	Action Reference Old Row	Action Reference New Row
ins_film	INSERT	ROW	AFTER	OLD	NEW
upd_film	UPDATE	ROW	AFTER	OLD	NEW
del_film	DELETE	ROW	AFTER	OLD	NEW

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_language_id	1	language_id	A	1				BTREE
1	idx_fk_original_language_id	1	original_language_id	A	1			YES	BTREE
1	idx_title	1	title	A	951				BTREE
0	PRIMARY	1	film_id	A	951				BTREE

Table

Create Table

film

CREATE TABLE `film` ( `film_id` smallint(5) unsigned NOT NULL auto_increment, `title` varchar(255) NOT NULL, `description` text, `release_year` year(4) default NULL, `language_id` tinyint(3) unsigned NOT NULL, `original_language_id` tinyint(3) unsigned default NULL, `rental_duration` tinyint(3) unsigned NOT NULL default '3', `rental_rate` decimal(4,2) NOT NULL default '4.99', `length` smallint(5) unsigned default NULL, `replacement_cost` decimal(5,2) NOT NULL default '19.99', `rating` enum('G','PG','PG-13','R','NC-17') default 'G', `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') default NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`film_id`), KEY `idx_title` (`title`), KEY `idx_fk_language_id` (`language_id`), KEY `idx_fk_original_language_id` (`original_language_id`), CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

film_actor

Column Name	Column Type	Null?	Column Comment
actor_id	smallint(5) unsigned	NO
film_id	smallint(5) unsigned	NO
last_update	timestamp	NO

View Name

actor_info

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_film_id	1	film_id	A	2571				BTREE
0	PRIMARY	1	actor_id	A	395				BTREE
0	PRIMARY	2	film_id	A	5143				BTREE

Table

Create Table

film_actor

CREATE TABLE `film_actor` ( `actor_id` smallint(5) unsigned NOT NULL, `film_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`,`film_id`), KEY `idx_fk_film_id` (`film_id`), CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

film_category

Column Name	Column Type	Null?	Column Comment
film_id	smallint(5) unsigned	NO
category_id	tinyint(3) unsigned	NO
last_update	timestamp	NO

View Name

actor_info

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	fk_film_category_category	1	category_id	A	32				BTREE
0	PRIMARY	1	film_id	A	1792				BTREE
0	PRIMARY	2	category_id	A	1792				BTREE

Table

Create Table

film_category

CREATE TABLE `film_category` ( `film_id` smallint(5) unsigned NOT NULL, `category_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`film_id`,`category_id`), KEY `fk_film_category_category` (`category_id`), CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

film_text

Column Name	Column Type	Null?	Column Comment
film_id	smallint(6)	NO
title	varchar(255)	NO
description	text	YES

View Name

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_title_description	1	title						FULLTEXT
1	idx_title_description	2	description					YES	FULLTEXT
0	PRIMARY	1	film_id	A	1000				BTREE

Table	Create Table
film_text	CREATE TABLE `film_text` ( `film_id` smallint(6) NOT NULL, `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (`film_id`), FULLTEXT KEY `idx_title_description` (`title`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

inventory

Column Name	Column Type	Null?	Column Comment
inventory_id	mediumint(8) unsigned	NO
film_id	smallint(5) unsigned	NO
store_id	tinyint(3) unsigned	NO
last_update	timestamp	NO

View Name

Routine Type	Routine Name
PROCEDURE	film_in_stock
PROCEDURE	film_not_in_stock
FUNCTION	get_customer_balance
FUNCTION	inventory_in_stock

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_film_id	1	film_id	A	2336				BTREE
1	idx_store_id_film_id	1	store_id	A	1				BTREE
1	idx_store_id_film_id	2	film_id	A	4673				BTREE
0	PRIMARY	1	inventory_id	A	4673				BTREE

Table

Create Table

inventory

CREATE TABLE `inventory` ( `inventory_id` mediumint(8) unsigned NOT NULL auto_increment, `film_id` smallint(5) unsigned NOT NULL, `store_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`inventory_id`), KEY `idx_fk_film_id` (`film_id`), KEY `idx_store_id_film_id` (`store_id`,`film_id`), CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE, CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

language

Column Name	Column Type	Null?	Column Comment
language_id	tinyint(3) unsigned	NO
name	char(20)	NO
last_update	timestamp	NO

View Name

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
0	PRIMARY	1	language_id	A	6				BTREE

Table	Create Table
language	CREATE TABLE `language` ( `language_id` tinyint(3) unsigned NOT NULL auto_increment, `name` char(20) NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`language_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

payment

Column Name	Column Type	Null?	Column Comment
payment_id	smallint(5) unsigned	NO
customer_id	smallint(5) unsigned	NO
staff_id	tinyint(3) unsigned	NO
rental_id	int(11)	YES
amount	decimal(5,2)	NO
payment_date	datetime	NO
last_update	timestamp	NO

View Name

Routine Type	Routine Name
FUNCTION	get_customer_balance
PROCEDURE	rewards_report

Trigger Name	Event Manipulation	Action Orientation	Action Timing	Action Reference Old Row	Action Reference New Row
payment_date	INSERT	ROW	BEFORE	OLD	NEW

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	fk_payment_rental	1	rental_id	A	16451			YES	BTREE
1	idx_fk_customer_id	1	customer_id	A	1175				BTREE
1	idx_fk_staff_id	1	staff_id	A	6				BTREE
0	PRIMARY	1	payment_id	A	16451				BTREE

Table

Create Table

payment

CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL auto_increment, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) default NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`), CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

rental

Column Name	Column Type	Null?	Column Comment
rental_id	int(11)	NO
rental_date	datetime	NO
inventory_id	mediumint(8) unsigned	NO
customer_id	smallint(5) unsigned	NO
return_date	datetime	YES
staff_id	tinyint(3) unsigned	NO
last_update	timestamp	NO

View Name

Routine Type	Routine Name
FUNCTION	get_customer_balance
FUNCTION	inventory_held_by_customer
FUNCTION	inventory_in_stock

Trigger Name	Event Manipulation	Action Orientation	Action Timing	Action Reference Old Row	Action Reference New Row
rental_date	INSERT	ROW	BEFORE	OLD	NEW

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_customer_id	1	customer_id	A	1200				BTREE
1	idx_fk_inventory_id	1	inventory_id	A	15609				BTREE
1	idx_fk_staff_id	1	staff_id	A	3				BTREE
0	PRIMARY	1	rental_id	A	15609				BTREE
0	rental_date	1	rental_date	A	15609				BTREE
0	rental_date	2	inventory_id	A	15609				BTREE
0	rental_date	3	customer_id	A	15609				BTREE

Table

Create Table

rental

CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL auto_increment, `rental_date` datetime NOT NULL, `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime default NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`), KEY `idx_fk_inventory_id` (`inventory_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `idx_fk_staff_id` (`staff_id`), CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

staff

Column Name	Column Type	Null?	Column Comment
staff_id	tinyint(3) unsigned	NO
first_name	varchar(45)	NO
last_name	varchar(45)	NO
address_id	smallint(5) unsigned	NO
picture	blob	YES
email	varchar(50)	YES
store_id	tinyint(3) unsigned	NO
active	tinyint(1)	NO
username	varchar(16)	NO
password	varchar(40)	YES
last_update	timestamp	NO

View Name

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_address_id	1	address_id	A	1				BTREE
1	idx_fk_store_id	1	store_id	A	1				BTREE
0	PRIMARY	1	staff_id	A	1				BTREE

Table

Create Table

staff

CREATE TABLE `staff` ( `staff_id` tinyint(3) unsigned NOT NULL auto_increment, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `address_id` smallint(5) unsigned NOT NULL, `picture` blob, `email` varchar(50) default NULL, `store_id` tinyint(3) unsigned NOT NULL, `active` tinyint(1) NOT NULL default '1', `username` varchar(16) NOT NULL, `password` varchar(40) character set utf8 collate utf8_bin default NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`staff_id`), KEY `idx_fk_store_id` (`store_id`), KEY `idx_fk_address_id` (`address_id`), CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE, CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

store

Column Name	Column Type	Null?	Column Comment
store_id	tinyint(3) unsigned	NO
manager_staff_id	tinyint(3) unsigned	NO
address_id	smallint(5) unsigned	NO
last_update	timestamp	NO

View Name

Routine Type

Routine Name

Trigger Name

Event Manipulation

Action Orientation

Action Timing

Action Reference Old Row

Action Reference New Row

Non Unique	Index Name	Seq In Index	Column Name	Collation	Cardinality	Sub Part	Packed	Nullable	Index Type	Comment
1	idx_fk_address_id	1	address_id	A	2				BTREE
0	idx_unique_manager	1	manager_staff_id	A	2				BTREE
0	PRIMARY	1	store_id	A	2				BTREE

Table

Create Table

store

CREATE TABLE `store` ( `store_id` tinyint(3) unsigned NOT NULL auto_increment, `manager_staff_id` tinyint(3) unsigned NOT NULL, `address_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`store_id`), UNIQUE KEY `idx_unique_manager` (`manager_staff_id`), KEY `idx_fk_address_id` (`address_id`), CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE, CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Views

View Name	View References	Routine References
actor_info	0	0
customer_list	0	0
film_list	0	0
nicer_but_slower_film_list	0	0
sales_by_film_category	0	0
sales_by_store	0	0
staff_list	0	0

actor_info

Column Name	Column Type	Null?	Column Comment
actor_id	smallint(5) unsigned	NO
first_name	varchar(45)	NO
last_name	varchar(45)	NO
film_info	varchar(341)	YES

View

Create View

actor_info

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `sakila`.`actor_info` AS select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,_utf8': ',(select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') AS `GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')` from ((`sakila`.`film` `f` join `sakila`.`film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `sakila`.`film_actor` `fa` on((`f`.`film_id` = `fa`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`sakila`.`actor` `a` left join `sakila`.`film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`))) left join `sakila`.`film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`))) left join `sakila`.`category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`

View Name

Routine Type

Routine Name

customer_list

Column Name	Column Type	Null?	Column Comment
ID	smallint(5) unsigned	NO
name	varchar(91)	YES
address	varchar(50)	NO
zip code	varchar(10)	YES
phone	varchar(20)	NO
city	varchar(50)	NO
country	varchar(50)	NO
notes	varchar(6)	NO
SID	tinyint(3) unsigned	NO

View

Create View

customer_list

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`customer_list` AS select `cu`.`customer_id` AS `ID`,concat(`cu`.`first_name`,_utf8' ',`cu`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,if(`cu`.`active`,_utf8'active',_utf8'') AS `notes`,`cu`.`store_id` AS `SID` from (((`sakila`.`customer` `cu` join `sakila`.`address` `a` on((`cu`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)))

View Name

Routine Type

Routine Name

film_list

Column Name	Column Type	Null?	Column Comment
FID	smallint(5) unsigned	YES
title	varchar(255)	YES
description	text	YES
category	varchar(25)	NO
price	decimal(4,2)	YES
length	smallint(5) unsigned	YES
rating	enum('G','PG','PG-13','R','NC-17')	YES
actors	varchar(341)	YES

View

Create View

film_list

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`film_list` AS select `sakila`.`film`.`film_id` AS `FID`,`sakila`.`film`.`title` AS `title`,`sakila`.`film`.`description` AS `description`,`sakila`.`category`.`name` AS `category`,`sakila`.`film`.`rental_rate` AS `price`,`sakila`.`film`.`length` AS `length`,`sakila`.`film`.`rating` AS `rating`,group_concat(concat(`sakila`.`actor`.`first_name`,_utf8' ',`sakila`.`actor`.`last_name`) separator ', ') AS `actors` from ((((`sakila`.`category` left join `sakila`.`film_category` on((`sakila`.`category`.`category_id` = `sakila`.`film_category`.`category_id`))) left join `sakila`.`film` on((`sakila`.`film_category`.`film_id` = `sakila`.`film`.`film_id`))) join `sakila`.`film_actor` on((`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`))) join `sakila`.`actor` on((`sakila`.`film_actor`.`actor_id` = `sakila`.`actor`.`actor_id`))) group by `sakila`.`film`.`film_id`

View Name

nicer_but_slower_film_list

Routine Type

Routine Name

Column Name	Column Type	Null?	Column Comment
FID	smallint(5) unsigned	YES
title	varchar(255)	YES
description	text	YES
category	varchar(25)	NO
price	decimal(4,2)	YES
length	smallint(5) unsigned	YES
rating	enum('G','PG','PG-13','R','NC-17')	YES
actors	varchar(341)	YES

View

Create View

nicer_but_slower_film_list

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`nicer_but_slower_film_list` AS select `sakila`.`film`.`film_id` AS `FID`,`sakila`.`film`.`title` AS `title`,`sakila`.`film`.`description` AS `description`,`sakila`.`category`.`name` AS `category`,`sakila`.`film`.`rental_rate` AS `price`,`sakila`.`film`.`length` AS `length`,`sakila`.`film`.`rating` AS `rating`,group_concat(concat(concat(ucase(substr(`sakila`.`actor`.`first_name`,1,1)),lcase(substr(`sakila`.`actor`.`first_name`,2,length(`sakila`.`actor`.`first_name`))),_utf8' ',concat(ucase(substr(`sakila`.`actor`.`last_name`,1,1)),lcase(substr(`sakila`.`actor`.`last_name`,2,length(`sakila`.`actor`.`last_name`)))))) separator ', ') AS `actors` from ((((`sakila`.`category` left join `sakila`.`film_category` on((`sakila`.`category`.`category_id` = `sakila`.`film_category`.`category_id`))) left join `sakila`.`film` on((`sakila`.`film_category`.`film_id` = `sakila`.`film`.`film_id`))) join `sakila`.`film_actor` on((`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`))) join `sakila`.`actor` on((`sakila`.`film_actor`.`actor_id` = `sakila`.`actor`.`actor_id`))) group by `sakila`.`film`.`film_id`

View Name

Routine Type

Routine Name

sales_by_film_category

Column Name	Column Type	Null?	Column Comment
category	varchar(25)	NO
total_sales	decimal(27,2)	YES

View

Create View

sales_by_film_category

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`sales_by_film_category` AS select `c`.`name` AS `category`,sum(`p`.`amount`) AS `total_sales` from (((((`sakila`.`payment` `p` join `sakila`.`rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `sakila`.`inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `sakila`.`film` `f` on((`i`.`film_id` = `f`.`film_id`))) join `sakila`.`film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `sakila`.`category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `c`.`name` order by sum(`p`.`amount`) desc

View Name

Routine Type

Routine Name

sales_by_store

Column Name	Column Type	Null?	Column Comment
store	varchar(101)	YES
manager	varchar(91)	YES
total_sales	decimal(27,2)	YES

View

Create View

sales_by_store

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`sales_by_store` AS select concat(`c`.`city`,_utf8',',`cy`.`country`) AS `store`,concat(`m`.`first_name`,_utf8' ',`m`.`last_name`) AS `manager`,sum(`p`.`amount`) AS `total_sales` from (((((((`sakila`.`payment` `p` join `sakila`.`rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `sakila`.`inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `sakila`.`store` `s` on((`i`.`store_id` = `s`.`store_id`))) join `sakila`.`address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` `c` on((`a`.`city_id` = `c`.`city_id`))) join `sakila`.`country` `cy` on((`c`.`country_id` = `cy`.`country_id`))) join `sakila`.`staff` `m` on((`s`.`manager_staff_id` = `m`.`staff_id`))) group by `s`.`store_id` order by `cy`.`country`,`c`.`city`

View Name

Routine Type

Routine Name

staff_list

Column Name	Column Type	Null?	Column Comment
ID	tinyint(3) unsigned	NO
name	varchar(91)	YES
address	varchar(50)	NO
zip code	varchar(10)	YES
phone	varchar(20)	NO
city	varchar(50)	NO
country	varchar(50)	NO
SID	tinyint(3) unsigned	NO

View

Create View

staff_list

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`staff_list` AS select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8' ',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,`s`.`store_id` AS `SID` from (((`sakila`.`staff` `s` join `sakila`.`address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)))

View Name

Routine Type

Routine Name

Procedure

Procedure	Table References
film_in_stock	1
film_not_in_stock	1
rewards_report	1

film_in_stock

Procedure	Sql Mode	Create Procedure
film_in_stock	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER	CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) READS SQL DATA BEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND inventory_in_stock(inventory_id); SELECT FOUND_ROWS() INTO p_film_count; END

Table Name

inventory

film_not_in_stock

Procedure	Sql Mode	Create Procedure
film_not_in_stock	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER	CREATE DEFINER=`root`@`localhost` PROCEDURE `film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) READS SQL DATA BEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND NOT inventory_in_stock(inventory_id); SELECT FOUND_ROWS() INTO p_film_count; END

Table Name

inventory

rewards_report

Procedure	Sql Mode	Create Procedure
rewards_report	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER	CREATE DEFINER=`root`@`localhost` PROCEDURE `rewards_report`( IN min_monthly_purchases TINYINT UNSIGNED , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED , OUT count_rewardees INT ) READS SQL DATA COMMENT 'Provides a customizable report on best customers' proc: BEGIN DECLARE last_month_start DATE; DECLARE last_month_end DATE; IF min_monthly_purchases = 0 THEN SELECT 'Minimum monthly purchases parameter must be > 0'; LEAVE proc; END IF; IF min_dollar_amount_purchased = 0.00 THEN SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00'; LEAVE proc; END IF; SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH); SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d'); SET last_month_end = LAST_DAY(last_month_start); CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY); INSERT INTO tmpCustomer (customer_id) SELECT p.customer_id FROM payment AS p WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end GROUP BY customer_id HAVING SUM(p.amount) > min_dollar_amount_purchased AND COUNT(customer_id) > min_monthly_purchases; SELECT COUNT() FROM tmpCustomer INTO count_rewardees; SELECT c. FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id; DROP TABLE tmpCustomer; END

Table Name

payment

Function

Function	Dtd Identifier	Routine Comment	Table References
get_customer_balance	decimal(5,2)		5
inventory_held_by_customer	int(11)		1
inventory_in_stock	tinyint(1)		2

get_customer_balance

Function	Sql Mode	Create Function
get_customer_balance	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER	CREATE DEFINER=`root`@`localhost` FUNCTION `get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2) READS SQL DATA DETERMINISTIC BEGIN DECLARE v_rentfees DECIMAL(5,2); DECLARE v_overfees INTEGER; DECLARE v_payments DECIMAL(5,2); SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees FROM film, inventory, rental WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration, ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees FROM rental, inventory, film WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT IFNULL(SUM(payment.amount),0) INTO v_payments FROM payment WHERE payment.payment_date <= p_effective_date AND payment.customer_id = p_customer_id; RETURN v_rentfees + v_overfees - v_payments; END

inventory_held_by_customer

Table Name

customer

film

inventory

payment

rental

Function	Sql Mode	Create Function
inventory_held_by_customer	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER	CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_held_by_customer`(p_inventory_id INT) RETURNS int(11) READS SQL DATA BEGIN DECLARE v_customer_id INT; DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL; SELECT customer_id INTO v_customer_id FROM rental WHERE return_date IS NULL AND inventory_id = p_inventory_id; RETURN v_customer_id; END

Table Name

rental

inventory_in_stock

Function	Sql Mode	Create Function
inventory_in_stock	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER	CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1) READS SQL DATA BEGIN DECLARE v_rentals INT; DECLARE v_out INT; SELECT COUNT(*) INTO v_rentals FROM rental WHERE inventory_id = p_inventory_id; IF v_rentals = 0 THEN RETURN TRUE; END IF; SELECT COUNT(rental_id) INTO v_out FROM inventory LEFT JOIN rental USING(inventory_id) WHERE inventory.inventory_id = p_inventory_id AND rental.return_date IS NULL; IF v_out > 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END