CREATE TABLE Patients
(
 Nrsis NUMBER(11)
         CONSTRAINT CpNrsis PRIMARY KEY,
 Nom VARCHAR2(25)
         CONSTRAINT PatientsNom NOT NULL,
 Prenom VARCHAR2(25)
         CONSTRAINT PatientsPrenom NOT NULL,
 Sexe VARCHAR2(1)
         CONSTRAINT SexeCheck CHECK (Sexe IN ( 'F', 'M'))
         CONSTRAINT PatientsSexe NOT NULL,
 Groupesanguin VARCHAR(2)
         CONSTRAINT GroupesanguinCheck CHECK (Groupesanguin IN ('A', 'B', 'AB', 'O'))
         CONSTRAINT PatientsGroupesanguin NOT NULL,
 Datenais DATE,
 Hospitalise VARCHAR2(1) DEFAULT 'N'
         CONSTRAINT HospitaliseCheck CHECK (Hospitalise IN ('O', 'N'))
         CONSTRAINT PatientsHospitalise NOT NULL
);

CREATE TABLE Specialites
(
  Specialite VARCHAR2(20)
          CONSTRAINT CpSpecialite PRIMARY KEY,
  Libelle VARCHAR2(30)
);

CREATE TABLE Medecins
(
  NumOrdre VARCHAR2(20)
          CONSTRAINT CpNumOrdre PRIMARY KEY,
  Nom VARCHAR2(25)
                  CONSTRAINT MedecinsNom NOT NULL,
  Prenom VARCHAR2(25)
          CONSTRAINT MedecinsPrenom NOT NULL,
  Etatcivil VARCHAR2(12)
          CONSTRAINT MedecinsEtatcivil NOT NULL,
  Datediplome DATE,
  CptBancaire VARCHAR2(14),
  Specialite VARCHAR2(20),
          CONSTRAINT RefSpecialiteSpecialites FOREIGN KEY (Specialite)
          REFERENCES Specialites (Specialite)
);

CREATE TABLE Services
(
  Nrservice NUMBER(4)
          CONSTRAINT ServicesNrService CHECK (Nrservice BETWEEN 1 AND 9999)
          CONSTRAINT CpNrService PRIMARY KEY,
  Nomservice VARCHAR2(30)
          CONSTRAINT ServicesNomservice NOT NULL,
  Numchefservice VARCHAR(20),
          CONSTRAINT RefNumchefservice FOREIGN KEY (Numchefservice)
          REFERENCES Medecins (Numordre)
);

CREATE TABLE Chambres
(
  Nrservice NUMBER(4)
          CONSTRAINT ChambresNrservice CHECK (Nrservice BETWEEN 1 AND 9999),
  Nrchambre NUMBER(4)
          CONSTRAINT ChambresNrchambre CHECK (Nrchambre BETWEEN 1 AND 9999),
          CONSTRAINT CpChambres PRIMARY KEY (Nrchambre, Nrservice),
          CONSTRAINT RefNrservice FOREIGN KEY (Nrservice)
          REFERENCES Services (Nrservice)
);

CREATE TABLE Patientschambres
(
 Nrsis NUMBER(11),
 Nrservice NUMBER(4)
                 CONSTRAINT PatientschambresNrservice CHECK (Nrservice BETWEEN 1 AND 9999),
 Nrchambre NUMBER(4)
                 CONSTRAINT PatientschambresNrchambre CHECK (Nrchambre BETWEEN 1 AND 9999),
 Dateentree DATE,
 Datesortie DATE,
         CONSTRAINT CpPatientschambre PRIMARY KEY (Nrsis,Nrservice,Nrchambre,Dateentree),
         CONSTRAINT RefNrsis FOREIGN KEY (Nrsis)
         REFERENCES Patients (Nrsis),
         CONSTRAINT RefNrservice1 FOREIGN KEY (Nrservice)
         REFERENCES Services (Nrservice),
         CONSTRAINT RefNrchambre FOREIGN KEY (Nrchambre)
         REFERENCES Chambres (Nrchambre)
);