CREATE SCHEMA Bibliotheque;
SET search_path TO Bibliotheque, public;
CREATE TABLE Bibliotheque.Tarifs (
  -- Typage des attributs
  NbEmpruntsAutorises integer CHECK (NbEmpruntsAutorises >= 0),
  Caution integer  CHECK (NbEmpruntsAutorises >= 0),
  -- Clefs candidates
  PRIMARY KEY (NbEmpruntsAutorises)
);
CREATE TABLE Bibliotheque.Adherents (
  -- Typage des attributs
  Id serial NOT NULL,        -- serial = sequence d'entier 1, 2, 3, ... 
  Nom text NOT NULL,
  Prenom text NOT NULL,
  NbEmpruntsAutorises integer NOT NULL,
  -- Clefs candidates
  PRIMARY KEY (id),
  -- Clefs etrangeres
  FOREIGN KEY (NbEmpruntsAutorises) REFERENCES Bibliotheque.Tarifs(NbEmpruntsAutorises)
);
CREATE TABLE Bibliotheque.Oeuvres (
  -- Typage des attributs
  ISBN text NOT NULL,
  Titre text NOT NULL,
  Editeur text NOT NULL,
  Auteur text NOT NULL,  -- Separer les auteurs par une virgule pour la lisibilite
  -- Clefs candidates
  PRIMARY KEY (ISBN)
);
CREATE TABLE Bibliotheque.Livres (
  -- Typage des attributs
  Id serial NOT NULL,        -- serial = sequence d'entier 1, 2, 3, ...
  ISBN text NOT NULL,
  DateAchat date NOT NULL,
  -- Clefs candidates
  PRIMARY KEY (Id),
  -- Clefs etrangeres
  FOREIGN KEY (ISBN) REFERENCES Bibliotheque.Oeuvres(ISBN)
);
CREATE TABLE Bibliotheque.Emprunts (
  -- Typage des attributs
  Livre integer NOT NULL,
  DateEmprunt date NOT NULL,
  DateRetour date NOT NULL DEFAULT 'infinity',
  Adherent integer NOT NULL,
  -- Clefs candidates
  PRIMARY KEY (Livre, DateEmprunt),
  UNIQUE (Livre, DateRetour),
  -- Clefs etrangeres
  FOREIGN KEY (Adherent) REFERENCES Bibliotheque.Adherents(Id),
  FOREIGN KEY (Livre) REFERENCES Bibliotheque.Livres(Id),
  -- Contraintes d'integrite elementaire
  CHECK (DateEmprunt < DateRetour)
);
