From Wiki-UX.info

MDB302 Sakila para Oracle
Jump to: navigation, search

MDB302 Sakila para Oracle

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

Diagrama Relacional Sakila Oracle

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 );

Referencias

Autor

This page was last modified on 8 November 2016, at 06:37. This page has been accessed 210 times.