MDB302 Sakila para Oracle
From Wiki-UX.info
Contents
Resumen
El siguiente articulo detalla la implementación de la base de datos de ejemplo Sakila para MySQL en el SADB Oracle.
Sakila Original
La base de datos Sakila para Oracle brinda 599 clientes, 1000 películas, 200 actores, 5462 relaciones entre actores y películas y un total de 16044 alquileres.
Los rangos de los alquileres son entre el 27 de marzo de 2005 y y 14 de febrero del 2006.
Sakila BIG
La base de datos Sakila BIG para Oracle utiliza el mismo diseño pero brinda in volume de información mucho más alto.
Se mantiene el mismo inventario de 1000 películas, 200 actores, 5462 relaciones entre actores y películas.
El catálogo de clientes ha sido repoblado con información de 500 mil costarricenses.
La información de alquileres contiene un total de 241560 alquileres a los que complementan 197984 operaciones de pago.
Cómo poblar RENTAL y PAYMENT con 241 mil transacciones
-- Delete payments and rentals
DELETE FROM PAYMENT;
DELETE FROM RENTAL;
-- Create a function to general random rentals based on CUSTOMER and INVENTORY Primary Key limits
CREATE OR REPLACE TYPE rental_record AS OBJECT (
RENTAL_DATE DATE,
INVENTORY_ID INTEGER,
CUSTOMER_ID INTEGER,
RETURN_DATE DATE,
STAFF_ID INTEGER
);
CREATE OR REPLACE TYPE rental_record_set AS TABLE OF rental_record;
CREATE OR REPLACE FUNCTION random_rent(
rental_date IN VARCHAR2
) RETURN rental_record_set AS v_rental_record_set rental_record_set;
BEGIN
-- ADD Rental
SELECT rental_record(RENTAL_DATE, INVENTORY_ID, CUSTOMER_ID, RETURN_DATE, STAFF_ID)
BULK COLLECT INTO v_rental_record_set
FROM
(
SELECT
TO_DATE(rental_date,'YYYY-MM-DD HH24:MI:SS') AS RENTAL_DATE
, I.INVENTORY_ID
, ROUND(DBMS_RANDOM.VALUE(0.5,500000.4999999999),0) AS CUSTOMER_ID
, TO_DATE(rental_date,'YYYY-MM-DD HH24:MI:SS') + ROUND(DBMS_RANDOM.VALUE(1.5,F.RENTAL_DURATION+3.4999999999),0) AS RETURN_DATE
-- Random Staff ID (1-2)
, ROUND(DBMS_RANDOM.VALUE(0.5,2.4999999999),0) AS STAFF_ID
FROM INVENTORY I
JOIN FILM F
ON I.FILM_ID = F.FILM_ID
-- Random movie id
WHERE I.INVENTORY_ID = ROUND(DBMS_RANDOM.VALUE(0.5,6000.4999999999),0)
);
RETURN v_rental_record_set;
END;
-- Test random_rent
select * from TABLE(random_rent('2016-02-01 12:00:00'));
DECLARE
DATE_STRING CHAR(19);
RENTAIL_ID INTEGER := 1;
RENTAL_DATE DATE;
INVENTORY_ID INTEGER;
CUSTOMER_ID INTEGER;
RETURN_DATE DATE;
STAFF_ID INTEGER;
TYPE rented IS TABLE OF DATE INDEX BY BINARY_INTEGER;
inventory_rented rented;
CURSOR pop_dates IS
SELECT DSTRING FROM (
SELECT TMP_DAY.ID AS DID, TMP_MINUTE.ID AS MID, DAY || ' ' || MINUTE AS DSTRING
FROM TMP_DAY, TMP_MINUTE
-- WHERE TMP_DAY.ID < 11 -- 306 = 10-31-2016
ORDER BY TMP_DAY.ID ASC, TMP_MINUTE.ID ASC
);
CURSOR rental_info IS
SELECT RENTAL_DATE, INVENTORY_ID, CUSTOMER_ID, RETURN_DATE, STAFF_ID
FROM TABLE(random_rent(DATE_STRING));
BEGIN
OPEN pop_dates;
LOOP
FETCH pop_dates INTO DATE_STRING;
EXIT WHEN pop_dates%NOTFOUND;
OPEN rental_info;
LOOP
FETCH rental_info INTO RENTAL_DATE, INVENTORY_ID, CUSTOMER_ID, RETURN_DATE, STAFF_ID;
EXIT WHEN rental_info%NOTFOUND;
END LOOP;
CLOSE rental_info;
-- Check if random INVENTORY_ID is currently rented
IF inventory_rented.EXISTS(INVENTORY_ID) THEN
-- Check if random RETURN_DATE is higher that current date
-- Retry if true, delete if false
IF inventory_rented(INVENTORY_ID) > TO_DATE(DATE_STRING, 'YYYY-MM-DD HH24:MI:SS') THEN
OPEN rental_info;
LOOP
FETCH rental_info INTO RENTAL_DATE, INVENTORY_ID, CUSTOMER_ID, RETURN_DATE, STAFF_ID;
EXIT WHEN rental_info%NOTFOUND;
END LOOP;
CLOSE rental_info;
ELSE
inventory_rented.DELETE(INVENTORY_ID);
END IF;
ELSE
inventory_rented(INVENTORY_ID) := RETURN_DATE;
END IF;
-- Add rental info
INSERT INTO RENTAL (RENTAL_ID, RENTAL_DATE, INVENTORY_ID, CUSTOMER_ID, RETURN_DATE, STAFF_ID)
VALUES (RENTAIL_ID, RENTAL_DATE, INVENTORY_ID, CUSTOMER_ID, RETURN_DATE, STAFF_ID);
-- Next RENTAL_ID
RENTAIL_ID := RENTAIL_ID + 1;
END LOOP;
CLOSE pop_dates;
END;
SELECT INVENTORY_ID, COUNT(RENTAL_ID)
FROM RENTAL
GROUP BY INVENTORY_ID
ORDER BY 1 DESC;
-- Update Rental when return date HIGHER than last date
-- 97
SELECT * FROM RENTAL
WHERE RETURN_DATE > TO_DATE('2016-12-31 21:00:00','YYYY-MM-DD HH24:MI:SS');
UPDATE RENTAL SET RETURN_DATE = NULL
WHERE RETURN_DATE > TO_DATE('2016-10-31 21:00:00','YYYY-MM-DD HH24:MI:SS');
-- Populate payments based on rental information
INSERT INTO PAYMENT (PAYMENT_ID,CUSTOMER_ID,STAFF_ID,RENTAL_ID,AMOUNT,PAYMENT_DATE)
SELECT
ROWNUM AS PAYMENT_ID
, R.CUSTOMER_ID
, R.STAFF_ID
, R.RENTAL_ID
, CASE
WHEN (R.RETURN_DATE - R.RENTAL_DATE) <= F.RENTAL_DURATION
THEN F.RENTAL_RATE
ELSE
F.RENTAL_RATE + (((R.RETURN_DATE - R.RENTAL_DATE) - RENTAL_DURATION) * 500)
END AS AMOUNT
--, RENTAL_RATE
, R.RETURN_DATE AS PAYMENT_DATE
FROM RENTAL R
LEFT JOIN INVENTORY I
ON R.INVENTORY_ID = I.INVENTORY_ID
LEFT JOIN FILM F
ON I.FILM_ID = F.FILM_ID
WHERE RETURN_DATE IS NOT NULL;
Diagrama Relacional
Código SQL DML
CREATE TABLE ACTOR (
ACTOR_ID number(38,0) NOT NULL,
FIRST_NAME varchar2(45) NOT NULL,
LAST_NAMES varchar2(45) ,
LAST_UPDATE date DEFAULT sysdate
);
CREATE INDEX PK_ACTOR_ID ON ACTOR ( ACTOR_ID );
CREATE TABLE CATEGORY (
CATEGORY_ID number(38,0) NOT NULL,
NAME varchar2(25) NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp
);
CREATE INDEX PK_CATEGORY_ID ON CATEGORY ( CATEGORY_ID );
CREATE TABLE COUNTRY (
COUNTRY_ID number(38,0) NOT NULL,
COUNTRY varchar2(50) NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp NOT NULL
);
CREATE INDEX PK_COUNTRY_ID ON COUNTRY ( COUNTRY_ID );
CREATE TABLE LANGUAGE (
LANGUAGE_ID number(38,0) NOT NULL,
NAME varchar2(20) ,
LAST_UPDATE timestamp DEFAULT systimestamp
);
CREATE INDEX PK_LANGUAGE_ID ON LANGUAGE ( LANGUAGE_ID );
CREATE TABLE CITY (
CITY_ID number(38,0) NOT NULL,
CITY varchar2(50) NOT NULL,
COUNTRY_ID number(38,0) NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp NOT NULL
);
CREATE INDEX PK_CITY_ID ON CITY ( CITY_ID );
CREATE INDEX IDX_FK_COUNTRY_ID ON CITY ( COUNTRY_ID );
CREATE TABLE FILM (
FILM_ID number(38,0) NOT NULL,
TITLE varchar2(255) NOT NULL,
DESCRIPTION varchar2(4000) ,
RELEASE_YEAR number(38,0) ,
LANGUAGE_ID number(38,0) NOT NULL,
ORIGINAL_LANGUAGE_ID number(38,0) ,
RENTAL_DURATION number(38,0) DEFAULT 3 NOT NULL,
RENTAL_RATE number(8,2) DEFAULT 1000 NOT NULL,
LENGTH number(38,0) ,
REPLACEMENT_COST number(8,2) DEFAULT 12000 NOT NULL,
RATING char(5) DEFAULT 'G' NOT NULL,
SPECIAL_FEATURES varchar2(100) ,
LAST_UPDATE timestamp DEFAULT systimestamp NOT NULL,
CONSTRAINT PK_FILM PRIMARY KEY ( FILM_ID )
);
ALTER TABLE FILM ADD CONSTRAINT CK_FILM_RATING CHECK ( RATING IN ('G','PG','PG-13','R','NC-17') );
CREATE INDEX IDX_TITLE ON FILM ( TITLE );
CREATE INDEX IDX_FK_LANGUAGE_ID ON FILM ( LANGUAGE_ID );
CREATE INDEX IDX_ORIGINAL_LANGUAGE_ID ON FILM ( ORIGINAL_LANGUAGE_ID );
CREATE TABLE FILM_ACTOR (
ACTOR_ID number(38,0) NOT NULL,
FILM_ID number(38,0) NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp ,
CONSTRAINT PK_FILM_ACTOR PRIMARY KEY ( ACTOR_ID, FILM_ID )
);
CREATE INDEX IDX_FILM_ACTOR ON FILM_ACTOR ( FILM_ID );
CREATE INDEX IDX_FILM_ACTOR_0 ON FILM_ACTOR ( ACTOR_ID );
CREATE TABLE FILM_CATEGORY (
FILM_ID number(38,0) NOT NULL,
CATEGORY_ID number(38,0) NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp ,
CONSTRAINT PK_FILM_CATEGORY PRIMARY KEY ( FILM_ID, CATEGORY_ID )
);
CREATE INDEX IDX_FILM_CATEGORY ON FILM_CATEGORY ( CATEGORY_ID );
CREATE INDEX IDX_FILM_CATEGORY_0 ON FILM_CATEGORY ( FILM_ID );
CREATE TABLE FILM_TEXT (
FILM_ID number(38,0) NOT NULL,
TITLE varchar2(255) NOT NULL,
DESCRIPTION varchar2(4000) ,
CONSTRAINT PK_FILM_TEXT PRIMARY KEY ( FILM_ID )
);
CREATE TABLE ADDRESS (
ADDRESS_ID number(38,0) NOT NULL,
ADDRESS varchar2(50) NOT NULL,
ADDRESS2 varchar2(50) DEFAULT NULL ,
DISTRICT varchar2(20) ,
CITY_ID number(38,0) NOT NULL,
POSTAL_CODE varchar2(10) DEFAULT NULL ,
PHONE varchar2(20) ,
LAST_UPDATE timestamp DEFAULT systimestamp
);
CREATE INDEX PK_ADDRESS_ID ON ADDRESS ( ADDRESS_ID );
CREATE INDEX IDX_FK_CITY_ID ON ADDRESS ( CITY_ID );
CREATE TABLE CUSTOMER (
CUSTOMER_ID number(38,0) NOT NULL,
STORE_ID number(38,0) NOT NULL,
FIRST_NAME varchar2(45) NOT NULL,
LAST_NAMES varchar2(45) NOT NULL,
EMAIL varchar2(50) ,
ADDRESS_ID number(38,0) ,
ACTIVE char(1) NOT NULL,
CREATE_DATE date NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp
);
ALTER TABLE CUSTOMER ADD CONSTRAINT CK_CUSTOMER_ACTIVE CHECK ( ACTIVE IN ('0','1') );
CREATE INDEX PK_CUSTOMER_ID ON CUSTOMER ( CUSTOMER_ID );
CREATE INDEX IDX_LAST_NAMES ON CUSTOMER ( LAST_NAMES );
CREATE INDEX IDX_FK_CUSTOMER_ADDRESS_ID ON CUSTOMER ( ADDRESS_ID );
CREATE TABLE INVENTORY (
INVENTORY_ID number(38,0) NOT NULL,
FILM_ID number(38,0) NOT NULL,
STORE_ID number(38,0) NOT NULL,
LAST_UPDATE timestamp DEFAULT sysdate ,
CONSTRAINT PK_INVENTORY PRIMARY KEY ( INVENTORY_ID )
);
CREATE INDEX IDX_STORE_ID_FILM_ID ON INVENTORY ( STORE_ID, FILM_ID );
CREATE INDEX IDX_INVENTORY ON INVENTORY ( STORE_ID );
CREATE TABLE PAYMENT (
PAYMENT_ID number(38,0) NOT NULL,
CUSTOMER_ID number(38,0) NOT NULL,
STAFF_ID number(38,0) NOT NULL,
RENTAL_ID number(38,0) ,
AMOUNT number(8,2) NOT NULL,
PAYMENT_DATE date NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp ,
CONSTRAINT PK_PAYMENT PRIMARY KEY ( PAYMENT_ID )
);
CREATE INDEX IDX_FK_STAFF_ID ON PAYMENT ( STAFF_ID );
CREATE INDEX IDX_FK_CUSTOMER_ID ON PAYMENT ( CUSTOMER_ID );
CREATE TABLE RENTAL (
RENTAL_ID number(38,0) NOT NULL,
RENTAL_DATE date NOT NULL,
INVENTORY_ID number(38,0) NOT NULL,
CUSTOMER_ID number(38,0) NOT NULL,
RETURN_DATE date ,
STAFF_ID number(38,0) NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp NOT NULL,
CONSTRAINT IDX_UNIQUE_RENTAL UNIQUE ( RENTAL_DATE, INVENTORY_ID, CUSTOMER_ID ) ,
CONSTRAINT PK_RENTAL PRIMARY KEY ( RENTAL_ID )
);
CREATE INDEX IDX_FK_RENTAL_INVENTORY_ID ON RENTAL ( INVENTORY_ID );
CREATE INDEX IDX_FK_RENTAL_CUSTOMER_ID ON RENTAL ( CUSTOMER_ID );
CREATE INDEX IDX_FK_RENTAL_STAFF_ID ON RENTAL ( STAFF_ID );
CREATE TABLE STAFF (
STAFF_ID number(38,0) NOT NULL,
FIRST_NAME varchar2(45) NOT NULL,
LAST_NAMES varchar2(45) NOT NULL,
ADDRESS_ID number(38,0) NOT NULL,
PICTURE blob ,
EMAIL varchar2(50) ,
STORE_ID number(38,0) NOT NULL,
ACTIVE char(1) DEFAULT '1' NOT NULL,
USERNAME varchar2(16) NOT NULL,
PASSWORD varchar2(40) ,
LAST_UPDATE timestamp DEFAULT systimestamp ,
CONSTRAINT PK_STAFF PRIMARY KEY ( STAFF_ID )
);
ALTER TABLE STAFF ADD CONSTRAINT CK_STAFF_ACTIVE CHECK ( ACTIVE IN ('0','1') );
CREATE INDEX IDX_FK_STORE_ID ON STAFF ( STORE_ID );
CREATE INDEX IDX_FK_ADDRESS_ID ON STAFF ( ADDRESS_ID );
CREATE TABLE STORE (
STORE_ID number(38,0) NOT NULL,
MANAGER_STAFF_ID number(38,0) NOT NULL,
ADDRESS_ID number(38,0) NOT NULL,
LAST_UPDATE timestamp DEFAULT systimestamp ,
CONSTRAINT PK_STORE PRIMARY KEY ( STORE_ID ) ,
CONSTRAINT IDX_UNIQUE_MANAGER UNIQUE ( MANAGER_STAFF_ID )
);
CREATE INDEX IDX_FK_STORE_ADDRESS_ID ON STORE ( ADDRESS_ID );
CREATE VIEW ACTOR_INFO AS SELECT
A.ACTOR_ID
, FIRST_NAME
, LAST_NAMES
, LISTAGG(A.NAME || ': ' || B.TITLES, '; ') WITHIN GROUP (ORDER BY A.NAME) AS FILM_INFO
FROM (
SELECT
A.ACTOR_ID
, A.FIRST_NAME
, A.LAST_NAMES
, C.CATEGORY_ID
, C.NAME
FROM ACTOR A
LEFT JOIN FILM_ACTOR FA
ON A.ACTOR_ID = FA.ACTOR_ID
LEFT JOIN FILM_CATEGORY FC
ON FA.FILM_ID = FC.FILM_ID
LEFT JOIN CATEGORY C
ON FC.CATEGORY_ID = C.CATEGORY_ID
GROUP BY A.ACTOR_ID, A.FIRST_NAME, A.LAST_NAMES, C.CATEGORY_ID, C.NAME
) A
JOIN (
SELECT
A.ACTOR_ID
, FC.CATEGORY_ID
, LISTAGG(F.TITLE,', ') WITHIN GROUP (ORDER BY F.TITLE) AS TITLES
FROM FILM F
INNER JOIN FILM_CATEGORY FC
ON F.FILM_ID = FC.FILM_ID
INNER JOIN FILM_ACTOR FA
ON F.FILM_ID = FA.FILM_ID
INNER JOIN ACTOR A
ON FA.ACTOR_ID = A.ACTOR_ID
GROUP BY A.ACTOR_ID, FC.CATEGORY_ID
) B ON A.ACTOR_ID = B.ACTOR_ID AND A.CATEGORY_ID = B.CATEGORY_ID
GROUP BY A.ACTOR_ID, FIRST_NAME, LAST_NAMES, A.NAME;
CREATE VIEW CUSTOMER_LIST AS SELECT
CU.CUSTOMER_ID AS ID
, CU.FIRST_NAME || ' ' || CU.LAST_NAMES AS NAME
, A.ADDRESS AS ADDRESS
, A.POSTAL_CODE AS ZIP_CODE
, A.PHONE AS PHONE
, CITY.CITY AS CITY
, COUNTRY.COUNTRY AS COUNTRY
, CASE
WHEN ACTIVE = '1' THEN 'ACTIVE'
ELSE ''
END AS NOTES
, CU.STORE_ID AS SID
FROM CUSTOMER CU
JOIN ADDRESS A
ON CU.ADDRESS_ID = A.ADDRESS_ID
JOIN CITY ON A.CITY_ID = CITY.CITY_ID
JOIN COUNTRY ON CITY.COUNTRY_ID = COUNTRY.COUNTRY_ID;
CREATE VIEW FILM_LIST AS SELECT
FILM.FILM_ID AS FID
, FILM.TITLE AS TITLE
, FILM.DESCRIPTION AS DESCRIPTION
, CATEGORY.NAME AS CATEGORY
, FILM.RENTAL_RATE AS PRICE
, FILM.LENGTH AS LENGTH
, FILM.RATING AS RATING
, LISTAGG(ACTOR.FIRST_NAME || ' ' || ACTOR.LAST_NAMES,',') WITHIN GROUP (ORDER BY ACTOR.LAST_NAMES) AS ACTORS
FROM CATEGORY
LEFT JOIN FILM_CATEGORY
ON CATEGORY.CATEGORY_ID = FILM_CATEGORY.CATEGORY_ID
LEFT JOIN FILM
ON FILM_CATEGORY.FILM_ID = FILM.FILM_ID
JOIN FILM_ACTOR
ON FILM.FILM_ID = FILM_ACTOR.FILM_ID
JOIN ACTOR
ON FILM_ACTOR.ACTOR_ID = ACTOR.ACTOR_ID
GROUP BY FILM.FILM_ID, FILM.TITLE, FILM.DESCRIPTION
, FILM.RENTAL_RATE, FILM.LENGTH, FILM.RATING, CATEGORY.NAME;
CREATE VIEW SALES_BY_FILM_CATEGORY AS SELECT
C.NAME AS CATEGORY
, SUM(P.AMOUNT) AS TOTAL_SALES
FROM PAYMENT P
INNER JOIN RENTAL R ON P.RENTAL_ID = R.RENTAL_ID
INNER JOIN INVENTORY I ON R.INVENTORY_ID = I.INVENTORY_ID
INNER JOIN FILM F ON I.FILM_ID = F.FILM_ID
INNER JOIN FILM_CATEGORY FC ON F.FILM_ID = FC.FILM_ID
INNER JOIN CATEGORY C ON FC.CATEGORY_ID = C.CATEGORY_ID
GROUP BY C.NAME
ORDER BY TOTAL_SALES DESC;
CREATE VIEW SALES_BY_STORE AS SELECT
S.STORE_ID
, C.CITY || ',' || CY.COUNTRY AS STORE
, M.FIRST_NAME || ' ' || M.LAST_NAMES AS MANAGER_NAME
, SUM(P.AMOUNT) AS TOTAL_SALES
FROM PAYMENT P
JOIN RENTAL R ON P.RENTAL_ID = R.RENTAL_ID
JOIN INVENTORY I ON R.INVENTORY_ID = I.INVENTORY_ID
JOIN STORE S ON I.STORE_ID = S.STORE_ID
JOIN ADDRESS A ON S.ADDRESS_ID = A.ADDRESS_ID
JOIN CITY C ON A.CITY_ID = C.CITY_ID
JOIN COUNTRY CY ON C.COUNTRY_ID = CY.COUNTRY_ID
JOIN STAFF M ON S.MANAGER_STAFF_ID = M.STAFF_ID
GROUP BY S.STORE_ID, C.CITY, CY.COUNTRY, M.FIRST_NAME, M.LAST_NAMES
ORDER BY CY.COUNTRY, C.CITY;
CREATE VIEW STAFF_LIST AS SELECT
S.STAFF_ID AS ID
, S.FIRST_NAME || ' ' || S.LAST_NAMES AS NAME
, A.ADDRESS AS ADDRESS
, A.POSTAL_CODE AS ZIP_CODE
, A.PHONE AS PHONE
, CITY.CITY AS CITY
, COUNTRY.COUNTRY AS COUNTRY
, S.STORE_ID AS SID
FROM STAFF S
JOIN ADDRESS A
ON S.ADDRESS_ID = A.ADDRESS_ID
JOIN CITY ON A.CITY_ID = CITY.CITY_ID
JOIN COUNTRY ON CITY.COUNTRY_ID = COUNTRY.COUNTRY_ID;
ALTER TABLE ADDRESS ADD CONSTRAINT FK_ADDRESS_CITY FOREIGN KEY ( CITY_ID ) REFERENCES CITY( CITY_ID );
ALTER TABLE CITY ADD CONSTRAINT FK_CITY_COUNTRY FOREIGN KEY ( COUNTRY_ID ) REFERENCES COUNTRY( COUNTRY_ID );
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUSTOMER_ADDRESS FOREIGN KEY ( ADDRESS_ID ) REFERENCES ADDRESS( ADDRESS_ID );
ALTER TABLE FILM ADD CONSTRAINT FK_FILM_LANGUAGE FOREIGN KEY ( LANGUAGE_ID ) REFERENCES LANGUAGE( LANGUAGE_ID );
ALTER TABLE FILM ADD CONSTRAINT FK_FILM_LANGUAGE_ORIGINAL FOREIGN KEY ( ORIGINAL_LANGUAGE_ID ) REFERENCES LANGUAGE( LANGUAGE_ID );
ALTER TABLE FILM_ACTOR ADD CONSTRAINT FK_FILM_ACTOR_FILM FOREIGN KEY ( FILM_ID ) REFERENCES FILM( FILM_ID );
ALTER TABLE FILM_ACTOR ADD CONSTRAINT FK_FILM_ACTOR_ID FOREIGN KEY ( ACTOR_ID ) REFERENCES ACTOR( ACTOR_ID );
ALTER TABLE FILM_CATEGORY ADD CONSTRAINT FK_FILM_CATEGORY_CATEGORY FOREIGN KEY ( CATEGORY_ID ) REFERENCES CATEGORY( CATEGORY_ID );
ALTER TABLE FILM_CATEGORY ADD CONSTRAINT FK_FILM_CATEGORY_FILM FOREIGN KEY ( FILM_ID ) REFERENCES FILM( FILM_ID );
ALTER TABLE FILM_TEXT ADD CONSTRAINT FK_FILM_TEXT_FILM_ID FOREIGN KEY ( FILM_ID ) REFERENCES FILM( FILM_ID );
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_FILM FOREIGN KEY ( FILM_ID ) REFERENCES FILM( FILM_ID );
ALTER TABLE INVENTORY ADD CONSTRAINT FK_INVENTORY_STORE FOREIGN KEY ( STORE_ID ) REFERENCES STORE( STORE_ID );
ALTER TABLE PAYMENT ADD CONSTRAINT FK_PAYMENT_CUSTOMER FOREIGN KEY ( CUSTOMER_ID ) REFERENCES CUSTOMER( CUSTOMER_ID );
ALTER TABLE PAYMENT ADD CONSTRAINT FK_PAYMENT_STAFF FOREIGN KEY ( STAFF_ID ) REFERENCES STAFF( STAFF_ID );
ALTER TABLE RENTAL ADD CONSTRAINT FK_RENTAL_STAFF FOREIGN KEY ( STAFF_ID ) REFERENCES STAFF( STAFF_ID );
ALTER TABLE RENTAL ADD CONSTRAINT FK_RENTAL_INVENTORY FOREIGN KEY ( INVENTORY_ID ) REFERENCES INVENTORY( INVENTORY_ID );
ALTER TABLE RENTAL ADD CONSTRAINT FK_RENTAL_CUSTOMER FOREIGN KEY ( CUSTOMER_ID ) REFERENCES CUSTOMER( CUSTOMER_ID );
ALTER TABLE STAFF ADD CONSTRAINT FK_STAFF_ADDRESS FOREIGN KEY ( ADDRESS_ID ) REFERENCES ADDRESS( ADDRESS_ID );
ALTER TABLE STAFF ADD CONSTRAINT FK_STAFF_STORE FOREIGN KEY ( STORE_ID ) REFERENCES STORE( STORE_ID );
ALTER TABLE STORE ADD CONSTRAINT FK_STORE_ADDRESS FOREIGN KEY ( ADDRESS_ID ) REFERENCES ADDRESS( ADDRESS_ID );
ALTER TABLE STORE ADD CONSTRAINT FK_STORE_STAFF FOREIGN KEY ( MANAGER_STAFF_ID ) REFERENCES STAFF( STAFF_ID );
