--             Génération d'une base de données pour
--                      Oracle Version 9i
--                        (16/1/2009 11:08:20)
-- -----------------------------------------------------------------------------
--      Nom de la base : Gestion FCP
--      Projet : TP1
--      Auteur : Raymond RAKOTOZAFY
--      Date de dernière modification : 16/1/2009 11:03:58
-- -----------------------------------------------------------------------------

DROP TABLE FCP CASCADE CONSTRAINTS;
DROP TABLE Action CASCADE CONSTRAINTS;
DROP TABLE Client CASCADE CONSTRAINTS;
DROP TABLE Region CASCADE CONSTRAINTS;
DROP TABLE ComposeDe CASCADE CONSTRAINTS;
DROP TABLE PossedeAction CASCADE CONSTRAINTS;
DROP TABLE PossedeFCP CASCADE CONSTRAINTS;

-- -----------------------------------------------------------------------------
--       TABLE : FCP
-- -----------------------------------------------------------------------------
CREATE TABLE FCP
 ( codeFCP 	NUMBER(4)  		NOT NULL
 , nomFCP 		VARCHAR2(20)  
 , dateDebut 	DATE  
 , dateFin 	DATE  
 , CONSTRAINT PK_FCP PRIMARY KEY (codeFCP)  
  ) ;

-- -----------------------------------------------------------------------------
--       TABLE : Action
-- -----------------------------------------------------------------------------
CREATE TABLE Action
 ( codeAct 		NUMBER(4)  		NOT NULL
 , nomAct 			VARCHAR2(20)  
 , valeurCourante 	NUMBER(8,2)  
 , codeRegion 		NUMBER(4)  		NOT NULL
 , CONSTRAINT PK_Action PRIMARY KEY (codeAct)  
 ) ;

-- -----------------------------------------------------------------------------
--       TABLE : Client
-- -----------------------------------------------------------------------------
CREATE TABLE Client
 ( numCli 					NUMBER(4)  		NOT NULL
 , prenomCli 				VARCHAR2(20)  
 , nomCli 					VARCHAR2(20)  
 , dateOuvertureCompte 	DATE  
 , CONSTRAINT PK_Client PRIMARY KEY (numCli)  
 ) ;

-- -----------------------------------------------------------------------------
--       TABLE : Region
-- -----------------------------------------------------------------------------
CREATE TABLE Region
 ( codeRegion 	NUMBER(4)  	NOT NULL
 , nomRegion 	VARCHAR2(20)  
 , CONSTRAINT PK_Region PRIMARY KEY (codeRegion)  
  ) ;

-- -----------------------------------------------------------------------------
--       TABLE : ComposeDe
-- -----------------------------------------------------------------------------
CREATE TABLE ComposeDe
 ( codeFCP 	NUMBER(4)  NOT NULL
 , codeAct 	NUMBER(4)  NOT NULL
 , quantite 	NUMBER(4)  
 , prixAchat 	NUMBER(8,2)  
 , CONSTRAINT PK_ComposeDe PRIMARY KEY (codeFCP, codeAct)  
 ) ;

-- -----------------------------------------------------------------------------
--       TABLE : PossedeAction
-- -----------------------------------------------------------------------------

CREATE TABLE PossedeAction
 ( numCli 		NUMBER(4)  	NOT NULL
 , codeAct 	NUMBER(4)  	NOT NULL
 , quantite 	NUMBER(4)  
 , prixAchat 	NUMBER(8,2)  
 , CONSTRAINT PK_PossedeAction PRIMARY KEY (numCli, codeAct)  
 ) ;

-- -----------------------------------------------------------------------------
--       TABLE : PossedeFCP
-- -----------------------------------------------------------------------------
CREATE TABLE PossedeFCP
 ( numCli 			NUMBER(4)  	NOT NULL
 , codeFCP 		NUMBER(4)  	NOT NULL
 , quantiteFCP 	NUMBER(4)  
 , CONSTRAINT PK_PossedeFCP PRIMARY KEY (numCli, codeFCP)  
  ) ;

-- -----------------------------------------------------------------------------
--       CREATION DES REFERENCES DE TABLE
-- -----------------------------------------------------------------------------
ALTER TABLE Action ADD (
    CONSTRAINT FK_Action_Region
         FOREIGN KEY (codeRegion)
              REFERENCES Region (codeRegion))   ;

ALTER TABLE ComposeDe ADD (
    CONSTRAINT FK_ComposeDe_FCP
         FOREIGN KEY (codeFCP)
              REFERENCES FCP (codeFCP))   ;

ALTER TABLE ComposeDe ADD (
    CONSTRAINT FK_ComposeDe_Action
         FOREIGN KEY (codeAct)
              REFERENCES Action (codeAct))   ;

ALTER TABLE PossedeAction ADD (
    CONSTRAINT FK_PossedeAction_Client
         FOREIGN KEY (numCli)
              REFERENCES Client (numCli))   ;

ALTER TABLE PossedeAction ADD (
    CONSTRAINT FK_PossedeAction_Action
         FOREIGN KEY (codeAct)
              REFERENCES Action (codeAct))   ;

ALTER TABLE PossedeFCP ADD (
    CONSTRAINT FK_PossedeFCP_Client
         FOREIGN KEY (numCli)
              REFERENCES Client (numCli))   ;

ALTER TABLE PossedeFCP ADD (
    CONSTRAINT FK_PossedeFCP_FCP
         FOREIGN KEY (codeFCP)
              REFERENCES FCP (codeFCP))   ;

INSERT INTO Client VALUES(1, 'Pierre',  'Leloup', '22/12/2000');
INSERT INTO Client VALUES(2, 'Paul',    'Durand', '12/01/1998');
INSERT INTO Client VALUES(3, 'Louis',   'Dupont', '15/03/2001');
INSERT INTO Client VALUES(4, 'Jacques', 'Martin', '28/09/2001');
INSERT INTO Client VALUES(5, 'Pierre',  'Perrin', '11/06/2000');

INSERT INTO Region VALUES(1, 'Europe');
INSERT INTO Region VALUES(2, 'USA');

INSERT INTO Action VALUES(1, 'Alcatel',          5.1,  1);
INSERT INTO Action VALUES(2, 'Snecma',           19.3, 1);
INSERT INTO Action VALUES(3, 'General Electric', 95.6, 2);
INSERT INTO Action VALUES(4, 'BNP',              11.8, 1);
INSERT INTO Action VALUES(5, 'IBM',              21.3, 2);

INSERT INTO FCP VALUES(1, 'MAXITUNE',  '15/01/2000', '14/01/2006');
INSERT INTO FCP VALUES(2, 'PEPERE',    '28/03/1999', '27/03/2005');
INSERT INTO FCP VALUES(3, 'DYNAMIQUE', '01/04/2001', '31/03/2005');

INSERT INTO PossedeAction VALUES(1, 1, 100,   10.1);
INSERT INTO PossedeAction VALUES(1, 2, 1000,  5.6);
INSERT INTO PossedeAction VALUES(1, 3, 220,   20.5);
INSERT INTO PossedeAction VALUES(2, 1, 134,   20);
INSERT INTO PossedeAction VALUES(2, 5, 213,   15.3);
INSERT INTO PossedeAction VALUES(3, 1, 24434, 18);
INSERT INTO PossedeAction VALUES(3, 2, 112,   13.6);
INSERT INTO PossedeAction VALUES(3, 4, 6000,  6.1);
INSERT INTO PossedeAction VALUES(4, 3, 1000,  80.6);
INSERT INTO PossedeAction VALUES(5, 3, 123,   75.1);
INSERT INTO PossedeAction VALUES(5, 5, 500,   14.9);

INSERT INTO ComposeDe VALUES (1, 1, 10, 12.7);
INSERT INTO ComposeDe VALUES (1, 2, 15, 5.2);
INSERT INTO ComposeDe VALUES (1, 4, 12, 18.4);
INSERT INTO ComposeDe VALUES (2, 1, 3,  22.1);
INSERT INTO ComposeDe VALUES (2, 2, 5,  21);
INSERT INTO ComposeDe VALUES (2, 3, 1,  10);
INSERT INTO ComposeDe VALUES (2, 4, 20, 12.4);
INSERT INTO ComposeDe VALUES (3, 3, 12, 68.1);
INSERT INTO ComposeDe VALUES (3, 5, 5,  15.3);

INSERT INTO PossedeFCP VALUES(1, 1, 50);
INSERT INTO PossedeFCP VALUES(1, 2, 75);
INSERT INTO PossedeFCP VALUES(2, 1, 50);
INSERT INTO PossedeFCP VALUES(4, 3, 100);

SELECT * FROM Client;

SELECT * FROM Region;

SELECT * FROM Action;

SELECT * FROM FCP;

SELECT * FROM PossedeAction;

SELECT * FROM ComposeDe;

SELECT * FROM PossedeFCP;