microfolie/sql/create.sql

CREATE TABLE Espace 
  ( 
     ID      BIGINT NOT NULL PRIMARY KEY GENERATED always AS IDENTITY, 
     LIBELLE VARCHAR(250) NOT NULL, 
     CODE    VARCHAR(100) UNIQUE NOT NULL 
  );

CREATE TABLE Ville 
  ( 
     ID         BIGINT NOT NULL PRIMARY KEY GENERATED always AS IDENTITY, 
     LIBELLE    VARCHAR(250) NOT NULL, 
     CODEPOSTAL VARCHAR(5) 
  );

CREATE TABLE Niveau 
  ( 
     ID      BIGINT NOT NULL PRIMARY KEY GENERATED always AS IDENTITY, 
     LIBELLE VARCHAR(250) NOT NULL 
  );

CREATE TABLE Ecole 
  ( 
     ID      BIGINT NOT NULL PRIMARY KEY GENERATED always AS IDENTITY, 
     LIBELLE VARCHAR(250) NOT NULL, 
     VILLE   BIGINT, 
     NIVEAU  BIGINT, 
     CONSTRAINT fk_ecole_ville FOREIGN KEY (VILLE) REFERENCES Ville(ID), 
     CONSTRAINT fk_ecole_niveau FOREIGN KEY (NIVEAU) REFERENCES Niveau(ID) 
  );

CREATE TABLE Usager 
  ( 
     ID              BIGINT NOT NULL PRIMARY KEY GENERATED always AS IDENTITY, 
     NOM             VARCHAR(250) NOT NULL, 
     PRENOM          VARCHAR(250) NOT NULL, 
     GENRE           VARCHAR(100) NOT NULL,
     DATEDENAISSANCE DATE NOT NULL, 
     EMAIL           VARCHAR(250) NOT NULL, 
     TELEPHONE       VARCHAR(12) NOT NULL, 
     CONTACTURGENCE  VARCHAR(250) NOT NULL, 
     CODE            VARCHAR(4) UNIQUE NOT NULL, 
     SCOLAIRE        BOOLEAN NOT NULL, 
     DESCRIPTION     VARCHAR(250) NOT NULL, 
     VILLE           BIGINT, 
     ECOLE           BIGINT, 
     CONSTRAINT fk_usager_ville FOREIGN KEY (VILLE) REFERENCES Ville(ID), 
     CONSTRAINT fk_usager_ecole FOREIGN KEY (ECOLE) REFERENCES Ecole(ID) 
  );

CREATE TABLE Frequentation 
  ( 
     ID     BIGINT NOT NULL PRIMARY KEY GENERATED always AS IDENTITY, 
     ESPACE BIGINT, 
     USAGER BIGINT, 
     DATE   DATE NOT NULL, 
     CONSTRAINT fk_frequentation_espace FOREIGN KEY (ESPACE) REFERENCES Espace(ID), 
     CONSTRAINT fk_frequentation_usager FOREIGN KEY (USAGER) REFERENCES Usager(ID) 
  );