The Pagila database is a sample database often used to demonstrate the features and capabilities of PostgreSQL. It is a more advanced version of the popular Sakila database (originally designed for MySQL). Pagila is specifically tailored to take advantage of PostgreSQL-specific features while retaining the same use case scenario: a DVD rental store.

Key Features of Pagila Database

1. Realistic Use Case:
   • Models a DVD rental business with tables representing customers, rentals, inventory, films, actors, and stores.
   • Useful for practicing SQL queries and database management tasks.
2. PostgreSQL-Specific Features:
   • Includes ENUM types for more efficient data representation (e.g., film ratings).
   • Demonstrates the use of advanced PostgreSQL features like triggers and functions.
3. Normalization:
   • Fully normalized database schema (3NF or better), making it a great learning tool for relational database design.
4. Complex Relationships:
   • Contains many-to-many relationships, foreign keys, and cascading behaviors, providing realistic data modeling.

Main Tables in Pagila

Here are the core tables and their purposes:

• actor: Stores details about actors.
• film: Contains details about the films, such as title, release year, and rating.
• category: Represents film categories (e.g., Action, Comedy).
• film_category: Junction table for films and their categories.
• inventory: Tracks copies of each film in stock at each store.
• store: Represents physical locations of the DVD rental stores.
• customer: Holds information about customers.
• rental: Tracks film rentals, including rental and return dates.
• payment: Stores payment information for rentals.

Example Use Cases

• Practice basic and advanced SQL queries, including joins, aggregates, subqueries, and window functions.
• Learn how to work with PostgreSQL-specific data types and features.
• Explore database design principles and normalization.
• Understand how to handle large datasets in a transactional database.

How to Get Pagila Database

The Pagila database is open-source and can be downloaded from its GitHub repository or other official sources. You can install it by running the provided SQL scripts in a PostgreSQL instance.

To explore Pagila, you can use commands like:

-- List all tables
\dt

-- Describe a table (e.g., actor)
\d actor

-- Query data
SELECT * FROM actor;

It’s an excellent tool for database students, developers, and anyone looking to improve their SQL skills!

We can download Pagila Database from Kaggle : Get Pagila Database

ER Diagram given in the Kaggle :