CMIS 320 Project: Implementing a Video Store Database Using SQL

This project focuses on the physical design and implementation of a database for a Mom and Pop Johnson Video Store using SQL Data Definition Language (DDL) and Data Manipulation Language (DML). The project involves creating Oracle database tables based on the provided entity-relationship diagram and metadata, including tables for Customer, Distributor, DVD, Fee, Movie, Order, and Video. The implementation ensures entity and referential integrity by declaring primary and foreign keys. The project includes SQL statements for creating tables, inserting data, and performing queries such as selecting customer names and account numbers, retrieving video titles rented within the last 30 days, and ordering distributors by name. Additionally, the project demonstrates updating and deleting records within the database, showcasing essential database management operations. Desklib provides access to this and many other solved assignments for students.

Entity Relationship Diagram:
Figure 1: Entity Relationship Diagram of Ace Software, Inc.
(Source: Created by Author)

1. Create Tables:
CREATE TABLE Customer
(
accountNumber NUMBER(11),
cus_name VARCHAR2(200),
street VARCHAR2(200),
city VARCHAR2(200),
zip NUMBER(6),
contactNumber NUMBER(10),
CONSTRAINT customer_pk PRIMARY KEY (accountNumber)
);
CREATE TABLE Customer_Rent
(
rentID NUMBER(11),
customer NUMBER(11),
rentDate DATE,
expectedReturnDate DATE,
actualReturnDate DATE,

totalPay NUMBER(6,2),
CONSTRAINT rent_pk PRIMARY KEY (rentID),
FOREIGN KEY (customer) REFERENCES Customer (accountNumber) ON DELETE
SET NULL
);
CREATE TABLE Fees
(
feeID NUMBER(11),
rentID NUMBER(11),
type VARCHAR2(200),
amount NUMBER(6,2),
CONSTRAINT fees_pk PRIMARY KEY (feeID),
FOREIGN KEY (rentID) REFERENCES Customer_Rent (rentID)
);
CREATE TABLE Movie
(
movieID NUMBER(11),
title VARCHAR2(200),
genre VARCHAR2(200),

length VARCHAR2(200),
releaseYear Date,
country VARCHAR2(200),
CONSTRAINT movie_pk PRIMARY KEY (movieID)
);
CREATE TABLE DVD
(
dvdID NUMBER(11),
movieID NUMBER(11),
availableCopy NUMBER(11),
CONSTRAINT dvd_pk PRIMARY KEY (dvdID),
FOREIGN KEY (movieID) REFERENCES Movie (movieID)
);
CREATE TABLE Video
(
videoID NUMBER(11),
movieID NUMBER(11),
availableCopy NUMBER(11),

CONSTRAINT video_pk PRIMARY KEY (videoID),
FOREIGN KEY (movieID) REFERENCES Movie (movieID)
);
CREATE TABLE VideoRent
(
rentID NUMBER(11),
videoID NUMBER(11),
status VARCHAR2(200),
CONSTRAINT video_rent_pk PRIMARY KEY (rentID, videoID),
FOREIGN KEY (rentID) REFERENCES Customer_Rent (rentID),
FOREIGN KEY (videoID) REFERENCES Video (videoID)
);
CREATE TABLE DVDRent
(
rentID NUMBER(11),
dvdID NUMBER(11),
status VARCHAR2(200),
CONSTRAINT dvd_rent_pk PRIMARY KEY (rentID, dvdID),

FOREIGN KEY (rentID) REFERENCES Customer_Rent (rentID),
FOREIGN KEY (dvdID) REFERENCES DVD (dvdID)
);
CREATE TABLE Distributor
(
distributorID NUMBER(11),
distributorName VARCHAR2(200),
contact NUMBER(10),
CONSTRAINT distributor_pk PRIMARY KEY (distributorID)
);
CREATE TABLE Puchase
(
distributorID NUMBER(11),
movieID NUMBER(11),
price NUMBER(10,2),
CONSTRAINT purchase_pk PRIMARY KEY (distributorID, movieID),
FOREIGN KEY (distributorID) REFERENCES Distributor (distributorID),
FOREIGN KEY (movieID) REFERENCES Movie (movieID)

);
Insert Data:
insert into Customer values (1, 'Indiana Hocking', '51 Carlisle Street', 'YOUANMITE',
3646, 0353357933);
insert into Customer values (2, 'Samantha Petersen', '49 Cubbine Road',
'BOODAROCKIN', 6423, 0890708554);
insert into Customer values (3, 'Ryan Cartledge', '41 Walter Crescent', 'BUDDEROO',
2535, 0240266100);
insert into Customer values (4, 'Beau Kane', '90 Larissa Court', 'RED CLIFFS', 3496,
0353599095);
insert into Customer values (5, 'Olivia Blakeley', '76 Boughtman Street',
'KEYSBOROUGH', 3173, 0397999310);
insert into Distributor values (1, 'A', 0758857956);
insert into Distributor values (2, 'B', 0658812959);
insert into Distributor values (3, 'C', 0438857958);
insert into Distributor values (4, 'D', 0598857935);
insert into Distributor values (5, 'E', 0238857164);
insert into Movie values (1, 'The Shawshank Redemption', 'Drama', '142 min',
TO_DATE('1994', 'yyyy'), 'USA');

insert into Movie values (2, 'The Godfather', 'Crime, Drama', '175 min',
TO_DATE('1972', 'yyyy'), 'USA');
insert into Movie values (3, 'The Dark Knight', 'Action, Crime, Drama', '152 min',
TO_DATE('2008', 'yyyy'), 'USA');
insert into Movie values (4, 'Schindlers List', 'Biography, Drama, History', '195 min',
TO_DATE('1993', 'yyyy'), 'UK');
insert into Movie values (5, 'Seven Samurai', 'Adventure, Drama', '207 min',
TO_DATE('1954', 'yyyy'), 'Japan');
insert into Video values (1, 1, 110);
insert into Video values (2, 5, 210);
insert into Video values (4, 4, 510);
insert into Video values (3, 2, 510);
insert into Video values (5, 3, 430);
insert into Customer_Rent values (1, 1, TO_DATE('2019/02/13', 'yyyy/mm/dd'),
TO_DATE('2019/02/18', 'yyyy/mm/dd'), TO_DATE('2019/02/15', 'yyyy/mm/dd'), 2.30);
insert into Customer_Rent values (2, 2, TO_DATE('2019/04/25', 'yyyy/mm/dd'),
TO_DATE('2019/04/29', 'yyyy/mm/dd'), TO_DATE('2019/04/27', 'yyyy/mm/dd'), 4.50);
insert into Customer_Rent values (3, 4, TO_DATE('2019/03/19', 'yyyy/mm/dd'),
TO_DATE('2019/03/27', 'yyyy/mm/dd'), TO_DATE('2019/03/25', 'yyyy/mm/dd'), 3.55);

insert into Customer_Rent values (4, 3, TO_DATE('2019/01/16', 'yyyy/mm/dd'),
TO_DATE('2019/01/26', 'yyyy/mm/dd'), TO_DATE('2019/01/19', 'yyyy/mm/dd'), 1.77);
insert into Customer_Rent values (5, 2, TO_DATE('2019/04/23', 'yyyy/mm/dd'),
TO_DATE('2019/04/27', 'yyyy/mm/dd'), TO_DATE('2019/04/28', 'yyyy/mm/dd'), 4.50);
insert into VideoRent values (1, 1, 'Returned');
insert into VideoRent values (2, 5, 'Returned');
insert into VideoRent values (3, 3, 'Returned');
insert into VideoRent values (4, 4, 'Returned');
insert into VideoRent values (5, 2, 'Returned');
SQL Queries:
select cus_name, accountNumber, Concat (street, zip) from customer Order By
accountNumber;
select Video.videoID, title from Customer_Rent Inner Join VideoRent ON
Customer_Rent.rentID = VideoRent.rentID
Inner Join Video ON VideoRent.videoID = Video.videoID
Inner Join Movie ON Video.movieID = Movie.movieID
Where rentDate > trunc(sysdate-30)
Order By rentDate;
select * From Distributor Order By distributorName;

Update Customer Set cus_name = 'Samantha Hobbs' where accountNumber=2;
Delete from Customer where accountNumber=3;