création du schéma relationnel
CREATE TABLE "Salarie" ( "code" serial(3) NOT NULL, "nom" serial(15) NOT NULL, "prenom" serial(15) NOT NULL, "adresse" serial NOT NULL, "telephone" serial(10) NOT NULL, CONSTRAINT "Salarie_pk" PRIMARY KEY ("code") ) WITH ( OIDS=FALSE );
CREATE TABLE "Service" ( "code" serial(3) NOT NULL, "libelle" serial(15) NOT NULL, CONSTRAINT "Service_pk" PRIMARY KEY ("code") ) WITH ( OIDS=FALSE );
CREATE TABLE "Travailler" ( "codeSalarie" varchar(3) NOT NULL, "codeService" serial(3) NOT NULL, "dateDebut" serial NOT NULL, CONSTRAINT "Travailler_pk" PRIMARY KEY ("codeSalarie","codeService","dateDebut") ) WITH ( OIDS=FALSE );
CREATE TABLE "situer" ( "codeService" serial NOT NULL, "codeBatiment" serial NOT NULL, CONSTRAINT "situer_pk" PRIMARY KEY ("codeService","codeBatiment") ) WITH ( OIDS=FALSE );
CREATE TABLE "batiment" ( "code" serial NOT NULL, "nom" serial(15) NOT NULL, CONSTRAINT "batiment_pk" PRIMARY KEY ("code") ) WITH ( OIDS=FALSE );
ALTER TABLE "Travailler" ADD CONSTRAINT "Travailler_fk0" FOREIGN KEY ("codeSalarie") REFERENCES "Salarie"("code"); ALTER TABLE "Travailler" ADD CONSTRAINT "Travailler_fk1" FOREIGN KEY ("codeService") REFERENCES "Service"("code");
ALTER TABLE "situer" ADD CONSTRAINT "situer_fk0" FOREIGN KEY ("codeService") REFERENCES "Service"("code"); ALTER TABLE "situer" ADD CONSTRAINT "situer_fk1" FOREIGN KEY ("codeBatiment") REFERENCES "batiment"("code");