IMDB on IBMi

A complete, normalized, downloadable dataset for IBM i developers — built so you can pull it down, load it into a library on PUB400 (or any IBM i partition), and start writing real SQL, RPGLE, and SQLRPGLE against data that looks like something you'd actually be asked to report on at work.

RPG isn't a dead language. This repo exists so there's finally something to practice it on that isn't a three-column sample file.

Table of contents

• Why this exists
• What's in the box
• Data source and license
• Schema
• Design notes
• Data dictionary
• Known data-quality fix
• Getting started on PUB400
• Verifying the load
• Example queries
• Suggested exercises
• Repo structure
• Regenerating the load scripts
• Contributing
• License
• Acknowledgments

Why this exists

There's no ready-made dataset an IBM i developer can download, drop into a library, and immediately start querying. Most public sample data is either locked inside a vendor's demo environment or lives as a single flat file that doesn't exercise joins, foreign keys, or any of the relational modeling an RPG shop actually deals with day to day.

This project takes the Kaggle "top 1000 movies" dataset and normalizes it into eight related DB2 for i tables — identity primary keys, foreign keys, bridge tables for the many-to-many relationships, the works. It's meant to be the dataset you point students, junior devs, or your own practice programs at when you want something with real shape to it.

What's in the box

• DDL for eight 3NF tables with identity primary keys and foreign key constraints
• Load scripts that populate all ~9,700 rows from the source CSV, with the known data-quality issue in the source file corrected
• Short IBM i "system names" on every table/column over 10 characters, so the schema works from both free-format RPGLE/embedded SQL and classic fixed-format RPG or DDS-described access
• The Python script used to generate the load scripts, so the dataset can be regenerated if the source CSV changes

Data source and license

The data originates from the IMDB Movies Dataset on Kaggle (scraped from IMDb, ~1000 rows, 16 columns), compiled by Kaggle user harshitshankhdhar.

Before publishing this repo, confirm the license terms on that Kaggle dataset page and add a matching notice here. IMDb's own terms of use place restrictions on scraping and redistributing their data, and the license attached to a Kaggle dataset doesn't automatically override that — it's worth a deliberate look rather than an assumption. Depending on what you find, you may want to:

• Link to the Kaggle page and ask users to download the source CSV themselves rather than committing it to the repo, or
• Include the CSV with a clear attribution/license notice, or
• Keep only the derived schema and load scripts (no raw data) if redistribution is the safer path.

Everything in this repo about the data (the schema design, the SQL, the Python generator) is yours to license however you'd like — that's a separate question from the data itself.

Schema

Eight tables. Every table has its own single-column GENERATED ALWAYS AS IDENTITY primary key — including the bridge tables, so every row is addressable by a simple surrogate key the way an RPG program expects.

erDiagram
  CERTIFICATE ||--o{ MOVIE : rates
  MOVIE ||--o{ MOVIE_GENRE : has
  GENRE ||--o{ MOVIE_GENRE : tags
  MOVIE ||--o{ MOVIE_CREDIT : credits
  PERSON ||--o{ MOVIE_CREDIT : appears
  ROLE_TYPE ||--o{ MOVIE_CREDIT : classifies

  CERTIFICATE {
    int certificate_id PK
    string certificate_code
  }
  GENRE {
    int genre_id PK
    string genre_name
  }
  PERSON {
    int person_id PK
    string person_name
  }
  ROLE_TYPE {
    int role_id PK
    string role_code
  }
  MOVIE {
    int movie_id PK
    string title
    int release_year
    int certificate_id FK
    int runtime_minutes
    decimal imdb_rating
    string overview
    int meta_score
    int no_of_votes
    decimal gross_usd
    string poster_url
  }
  MOVIE_GENRE {
    int movie_genre_id PK
    int movie_id FK
    int genre_id FK
  }
  MOVIE_CREDIT {
    int movie_credit_id PK
    int movie_id FK
    int person_id FK
    int role_id FK
    int billing_order
  }

Loading

(GitHub renders the diagram above automatically. If you're reading this somewhere that doesn't support Mermaid, see Design notes for the same relationships in prose.)

Design notes

• Genre is a repeating group in the source CSV — one cell holds "Crime, Drama". That's a 1NF violation on its own, so it splits into a GENRE lookup plus the MOVIE_GENRE bridge table.
• Director and Star1–Star4 are four columns holding the same kind of fact: a person tied to a movie in some role. Rather than model four separate "person" concepts, they collapse into one PERSON table, a ROLE_TYPE lookup (DIRECTOR / ACTOR), and a MOVIE_CREDIT bridge with a BILLING_ORDER column (NULL for the director, 1–4 for stars in credited order). This also correctly models someone who directs one film and stars in another — four separate star columns can't do that without duplicating the person.
• Certificate is its own lookup table rather than a free-text column on MOVIE. It's low-cardinality (16 distinct values across 1000 movies) and keeping it separate keeps MOVIE in 3NF — no attribute whose meaning depends on another non-key attribute.
• MOVIE.MOVIE_ID is a surrogate key on purpose, not title. The source data has a legitimate duplicate title: Drishyam appears twice — the 2013 original and a 2015 remake, with different directors. Title alone can't be a primary key here. It's a good real-world example of why you don't build a PK out of "natural" attributes without checking first.

IBM i naming

Every table or column name longer than 10 characters carries a short system name (FOR SYSTEM NAME on tables, FOR COLUMN on columns) alongside its long SQL name — for example MOVIE_CREDIT → system name MOVCREDT, and MOVIE.CERTIFICATE_ID → column CERTID. That keeps the tables usable from classic fixed-format RPG and DDS-described file access, not just modern free-format RPGLE and embedded SQL.

Identity columns: ALWAYS, not BY DEFAULT

All eight identity columns use GENERATED ALWAYS AS IDENTITY (NO MINVALUE NO MAXVALUE NO CYCLE NO ORDER CACHE 20). Because the identity is ALWAYS, the load scripts insert explicit ID values using OVERRIDING SYSTEM VALUE so foreign keys wire up deterministically across thousands of generated rows, and each load script finishes with ALTER TABLE ... RESTART WITH to push the sequence past the seed data — so the first ad hoc INSERT a student runs afterward, without specifying an ID, picks up cleanly where the seed data left off.

Data dictionary

CERTIFICATE — 16 rows

Column	System name	Type	Nullable	Notes
CERTIFICATE_ID	CERTID	INT (identity)	No	PK
CERTIFICATE_CODE	CERTCODE	VARCHAR(10)	No	e.g.U, PG-13, R, TV-MA

GENRE — 21 rows

Column	System name	Type	Nullable	Notes
GENRE_ID	(fits, 8 chars)	INT (identity)	No	PK
GENRE_NAME	(fits, 10 chars)	VARCHAR(20)	No	e.g.Drama, Film-Noir

PERSON — 3,202 rows

Column	System name	Type	Nullable	Notes
PERSON_ID	(fits, 9 chars)	INT (identity)	No	PK
PERSON_NAME	PERSNAME	VARCHAR(60)	No	Directors and stars share this table

ROLE_TYPE — 2 rows

Column	System name	Type	Nullable	Notes
ROLE_ID	(fits, 7 chars)	INT (identity)	No	PK
ROLE_CODE	(fits, 9 chars)	VARCHAR(10)	No	DIRECTOR or ACTOR

MOVIE — 1,000 rows

Column	System name	Type	Nullable	Notes
MOVIE_ID	(fits, 8 chars)	INT (identity)	No	PK
TITLE	(fits, 5 chars)	VARCHAR(100)	No	Not unique — see Design notes
RELEASE_YEAR	RLSYEAR	SMALLINT	No	1920–2020 in this load
CERTIFICATE_ID	CERTID	INT	Yes	FK → CERTIFICATE; ~10% blank in source
RUNTIME_MINUTES	RUNTMIN	SMALLINT	No	Parsed from"142 min" style text
IMDB_RATING	IMDBRTG	DECIMAL(3,1)	No
OVERVIEW	(fits, 8 chars)	VARCHAR(500)	No	Longest source value is 313 chars
META_SCORE	(fits, 10 chars)	SMALLINT	Yes	~16% blank in source
NO_OF_VOTES	NBRVOTES	INT	No
GROSS_USD	(fits, 9 chars)	DECIMAL(15,2)	Yes	Parsed from comma-formatted text; ~17% blank
POSTER_URL	(fits, 10 chars)	VARCHAR(300)	Yes

MOVIE_GENRE — 2,541 rows (bridge)

Column	System name	Type	Nullable	Notes
MOVIE_GENRE_ID	MOVGENID	INT (identity)	No	PK
MOVIE_ID	(fits, 8 chars)	INT	No	FK → MOVIE, cascade delete
GENRE_ID	(fits, 8 chars)	INT	No	FK → GENRE

MOVIE_CREDIT — 4,996 rows (bridge)

Column	System name	Type	Nullable	Notes
MOVIE_CREDIT_ID	CREDITID	INT (identity)	No	PK
MOVIE_ID	(fits, 8 chars)	INT	No	FK → MOVIE, cascade delete
PERSON_ID	(fits, 9 chars)	INT	No	FK → PERSON
ROLE_ID	(fits, 7 chars)	INT	No	FK → ROLE_TYPE
BILLING_ORDER	BILLORD	SMALLINT	Yes	NULL for directors, 1–4 for stars

Known data-quality fix

The source CSV has one row with shifted values: the Apollo 13 row reads Released_Year = 'PG' and Certificate = 'U'. Apollo 13 was released in 1995 and carries a PG certificate, so the load script corrects both fields during generation. Everything else in the raw file is left as genuine real-world messiness for the load script to clean up in transit — comma-formatted Gross, "142 min" style Runtime text, and blank Certificate / Meta_score / Gross values. Worth walking through with students as a data-cleansing exercise in its own right.

Getting started on PUB400

1. Sign up for a PUB400 account (or use whatever IBM i partition you have access to).
2. Upload the three SQL files from sql/ to the IFS, or open each one in ACS Run SQL Scripts directly.
3. Run them in order:

RUNSQLSTM SRCSTMF('/home/yourprofile/imdb-on-ibmi/sql/01_create_tables.sql') COMMIT(*NONE)
RUNSQLSTM SRCSTMF('/home/yourprofile/imdb-on-ibmi/sql/02_load_reference_data.sql') COMMIT(*NONE)
RUNSQLSTM SRCSTMF('/home/yourprofile/imdb-on-ibmi/sql/03_load_movie_data.sql') COMMIT(*NONE)

The scripts create and use a schema/library named IMDBIBMI. If you want a different name, change the CREATE SCHEMA / SET SCHEMA lines at the top of each file before running.

Verifying the load

SET SCHEMA IMDBIBMI;

SELECT 'CERTIFICATE' TBL, COUNT(*) ROWS FROM CERTIFICATE UNION ALL
SELECT 'GENRE', COUNT(*) FROM GENRE UNION ALL
SELECT 'PERSON', COUNT(*) FROM PERSON UNION ALL
SELECT 'ROLE_TYPE', COUNT(*) FROM ROLE_TYPE UNION ALL
SELECT 'MOVIE', COUNT(*) FROM MOVIE UNION ALL
SELECT 'MOVIE_GENRE', COUNT(*) FROM MOVIE_GENRE UNION ALL
SELECT 'MOVIE_CREDIT', COUNT(*) FROM MOVIE_CREDIT;

Expected counts: CERTIFICATE 16, GENRE 21, PERSON 3202, ROLE_TYPE 2, MOVIE 1000, MOVIE_GENRE 2541, MOVIE_CREDIT 4996.

Example queries

-- Every movie's title, certificate, and genres in one row
SELECT m.TITLE, c.CERTIFICATE_CODE,
       LISTAGG(g.GENRE_NAME, ', ') AS GENRES
FROM MOVIE m
LEFT JOIN CERTIFICATE c ON m.CERTIFICATE_ID = c.CERTIFICATE_ID
JOIN MOVIE_GENRE mg ON mg.MOVIE_ID = m.MOVIE_ID
JOIN GENRE g ON g.GENRE_ID = mg.GENRE_ID
GROUP BY m.TITLE, c.CERTIFICATE_CODE
ORDER BY m.TITLE;

-- Full cast and director for one movie, in billing order
SELECT p.PERSON_NAME, r.ROLE_CODE, mc.BILLING_ORDER
FROM MOVIE_CREDIT mc
JOIN MOVIE m ON mc.MOVIE_ID = m.MOVIE_ID
JOIN PERSON p ON mc.PERSON_ID = p.PERSON_ID
JOIN ROLE_TYPE r ON mc.ROLE_ID = r.ROLE_ID
WHERE m.TITLE = 'The Godfather'
ORDER BY r.ROLE_CODE, mc.BILLING_ORDER;

-- Actors who show up in the most top-1000 movies
SELECT p.PERSON_NAME, COUNT(*) AS APPEARANCES
FROM MOVIE_CREDIT mc
JOIN PERSON p ON mc.PERSON_ID = p.PERSON_ID
JOIN ROLE_TYPE r ON mc.ROLE_ID = r.ROLE_ID
WHERE r.ROLE_CODE = 'ACTOR'
GROUP BY p.PERSON_NAME
ORDER BY APPEARANCES DESC
FETCH FIRST 10 ROWS ONLY;

Suggested exercises

Ideas for companion RPGLE/SQLRPGLE material against this schema:

• A subfile program that lists movies by genre, with certificate and runtime displayed.
• An SQLRPGLE cursor-based CRUD program against MOVIE_CREDIT — add, reassign, or remove a credit and watch the FK constraints do their job.
• A batch RPGLE program that recomputes each director's average IMDB rating across their movies in this dataset.
• An exercise on GENERATED ALWAYS AS IDENTITY itself: insert a new movie without specifying MOVIE_ID, then trace how it gets assigned.

Repo structure

imdb-on-ibmi/
├── README.md
├── sql/
│   ├── 01_create_tables.sql
│   ├── 02_load_reference_data.sql
│   └── 03_load_movie_data.sql
├── scripts/
│   └── generate_load_scripts.py
└── data/
    └── imdb_top_1000.csv        (include only after checking the license — see above)

Regenerating the load scripts

scripts/generate_load_scripts.py reads the source CSV, applies the Apollo 13 correction and the rest of the cleanup described above, and writes 02_load_reference_data.sql and 03_load_movie_data.sql. Re-run it if the source CSV is updated or replaced:

pip install pandas
python3 scripts/generate_load_scripts.py

Contributing

Issues and pull requests are welcome — additional exercises, corrections to the schema, or notes on running this against other IBM i environments are all fair game. (Fill in your preferred contribution process here.)

License

(Add your chosen license for the schema/SQL/scripts in this section — MIT is a common choice for teaching material. Keep this separate from the data license discussed above.)

Acknowledgments

• Dataset compiled by Kaggle user harshitshankhdhar, sourced from IMDb.
• Built as part of the Honor Your Heritage. Engineer Your Future. series.