Setup sample database in Postgres

Setup Postgres sample database

In this post, I will introduce you to a PostgreSQL sample database that you can use for learning and practice PostgreSQL. We will use the DVD rental database for demonstrating the features of PostgreSQL. The DVD rental database represents the business processes of a DVD rental store. The DVD rental database has many objects including:

  • 15 tables
  • 1 trigger
  • 7 views
  • 8 functions
  • 1 domain
  • 13 sequences

Tables

There are 15 tables in the DVD Rental database:

  • actor – stores actors data including first name and last name.
  • film – stores films data such as title, release year, length, rating, etc.
  • film_actor – stores the relationships between films and actors.
  • category – stores film’s categories data.
  • film_category - stores the relationships between films and categories.
  • store – contains the store data including manager staff and address.
  • inventory – stores inventory data.
  • rental – stores rental data.
  • payment – stores customer’s payments.
  • staff – stores staff data.
  • customer – stores customers data.
  • address – stores address data for staff and customers
  • city – stores the city names.
  • country – stores the country names.

ER diagram

sakila

Load the database into Postgres server

The database can be downloaded using from here TODO which is of the form database.tar. This file needs to loaded into Postgres server.

Create a new database: We need to create a new database in PostgresSQL database server before loading database schema and data into the database.

  1. Launch pgAdmin window and connect to the localhost server (if you have postgres database server running on a remote host, then specify the url/port).
  2. Create database dvdrental. Next, right click and click Restore

pgadmin

  1. Right click on the dvdrental database and restore.

restore

NOTE: If you see any errors during restoration with regards to role: postgres not available, then create that role.

Query the database

%load_ext sql
%sql postgresql://shravan:shravan@127.0.0.1/dvdrental
'Connected: shravan@dvdrental'
%%sql
-- Query the right table in information_schema
SELECT table_name
FROM information_schema.tables
-- Specify the correct table_schema value
WHERE table_schema = 'public';
 * postgresql://shravan:***@127.0.0.1/dvdrental
22 rows affected.
table_name
actor
actor_info
customer_list
film_list
nicer_but_slower_film_list
sales_by_film_category
store
sales_by_store
staff_list
address
category
city
country
customer
film_actor
film_category
inventory
language
rental
staff
payment
film
%%sql
SELECT
   *
FROM customer
LIMIT 10;
 * postgresql://shravan:***@127.0.0.1/dvdrental
10 rows affected.
customer_id store_id first_name last_name email address_id activebool create_date last_update active
524 1 Jared Ely jared.ely@sakilacustomer.org 530 True 2006-02-14 2013-05-26 14:49:45.738000 1
1 1 Mary Smith mary.smith@sakilacustomer.org 5 True 2006-02-14 2013-05-26 14:49:45.738000 1
2 1 Patricia Johnson patricia.johnson@sakilacustomer.org 6 True 2006-02-14 2013-05-26 14:49:45.738000 1
3 1 Linda Williams linda.williams@sakilacustomer.org 7 True 2006-02-14 2013-05-26 14:49:45.738000 1
4 2 Barbara Jones barbara.jones@sakilacustomer.org 8 True 2006-02-14 2013-05-26 14:49:45.738000 1
5 1 Elizabeth Brown elizabeth.brown@sakilacustomer.org 9 True 2006-02-14 2013-05-26 14:49:45.738000 1
6 2 Jennifer Davis jennifer.davis@sakilacustomer.org 10 True 2006-02-14 2013-05-26 14:49:45.738000 1
7 1 Maria Miller maria.miller@sakilacustomer.org 11 True 2006-02-14 2013-05-26 14:49:45.738000 1
8 2 Susan Wilson susan.wilson@sakilacustomer.org 12 True 2006-02-14 2013-05-26 14:49:45.738000 1
9 2 Margaret Moore margaret.moore@sakilacustomer.org 13 True 2006-02-14 2013-05-26 14:49:45.738000 1
%%sql
SELECT *
FROM film
LIMIT 10;
 * postgresql://shravan:***@127.0.0.1/dvdrental
10 rows affected.
film_id title description release_year language_id rental_duration rental_rate length replacement_cost rating last_update special_features fulltext
133 Chamber Italian A Fateful Reflection of a Moose And a Husband who must Overcome a Monkey in Nigeria 2006 1 7 4.99 117 14.99 NC-17 2013-05-26 14:50:58.951000 ['Trailers'] 'chamber':1 'fate':4 'husband':11 'italian':2 'monkey':16 'moos':8 'must':13 'nigeria':18 'overcom':14 'reflect':5
384 Grosse Wonderful A Epic Drama of a Cat And a Explorer who must Redeem a Moose in Australia 2006 1 5 4.99 49 19.99 R 2013-05-26 14:50:58.951000 ['Behind the Scenes'] 'australia':18 'cat':8 'drama':5 'epic':4 'explor':11 'gross':1 'moos':16 'must':13 'redeem':14 'wonder':2
8 Airport Pollock A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India 2006 1 6 4.99 54 15.99 R 2013-05-26 14:50:58.951000 ['Trailers'] 'airport':1 'ancient':18 'confront':14 'epic':4 'girl':11 'india':19 'monkey':16 'moos':8 'must':13 'pollock':2 'tale':5
98 Bright Encounters A Fateful Yarn of a Lumberjack And a Feminist who must Conquer a Student in A Jet Boat 2006 1 4 4.99 73 12.99 PG-13 2013-05-26 14:50:58.951000 ['Trailers'] 'boat':20 'bright':1 'conquer':14 'encount':2 'fate':4 'feminist':11 'jet':19 'lumberjack':8 'must':13 'student':16 'yarn':5
1 Academy Dinosaur A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies 2006 1 6 0.99 86 20.99 PG 2013-05-26 14:50:58.951000 ['Deleted Scenes', 'Behind the Scenes'] 'academi':1 'battl':15 'canadian':20 'dinosaur':2 'drama':5 'epic':4 'feminist':8 'mad':11 'must':14 'rocki':21 'scientist':12 'teacher':17
2 Ace Goldfinger A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China 2006 1 3 4.99 48 12.99 G 2013-05-26 14:50:58.951000 ['Trailers', 'Deleted Scenes'] 'ace':1 'administr':9 'ancient':19 'astound':4 'car':17 'china':20 'databas':8 'epistl':5 'explor':12 'find':15 'goldfing':2 'must':14
3 Adaptation Holes A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory 2006 1 7 2.99 50 18.99 NC-17 2013-05-26 14:50:58.951000 ['Trailers', 'Deleted Scenes'] 'adapt':1 'astound':4 'baloon':19 'car':11 'factori':20 'hole':2 'lumberjack':8,16 'must':13 'reflect':5 'sink':14
4 Affair Prejudice A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank 2006 1 5 2.99 117 26.99 G 2013-05-26 14:50:58.951000 ['Commentaries', 'Behind the Scenes'] 'affair':1 'chase':14 'documentari':5 'fanci':4 'frisbe':8 'lumberjack':11 'monkey':16 'must':13 'prejudic':2 'shark':19 'tank':20
5 African Egg A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico 2006 1 6 2.99 130 22.99 G 2013-05-26 14:50:58.951000 ['Deleted Scenes'] 'african':1 'chef':11 'dentist':14 'documentari':7 'egg':2 'fast':5 'fast-pac':4 'forens':19 'gulf':23 'mexico':25 'must':16 'pace':6 'pastri':10 'psychologist':20 'pursu':17
6 Agent Truman A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China 2006 1 3 2.99 169 17.99 PG 2013-05-26 14:50:58.951000 ['Deleted Scenes'] 'agent':1 'ancient':19 'boy':11 'china':20 'escap':14 'intrepid':4 'must':13 'panorama':5 'robot':8 'sumo':16 'truman':2 'wrestler':17