SQLtest

Sakila DB description

Sakila DB ER diagram

The Sakila database contains 16 main tables describing various aspects of a DVD rental company.

Below is a list of these tables:

Table actor

• actor_id - Unique identifier for each actor.
• first_name - First name of the actor.
• last_name - Last name of the actor.
• last_update - When the row was created or most recently updated.

Table film

• film_id - Unique identifier for each film.
• title - The title of the film.
• description - A short description or plot summary of the film.
• release_year - The year in which the movie was released.
• language_id - Foreign key referencing the language table; identifies the language of the film.
• original_language_id - A foreign key pointing at the language table; identifies the original language of the film. Used when a film has been dubbed into a new language.
• rental_duration - The length of the rental period, in days.
• rental_rate - The cost to rent the film for the period specified in the rental_duration column.
• length - Duration of the film, in minutes.
• replacement_cost - The amount charged to the customer if the film is not returned or is returned in a damaged state.
• rating - The rating assigned to the film. Can be one of: G, PG, PG-13, R, or NC-17.
• special_features - Lists which common special features are included on the DVD. Can be zero or more of: Trailers, Commentaries, Deleted Scenes, Behind the Scenes.
• last_update - When the row was created or most recently updated.

Table film_text

• film_id - Unique identifier for each film.
• title - Title of the film.
• description - Description of the film.

Table film_actor

• actor_id - Unique identifier for actor.
• film_id - Unique identifier for film.
• last_update - When the row was created or most recently updated.

Table customer

• customer_id - Unique identifier for each customer.
• store_id - Foreign key referencing the store table.
• first_name - First name of the customer.
• last_name - Last name of the customer.
• email - Email address of the customer.
• address_id - Foreign key referencing the address table.
• active - Indicates whether the customer is active.
• create_date - Timestamp indicating when the customer was added to the database.
• last_update - When the row was created or most recently updated.

Table address

• address_id - Unique identifier for each address.
• address - Street address.
• address2 - Additional address.
• district - District or region.
• city_id - Foreign key referencing the city table.
• postal_code - Postal code.
• phone - Phone number.
• last_update - When the row was created or most recently updated.

Table city

• city_id - Unique identifier for each city.
• city - City name.
• country_id - Foreign key referencing the country table.
• last_update - When the row was created or most recently updated.

Table country

• country_id - Unique identifier for each country.
• country - Country name.
• last_update - When the row was created or most recently updated.

Table category

• category_id - Unique identifier for each category.
• name - Name of the category.
• last_update - When the row was created or most recently updated.

Table film_category

• film_id - Unique identifier for each film.
• category_id - Unique identifier for each category.
• last_update - When the row was created or most recently updated.

Table language

• language_id - Unique identifier for each language.
• name - Language name.
• last_update - When the row was created or most recently updated.

Table staff

• staff_id - Unique identifier for each staff member.
• first_name - First name of the staff member.
• last_name - Last name of the staff member.
• address_id - Foreign key referencing the address table.
• picture - Staff member picture.
• email - Email address of the staff member.
• store_id - Foreign key referencing the store table.
• active - Indicates whether the staff member is active.
• username - Username for login.
• password - Password for login.
• last_update - When the row was created or most recently updated.

Table staff_list

• ID - Unique identifier for each staff list entry.
• name - Staff member's name.
• address - Staff member's address.
• zip code - Staff member's zip code.
• phone - Staff member's phone number.
• city - Staff member's city.
• country - Staff member's country.
• SID - Foreign key referencing the staff table.

Table store

• store_id - Unique identifier for each store.
• manager_staff_id - Foreign key referencing the staff table for the store manager.
• address_id - Foreign key referencing the address table.
• last_update - When the row was created or most recently updated.

Table payment

• payment_id - Unique identifier for each payment.
• customer_id - Foreign key referencing the customer table.
• staff_id - Foreign key referencing the staff table.
• rental_id - Foreign key referencing the rental table.
• amount - Payment amount.
• payment_date - Date of the payment.
• last_update - When the row was created or most recently updated.

Table inventory

• inventory_id - Unique identifier for each inventory item.
• film_id - Unique identifier for each film in the inventory.
• store_id - Unique identifier for the store where the inventory item is located.
• last_update - When the row was created or most recently updated.

Table rental

• rental_id - Unique identifier for each rental.
• rental_date - Date when the rental occurred.
• inventory_id - Foreign key referencing the inventory table.
• customer_id - Foreign key referencing the customer table.
• return_date - Date when the rental was returned.
• staff_id - Foreign key referencing the staff table.
• last_update - When the row was created or most recently updated.