------------------------------------------------------------
--        Script SQLite  
------------------------------------------------------------


------------------------------------------------------------
-- Table: MEDICAMENT
------------------------------------------------------------
CREATE TABLE MEDICAMENT(
	code      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
	libele    TEXT NOT NULL
);


------------------------------------------------------------
-- Table: MEDECIN
------------------------------------------------------------
CREATE TABLE MEDECIN(
	matricule      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
	nom_medicin    TEXT NOT NULL
);


------------------------------------------------------------
-- Table: SERVICE
------------------------------------------------------------
CREATE TABLE SERVICE(
	id                INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
	localisation      TEXT NOT NULL ,
	nom_Service       TEXT NOT NULL ,
	specialisation    TEXT NOT NULL
);


------------------------------------------------------------
-- Table: PATIENT
------------------------------------------------------------
CREATE TABLE PATIENT(
	no_Patient     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
	nom_Patient    TEXT NOT NULL ,
	no_SS          INTEGER NOT NULL ,
	id             INTEGER

	,CONSTRAINT PATIENT_SERVICE_FK FOREIGN KEY (id) REFERENCES SERVICE(id)
);


------------------------------------------------------------
-- Table: CONSULTATION
------------------------------------------------------------
CREATE TABLE CONSULTATION(
	no_Consultation      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
	date_Consultation    NUMERIC NOT NULL ,
	matricule            INTEGER NOT NULL ,
	no_Patient           INTEGER NOT NULL

	,CONSTRAINT CONSULTATION_MEDECIN_FK FOREIGN KEY (matricule) REFERENCES MEDECIN(matricule)
	,CONSTRAINT CONSULTATION_PATIENT0_FK FOREIGN KEY (no_Patient) REFERENCES PATIENT(no_Patient)
);


------------------------------------------------------------
-- Table: PRESCRIPTION
------------------------------------------------------------
CREATE TABLE PRESCRIPTION(
	id_Prescription    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
	nb_prises          INTEGER NOT NULL ,
	code               INTEGER NOT NULL ,
	no_Consultation    INTEGER NOT NULL

	,CONSTRAINT PRESCRIPTION_MEDICAMENT_FK FOREIGN KEY (code) REFERENCES MEDICAMENT(code)
	,CONSTRAINT PRESCRIPTION_CONSULTATION0_FK FOREIGN KEY (no_Consultation) REFERENCES CONSULTATION(no_Consultation)
	,CONSTRAINT PRESCRIPTION_MEDICAMENT_AK UNIQUE (code)
);


------------------------------------------------------------
-- Table: appartient
------------------------------------------------------------
CREATE TABLE appartient(
	id           INTEGER NOT NULL ,
	matricule    INTEGER NOT NULL,
	CONSTRAINT appartient_PK PRIMARY KEY (id,matricule)

	,CONSTRAINT appartient_SERVICE_FK FOREIGN KEY (id) REFERENCES SERVICE(id)
	,CONSTRAINT appartient_MEDECIN0_FK FOREIGN KEY (matricule) REFERENCES MEDECIN(matricule)
);


