TP3_SQL_SCRIPTS

_00-MASTER_SCRIPT.sql

-- Script that runs the other scripts in the proper order

-- Reset the value of scripts_path in case it was already set
UNDEFINE scripts_path

-- ask the user to set the scripts path
PROMPT Set the path to the directory in which this file currently is.
PROMPT     (ex: if the path to this script is C:\SQL\_MASTER_SCRIPT.sql,
PROMPT     then the path should be C:\SQL\ )
PROMPT 

-- set the scripts path
DEFINE scripts_path=&scripts_path

-- notify the user of what path he/she has set
PROMPT The path has been set to: &scripts_path.

-- execute scripts
PROMPT Executing scripts...

@"&scripts_path._01-CREATE_TABLES_AND_SEQUENCES.sql"
@"&scripts_path._02-POPULATE.sql"
@"&scripts_path._03-CREATE_VIEWS.sql"
@"&scripts_path._04-GRANT.sql"

PROMPT Finished running all scripts!

_01-CREATE_TABLES_AND_SEQUENCES.sql

-- Auteur : Mihoubi 
-- Date : 2016-02-27 
-- type : Oracle Database 11g

DROP TABLE AUTEUR CASCADE CONSTRAINTS;
DROP TABLE UTILISATEUR CASCADE CONSTRAINTS;
DROP TABLE DOCUMENT CASCADE CONSTRAINTS;
DROP TABLE TRANSACTION CASCADE CONSTRAINTS;

DROP SEQUENCE seq_tp3_utilisateur;

CREATE TABLE AUTEUR(
    id_aut  INTEGER NOT NULL ,
    nom_aut VARCHAR2 (40) NOT NULL) ;
    
ALTER TABLE AUTEUR ADD CONSTRAINT AUTEUR_PK PRIMARY KEY (id_aut);

CREATE TABLE DOCUMENT(
    id_doc                  INTEGER,
    titre_doc               VARCHAR2 (50) NOT NULL ,
    cote_doc                CHAR (2) ,
    date_creation_doc       DATE ,
    date_enregistrement_doc DATE NOT NULL ,
    id_aut                  INTEGER NOT NULL,
    isbn_liv                VARCHAR2 (15), 
    version_liv             NUMBER (1), 
    duree_mn_vid            INTEGER ,
    support_vid             VARCHAR2(10)) ;
    
ALTER TABLE DOCUMENT ADD CONSTRAINT DOCUMENT_PK PRIMARY KEY(id_doc);

ALTER TABLE document ADD CONSTRAINT ck_documents_type CHECK (
	(
		(duree_mn_vid IS NULL AND support_vid IS NULL)
		AND
		(isbn_liv IS NOT NULL)
	)
	OR
	(
		(support_vid IN ('DVD', 'CD', 'BANDE'))
		AND
		(isbn_liv IS NULL AND version_liv IS NULL)
	)
);

CREATE TABLE TRANSACTION
  (
    id_tra   INTEGER NOT NULL ,
    date_tra DATE NOT NULL ,
    type_tra CHAR (3 CHAR) NOT NULL ,
    id_uti   INTEGER NOT NULL ,
    id_doc   INTEGER NOT NULL
  ) ;
ALTER TABLE TRANSACTION ADD CONSTRAINT TRANSACTION_PK PRIMARY KEY (id_tra);

CREATE TABLE UTILISATEUR (
    id_uti      INTEGER NOT NULL ,
    nom_uti     VARCHAR2 (30 CHAR) NOT NULL ,
    Prenom_uti  VARCHAR2 (30) ,
    adresse_uti VARCHAR2 (50 CHAR) NOT NULL
  ) ;
ALTER TABLE UTILISATEUR ADD CONSTRAINT UTILISATEUR_PK PRIMARY KEY (id_uti);

ALTER TABLE DOCUMENT ADD CONSTRAINT DOCUMENT_AUTEUR_FK FOREIGN KEY (id_aut)
                              REFERENCES AUTEUR (id_aut) ;

ALTER TABLE TRANSACTION ADD CONSTRAINT TRANSACTION_DOCUMENT_FK FOREIGN KEY (id_doc)
                              REFERENCES DOCUMENT (id_doc) ;

ALTER TABLE TRANSACTION ADD CONSTRAINT TRANSACTION_UTILISATEUR_FK FOREIGN KEY (id_uti)
                              REFERENCES UTILISATEUR ( id_uti ) ON DELETE CASCADE;

CREATE SEQUENCE seq_tp3_utilisateur
	START WITH 1
	INCREMENT BY 1
	NOCYCLE;
	
	

_02-POPULATE.sql

-- Table populating script

-------------
-- AUTHORS --
-------------

---- movie authors
INSERT INTO auteur VALUES (1, 'Author A');
INSERT INTO auteur VALUES (2, 'Author B');

---- book authors
INSERT INTO auteur VALUES (3, 'Author C'); 
INSERT INTO auteur VALUES (4, 'Author D');



---------------
-- DOCUMENTS --
---------------

---- video documents
INSERT INTO document VALUES (1, 'Movie A', NULL, NULL, SYSDATE, 1, NULL, NULL, 75, 'CD');
INSERT INTO document VALUES (2, 'Movie B', NULL, NULL, SYSDATE, 2, NULL, NULL, 45, 'DVD');

---- book documents
INSERT INTO document VALUES (3, 'Book A', NULL, NULL, SYSDATE, 3, '0000000001', 1, NULL, NULL);
INSERT INTO document VALUES (4, 'Book B', NULL, NULL, SYSDATE, 4, '0000000002', 1, NULL, NULL);



-----------
-- USERS --
-----------

INSERT INTO utilisateur VALUES (seq_tp3_utilisateur.nextval, 'Doe', 'John', '2591 Leslie Street');
INSERT INTO utilisateur VALUES (seq_tp3_utilisateur.nextval, 'Cole', 'Stephen', '3255 Port Washington Road');
INSERT INTO utilisateur VALUES (seq_tp3_utilisateur.nextval, 'Gerry', 'George', '3774 Wharf Rd');
INSERT INTO utilisateur VALUES (seq_tp3_utilisateur.nextval, 'Howard', 'Michael', '3832 Danforth Avenue');



------------------
-- TRANSACTIONS --
------------------

---- user 1 (John Doe)
INSERT INTO transaction VALUES (1, TO_DATE('2017-01-04', 'YYYY-MM-DD'), 'BOR', 1, 1);
INSERT INTO transaction VALUES (2, TO_DATE('2017-01-07', 'YYYY-MM-DD'), 'RET', 1, 1);
INSERT INTO transaction VALUES (3, TO_DATE('2017-02-01', 'YYYY-MM-DD'), 'BOR', 1, 3);
INSERT INTO transaction VALUES (4, TO_DATE('2017-02-13', 'YYYY-MM-DD'), 'RET', 1, 3);

---- user 2 (Stephen Cole)
INSERT INTO transaction VALUES (5, TO_DATE('2017-01-05', 'YYYY-MM-DD'), 'BOR', 2, 2);
INSERT INTO transaction VALUES (6, TO_DATE('2017-01-17', 'YYYY-MM-DD'), 'RET', 2, 2);
INSERT INTO transaction VALUES (7, TO_DATE('2017-01-27', 'YYYY-MM-DD'), 'BOR', 2, 3);
INSERT INTO transaction VALUES (8, TO_DATE('2017-01-27', 'YYYY-MM-DD'), 'BOR', 2, 4);
INSERT INTO transaction VALUES (9, TO_DATE('2017-02-11', 'YYYY-MM-DD'), 'RET', 2, 3);
INSERT INTO transaction VALUES (10, TO_DATE('2017-02-11', 'YYYY-MM-DD'), 'RET', 2, 4);
INSERT INTO transaction VALUES (11, TO_DATE('2017-04-05', 'YYYY-MM-DD'), 'BOR', 2, 1);
INSERT INTO transaction VALUES (12, TO_DATE('2017-04-13', 'YYYY-MM-DD'), 'RET', 2, 4);

---- user 3 (George Gerry)
INSERT INTO transaction VALUES (13, TO_DATE('2017-01-01', 'YYYY-MM-DD'), 'BOR', 3, 2);
INSERT INTO transaction VALUES (14, TO_DATE('2017-02-01', 'YYYY-MM-DD'), 'RET', 3, 2);
INSERT INTO transaction VALUES (15, TO_DATE('2017-02-01', 'YYYY-MM-DD'), 'BOR', 3, 1);
INSERT INTO transaction VALUES (16, TO_DATE('2017-03-01', 'YYYY-MM-DD'), 'RET', 3, 1);
INSERT INTO transaction VALUES (17, TO_DATE('2017-03-01', 'YYYY-MM-DD'), 'BOR', 3, 3);

---- user 4 (Michael Howard)
INSERT INTO transaction VALUES (18, TO_DATE('2014-04-01', 'YYYY-MM-DD'), 'BOR', 4, 1);
INSERT INTO transaction VALUES (19, TO_DATE('2014-09-23', 'YYYY-MM-DD'), 'RET', 4, 1);
INSERT INTO transaction VALUES (20, TO_DATE('2015-06-08', 'YYYY-MM-DD'), 'BOR', 4, 2);
INSERT INTO transaction VALUES (21, TO_DATE('2015-11-16', 'YYYY-MM-DD'), 'RET', 4, 2);
INSERT INTO transaction VALUES (22, TO_DATE('2016-11-01', 'YYYY-MM-DD'), 'BOR', 4, 3);
INSERT INTO transaction VALUES (23, TO_DATE('2017-01-01', 'YYYY-MM-DD'), 'RET', 4, 3);


----------------------
-- SAVE ALL CHANGES --
----------------------
COMMIT;

_03-CREATE_VIEWS.sql

-- Script that creates the necessary VIEW objects 

-- USERS
CREATE OR REPLACE VIEW vw_users AS SELECT * FROM utilisateur;

-- STATISTICS
CREATE OR REPLACE VIEW vw_stats (id_uti, user_name, titre_doc, type_doc, date_tra, type_tra)
    AS 
    SELECT u.id_uti,
        (u.prenom_uti || ' ' || u.nom_uti), 
        d.titre_doc, 
        CASE WHEN d.isbn_liv IS NULL THEN 'Video' ELSE 'Book' END,
        TO_CHAR(t.date_tra, 'YYYY-MM-DD'),
        t.type_tra
    FROM document d, utilisateur u, transaction t
    WHERE u.id_uti = t.id_uti 
        AND t.id_doc = d.id_doc;

_04-GRANT.sql

-- Script that grants the necessary privileges

GRANT SELECT, INSERT ON vw_users TO AN2_1440009, kmihoubi
GRANT SELECT ON vw_stats TO AN2_1440009, kmihoubi
GRANT UPDATE, DELETE ON vw_users TO kmihoubi