You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
69 lines
2.5 KiB
69 lines
2.5 KiB
DROP TABLE PERSONNEL cascade constraints;
|
|
DROP TABLE AFFECTATION ;
|
|
|
|
DROP TABLE FONCTION cascade constraints;
|
|
DROP TABLE PATIENT cascade constraints;
|
|
DROP TABLE HOPITAL cascade constraints;
|
|
|
|
CREATE TABLE FONCTION(id_fonction CHAR(6) PRIMARY KEY,
|
|
Designation VARCHAR(20),
|
|
Salaire NUMBER);
|
|
|
|
CREATE TABLE HOPITAL(id_hopital CHAR(6) PRIMARY KEY,
|
|
Nom VARCHAR(20),
|
|
Ville VARCHAR(20),
|
|
Date_creation DATE);
|
|
|
|
|
|
CREATE TABLE PATIENT(id_patient CHAR(6) PRIMARY KEY,
|
|
Nom VARCHAR(20),
|
|
Prenom VARCHAR(20),
|
|
Date_naissance DATE);
|
|
|
|
CREATE TABLE PERSONNEL(id_personnel CHAR(6) PRIMARY KEY,
|
|
id_fonction CHAR(6) references FONCTION,
|
|
id_hopital CHAR(6) references HOPITAL,
|
|
Nom VARCHAR(20),
|
|
Prenom VARCHAR(20));
|
|
|
|
|
|
CREATE TABLE AFFECTATION(id_contrat CHAR(6) PRIMARY KEY,
|
|
id_personnel CHAR(6) references PERSONNEL,
|
|
id_patient CHAR(6) references PATIENT,
|
|
Date_debut DATE,
|
|
Date_fin DATE);
|
|
|
|
insert into HOPITAL values ('H01','CHU G MONPIED','CLERMONT','01-Jan-1980');
|
|
insert into HOPITAL values ('H02','CHU ESTAING','CLERMONT','01-Jan-2010');
|
|
insert into HOPITAL values ('H03','Chataigneraie','BEAUMONT','01-Jan-2000');
|
|
|
|
insert into FONCTION values ('F01','Docteur',8000);
|
|
insert into FONCTION values ('F02','Psy',11000);
|
|
insert into FONCTION values ('F03','ORL',10000);
|
|
|
|
insert into PERSONNEL values ('PE01','F03','H03','Who','John');
|
|
insert into PERSONNEL values ('PE02','F02','H01','Jekyll','Henry');
|
|
insert into PERSONNEL values ('PE03','F01','H02','Watson','James');
|
|
insert into PERSONNEL values ('PE04','F02','H03','Palmer','Jimmy');
|
|
insert into PERSONNEL values ('PE05','F01','H01','Mallard','Donald');
|
|
insert into PERSONNEL values ('PE06','F03','H02','Blacke','Penelope');
|
|
|
|
insert into PATIENT values ('PA01','Marley','Bob','04-Apr-2001');
|
|
insert into PATIENT values ('PA02','Hendrix','Jimmy','04-Apr-1998');
|
|
insert into PATIENT values ('PA03','Dupont','Paul','04-Apr-1990');
|
|
insert into PATIENT values ('PA04','Springsteen','Bruce','24-Apr-1990');
|
|
insert into PATIENT values ('PA05','Jackson','Michael','14-Apr-1990');
|
|
insert into PATIENT values ('PA06','Georges','Paul','04-Apr-1991');
|
|
|
|
insert into AFFECTATION values ('A01','PE01','PA01','04-Jan-1980',NULL);
|
|
insert into AFFECTATION values ('A02','PE02','PA02','04-Jan-1981',NULL);
|
|
insert into AFFECTATION values ('A03','PE03','PA03','04-Jan-1981','04-Jan-2010');
|
|
insert into AFFECTATION values ('A04','PE04','PA04','04-Jan-1981','04-Jan-2010');
|
|
insert into AFFECTATION values ('A05','PE05','PA05','04-Jan-1981','04-Jan-2010');
|
|
insert into AFFECTATION values ('A06','PE06','PA06','04-Jan-1981','04-Jan-2010');
|
|
|
|
|
|
|
|
|
|
|