-- ###################################################################
-- # Application : SQL script
-- # File        : create_fill_tp_note.sql
-- # Revision    : janvier 2014 
-- # Author      : Marie Szafranski
-- # Function    : structure et alimentation de la base de données 
--                 formation
-- ###################################################################

-- *********************
-- * Creation du schema
-- *********************

-- suppression des tables si besoin
DROP TABLE IF EXISTS t_Diagnostic;
DROP TABLE IF EXISTS t_Diagnostic_details;
DROP VIEW IF EXISTS v_Medecin;
DROP TABLE IF EXISTS t_Medecin;
DROP VIEW IF EXISTS v_Patient;
DROP TABLE IF EXISTS t_Patient;
DROP TABLE IF EXISTS t_Personne;
DROP TABLE IF EXISTS t_Specialite;

CREATE TABLE t_Specialite(
   code_specialite INTEGER,
   libelle VARCHAR(50) NOT NULL,
   CONSTRAINT pk_Spe PRIMARY KEY (code_specialite));

CREATE TABLE t_Personne(
   code_personne INTEGER,
   nom VARCHAR(50) NOT NULL,
   prenom VARCHAR(50),
   CONSTRAINT pk_Per PRIMARY KEY (code_personne));
      
CREATE TABLE t_Patient(
   code_patient INTEGER,
   CONSTRAINT pk_Pat PRIMARY KEY (code_patient),
   CONSTRAINT fk_Pat_code_patient FOREIGN KEY (code_patient)
      REFERENCES t_Personne (code_personne)); 
   
CREATE TABLE t_Medecin(
   code_medecin INTEGER,
   specialite_1 INTEGER NOT NULL,
   specialite_2 INTEGER,
   nb_cas INTEGER,
   CONSTRAINT pk_Med_code_medecin PRIMARY KEY (code_medecin),
   CONSTRAINT fk_Med_code_medecin FOREIGN KEY (code_medecin)
      REFERENCES t_Personne (code_personne),
   CONSTRAINT fk_Med_specialite_1 FOREIGN KEY (specialite_1)
      REFERENCES t_Specialite(code_specialite),
   CONSTRAINT fk_Med_specialite_2 FOREIGN KEY (specialite_2)
      REFERENCES t_Specialite(code_specialite));
     
CREATE TABLE t_Diagnostic_details(
   code_diagnostic VARCHAR(3),
   maladie VARCHAR(100) NOT NULL,
   symptome_1 VARCHAR(100),
   symptome_2 VARCHAR(100),
   code_specialite INTEGER,
   CONSTRAINT pk_Dia_d PRIMARY KEY (code_diagnostic),
   CONSTRAINT fk_Dia_d_specialite FOREIGN KEY (code_specialite)
      REFERENCES t_Specialite(code_specialite)); 
   
CREATE TABLE t_Diagnostic(
   code_patient INTEGER,
   code_medecin INTEGER,
   diagnostic VARCHAR(3),
   CONSTRAINT pk_t_Dia 
      PRIMARY KEY (code_patient, code_medecin), 
   CONSTRAINT fk_t_Dia_code_patient FOREIGN KEY (code_patient)
      REFERENCES t_Patient(code_patient),
   CONSTRAINT fk_t_Dia_code_medecin FOREIGN KEY (code_medecin)
      REFERENCES t_Medecin(code_medecin),
   CONSTRAINT fk_Dia_diagnostic FOREIGN KEY (diagnostic)
      REFERENCES t_Diagnostic_details(code_diagnostic));

-- **************************
-- * Alimentation de la base
-- **************************

DELETE FROM t_Diagnostic;
DELETE FROM t_Diagnostic_details;
DELETE FROM t_Patient;
DELETE FROM t_Medecin;
DELETE FROM t_Personne;
DELETE FROM t_Specialite;

--> table t_Specialite
INSERT INTO t_Specialite VALUES (1, 'Maladies infectieuses');
INSERT INTO t_Specialite VALUES (2, 'Nephrologie');
INSERT INTO t_Specialite VALUES (3, 'Medecine interne');
INSERT INTO t_Specialite VALUES (4, 'Endocrinologie');
INSERT INTO t_Specialite VALUES (5, 'Oncologie');
INSERT INTO t_Specialite VALUES (6, 'Neurologie');
INSERT INTO t_Specialite VALUES (7, 'Immunologie');
INSERT INTO t_Specialite VALUES (8, 'Allergologie');
INSERT INTO t_Specialite VALUES (9, 'Cardiologie');
INSERT INTO t_Specialite VALUES (10, 'Chirurgie');
INSERT INTO t_Specialite VALUES (11, 'Chirurgie plastique');
INSERT INTO t_Specialite VALUES (12, 'Medecine du sport');
INSERT INTO t_Specialite VALUES (13, 'Maladies genetiques');
INSERT INTO t_Specialite VALUES (14, 'Psychiatrie');
INSERT INTO t_Specialite VALUES (15, 'Toxicologie');

--> table t_Personne
INSERT INTO t_Personne VALUES (1, 'House', 'Gregory');
INSERT INTO t_Personne VALUES (2, 'Cuddy', 'Lisa');
INSERT INTO t_Personne VALUES (3, 'Wilson', 'James');
INSERT INTO t_Personne VALUES (4, 'Foreman', 'Eric');
INSERT INTO t_Personne VALUES (5, 'Cameron', 'Allison');
INSERT INTO t_Personne VALUES (6, 'Chase', 'Robert');
INSERT INTO t_Personne VALUES (7, 'Taub', 'Chris');
INSERT INTO t_Personne VALUES (8, 'Kutner', 'Lawrence');
INSERT INTO t_Personne VALUES (9, 'Adams', 'Jessica');
INSERT INTO t_Personne VALUES (10, 'Park', 'Chi');
INSERT INTO t_Personne VALUES (11, 'Adler', 'Rebecca'); 
INSERT INTO t_Personne VALUES (12, 'Augustine', 'Mary');
INSERT INTO t_Personne VALUES (13, 'Hadley', 'Remy');
INSERT INTO t_Personne VALUES (14, 'Palmero', 'Lucy');
INSERT INTO t_Personne VALUES (15, 'Arnello', 'Joey');
INSERT INTO t_Personne VALUES (16, 'Cool J', 'Clarence');
INSERT INTO t_Personne VALUES (17, 'Jovanovich', 'Anica');
INSERT INTO t_Personne VALUES (18, 'Stone', 'Fletcher');
INSERT INTO t_Personne VALUES (19, 'Richardson', 'Alex');
INSERT INTO t_Personne VALUES (20, 'Mays', 'Hanna');
INSERT INTO t_Personne VALUES (21, 'Davis', 'Leona');
INSERT INTO t_Personne VALUES (22, 'Mc Neil', 'Richard');
INSERT INTO t_Personne VALUES (23, 'Powell', 'Ezra');
INSERT INTO t_Personne VALUES (24, 'Hartman', 'Alice');
INSERT INTO t_Personne VALUES (25, 'Lane', 'Nate');
INSERT INTO t_Personne VALUES (26, 'Skye', 'Irene');
INSERT INTO t_Personne VALUES (27, 'Thomas', 'Kenny');
INSERT INTO t_Personne VALUES (28, 'Liberato', 'Jane');
INSERT INTO t_Personne VALUES (29, 'Ruseckas', 'Larry');
INSERT INTO t_Personne VALUES (30, 'Ivanek', 'Jason');
INSERT INTO t_Personne VALUES (31, 'Henderson', 'Jeff');
INSERT INTO t_Personne VALUES (32, 'Bresson', 'Daniel');
INSERT INTO t_Personne VALUES (33, 'Karnes', 'Nick');
INSERT INTO t_Personne VALUES (34, 'West', 'Morgane');

--> table t_Medecins
INSERT INTO t_Medecin VALUES (1, 1, 3, NULL);
INSERT INTO t_Medecin VALUES (2, 4, NULL, NULL);
INSERT INTO t_Medecin VALUES (3, 5, NULL, NULL);
INSERT INTO t_Medecin VALUES (4, 6, 3, NULL);
INSERT INTO t_Medecin VALUES (5, 7, 1, NULL);
INSERT INTO t_Medecin VALUES (6, 9, 3, NULL);
INSERT INTO t_Medecin VALUES (7, 11, NULL, NULL);
INSERT INTO t_Medecin VALUES (8, 1, 12, NULL);
INSERT INTO t_Medecin VALUES (9, 3, NULL, NULL);
INSERT INTO t_Medecin VALUES (10, 6, 13, NULL);
INSERT INTO t_Medecin VALUES (13, 3, 1, NULL);

--> table t_Patient
INSERT INTO t_Patient VALUES (2);
INSERT INTO t_Patient VALUES (4);
INSERT INTO t_Patient VALUES (11);
INSERT INTO t_Patient VALUES (12);
INSERT INTO t_Patient VALUES (13);
INSERT INTO t_Patient VALUES (14);
INSERT INTO t_Patient VALUES (15);
INSERT INTO t_Patient VALUES (16);
INSERT INTO t_Patient VALUES (17);
INSERT INTO t_Patient VALUES (18);
INSERT INTO t_Patient VALUES (19);
INSERT INTO t_Patient VALUES (20);
INSERT INTO t_Patient VALUES (21);
INSERT INTO t_Patient VALUES (22);
INSERT INTO t_Patient VALUES (23);
INSERT INTO t_Patient VALUES (24);
INSERT INTO t_Patient VALUES (25);
INSERT INTO t_Patient VALUES (26);
INSERT INTO t_Patient VALUES (27);
INSERT INTO t_Patient VALUES (28);
INSERT INTO t_Patient VALUES (29);
INSERT INTO t_Patient VALUES (30);
INSERT INTO t_Patient VALUES (31);
INSERT INTO t_Patient VALUES (32);
INSERT INTO t_Patient VALUES (33);
INSERT INTO t_Patient VALUES (34);


--> table t_Diagnostic_details
INSERT INTO t_Diagnostic_details 
VALUES('D1', 'Hysterie collective','Vomissements', 'Photophobie', 14);
INSERT INTO t_Diagnostic_details 
VALUES('D2', 'Meningo-encephalite amibienne primitive', 'Cecite', 'Pancreatite', 1);
INSERT INTO t_Diagnostic_details 
VALUES ('D3', 'Maladie de Huntington', 'Tremblements', NULL, 13);
INSERT INTO t_Diagnostic_details
VALUES ('D4', 'Neurocysticercose', 'Aphasie', 'Convulsions', 1);
INSERT INTO t_Diagnostic_details
VALUES ('D5', 'Allergie au cuivre', 'Dermatose', 'Hypoxie', 8);
INSERT INTO t_Diagnostic_details
VALUES ('D6', 'Madadie de Wilson', 'Thrombose veineuse', 'Schizophrenie', 13);
INSERT INTO t_Diagnostic_details
VALUES ('D7', 'Deficit en ornithine carbamyl transferase', 'Coma', 'Anemie aplasique', 13);
INSERT INTO t_Diagnostic_details
VALUES ('D8', 'Pheochromocytome', 'Hypoxie', 'Acces de rage', 4);
INSERT INTO t_Diagnostic_details
VALUES ('D9', 'Infection par clostridium perfringens', 'Convulsions', 'Anemie aplasique', 1);
INSERT INTO t_Diagnostic_details
VALUES ('D10', 'Paludisme', 'Aphasie', 'Insuffisance renale', 1);
INSERT INTO t_Diagnostic_details
VALUES ('D11', 'Cancer des testicules', 'Acces de rage', 'Coma', 5);
INSERT INTO t_Diagnostic_details
VALUES ('D12', 'Peste bubonique', 'Insomie', 'Allergie aux poils de chiens', 1);
INSERT INTO t_Diagnostic_details
VALUES ('D13', 'Hemochromatose genetique', 'Hallucinations', 'Hemochromatose', 13);
INSERT INTO t_Diagnostic_details
VALUES ('D14', 'Maladie d Addison', 'Fievre', 'Insuffisance surrenale', 4);
INSERT INTO t_Diagnostic_details
VALUES ('D15', 'Amyloidose', 'Epanchement pleural', 'Insuffisance respiratoire', 3);
INSERT INTO t_Diagnostic_details 
VALUES ('D16', 'Protoporphyrie erythropoietique', 'Terreurs diurnes', 'Terrain allergique', 13);
INSERT INTO t_Diagnostic_details
VALUES ('D17', 'Hemochromatose genetique', 'Acces de rage', 'Cephalees', 13);
INSERT INTO t_Diagnostic_details
VALUES ('D18', 'Ergotisme', 'Hallucinations', 'Cephalees', 15);
INSERT INTO t_Diagnostic_details
VALUES ('D19', 'Maladie de Lyme', 'Malformation faciale', 'Eruptions cutanees', 1);
INSERT INTO t_Diagnostic_details
VALUES ('D20', 'Cancer du sein', 'Paralysie', 'Cecite', 5);
INSERT INTO t_Diagnostic_details
VALUES ('D21', 'Empoisonnement au plomb', 'Agoraphobie', 'Douleurs abdominales', 15);
INSERT INTO t_Diagnostic_details
VALUES ('D22', 'Melioidose',  'Eruptions cutanees', 'Insuffisance respiratoire', 1);
INSERT INTO t_Diagnostic_details
VALUES ('D23', 'Epilepsie', 'Cephalees', 'Embolie pulmonaire', 2);
INSERT INTO t_Diagnostic_details
VALUES ('D24', 'Syndrome de Wiskott-Aldrich', 'Hallucinations', 'Necrose', 7);
INSERT INTO t_Diagnostic_details
VALUES ('D25', 'Syndrome de Dodge-Potter', 'Desinhibition', 'Epanchement pleural', 5);
INSERT INTO t_Diagnostic_details
VALUES ('D26', 'Tumeur carcinoide de l appendice', 'Convulsions', 'Bronchospasme', 5);

--> table t_Diagnostic
INSERT INTO t_Diagnostic VALUES (2, 1, 'D1');
INSERT INTO t_Diagnostic VALUES (4, 1, 'D2');
INSERT INTO t_Diagnostic VALUES (4, 5, 'D2');
INSERT INTO t_Diagnostic VALUES (4, 6, 'D2');
INSERT INTO t_Diagnostic VALUES (13, 1, 'D3');
INSERT INTO t_Diagnostic  VALUES (11, 1, 'D4');
INSERT INTO t_Diagnostic  VALUES (11, 3, 'D4');
INSERT INTO t_Diagnostic  VALUES (11, 4, 'D4');
INSERT INTO t_Diagnostic  VALUES (11, 5, 'D4');
INSERT INTO t_Diagnostic  VALUES (11, 6, 'D4');
INSERT INTO t_Diagnostic  VALUES (12, 1, 'D5');
INSERT INTO t_Diagnostic  VALUES (12, 4, 'D5');
INSERT INTO t_Diagnostic  VALUES (12, 5, 'D5');
INSERT INTO t_Diagnostic  VALUES (12, 6, 'D5');
INSERT INTO t_Diagnostic  VALUES (14, 1, 'D6');
INSERT INTO t_Diagnostic  VALUES (14, 4, 'D6');
INSERT INTO t_Diagnostic  VALUES (14, 5, 'D6');
INSERT INTO t_Diagnostic  VALUES (14, 6, 'D6');
INSERT INTO t_Diagnostic  VALUES (15, 1, 'D7');
INSERT INTO t_Diagnostic  VALUES (15, 4, 'D7');
INSERT INTO t_Diagnostic  VALUES (15, 5, 'D7');
INSERT INTO t_Diagnostic  VALUES (15, 6, 'D7');
INSERT INTO t_Diagnostic  VALUES (16, 1, 'D8');
INSERT INTO t_Diagnostic  VALUES (16, 4, 'D8');
INSERT INTO t_Diagnostic  VALUES (16, 6, 'D8');
INSERT INTO t_Diagnostic  VALUES (17, 1, 'D9');
INSERT INTO t_Diagnostic  VALUES (17, 4, 'D9');
INSERT INTO t_Diagnostic  VALUES (17, 5, 'D9');
INSERT INTO t_Diagnostic  VALUES (17, 6, 'D9');
INSERT INTO t_Diagnostic  VALUES (18, 1, 'D10');
INSERT INTO t_Diagnostic  VALUES (18, 4, 'D10');
INSERT INTO t_Diagnostic  VALUES (18, 5, 'D10');
INSERT INTO t_Diagnostic  VALUES (18, 6, 'D10');
INSERT INTO t_Diagnostic  VALUES (19, 1, 'D11');
INSERT INTO t_Diagnostic  VALUES (19, 3, 'D11');
INSERT INTO t_Diagnostic  VALUES (19, 4, 'D11');
INSERT INTO t_Diagnostic  VALUES (19, 5, 'D11');
INSERT INTO t_Diagnostic  VALUES (19, 6, 'D11');
INSERT INTO t_Diagnostic  VALUES (20, 1, 'D12');
INSERT INTO t_Diagnostic  VALUES (20, 4, 'D12');
INSERT INTO t_Diagnostic  VALUES (20, 5, 'D12');
INSERT INTO t_Diagnostic  VALUES (20, 6, 'D12');
INSERT INTO t_Diagnostic  VALUES (21, 1, 'D13');
INSERT INTO t_Diagnostic  VALUES (21, 4, 'D13');
INSERT INTO t_Diagnostic  VALUES (21, 5, 'D13');
INSERT INTO t_Diagnostic  VALUES (22, 1, 'D14');
INSERT INTO t_Diagnostic  VALUES (22, 4, 'D14');
INSERT INTO t_Diagnostic  VALUES (22, 5, 'D14');
INSERT INTO t_Diagnostic  VALUES (22, 6, 'D14');
INSERT INTO t_Diagnostic  VALUES (23, 1, 'D15');
INSERT INTO t_Diagnostic  VALUES (23, 4, 'D15');
INSERT INTO t_Diagnostic  VALUES (23, 5, 'D15');
INSERT INTO t_Diagnostic  VALUES (23, 6, 'D15');
INSERT INTO t_Diagnostic  VALUES (24, 1, 'D16');
INSERT INTO t_Diagnostic  VALUES (24, 4, 'D16');
INSERT INTO t_Diagnostic  VALUES (24, 5, 'D16');
INSERT INTO t_Diagnostic  VALUES (24, 6, 'D16');
INSERT INTO t_Diagnostic  VALUES (25, 1, 'D17');
INSERT INTO t_Diagnostic  VALUES (25, 4, 'D17');
INSERT INTO t_Diagnostic  VALUES (25, 5, 'D17');
INSERT INTO t_Diagnostic  VALUES (25, 6, 'D17');
INSERT INTO t_Diagnostic  VALUES (26, 1, 'D18');
INSERT INTO t_Diagnostic  VALUES (26, 13, 'D18');
INSERT INTO t_Diagnostic  VALUES (26, 8, 'D18');
INSERT INTO t_Diagnostic  VALUES (27, 1, 'D19');
INSERT INTO t_Diagnostic  VALUES (27, 13, 'D19');
INSERT INTO t_Diagnostic  VALUES (28, 1, 'D20');
INSERT INTO t_Diagnostic  VALUES (28, 3, 'D20');
INSERT INTO t_Diagnostic  VALUES (28, 13, 'D20');
INSERT INTO t_Diagnostic  VALUES (28, 7, 'D20');
INSERT INTO t_Diagnostic  VALUES (29, 1, 'D21');
INSERT INTO t_Diagnostic  VALUES (29, 5, 'D21');
INSERT INTO t_Diagnostic  VALUES (29, 7, 'D21');
INSERT INTO t_Diagnostic  VALUES (29, 6, 'D21');
INSERT INTO t_Diagnostic  VALUES (30, 1, 'D22');
INSERT INTO t_Diagnostic  VALUES (30, 13, 'D22');
INSERT INTO t_Diagnostic  VALUES (30, 7, 'D22');
INSERT INTO t_Diagnostic  VALUES (31, 1, 'D23');
INSERT INTO t_Diagnostic  VALUES (31, 13, 'D23');
INSERT INTO t_Diagnostic  VALUES (31, 4, 'D23');
INSERT INTO t_Diagnostic  VALUES (31, 7, 'D23');
INSERT INTO t_Diagnostic  VALUES (31, 8, 'D23');
INSERT INTO t_Diagnostic  VALUES (32, 1, 'D24');
INSERT INTO t_Diagnostic  VALUES (32, 3, 'D24');
INSERT INTO t_Diagnostic  VALUES (32, 13, 'D24');
INSERT INTO t_Diagnostic  VALUES (32, 4, 'D24');
INSERT INTO t_Diagnostic  VALUES (32, 5, 'D24');
INSERT INTO t_Diagnostic  VALUES (32, 6, 'D24');
INSERT INTO t_Diagnostic  VALUES (32, 7, 'D24');
INSERT INTO t_Diagnostic  VALUES (33, 1, 'D25');
INSERT INTO t_Diagnostic  VALUES (33, 13, 'D25');
INSERT INTO t_Diagnostic  VALUES (33, 4, 'D25');
INSERT INTO t_Diagnostic  VALUES (33, 7, 'D25');
INSERT INTO t_Diagnostic  VALUES (33, 8, 'D25');
INSERT INTO t_Diagnostic  VALUES (34, 1, 'D26');
INSERT INTO t_Diagnostic  VALUES (34, 13, 'D26');
INSERT INTO t_Diagnostic  VALUES (34, 4, 'D26');
INSERT INTO t_Diagnostic  VALUES (34, 7, 'D26');
INSERT INTO t_Diagnostic  VALUES (34, 8, 'D26');
