header2
bio projects contact
computer graphics
computer graphics
cvision
search
programming
design

 

_______________ C/C++__________Java__________Relational Database__________Scripting/UNIX__________Networking

RDMS: Relational Database Managment System

Term Group Project: Online Movie Rental Application
Development: SQL*Plus/Oracle 10g using PHP/Apache

Platform: Linux

Overview
Developed an online movie rental website, users are able to rent or purchase DVDs and concession items. The
application manages a movie distribution service including maintaining an inventory, user accounts, processing orders, recording and logging transactions. My responsibilities included inventory search, adding items to shopping cart, customer statistics and processing customer transactions. Developed in PHP and SQL*Plus/Oracle using Apache server.

ER Model

ER Model

Schema - Overview

Sample SQL Tables included in our schema:

CREATE TABLE Rental ( tid CHAR(8), ruid CHAR(8) );
CREATE TABLE Contains_Purchase_DVD ( tid CHAR(8), uqid CHAR(8));
CREATE TABLE Contains_Purchase_nonDVD ( tid CHAR(8), uqid CHAR(8));


CREATE TABLE DVD_Inventory (
title CHAR(60),
unique_id CHAR(8),
director CHAR(15),
year INTEGER,
genre CHAR(15),
starring1 CHAR(25),
starring2 CHAR(25),
type INTEGER,
PRIMARY KEY(unique_id),
CONSTRAINT check_year CHECK( year BETWEEN 1900 AND 2010 ) );

CREATE TABLE Account (
accountNum INTEGER,
name CHAR(20),
phoneNum CHAR(12),
creditCardNum CHAR(16),
creditExpiry CHAR(5),
cardOwner CHAR(20),
creditType CHAR(4),
PRIMARY KEY(accountNum),
UNIQUE(creditCardNum),
CONSTRAINT check_ccnum CHECK( creditCardNum > 0 ) );

CREATE TABLE User_Login (
username CHAR(20),
password CHAR(10),
PRIMARY KEY( username ));

CREATE TABLE User_hasAccount (
username CHAR(20),
accountNum INTEGER,
UNIQUE ( username, accountNum ),
PRIMARY KEY( accountNum ),
FOREIGN KEY( accountNum ) REFERENCES Account( accountNum ) ON DELETE CASCADE,
FOREIGN KEY( username ) REFERENCES User_Login( username ) ON DELETE CASCADE );

CREATE TABLE Shopping_Cart (
unique_id CHAR(8),
username CHAR(8),
FOREIGN KEY (unique_id) REFERENCES DVD_Inventory(unique_id) ON DELETE CASCADE);

Views Created

Customer statistics: view rentals by genre
CREATE VIEW NumRentedGenre AS
SELECT d.genre, count(*) AS rented
FROM dvd_inventory d, rental r
WHERE d.unique_id= r.ruid
GROUP BY d.genre;

Customer statistics: view customers' spending on snacks purchased when renting DVDs
CREATE VIEW ItemStats AS
SELECT m.type, avg(nd.price) spentAvg
FROM contains_membership_info m, non_dvd nd, performs_transaction pt, contains_purchase_nondvd p
WHERE m.accountnum = pt.accountnum AND pt.tid = p.tid AND nd.unique_id = p.uqid
GROUP BY m.type;

Sample SQL Queries

searchActorGenre.php
select d.genre, d.title, d.year, d.director, d.starring1, d.starring2
from dvd_inventory d
where d.genre = '$genre' and
( d.starring1 like '%$upper%' OR d.starring2 like '%$upper%') and not exists (select ny.uq_id from not_yet_returned_dvd ny where ny.uq_id = d.unique_id) order by d.genre asc


basicSearchDirector.php
select title, year, director, starring1, starring2, genre
from dvd_inventory
where director LIKE '%$director%' and not exists
(select ny.uq_id
from not_yet_returned_dvd ny
where ny.uq_id = unique_id) order by genre asc;


dvd.php - view rentals by popularity
select d.title, count(d.unique_id) as RentedNum
from dvd_inventory d, rentalf r
where d.unique_id = r.ruid group by d.unique_id, d.title having count(*) > 1 order by RentedNum desc

clear.php
select unique_id from shopping_Cart where username='$thisUser';
DELETE FROM SHOPPING_CART WHERE unique_id = '$row[0]' and username='$thisUser';
select unique_id from cart_nondvd where username='$thisUser';
DELETE FROM CART_NONDVD WHERE unique_id = '$row[0]' and username='$thisUser';
select unique_id from cart_dvd where username='$thisUser'
DELETE FROM CART_DVD WHERE unique_id = '$row[0]' and username='$thisUser'

orderConfirmation.php
SELECT d.title, d.director, d.year, d.genre FROM dvd_inventory d JOIN Shopping_cart sc ON
sc.unique_Id=d.unique_Id and sc.username='$thisUser' ORDER BY d.title;
SELECT nd.name, nd.price FROM non_dvd nd JOIN cart_nondvd cd ON
nd.unique_Id=cd.unique_Id and cd.username='user21' ORDER BY nd.name;

process_order.php
SELECT accountNum from user_hasAccount where username = '$thisUser';
INSERT into Transaction values ( '$transNum', to_date('$d/$m/$y', 'dd/mm/yyyy') );
INSERT into Performs_Transaction values ( '$transNum', '$thisAccount[0]' );
select unique_id from cart_nondvd where username='$thisUser';
INSERT INTO contains_purchase_nondvd values ( '$transNum', '$row[0]' );
DELETE FROM CART_NONDVD WHERE unique_id = '$row[0]' and username='$thisUser';
select unique_id from shopping_Cart where username='$thisUser';
INSERT INTO rental values ( '$transNum', '$row[0]' );
INSERT INTO Not_Yet_Returned_DVD values ( '$thisAccount[0]', '$row[0]' );
DELETE FROM SHOPPING_CART WHERE unique_id = '$row[0]' and username='$thisUser';