-- ---------------------------------------------------------------------
-- Création de l'utilisateur et du schéma
-- ---------------------------------------------------------------------
DROP USER TELE CASCADE;




CREATE TABLE INVITES
(
IDINVITE NUMBER(4, 0) NOT NULL,
NOMINV VARCHAR2(30) NOT NULL,
PRENOMINV VARCHAR2(30) NOT NULL,
TELINV VARCHAR2(15),
GSM VARCHAR2(15),
EMAILINV VARCHAR2(20),
ADRESSEINV VARCHAR2(50)
)
;

CREATE TABLE PRODUCTEURS
(
IDPRODUCTEUR NUMBER(4, 0) NOT NULL,
NOMPROD VARCHAR2(30) NOT NULL,
PRENOMPROD VARCHAR2(30) NOT NULL,
TELPROD VARCHAR2(15),
GSM VARCHAR2(15),
EMAILPROD VARCHAR2(20),
NUMID NUMBER(*, 0)
)
;

CREATE TABLE SERIES
(
IDSERIE CHAR(8) NOT NULL,
TITRESERIE VARCHAR2(30) NOT NULL,
PREMIERE DATE,
DERNIERE DATE,
IDPRODUCTEUR NUMBER(4, 0)
)
;

CREATE TABLE PARTICIPER
(
IDINVITE NUMBER(4, 0) NOT NULL,
IDEMISSION NUMBER(4, 0) NOT NULL
)
;

CREATE TABLE PRESENTATEURS
(
IDPRESENTATEUR NUMBER(4, 0) NOT NULL,
NOMPRES VARCHAR2(30) NOT NULL,
PRENOMPRES VARCHAR2(30) NOT NULL,
GSM VARCHAR2(15),
TELPRES VARCHAR2(15),
EMAILPRES VARCHAR2(20),
NUMID NUMBER(*, 0)
)
;

CREATE TABLE PRESENTER
(
IDEMISSION NUMBER(4, 0) NOT NULL,
IDPRESENTATEUR NUMBER(4, 0) NOT NULL
)
;


-- ---------------------------------------------------------------------
-- Créations des liens entre les tables
-- ---------------------------------------------------------------------

ALTER TABLE EMISSIONS
ADD CONSTRAINT PKEMISSIONS PRIMARY KEY
(
IDEMISSION
)
 ENABLE
;

ALTER TABLE INVITES
ADD CONSTRAINT PKINVITES PRIMARY KEY
(
IDINVITE
)
 ENABLE
;

ALTER TABLE PRODUCTEURS
ADD CONSTRAINT PKPRODUCTEURS PRIMARY KEY
(
IDPRODUCTEUR
)
 ENABLE
;

ALTER TABLE SERIES
ADD CONSTRAINT PKSERIES PRIMARY KEY
(
IDSERIE
)
 ENABLE
;

ALTER TABLE PARTICIPER
ADD CONSTRAINT PKPARTICIPER PRIMARY KEY
(
IDINVITE,
IDEMISSION
)
 ENABLE
;

ALTER TABLE PRESENTATEURS
ADD CONSTRAINT PKPRESENTATEURS PRIMARY KEY
(
IDPRESENTATEUR
)
 ENABLE
;

ALTER TABLE PRESENTER
ADD CONSTRAINT PKPRESENTER PRIMARY KEY
(
IDEMISSION,
IDPRESENTATEUR
)
 ENABLE
;

ALTER TABLE EMISSIONS
ADD CONSTRAINT FKEMISER FOREIGN KEY
(
IDSERIE
)
REFERENCES SERIES
(
IDSERIE
) ENABLE
;

ALTER TABLE SERIES
ADD CONSTRAINT FKSERPRO FOREIGN KEY
(
IDPRODUCTEUR
)
REFERENCES PRODUCTEURS
(
IDPRODUCTEUR
) ENABLE
;

ALTER TABLE PARTICIPER
ADD CONSTRAINT FKPARINV FOREIGN KEY
(
IDINVITE
)
REFERENCES INVITES
(
IDINVITE
) ENABLE
;

ALTER TABLE PARTICIPER
ADD CONSTRAINT FKPAREMI FOREIGN KEY
(
IDEMISSION
)
REFERENCES EMISSIONS
(
IDEMISSION
) ENABLE
;

ALTER TABLE PRESENTER
ADD CONSTRAINT FKPRESEMI FOREIGN KEY
(
IDEMISSION
)
REFERENCES EMISSIONS
(
IDEMISSION
) ENABLE
;

ALTER TABLE PRESENTER
ADD CONSTRAINT FKPRESPRES FOREIGN KEY
(
IDPRESENTATEUR
)
REFERENCES PRESENTATEURS
(
IDPRESENTATEUR
) ENABLE
;

-- ---------------------------------------------------------------------
-- Insertion des données
-- ---------------------------------------------------------------------


-- INSERTING into PRODUCTEURS
INSERT INTO PRODUCTEURS  VALUES (1,'Gorini','Georges','02-123456',null,'georges@telexy.com',63);
INSERT INTO PRODUCTEURS  VALUES (2,'Sanjero','Sandra','02-123457',null,'sandra@telexy.com',64);
INSERT INTO PRODUCTEURS  VALUES (3,'Mandrikha','Magali','02-123458',null,'magali@telexy.com',65);

-- INSERTING into SERIES
INSERT INTO SERIES  VALUES ('FichSoi ','Fichue soirée',to_date('2008-09-01','YYYY/MM/DD'),to_date('2009-06-30','YYYY/MM/DD'),1);
INSERT INTO SERIES  VALUES ('PasChoi ','C''est pas votre choix',to_date('2008-09-01','YYYY/MM/DD'),to_date('2008-09-30','YYYY/MM/DD'),2);
INSERT INTO SERIES  VALUES ('BofSto  ','Bof Story',to_date('2008-09-01','YYYY/MM/DD'),to_date('2008-09-30','YYYY/MM/DD'),3);

-- INSERTING into EMISSIONS
INSERT INTO EMISSIONS  VALUES (1,'Le sommeil',to_date('2008-09-04','YYYY/MM/DD'),'FichSoi ');
INSERT INTO EMISSIONS  VALUES (2,'Le goût',to_date('2008-09-11','YYYY/MM/DD'),'FichSoi ');
INSERT INTO EMISSIONS  VALUES (3,'L''amour',to_date('2008-09-18','YYYY/MM/DD'),'FichSoi ');
INSERT INTO EMISSIONS  VALUES (4,'Les enfants',to_date('2008-09-25','YYYY/MM/DD'),'FichSoi ');
INSERT INTO EMISSIONS  VALUES (5,'Manger du chocolat',to_date('2008-09-06','YYYY/MM/DD'),'PasChoi ');
INSERT INTO EMISSIONS  VALUES (6,'Dormir tout nu',to_date('2008-09-13','YYYY/MM/DD'),'PasChoi ');
INSERT INTO EMISSIONS  VALUES (7,'Aller en vacances à Paris',to_date('2008-09-20','YYYY/MM/DD'),'PasChoi ');
INSERT INTO EMISSIONS  VALUES (8,'Critiquer son prof',to_date('2008-09-27','YYYY/MM/DD'),'PasChoi ');
INSERT INTO EMISSIONS  VALUES (9,'Je déteste Maman',to_date('2008-09-08','YYYY/MM/DD'),'BofSto  ');
INSERT INTO EMISSIONS  VALUES (10,'J''aime pas les maths',to_date('2008-09-15','YYYY/MM/DD'),'BofSto  ');
INSERT INTO EMISSIONS  VALUES (11,'Les filles m''ennuient',to_date('2008-09-22','YYYY/MM/DD'),'BofSto  ');
INSERT INTO EMISSIONS  VALUES (12,'Bach m''horripile',to_date('2008-09-29','YYYY/MM/DD'),'BofSto  ');
INSERT INTO EMISSIONS  VALUES (131,'Test',to_date('2008-03-01','YYYY/MM/DD'),'BofSto  ');

-- INSERTING into INVITES
INSERT INTO INVITES  VALUES (1,'Tchernia','Pierre',null,null,null,null);
INSERT INTO INVITES  VALUES (2,'Halliday','Johnny',null,null,null,null);
INSERT INTO INVITES  VALUES (3,'Bruel','Patrick',null,null,null,null);
INSERT INTO INVITES  VALUES (4,'Barthez','Fabien',null,null,null,null);
INSERT INTO INVITES  VALUES (5,'Clerc','Julien',null,null,null,null);
INSERT INTO INVITES  VALUES (6,'Segara','Hýlýne',null,null,null,null);
INSERT INTO INVITES  VALUES (7,'Souchon','Alain',null,null,null,null);
INSERT INTO INVITES  VALUES (8,'Marielle','Jean-Pierre',null,null,null,null);
INSERT INTO INVITES  VALUES (9,'Chirac','Jacques',null,null,null,null);
INSERT INTO INVITES  VALUES (10,'Jospin','Lionel',null,null,null,null);
INSERT INTO INVITES  VALUES (11,'Thoorens','Jacques',null,null,null,null);

-- INSERTING into PARTICIPER
INSERT INTO PARTICIPER  VALUES (1,1);
INSERT INTO PARTICIPER  VALUES (1,2);
INSERT INTO PARTICIPER  VALUES (1,3);
INSERT INTO PARTICIPER  VALUES (1,4);
INSERT INTO PARTICIPER  VALUES (1,5);
INSERT INTO PARTICIPER  VALUES (1,6);
INSERT INTO PARTICIPER  VALUES (1,7);
INSERT INTO PARTICIPER  VALUES (1,8);
INSERT INTO PARTICIPER  VALUES (1,9);
INSERT INTO PARTICIPER  VALUES (1,10);
INSERT INTO PARTICIPER  VALUES (2,10);
INSERT INTO PARTICIPER  VALUES (3,11);
INSERT INTO PARTICIPER  VALUES (4,12);
INSERT INTO PARTICIPER  VALUES (5,1);
INSERT INTO PARTICIPER  VALUES (6,2);
INSERT INTO PARTICIPER  VALUES (7,3);
INSERT INTO PARTICIPER  VALUES (7,4);
INSERT INTO PARTICIPER  VALUES (8,5);
INSERT INTO PARTICIPER  VALUES (8,8);
INSERT INTO PARTICIPER  VALUES (9,7);
INSERT INTO PARTICIPER  VALUES (9,9);

-- INSERTING into PRESENTATEURS
INSERT INTO PRESENTATEURS  VALUES (1,'Nougat','Pimprenelle',null,null,null,null);
INSERT INTO PRESENTATEURS  VALUES (2,'Dupont','Simon',null,null,null,68);
INSERT INTO PRESENTATEURS  VALUES (3,'Durant','Jacquot',null,null,null,69);
INSERT INTO PRESENTATEURS  VALUES (4,'Hur','Ben',null,null,null,70);

-- INSERTING into PRESENTER
INSERT INTO PRESENTER  VALUES (1,1);
INSERT INTO PRESENTER  VALUES (2,1);
INSERT INTO PRESENTER  VALUES (3,1);
INSERT INTO PRESENTER  VALUES (4,1);
INSERT INTO PRESENTER  VALUES (5,2);
INSERT INTO PRESENTER  VALUES (5,3);
INSERT INTO PRESENTER  VALUES (6,2);
INSERT INTO PRESENTER  VALUES (6,3);
INSERT INTO PRESENTER  VALUES (7,2);
INSERT INTO PRESENTER  VALUES (7,3);
INSERT INTO PRESENTER  VALUES (8,2);
INSERT INTO PRESENTER  VALUES (8,3);
INSERT INTO PRESENTER  VALUES (9,4);
INSERT INTO PRESENTER  VALUES (10,4);
INSERT INTO PRESENTER  VALUES (11,1);
INSERT INTO PRESENTER  VALUES (12,2);

COMMIT;