drop table soigne;
drop table hospitalisation;
drop table malade;
drop table chambre;
drop table infirmier;
drop table service;
drop table docteur;
drop table employe;


create table employe (
        numero decimal (4) primary key,
        nom varchar (12) not null,
        prenom varchar (12) not null,
        adresse varchar (40),
        tel char (14),
        unique (nom, prenom, tel) ) ;

insert into employe values
        (19,'Safin','Marat','61 rue Fermee, 78430 Louveciennes','01 06 70 38 90');
insert into employe values
        (34,'Dechy','Nathalie','6 Bd. Aristide Briand, 78870 Bailly','01 13 71 36 65');
insert into employe values
        (80,'Hewitt','Lleyton','25 rue de Jouques, 78870 Bailly','01 29 52 84 64');
insert into employe values
        (10,'Ferrer','David','21 Bd. Jean Jaures, 78310 Maurepas','01 03 71 38 64');
insert into employe values
        (26,'Hantuchova','Daniela','28 rue Buscaille, 78000 Versailles','01 10 70 09 57');
insert into employe values
        (53,'Hingis','Martina','3 rue Gaston de Saporta, 78870 Bailly','01 17 71 19 57');
insert into employe values
        (64,'Henin','Justine','1 cours Gambetta, 78660 Ablis','01 20 71 11 93');
insert into employe values
        (85,'Mathieu','Paul-Henri','63 rue de l''Opera, 78000 Versailles','01 32 43 06 65');
insert into employe values
        (88,'Rochus','Olivier','40 rue Marcel Guillaume, 78870 Bailly','01 33 71 22 90');
insert into employe values
        (113,'Williams','Venus','5 rue de l''Annonciade, 78310 Maurepas','01 52 53 35 74');
insert into employe values
        (114,'Monfils','Gael','33 rue Littera, 78240 Chambourcy','01 53 34 45 65');
insert into employe values
        (126,'Djokovic','Novak','35 Av. des Thermes, 78990 Elancourt','01 58 30 36 63');
insert into employe values
        (135,'Gasquet','Richard','22 rue Boulegon, 78300 Poissy','01 66 55 11 60');
insert into employe values
        (12,'Davenport','Lindsay','56 rue des Muletiers, 78660 Ablis','01 04 70 01 65');
insert into employe values
        (15,'Haas','Tommy','68 Av. Pasteur, 78000 Versailles','01 05 70 39 78');
insert into employe values
        (22,'Gicquel','Marc','67 rue de la Fontaine, 78300 Poissy','01 08 70 19 78');
insert into employe values
        (25,'Benneteau','Julien','32 rue Signoret, 78430 Louveciennes','01 09 70 24 85');
insert into employe values
        (29,'Pavel','Andrei','5 rue du Plan, 78300 Poissy','01 10 70 10 74');
insert into employe values
        (45,'Schnyder','Patty','50 rue Villars, 78130 Les Mureaux','01 15 71 25 67');
insert into employe values
        (57,'Pennetta','Flavia','48 rue de l''Ecole, 78120 Rambouillet','01 18 60 41 33');
insert into employe values
        (71,'Berdych','Tomas','29 rue Lieutaud, 78430 Louveciennes','01 23 71 09 99');
insert into employe values
        (73,'Cornet','Alize','24 rue des Bremondis, 78000 Versailles','01 25 71 11 66');
insert into employe values
        (95,'Sugiyama','Ai','34 rue des Carmes, 78000 Versailles','01 38 62 11 31');
insert into employe values
        (97,'Canas','Guillermo','51 rue Villevieille, 78120 Rambouillet','01 38 71 12 60');
insert into employe values
        (127,'Clement','Arnaud','10 rue Granet, 78000 Versailles','01 61 47 28 67');
insert into employe values
        (139,'Molik','Alicia','18 rue d''Italie, 78870 Bailly','01 67 36 09 91');
insert into employe values
        (162,'Williams','Serena','27 rue Boueno Carriero, 78310 Maurepas','01 89 53 35 89');
insert into employe values
        (8,'Grosjean','Sebastien','57 Av. Napoleon Bonaparte, 78190 Trappes','01 25 60 00 87');
insert into employe values
        (122,'Mauresmo','Amelie','39 cours de la Trinite, 78000 Versailles','01 22 47 75 89');
insert into employe values
        (4,'Nadal','Rafael','18 place Bellegarde, 78990 Elancourt','01 29 71 13 76') ;

insert into employe values
        (82,'Dementieva','Elena','52 rue de la Molle, 78240 Chambourcy','01 29 72 72 48');
insert into employe values
        (99,'Federer','Roger','70 rue Paul Doumer, 78310 Maurepas','01 39 71 39 66');
insert into employe values
        (180,'Nalbandian','David','46 Rue Courteissade, 78310 Maurepas','01 94 53 22 54');
insert into employe values
        (24,'Zvonareva','Vera','11 place des Augustins, 78300 Poissy','01 08 71 38 93');
insert into employe values
        (31,'Stepanek','Radek','33 rue Cardinale, 78000 Versailles','01 11 71 87 71');
insert into employe values
        (54,'Andreev','Igor','2 rue Pierre et Marie Curie, 78530 Buc','01 17 71 77 33');
insert into employe values
        (89,'Moya','Carlos','21 rue Roux Alpheran, 78990 Elancourt','01 34 71 09 56');
insert into employe values
        (140,'Dulko','Gisela','22 rue des chapeliers, 78112 Fourqueux','01 69 73 18 64');
insert into employe values
        (141,'Golovin','Tatiana','8 place des Precheurs, 78300 Poissy','01 70 73 51 79');
insert into employe values
        (144,'Ivanovic','Ana','4 rue Gianotti, 78240 Chambourcy','01 71 47 11 89');
insert into employe values
        (152,'Murray','Andy','6 rue de la Glaciere, 78280 Guyancourt','01 80 54 56 25');
insert into employe values
        (179,'Santoro','Fabrice','52 rue des Epinaux, 78310 Maurepas','01 93 70 51 41');
insert into employe values
        (196,'Kuznetsova','Svetlana','12 rue du Griffon, 78300 Poissy','01 99 70 13 51');
insert into employe values
        (49,'Kremer','Anne','15 rue Baratanque, 78130 Les Mureaux','01 17 37 09 65');
insert into employe values
        (86,'Vaidisova','Nicole','69 rue Paul Bert, 78870 Bailly','01 32 70 13 51');
insert into employe values
        (98,'Lopez','Feliciano','39 rue Chastel, 78190 Trappes','01 39 71 19 90');
insert into employe values
        (116,'Ancic','Mario','47 rue des Menudieres, 78870 Bailly','01 54 53 00 90');
insert into employe values
        (130,'Ljubicic','Ivan','9 rue Goyrand, 78120 Rambouillet','01 64 73 66 30');
insert into employe values
        (131,'Harkleroad','Ashley','64 rue Finsonius, 78000 Versailles','01 66 43 02 49');
insert into employe values
        (151,'Baghdatis','Marcos','58 rue Fauchier, 78990 Elancourt','01 80 43 39 73');
insert into employe values
        (155,'Simon','Gilles','42 rue des Marseillais, 78190 Trappes','01 82 53 18 73');
insert into employe values
        (163,'Bartoli','Marion','8 rue Gontard, 78112 Fourqueux','01 89 53 56 26');
insert into employe values
        (169,'Roddick','Andy','62 rue Fernand Dol, 78530 Buc','01 91 53 36 33');
insert into employe values
        (176,'Serra','Florent','50 place Miollis, 78300 Poissy','01 92 71 13 64');
insert into employe values
        (189,'Razzano','Virginie','29 cours Saint Louis, 78660 Ablis','01 96 37 14 82');
insert into employe values
        (194,'Blake','James','33 rue Suffren, 78310 Maurepas','01 98 53 39 89');
insert into employe values
        (195,'Mayer','Florian','54 rue de Montigny, 78000 Versailles','01 99 61 09 68');
insert into employe values
        (7,'Bjorkman','Jonas','59 rue du Felibre Gault, 78310 Maurepas','01 91 74 00 55');
insert into employe values
        (50,'Parmentier','Pauline','4 place des Tanneurs, 78130 Les Mureaux','01 80 53 13 30');
insert into employe values
        (27,'Spadea','Vincent','32 place des Cardeurs, 78000 Versailles','01 55 54 51 79') ;

create table docteur (
        numero decimal (4) primary key,
    specialite varchar2(32) not null,
        foreign key (numero) references employe(numero) ) ;

insert into docteur values (19,'Traumatologue');
insert into docteur values (34,'Pneumologue');
insert into docteur values (80,'Cardiologue');
insert into docteur values (10,'Cardiologue');
insert into docteur values (26,'Orthopediste');
insert into docteur values (53,'Traumatologue');
insert into docteur values (64,'Radiologue');
insert into docteur values (85,'Anesthesiste');
insert into docteur values (88,'Cardiologue');
insert into docteur values (113,'Pneumologue');
insert into docteur values (114,'Traumatologue');
insert into docteur values (126,'Radiologue');
insert into docteur values (135,'Anesthesiste');
insert into docteur values (8,'Cardiologue');
insert into docteur values (122,'Pneumologue');
insert into docteur values (4,'Orthopediste') ;
insert into docteur values (24,'Orthopediste');
insert into docteur values (31,'Anesthesiste');
insert into docteur values (54,'Pneumologue');
insert into docteur values (82,'Orthopediste');
insert into docteur values (89,'Radiologue');
insert into docteur values (99,'Anesthesiste');
insert into docteur values (140,'Cardiologue');
insert into docteur values (141,'Traumatologue');
insert into docteur values (144,'Radiologue');
insert into docteur values (152,'Cardiologue');
insert into docteur values (179,'Anesthesiste');
insert into docteur values (180,'Cardiologue');
insert into docteur values (196,'Traumatologue');
insert into docteur values (7,'Cardiologue');
insert into docteur values (50,'Pneumologue');
insert into docteur values (27,'Orthopediste') ;

create table service (
        code varchar (3) primary key,
        nom varchar (30) not null,
        batiment char (1) not null,
        directeur decimal (4),
        foreign key (directeur) references docteur (numero)) ;

insert into service values ('REA','Reanimation et Traumatologie','A',19);
insert into service values ('CHG','Chirurgie generale','A',34);
insert into service values ('CAR','Cardiologie','B',80);

create table infirmier (
        numero decimal (4) primary key,
        code_service varchar2 (3),
    rotation varchar(4),
        salaire decimal (6,2),
        foreign key (numero) references employe (numero),
        foreign key (code_service) references service (code)  ) ;

insert into infirmier values
        (12,'REA','JOUR',1256.78);
insert into infirmier values
        (15,'CHG','JOUR',1178.48);
insert into infirmier values
        (22,'REA','JOUR',1498.21);
insert into infirmier values
        (25,'CAR','JOUR',1574.25);
insert into infirmier values
        (29,'CHG','JOUR',1358.45);
insert into infirmier values
        (45,'CAR','JOUR',1465.25);
insert into infirmier values
        (57,'CHG','JOUR',1765.21);
insert into infirmier values
        (71,'CAR','NUIT',1335.86);
insert into infirmier values
        (73,'REA','NUIT',1473.29);
insert into infirmier values
        (95,'CAR','NUIT',1947.61);
insert into infirmier values
        (97,'CHG','NUIT',1184.26);
insert into infirmier values
        (127,'REA','NUIT',1267.38);
insert into infirmier values
        (139,'CHG','NUIT',2034.82);
insert into infirmier values
        (162,'CHG','NUIT',1381.29) ;

insert into infirmier values
        (49,'REA','JOUR',1256.78);
insert into infirmier values
        (86,'CHG','JOUR',1178.48);
insert into infirmier values
        (98,'CAR','JOUR',1498.21);
insert into infirmier values
        (116,'REA','JOUR',1574.25);
insert into infirmier values
        (130,'CHG','JOUR',1358.45);
insert into infirmier values
        (131,'CAR','JOUR',1465.25);
insert into infirmier values
        (151,'CHG','JOUR',1765.21);
insert into infirmier values
        (155,'CHG','NUIT',1335.86);
insert into infirmier values
        (163,'CAR','NUIT',1473.29);
insert into infirmier values
        (169,'CAR','NUIT',1947.61);
insert into infirmier values
        (176,'CHG','NUIT',1184.26);
insert into infirmier values
        (189,'REA','NUIT',1267.38);
insert into infirmier values
        (194,'CHG','NUIT',2034.82);
insert into infirmier values
    (195,'CHG','NUIT',1381.29) ;

create table chambre (
        code_service varchar2 (3),
        no_chambre decimal (3),
        primary key (code_service, no_chambre),
        surveillant decimal (4),
        nb_lits decimal (2),
        foreign key (code_service) references service (code),
        foreign key (surveillant) references infirmier (numero)) ;

insert into chambre values
        ('REA',101,12,1);
insert into chambre values
        ('REA',102,12,1);
insert into chambre values
        ('REA',103,22,2);
insert into chambre values
        ('REA',104,22,2);
insert into chambre values
        ('CHG',201,29,4);
insert into chambre values
        ('CHG',202,29,4);
insert into chambre values
        ('CHG',301,57,2);
insert into chambre values
        ('CHG',302,57,2);
insert into chambre values
        ('CHG',303,57,1);
insert into chambre values
        ('CAR',101,95,3);
insert into chambre values
        ('CAR',102,95,2);
insert into chambre values
        ('CAR',103,95,1) ;

insert into chambre values
        ('REA',105,49,1);
insert into chambre values
        ('REA',106,49,1);
insert into chambre values
        ('REA',107,49,2);
insert into chambre values
        ('REA',108,116,2);
insert into chambre values
        ('CHG',401,130,4);
insert into chambre values
        ('CHG',402,130,4);
insert into chambre values
        ('CHG',403,151,2);
insert into chambre values
        ('CHG',404,151,2);
insert into chambre values
        ('CHG',405,151,1);
insert into chambre values
        ('CAR',104,169,3);
insert into chambre values
        ('CAR',105,169,2);
insert into chambre values
        ('CAR',106,169,1) ;

create table malade (
        numero decimal (4) primary key,
        nom varchar2 (12) not null,
        prenom varchar2 (12) not null,
        adresse varchar2 (40),
        tel varchar2 (14),
    mutuelle varchar2 (6)) ;

insert into malade values
    (1,'Querrey','Sam','43 rue Vauvenargues, 78000 Versailles','01 52 53 23 82','MNAM');
insert into malade values
    (3,'Paszek','Tamira','66 impasse du Paradis, 78300 Poissy','01 89 61 09 67','LMDE');
insert into malade values
    (6,'Osterloh','Lilia','43 rue de la Masse, 78310 Maurepas','01 66 55 77 29','MNH');
insert into malade values
    (13,'Gallovits','Edina','9 rue du Puits Juif, 78130 Les Mureaux','01 17 73 13 69','MAAF');
insert into malade values
    (14,'Rodionova','Anastasia','50 Bd. Emile Zola, 78240 Chambourcy','01 95 53 20 73','MGEN');
insert into malade values
        (21,'Sela','Dudi','6 Bd. des Poilus, 78630 Orgeval','01 04 71 42 63','MMA');
insert into malade values
        (23,'Bammer','Sybille','35 rue Loubon, 78990 Elancourt','01 96 37 35 60','CNAMTS');
insert into malade values
    (33,'Volandri','Filippo','25 rue des Bretons, 78430 Louveciennes','01 81 62 11 89','CCVRP');
insert into malade values
        (35,'Ventura','Santiago','46 place de Verdun, 78990 Elancourt','01 91 61 35 69','MNFTC');
insert into malade values
        (36,'Horna','Luis','38 place des Chapeliers, 78530 Buc','01 66 55 11 67','MAS');
insert into malade values
        (37,'Almagro','Nicolas','22 place Jeanne d''Arc, 78770 Auteuil','01 29 70 14 85','AG2R');
insert into malade values
        (41,'Zabaleta','Mariano','10 rue Aude, 78630 Orgeval','01 77 58 10 55','MGSP');
insert into malade values
    (43,'Poutchek','Tatiana','34 rue Loubet, 78000 Versailles','01 48 71 72 65','MNAM');
insert into malade values
        (44,'Fish','Mardy','63 place Ferreol, 78310 Maurepas','01 21 47 03 27','LMDE');
insert into malade values
        (46,'Cilic','Marin','26 Av. Jules Isaac, 78000 Versailles','01 07 72 15 93','MNH');
insert into malade values
        (52,'Hanescu','Victor','12 rue Vieille, 78000 Versailles','01 04 70 13 38','MAAF');
insert into malade values
        (55,'Falla','Alejandro','37 rue Tournefort, 78310 Maurepas','01 55 54 12 55','MGEN');
insert into malade values
        (56,'Monaco','Juan','16 place Richelme, 78530 Buc','01 27 37 25 94','MMA');
insert into malade values
    (60,'Hernandez','Oscar','2 rue Ganay, 78770 Auteuil','01 96 53 11 31','CNAMTS');
insert into malade values
    (61,'Gonzalez','Fernando','20 Bd. du Roi Rene, 78120 Rambouillet','01 18 71 36 73','CCVRP');
insert into malade values
    (63,'Peer','Shahar','41 place de L''Universite, 78300 Poissy','01 42 62 79 73','MNFTC');
insert into malade values
    (65,'Craybas','Jill','49 rue Victor Leydet, 78000 Versailles','01 13 71 28 05','MAS');
insert into malade values
    (66,'Cibulkova','Dominika','42 rue Clovis Hugues, 78000 Versailles','01 54 53 79 30','AG2R');
insert into malade values
    (67,'Errani','Sara','53 rue Monclar, 78430 Louveciennes','01 92 61 05 20','MGSP');
insert into malade values
    (68,'Sequera','Milagros','8 rue d''Arpille, 78300 Poissy','01 92 31 76 31','MNAM');
insert into malade values
    (70,'Tipsarevic','Janko','20 rue Jaubert, 78660 Ablis','01 23 47 77 96','LMDE');
insert into malade values
    (72,'Peng','Shuai','70 place Forbin, 78120 Rambouillet','01 66 43 42 66','MNH');
insert into malade values
    (74,'Capdeville','Paul','41 rue Clemenceau, 78120 Rambouillet','01 20 72 05 93','MAAF');
insert into malade values
        (75,'Gulbis','Ernests','46 rue Mejanes, 78000 Versailles','01 96 37 01 55','MGEN');
insert into malade values
    (76,'Govortsova','Olga','3 place Ganay, 78120 Rambouillet','01 77 58 44 41','MMA');
insert into malade values
    (77,'Obziler','Tzipora','19 Chemin Robert, 78310 Maurepas','01 35 71 31 74','CNAMTS');
insert into malade values
    (78,'Kanepi','Kaia','7 place d''Armenie, 78990 Elancourt','01 17 73 18 65','CCVRP');
insert into malade values
        (79,'Knapp','Karin','37 rue des Magnans, 78310 Maurepas','01 83 73 09 05','MNFTC');
insert into malade values
    (81,'Wozniacki','Caroline','64 cours d''Orbitelle, 78310 Maurepas','01 77 58 00 64','MAS');
insert into malade values
    (90,'Meusburger','Yvonne','13 rue des Guerriers, 78770 Auteuil','01 43 62 13 66','AG2R');
insert into malade values
    (91,'Brzezicki','Juan Pablo','17 Av. des Belges, 78300 Poissy','01 28 51 35 65','MGSP');
insert into malade values
    (92,'Massu','Nicolas','23 allee Rufinus, 78000 Versailles','01 28 70 11 63','MNAM');
insert into malade values
    (100,'Verdasco','Fernando','55 rue des Etuves, 78990 Elancourt','01 64 47 37 26','LMDE');
insert into malade values
    (101,'Melzer','Jurgen','44 rue Matheron, 78660 Ablis','01 91 43 51 66','MNH');
insert into malade values
        (102,'Poutchkova','Olga','7 rue des Gondraux, 78000 Versailles','01 03 71 02 73','MAAF') ;

insert into malade values
    (103,'Mahut','Nicolas','67 Av. du Parc, 78000 Versailles','01 14 71 01 26','MNAM');
insert into malade values
    (104,'Davydenko','Nikolay','34 rue des Tanneurs, 78770 Auteuil','01 91 43 35 67','LMDE');
insert into malade values
    (105,'Tursunov','Dmitry','58 rue Nazareth, 78870 Bailly','01 15 45 22 98','MNH');
insert into malade values
    (107,'Azarenka','Victoria','48 rue Merindol, 78990 Elancourt','01 14 71 79 67','MAAF');
insert into malade values
        (108,'Muller','Martina','5 rue Gibelin, 78112 Fourqueux','01 16 71 12 54','MGEN');
insert into malade values
    (109,'Calleri','Agustin','21 rue du Bon Pasteur, 78300 Poissy','01 52 53 11 55','MMA');
insert into malade values
        (117,'Ascione','Thierry','31 cours Sextius, 78240 Chambourcy','01 57 31 38 89','CNAMTS');
insert into malade values
    (119,'Schiavone','Francesca','28 rue Saint Joseph, 78310 Maurepas','01 89 61 00 41','CCVRP');
insert into malade values
    (120,'Malek','Tatjana','13 place Ramus, 78000 Versailles','01 81 53 12 57','MNFTC');
insert into malade values
        (121,'Eschauer','Werner','36 rue Thiers, 78430 Louveciennes','01 23 47 11 54','MAS');
insert into malade values
        (123,'Vesnina','Elena','51 cours Mirabeau, 78112 Fourqueux','01 94 70 38 67','AG2R');
insert into malade values
        (124,'Russell','Michael','31 rue du Cancel, 78112 Fourqueux','01 14 71 09 56','MGSP');
insert into malade values
    (128,'Srebotnik','Katarina','45 rue Venel, 78000 Versailles','01 35 71 87 41','MNAM');
insert into malade values
    (133,'Jankovic','Jelena','13 rue de l''Aumonerie, 78990 Elancourt','01 45 28 36 86','LMDE');
insert into malade values
    (137,'Berlocq','Carlos','49 rue Mignet, 78310 Maurepas','01 48 71 38 67','MNH');
insert into malade values
    (138,'Morigami','Akiko','14 Av. Giuseppe Verdi, 78990 Elancourt','01 28 47 97 93','MAAF');
insert into malade values
    (145,'Dushevina','Vera','11 Av. Grassi, 78280 Guyancourt','01 44 39 39 68','MGEN');
insert into malade values
    (146,'Mirza','Sania','37 rue Chabrier, 78870 Bailly','01 18 71 19 26','MMA');
insert into malade values
        (147,'Ditty','Julie','71 rue Pavillon, 78990 Elancourt','01 25 71 23 10','CNAMTS');
insert into malade values
        (148,'Dellacqua','Casey','38 Av. Malherbe, 78530 Buc','01 96 37 79 10','CCVRP');
insert into malade values
    (149,'Acasuso','Jose','16 rue Bedarrides, 78660 Ablis','01 01 70 79 73','MNFTC');
insert into malade values
        (153,'Youzhny','Mikhail','48 Av. Victor Hugo, 78130 Les Mureaux','01 72 59 02 57','MAS');
insert into malade values
    (154,'Seppi','Andreas','62 rue du Onze Novembre, 78300 Poissy','01 14 71 00 77','AG2R');
insert into malade values
    (158,'Shvedova','Yaroslava','60 rue des Nobles, 78120 Rambouillet','01 21 47 10 64','MGSP');
insert into malade values
    (159,'Vakulenko','Julia','19 Av. Benjamin Abram, 78530 Buc','01 19 71 12 10','MNAM');
insert into malade values
    (164,'Krajicek','Michaella','25 rue Saint Henri, 78310 Maurepas','01 25 71 10 92','LMDE');
insert into malade values
    (166,'Nakamura','Aiko','45 rue Mazarine, 78430 Louveciennes','01 08 70 56 87','MNH');
insert into malade values
        (167,'Santangelo','Mara','15 impasse Reynaud, 78310 Maurepas','01 03 70 01 25','MAAF');
insert into malade values
        (168,'Bondarenko','Kateryna','36 rue Celony, 78630 Orgeval','01 95 70 00 05','MGEN');
insert into malade values
    (171,'Pashanski','Boris','30 rue Campra, 78630 Orgeval','01 15 35 71 91','MMA');
insert into malade values
    (172,'Karlovic','Ivo','17 rue Rifle Rafle, 78112 Fourqueux','01 55 52 37 64','CNAMTS');
insert into malade values
    (175,'Dancevic','Frank','27 rue de Lacepede, 78000 Versailles','01 54 53 76 92','CCVRP');
insert into malade values
    (181,'Robredo','Tommy','49 rue Emeric David, 78870 Bailly','01 94 70 00 05','MNFTC');
insert into malade values
        (182,'Petrova','Nadia','30 rue Sallier, 78000 Versailles','01 28 51 33 93','MAS');
insert into malade values
    (184,'Bondarenko','Alona','72 rue Peyresc, 78130 Les Mureaux','01 71 47 72 26','AG2R');
insert into malade values
        (187,'Garbin','Tathiana','66 rue de la Fonderie, 78630 Orgeval','01 17 37 13 54','MGSP');
insert into malade values
        (188,'Vanek','Jiri','14 rue des Bagniers, 78240 Chambourcy','01 51 52 38 93','MNAM');
insert into malade values
    (190,'Petkovic','Andrea','18 rue Riquier, 78190 Trappes','01 55 52 39 65','LMDE');
insert into malade values
    (191,'Chan','Yung-Jan','65 rue Papassaudi, 78190 Trappes','01 82 53 39 63','MNH');
insert into malade values
        (192,'Luczak','Peter','39 rue Manuel, 78000 Versailles','01 96 37 48 73','MAAF') ;

create table hospitalisation (
        no_malade decimal (4) primary key,
        code_service varchar2 (3) not null,
        no_chambre decimal (3) not null,
        lit decimal (2),
        unique (code_service, no_chambre, lit),
        foreign key (no_malade) references malade (numero) ,
        foreign key (code_service) references service (code) ,
        foreign key (code_service, no_chambre) references chambre (code_service, no_chambre)  ) ;

insert into hospitalisation values
        (1,'REA',101,1);
insert into hospitalisation values
        (3,'REA',102,1);
insert into hospitalisation values
        (6,'REA',103,1);
insert into hospitalisation values
        (21,'REA',103,2);
insert into hospitalisation values
        (33,'REA',104,1);
insert into hospitalisation values
        (36,'REA',104,2);
insert into hospitalisation values
        (37,'CHG',201,1);
insert into hospitalisation values
        (41,'CHG',201,2);
insert into hospitalisation values
        (43,'CHG',201,3);
insert into hospitalisation values
        (46,'CHG',202,2);
insert into hospitalisation values
        (52,'CHG',202,3);
insert into hospitalisation values
        (55,'CHG',202,4);
insert into hospitalisation values
        (56,'CHG',301,1);
insert into hospitalisation values
        (61,'CHG',301,2);
insert into hospitalisation values
        (65,'CHG',302,1);
insert into hospitalisation values
        (66,'CHG',302,2);
insert into hospitalisation values
        (67,'CHG',303,1);
insert into hospitalisation values
        (68,'CAR',101,1);
insert into hospitalisation values
        (72,'CAR',101,3);
insert into hospitalisation values
        (74,'CAR',102,1);
insert into hospitalisation values
        (76,'CAR',102,2);
insert into hospitalisation values
        (77,'CAR',103,1) ;

insert into hospitalisation values
        (103,'REA',105,1);
insert into hospitalisation values
        (105,'REA',107,1);
insert into hospitalisation values
        (108,'REA',107,2);
insert into hospitalisation values
        (117,'REA',108,1);
insert into hospitalisation values
        (120,'CHG',401,1);
insert into hospitalisation values
        (123,'CHG',401,4);
insert into hospitalisation values
        (137,'CHG',402,1);
insert into hospitalisation values
        (145,'CHG',402,2);
insert into hospitalisation values
        (147,'CHG',402,3);
insert into hospitalisation values
        (149,'CHG',403,1);
insert into hospitalisation values
        (154,'CHG',403,2);
insert into hospitalisation values
        (159,'CHG',404,2);
insert into hospitalisation values
        (167,'CHG',405,1);
insert into hospitalisation values
        (172,'CAR',104,1);
insert into hospitalisation values
        (182,'CAR',104,3);
insert into hospitalisation values
        (188,'CAR',105,2);
insert into hospitalisation values
        (192,'CAR',106,1) ;

create table soigne (
        no_docteur decimal (4) not null,
        no_malade decimal (4) not null,
        primary key (no_docteur, no_malade),
        foreign key (no_docteur) references docteur (numero) ,
        foreign key (no_malade) references malade (numero) ) ;

insert into soigne values
        (10,72);
insert into soigne values
        (80,74);
insert into soigne values
        (126,74);
insert into soigne values
        (10,76);
insert into soigne values
        (80,76);
insert into soigne values
        (85,76);
insert into soigne values
        (64,76);
insert into soigne values
        (88,77);
insert into soigne values
        (126,77);
insert into soigne values
        (34,66);
insert into soigne values
        (113,67);
insert into soigne values
        (88,68);
insert into soigne values
        (64,68);
insert into soigne values
        (135,68);
insert into soigne values
        (34,65);
insert into soigne values
        (19,1);
insert into soigne values
        (85,1);
insert into soigne values
        (53,3);
insert into soigne values
        (85,3);
insert into soigne values
        (126,3);
insert into soigne values
        (114,6);
insert into soigne values
        (85,6);
insert into soigne values
        (135,6);
insert into soigne values
        (34,6);
insert into soigne values
        (19,21);
insert into soigne values
        (135,21);
insert into soigne values
        (64,21);
insert into soigne values
        (53,33);
insert into soigne values
        (135,33);
insert into soigne values
        (114,36);
insert into soigne values
        (135,36);
insert into soigne values
        (126,36);
insert into soigne values
        (113,37);
insert into soigne values
        (88,41);
insert into soigne values
        (113,41);
insert into soigne values
        (126,41);
insert into soigne values
        (26,43);
insert into soigne values
        (85,43);
insert into soigne values
        (53,46);
insert into soigne values
        (85,46);
insert into soigne values
        (88,52);
insert into soigne values
        (85,52);
insert into soigne values
        (34,52);
insert into soigne values
        (19,55);
insert into soigne values
        (135,55);
insert into soigne values
        (88,56);
insert into soigne values
        (135,56);
insert into soigne values
        (26,61);
insert into soigne values
        (135,61);
insert into soigne values
        (34,61);
insert into soigne values
        (10,75);
insert into soigne values
        (88,78);
insert into soigne values
        (26,79);
insert into soigne values
        (113,81);
insert into soigne values
        (53,90);
insert into soigne values
        (114,91);
insert into soigne values
        (10,92);
insert into soigne values
        (88,100);
insert into soigne values
        (26,101);
insert into soigne values
        (113,102);
insert into soigne values
        (53,70);
insert into soigne values
        (114,13);
insert into soigne values
        (10,14);
insert into soigne values
        (88,23);
insert into soigne values
        (26,35);
insert into soigne values
        (113,44);
insert into soigne values
        (53,60);
insert into soigne values
        (114,63);
insert into soigne values
        (8,13);
insert into soigne values
        (8,23);
insert into soigne values
        (8,35);
insert into soigne values
        (8,44);
insert into soigne values
        (122,70);
insert into soigne values
        (122,91);
insert into soigne values
        (122,102);
insert into soigne values
        (4,13);
insert into soigne values
        (4,23);
insert into soigne values
        (4,63);
insert into soigne values
        (4,78);
insert into soigne values
        (4,81);
insert into soigne values
        (4,100) ;

insert into soigne values
        (24,145);
insert into soigne values
        (24,147);
insert into soigne values
        (31,103);
insert into soigne values
        (31,145);
insert into soigne values
        (31,182);
insert into soigne values
        (54,120);
insert into soigne values
        (54,147);
insert into soigne values
        (82,108);
insert into soigne values
        (82,117);
insert into soigne values
        (82,137);
insert into soigne values
        (82,159);
insert into soigne values
        (89,103);
insert into soigne values
        (141,117);
insert into soigne values
        (89,123);
insert into soigne values
        (89,137);
insert into soigne values
        (89,147);
insert into soigne values
        (89,182);
insert into soigne values
        (99,108);
insert into soigne values
        (99,123);
insert into soigne values
        (99,172);
insert into soigne values
        (140,188);
insert into soigne values
        (140,192);
insert into soigne values
        (141,105);
insert into soigne values
        (141,147);
insert into soigne values
        (144,108);
insert into soigne values
        (144,120);
insert into soigne values
        (144,145);
insert into soigne values
        (144,154);
insert into soigne values
        (144,159);
insert into soigne values
        (144,192);
insert into soigne values
        (152,123);
insert into soigne values
        (152,149);
insert into soigne values
        (152,145);
insert into soigne values
        (152,167);
insert into soigne values
        (179,105);
insert into soigne values
        (179,117);
insert into soigne values
        (179,154);
insert into soigne values
        (179,192);
insert into soigne values
        (180,103);
insert into soigne values
        (180,105);
insert into soigne values
        (180,172);
insert into soigne values
        (180,182);
insert into soigne values
        (196,108);
insert into soigne values
        (196,117);
insert into soigne values
        (196,159);
insert into soigne values
        (140,104);
insert into soigne values
        (141,107);
insert into soigne values
        (89,107);
insert into soigne values
        (7,109);
insert into soigne values
        (50,119);
insert into soigne values
        (144,119);
insert into soigne values
        (7,119);
insert into soigne values
        (27,121);
insert into soigne values
        (140,124);
insert into soigne values
        (141,128);
insert into soigne values
        (27,128);
insert into soigne values
        (7,133);
insert into soigne values
        (50,138);
insert into soigne values
        (27,146);
insert into soigne values
        (89,146);
insert into soigne values
        (140,148);
insert into soigne values
        (141,153);
insert into soigne values
        (144,153);
insert into soigne values
        (7,158);
insert into soigne values
        (50,164);
insert into soigne values
        (27,164);
insert into soigne values
        (27,166);
insert into soigne values
        (140,168);
insert into soigne values
        (141,171);
insert into soigne values
        (50,171);
insert into soigne values
        (7,175);
insert into soigne values
        (50,181);
insert into soigne values
        (144,181);
insert into soigne values
        (27,184);
insert into soigne values
        (144,184);
insert into soigne values
        (141,184);
insert into soigne values
        (140,187);
insert into soigne values
        (141,190);
insert into soigne values
        (7,191);
insert into soigne values
        (140,172);
insert into soigne values
        (196,172);
insert into soigne values
        (152,159) ;
