Cs457b Final Project1

CS457B FINAL PROJECT VIDEO STORE Pranav Patel (ID-6798) Haresh Patel (ID-6746) Dhvanil Gamit (ID-6902)

ERD in crow’s foot notation CUSTOME R CUST_ID (PK) CUST_FNAM E CUST_LNAM E CUST_ADD CUST_PHON E

RENTAL CUST_ID DVD_ID HISTORY CATEGORY

PAYMENT RECEIPT_ID CUST_ID (FK) METHOD AMOUNT

DVD DVD_ID DVD_TITLE DVD_CAT ACTOR ACTRESS

Entity To Table mapping diagrams  Custome r

CUST_FNAM E

CUST_ID (PK) CUSTOME R

CUST_PHON E

CUST_LNAM E

 TotalRen t

CUST_ADD

DVD_ID (FK)

CUST_ID (FK) TotalRen t HISTORY

CATEGORY

Entity To Table mapping diagrams  DVD

DVD_ID (PK)

DVD_TITLE

DVD_CAT

DVD ACTOR

 Payment

ACTRESS

RECEIPT_ID

CUST_ID (FK)

PAYMENT METHOD

AMOUNT

TABLES

CUSTOMER Tables 

CREATE TABLE Customer (cust_ID NUMBER(3),cust_fname VARCHAR(10),cust_lname VARCHAR(10),cust_add CHAR(50), cust_phone NUMBER(10), CONSTRAINT Customer_pk PRIMARY KEY (cust_ID)); CUST_ID 100

CUST_FNA CUST_LNA CUST_AD ME ME D Alok Bhatt Milpitas

101

Mitul

Patel

Milpitas

102

Trilok

Purohit

Milpitas

CUST_PH ONE 408123134 4 408341546 6 408341553 3

DVD Table 

Create table DVD ( dvd_ID NUMBER(4), dvd_title VARCHAR(10), dvd_cat VARCHAR(10), Actor CHAR(10), Actress CHAR(10), CONSTRAINT DVD_pk PRIMARY KEY (dvd_ID));

DVD_ID

DVD_CAT

ACTOR

ACTRESS

1001

DVD_TITL E Allmighty

Comedy

Aniston

1002

Love Guru

Comedy

1003

Oceans 11

Drama

Jim Carrey Mike Myers Brad Pitt

1004

Bond007

Action

DanielCrag Eva Green

1005

OutOfSight Thriller

Clooney

1006

AGangster

Thriller

Rusel Crow JoshBrolin

1007

Hulk

Action

ENorton

Liv Tyler

1008

Titanic

Drama

LDecaprio

KWinslet

Alba Julia

JLopez

TOTAL RENT Table 

Create table TotalRent ( CUST_ID NUMBER(3), DVD_ID NUMBER(4), History VARCHAR(10), Category CHAR(10), CONSTRAINT TotalRent_fk1 FOREIGN KEY (CUST_ID) REFERENCES Customer(CUST_ID), CONSTRAINT TotalRent_fk2 FOREIGN KEY (DVD_ID) REFERENCES CUST_ID DVD_IDDVD(DVD_ID)); HISTORY CATEGORY 101

1001

Not Late

Comedy

101

1003

Not Late

Drama

101

1004

Not Late

Drama

102

1004

Not Late

Drama

102

1002

Not Late

Comedy

102

1008

Not Late

Action

102

1007

Late

Action

102

1006

Late

Thriller

100

1001

Late

Comedy

100

1006

Not Late

Thriller

PAYMENT Table 

Create table Payment ( Receipt_ID NUMBER(4),CUST_ID NUMBER(3),Method VARCHAR(10), Amount NUMBER(3), CONSTRAINT PAYMENT_pk PRIMARY KEY (Receipt_ID),CONSTRAINT PAYMENT_fk1 FOREIGN KEY (CUST_ID) REFERENCES Customer(CUST_ID)); RECEIPT_I D 9001

100

CARD

AMOUNT ($) 20

9002

101

CASH

30

9003

102

CARD

50

CUST_ID

METHOD

QUERRIES

How many movies a customer ever rented? SELECT count(*) FROM TotalRent where CUST_ID=101;

COUNT(*) 3

How many times a customer returned a movie late? SELECT count(*) FROM TotalRent where HISTORY='Late' and CUST_ID=101; COUNT(*) 0

Which category movie a customer rented out the most times? SELECT CUST_ID,COUNT(CATEGORY),CATEGORY from TotalRent WHERE (TotalRent.CUST_ID=102) group by CUST_ID,CATEGORY;

102

COUNT(CATEG ORY) 1

102

1

Drama

102

2

Action

102

1

Thriller

CUST_ID

CATEGORY Comedy

Thank You