🎬 Movie Rental System

A Dockerized Microsoft SQL Server database for a classic movie rental domain—customers, movies, inventory, rentals, and payments—with seed data, views, procs, and triggers.

🗺️ ER Diagram (Mermaid)

erDiagram
  CUSTOMERS ||--o{ RENTALS : makes
  STAFF ||--o{ RENTALS : handles
  STORES ||--o{ STAFF : employs
  STORES ||--o{ INVENTORY : stocks
  MOVIES ||--o{ INVENTORY : listed_as
  MOVIES ||--o{ MOVIEGENRES : labeled
  GENRES ||--o{ MOVIEGENRES : categorizes
  INVENTORY ||--o{ RENTALS : fulfills
  RENTALS ||--o{ PAYMENTS : billed

  CUSTOMERS { int CustomerID PK string FirstName string LastName string Email string Phone string Status }
  MOVIES { int MovieID PK string Title int ReleaseYear string Rating decimal RentalRate decimal ReplacementCost }
  GENRES { int GenreID PK string Name }
  MOVIEGENRES { int MovieID FK int GenreID FK }
  STORES { int StoreID PK string Name string AddressLine1 string City string State }
  STAFF { int StaffID PK int StoreID FK string FirstName string LastName string Email bool Active }
  INVENTORY { int InventoryID PK int MovieID FK int StoreID FK int CopiesTotal int CopiesAvailable }
  RENTALS { int RentalID PK int InventoryID FK int CustomerID FK int StaffID FK datetime RentedAt datetime DueAt datetime ReturnedAt string Status }
  PAYMENTS { int PaymentID PK int RentalID FK decimal Amount datetime PaidAt string Method }

Loading

🚀 Quick start

1. Create .env with SA_PASSWORD
2. docker compose -f docker/docker-compose.yml up -d
3. Apply scripts: sqlcmd -i scripts/apply.sql via container (see scripts section)
4. Try RentMovie / ReturnMovie to validate logic.

📂 Structure

• docker/ compose & runtime
• sql/ DDL, views, procs, triggers, seed
• scripts/ orchestration .sql
• data/ CSVs (future)

🔧 Scripts

Use scripts/apply.sql to apply in correct order.

---

Nightly Reports

Two CSVs are generated by the Nightly DB reports workflow and attached as run artifacts:

• overdue_rentals.csv (from dbo.vwOverdueRentals)
• low_inventory.csv (from dbo.vwLowInventory)

Trigger & download via CLI

gh workflow run "Nightly DB reports"
RUN_ID=$(gh run list --workflow "Nightly DB reports" --limit 1 --json databaseId -q '.[0].databaseId')
gh run watch "$RUN_ID" --interval 5

Download “reports” artifact into ./reports

gh run download "$RUN_ID" -n reports -D .
ls -la reports

---

📝 License

MIT — see LICENSE.