create_JSON.sql 840 Bytes
Newer Older
Gaetan Carabetta's avatar
Gaetan Carabetta committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE TABLE Client(
  idClient INT PRIMARY KEY,
  nom VARCHAR(30) NOT NULL,
  prenom VARCHAR(30) NOT NULL,
  ddn DATE NOT NULL,
  adresse VARCHAR(100) NOT NULL,
  numero INT NOT NULL,
  CHECK (numero >= 0100000000 AND numero <= 0799999999)
);

CREATE TABLE classeEspece(
  nomClasse VARCHAR(30) PRIMARY KEY 
);

CREATE TABLE Espece(
  classe VARCHAR(30) NOT NULL,
  nomEspece VARCHAR(30) PRIMARY KEY,
  FOREIGN KEY(classe) REFERENCES classeEspece(nomClasse)
);

CREATE TABLE Animal (
  idAnimal INT PRIMARY KEY,
  nom VARCHAR(30) NOT NULL,
  poids REAL NOT NULL,
  taille REAL NOT NULL,
  naissance DATE,
  traitements JSON NOT NULL,
  idClient INT NOT NULL,
  especeNom VARCHAR(30),
  FOREIGN KEY (idClient) REFERENCES Client(idClient),
  FOREIGN KEY (especeNom) REFERENCES Espece(nomEspece),
  CHECK (POIDS > 0),
  CHECK (TAILLE > 0)
);