Online Movie Rental System

Using SQL and Oracle Database

Simple Duel Game Screenshot

Online Movie Rental System

Developers: John Julius C. David, Christopherson C. Labios, Beau Lawyjet L. Sison, Marwin P. Panganiban, Gabriel P. Llanera

Completion Date: November, 2023

This project is a comprehensive relational database system designed for "Orakulo," a conceptual online media rental store. Adapting business rules from an online bookstore, this system provides the backend logic for managing movies, customers, and orders. The project was built using Oracle Database and demonstrates a thorough understanding of data modeling, business rule implementation, and complex SQL querying to solve realistic business problems.

Project Objectives

  1. To design and implement a relational database schema in Oracle based on a set of business rules.
  2. To normalize the database structure to reduce redundancy and improve data integrity.
  3. To create and populate database tables with realistic, sample data for movies, orders, and customers.
  4. To demonstrate proficiency in SQL (DDL and DML) for creating, managing, and manipulating database objects.
  5. To solve specific business problems by writing complex, multi-table SQL queries.
  6. To fulfill the final project requirement for the Information Management (CCS0021L) course.

Technologies Used

  • Database:
    • Oracle Database
  • Language:
    • SQL (Structured Query Language)
  • Development Environment:
    • Oracle SQL Developer (or similar database management tool)

Database Structure Highlights

The system was built around a set of core tables to manage the rental process:

  1. MYORDER: Stores high-level information about each order, such as the order date and the delivery channel.
  2. ORDERITEM: A junction table that links an order (`ORDERID`) to a specific movie (`MOVIEID`) and includes details like quantity and rental fee.
  3. MOVIE: A master table holding all details for a movie, such as its title, genre, and other attributes.
  4. (Other tables for Customers, Payments, etc. would also be part of the full schema)

SQL Query Showcase

A key part of the project was developing queries to retrieve meaningful data. Below are two examples from the project:

Query 1: Orders by Delivery Channel

Goal: To display the movie titles, quantities, and rental fees for all items that were processed via 'Postal Delivery'.

  • Tables Joined: `MOVIE`, `ORDERITEM`, `MYORDER`
  • Key Logic: Uses `WHERE` clauses to join the three tables on their respective keys and filters the results where `MYORDER.DELIVERYCHANNEL = 'Postal Delivery'`.

Query 2: High-Quantity Orders

Goal: To find all movies that were rented in large quantities (greater than 2) and display the movie title, quantity, and the date the order was placed.

  • Tables Joined: `MOVIE`, `ORDERITEM`, `MYORDER`
  • Key Logic: Joins the three tables and filters the results where `ORDERITEM.QUANTITY > 2`.

Conclusion

This project successfully demonstrates the design, implementation, and querying of a relational database in an Oracle environment. The resulting database system correctly models the business rules of an online media rental store and provides the necessary SQL logic to retrieve critical business intelligence, fulfilling all requirements for the final project.