DVD Rental
A project in PostgreSQL analyzing information from the DVD Rental Database
Scenario:
The scenario is that of the DVD Rental Company inventory manager who wants to determine how many copies of each DVD should be stocked on the shelves. To make this decision, the manager needs to know which DVDs are rented most frequently, and how many days they are rented for.
New table planning:
– New table creation to address the scenario
– Assemble useful data from existing database
Function with a trigger:
– Trigger executes after each insert
– Summary refreshes each time
– Results are GROUPed and COUNTed
Extract data:
– INSERT INTO the new tables
– DISTINCT ON resolves duplicates
– JOIN columns from mutiple source tables
Stored procedure:
– Procedure refreshes both tables
– Re-executes the INSERT with JOIN
– Could be run on a schedule
Dig into the details at the Github page.
Full Summary:
This project uses the DVD Rental sample database included in PostgreSQL. The scenario is that of the DVD Rental Company inventory manager who wants to determine how many copies of each DVD should be stocked on the shelves. To make this decision, the manager needs to know which DVDs are rented most frequently, and how many days they are rented for. This information needs to be store-specific because each store has their own stock of DVDs on the shelves.
The data used for the report includes rental information about the DVDs, such as the dates rented and returned. It also includes information identifying which store the DVDs are from, and what films they are.
There are two source tables needed to provide the data necessary for the detailed and summary sections of the report. These tables are the rental table and the inventory table. The two new tables created are the rental_detail, and rental_summary tables.
The detail table provides a record of every rental record in the report. This will be useful because it will indicate how many total rentals have taken place. It will also show the number of days that a DVD was rented out for each rental. The summary table shows the total number of times rented for each DVD (film_id) at each store.