-- Creation	des	tables et contraintes
CREATE TABLE segment
(
	n_segment VARCHAR(10),
	nom_segment	VARCHAR(20),
	CONSTRAINT pk_segment PRIMARY KEY(n_segment)
);

CREATE TABLE salle
(
	n_salle VARCHAR(7),
	nom_s VARCHAR(20),
	nb_poste NUMBER,
	n_segment VARCHAR(10) CONSTRAINT nn_salle_seg NOT NULL,
	CONSTRAINT pk_salle	PRIMARY	KEY(n_salle),
	CONSTRAINT fk_salle_segment	FOREIGN	KEY(n_segment)
	REFERENCES segment(n_segment)
);

CREATE TABLE logiciel
(
	n_log VARCHAR(5),
	nom_l VARCHAR(20),
	date_ach DATE DEFAULT SYSDATE,
	version	VARCHAR(7),
	type_l VARCHAR(6),
	CONSTRAINT pk_logiciel PRIMARY KEY(n_log)
);

CREATE TABLE installer
(
	n_poste VARCHAR(7),
	n_log VARCHAR(5),
	date_ins DATE,
	CONSTRAINT pk_install PRIMARY KEY(n_poste,n_log),
	CONSTRAINT fk_install_poste	FOREIGN	KEY(n_poste)
	REFERENCES poste(n_poste),
	CONSTRAINT fk_install_log FOREIGN KEY(n_log)
	REFERENCES logiciel(n_log)
);

CREATE TABLE type
(
	typ_lp VARCHAR(6),
	nom_type VARCHAR(20),
	CONSTRAINT pk_type PRIMARY KEY(typ_lp)
);