Pagila PostgreSQL DVD Rental Database

Columns

• actor: actor_id (unique identifier for the actor), first_name (actor's first name), last_name (actor's last name), last_update (timestamp of last modification).
• film: film_id (unique identifier for the film), title (film title), description (film summary), release_year (year of film release), language_id (ID of the film's language), original_language_id (ID of the original language), rental_duration (how long a film can be rented), rental_rate (cost to rent the film), length (film length in minutes), replacement_cost (cost to replace a lost or damaged film), rating (MPAA rating like G, PG, R, NC-17), last_update (timestamp of last modification), special_features (array of special features), fulltext (for full-text search).
• film_actor: actor_id (foreign key to actor table), film_id (foreign key to film table), last_update (timestamp of last modification) – links films to actors.
• category: category_id (unique identifier for the category), name (category name, e.g., 'Action', 'Comedy'), last_update (timestamp of last modification).
• film_category: film_id (foreign key to film table), category_id (foreign key to category table), last_update (timestamp of last modification) – links films to categories.
• store: store_id (unique identifier for the store), manager_staff_id (ID of the staff member managing the store), address_id (foreign key to address table), last_update (timestamp of last modification).
• inventory: inventory_id (unique identifier for an inventory item), film_id (foreign key to film table), store_id (foreign key to store table), last_update (timestamp of last modification).
• rental: rental_id (unique identifier for the rental), rental_date (date and time of rental), inventory_id (foreign key to inventory table), customer_id (foreign key to customer table), return_date (date and time of return), staff_id (foreign key to staff table), last_update (timestamp of last modification).
• payment: payment_id (unique identifier for the payment), customer_id (foreign key to customer table), staff_id (foreign key to staff table), rental_id (foreign key to rental table), amount (payment amount), payment_date (date and time of payment). This table is partitioned by date.
• staff: staff_id (unique identifier for the staff member), first_name (staff first name), last_name (staff last name), address_id (foreign key to address table), email (staff email), store_id (store where staff works), active (boolean indicating if staff is active), username (staff username), password (staff password), last_update (timestamp of last modification), picture (staff photo).
• customer: customer_id (unique identifier for the customer), store_id (store where customer is registered), first_name (customer first name), last_name (customer last name), email (customer email), address_id (foreign key to address table), activebool (boolean for active status), create_date (date customer record was created), last_update (timestamp of last modification), active (integer active status).
• address: address_id (unique identifier for the address), address (street address line 1), address2 (street address line 2), district (district name), city_id (foreign key to city table), postal_code (postal code), phone (phone number), last_update (timestamp of last modification).
• city: city_id (unique identifier for the city), city (city name), country_id (foreign key to country table), last_update (timestamp of last modification).
• country: country_id (unique identifier for the country), country (country name), last_update (timestamp of last modification).
• language: language_id (unique identifier for the language), name (language name, e.g., 'English'), last_update (timestamp of last modification).

Distribution

Usage

• Learning and demonstrating PostgreSQL features, including database creation, schema design, data manipulation, and advanced SQL functions.
• Practising SQL queries for data retrieval, analysis, and reporting.
• Developing and testing applications that require a relational database backend.
• Understanding data relationships and database normalisation principles.
• Analysing simulated business operations for insights into rentals, payments, customer behaviour, and inventory management.

Coverage

• Geographic Scope: While specific real-world locations are not detailed, the database schema includes tables for country, city, address, and store, allowing for geographic analysis within the simulated environment.
• Time Range: Payment data is structured with partitions for the year 2017. Film release years are designed to range from 1901 to 2155.
• Demographic Scope: Data covers customer and staff demographics, including names, addresses, and activity status.
• Update Frequency: The dataset is expected to be updated annually.

License

Who Can Use It

• Students and educators for learning database concepts and SQL.
• Database Administrators (DBAs) for practising PostgreSQL management and optimisation.
• Data Analysts for honing SQL querying skills and exploring business data.
• Software Developers needing a realistic dataset for application prototyping and testing.
• Anyone interested in business intelligence or movie industry data for analytical exercises.

Dataset Name Suggestions

• Pagila PostgreSQL DVD Rental Database
• DVD Rental Business Dataset
• PostgreSQL Sample Film Database
• Relational DVD Store Data

Attributes