oracle final project

oracleflix.db

-- create tables --
-- create customer's table
CREATE TABLE customers(
customer_id NUMBER CONSTRAINT customer_id_pk PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
home_phone VARCHAR2(12) NOT NULL,
address VARCHAR2(100) NOT NULL,
city VARCHAR2(30) NOT NULL,
state VARCHAR2(2) NOT NULL,
email VARCHAR2(25),
cell_phone VARCHAR2(12)
);

-- create movies table
CREATE TABLE movies(
title_id NUMBER(10) CONSTRAINT title_id_pk PRIMARY KEY,
title VARCHAR2(60) NOT NULL,
description VARCHAR2(400) NOT NULL,
rating VARCHAR2(4) CONSTRAINT movies_rating CHECK (rating IN ('G', 'PG','R','PG13')),
category VARCHAR2(20) CHECK (category IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI',
'DOCUMENTARY')),
release_date date NOT NULL
);

-- create media's table
CREATE TABLE media(
media_id NUMBER(10) CONSTRAINT media_id_pk PRIMARY KEY,
format VARCHAR2(3) NOT NULL,
title_id NUMBER(10) NOT NULL CONSTRAINT media_titleid_fk REFERENCES movies(title_id)
);

-- create rental history's table
CREATE TABLE rental_history(
media_id NUMBER(10) CONSTRAINT media_id_fk REFERENCES media(media_id),
rental_date date default SYSDATE NOT NULL,
customer_id NUMBER(10) NOT NULL CONSTRAINT customer_id_fk REFERENCES
customers(customer_id),
return_date date,
CONSTRAINT rental_history_pk PRIMARY KEY (media_id, rental_date)
);

-- create actors's table
CREATE TABLE actors
(actor_id NUMBER(10) CONSTRAINT actor_id_pk PRIMARY KEY,
stage_name VARCHAR2(40) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
birth_date date NOT NULL);

-- create star billings's table
CREATE TABLE star_billings
(actor_id NUMBER(10) CONSTRAINT actor_id_fk REFERENCES actors(actor_id),
title_id NUMBER(10) CONSTRAINT title_id_fk REFERENCES movies(title_id),
comments VARCHAR2(40),
CONSTRAINT star_billings_pk PRIMARY KEY (actor_id, title_id));

-- sequences (for auto_increment) --
-- for media
CREATE SEQUENCE media_ai INCREMENT BY 1 START WITH 92 MAXVALUE 50000 NOCACHE
NOCYCLE;

-- for movies
CREATE SEQUENCE title_ai INCREMENT BY 1 START WITH 1 MAXVALUE 50000 NOCACHE
NOCYCLE;

-- for customers
CREATE SEQUENCE customer_ai INCREMENT BY 1 START WITH 101 MAXVALUE 50000
NOCACHE NOCYCLE;

-- for actors
CREATE SEQUENCE actor_ai INCREMENT BY 1 START WITH 1001 MAXVALUE 50000 NOCACHE
NOCYCLE;

-- show the tables --
describe customers;
describe movies;
describe medias;
describe rental_history;
describe actors;
describe star_billings;


-- data insert
-- customers
INSERT INTO customers (customer_id, last_name, first_name, home_phone, address, city, state, email, cell_phone) 
VALUES (customer_ai.NEXTVAL, 'Baxter', 'Dylan','078-7999-1950','16 Thompsons Lane','MENIE HO.','Aberdeen','[email protected]','070-4319-0108');