bd.sql 2.47 KB
Newer Older
Osvaldo Valdivia Salas's avatar
Osvaldo Valdivia Salas 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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
CREATE TABLE ClasseEspece(
	nomClasse VARCHAR(30) NOT NULL,
	PRIMARY KEY(nomClasse)
);

CREATE TABLE Client(
	idClient INT NOT NULL,
	nom VARCHAR(30) NOT NULL,
	prenom VARCHAR(30) NOT NULL,
	ddn DATE NOT NULL,
	adresse VARCHAR(100) NOT NULL,
	numero INT NOT NULL,
	PRIMARY KEY(idClient),
  CHECK (numero >= 0100000000 AND numero <= 0799999999)
);

CREATE TABLE Assistant(
	idAssistant INT NOT NULL,
	nom VARCHAR(30) NOT NULL,
	prenom VARCHAR(30) NOT NULL,
	ddn DATE NOT NULL,
	adresse VARCHAR(100) NOT NULL,
	numero INT NOT NULL,
	specialite VARCHAR(30),
	PRIMARY KEY(idAssistant),
  FOREIGN KEY(specialite) REFERENCES ClasseEspece(nomClasse),
  CHECK (numero >= 0100000000 AND numero <= 0799999999)
);

CREATE TABLE Veterinaire(
	idVeterinaire INT NOT NULL,
	nom VARCHAR(30) NOT NULL,
	prenom VARCHAR(30) NOT NULL,
	ddn DATE NOT NULL,
	adresse VARCHAR(100) NOT NULL,
	numero INT NOT NULL,
	specialite VARCHAR(30),
	PRIMARY KEY(idVeterinaire),
  FOREIGN KEY(specialite) REFERENCES ClasseEspece(nomClasse),
  CHECK (numero >= 0100000000 AND numero <= 0799999999)
);

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

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

CREATE TABLE Medicament(
	nomMolec VARCHAR(30) NOT NULL,
	description VARCHAR(200) NOT NULL,
	PRIMARY KEY(nomMolec)
);

CREATE TABLE Traitement(
Gaetan Carabetta's avatar
Gaetan Carabetta committed
72
	idTraitement SERIAL NOT NULL,
Osvaldo Valdivia Salas's avatar
Osvaldo Valdivia Salas committed
73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
	idAnimal INT NOT NULL,
	idVeterinaire INT NOT NULL,
	PRIMARY KEY(idTraitement),
	FOREIGN KEY(idVeterinaire) REFERENCES Veterinaire(idVeterinaire),
	FOREIGN KEY(idAnimal) REFERENCES Animal(idAnimal)
);

CREATE TABLE Espece_Med(
	nomEspece VARCHAR(30) NOT NULL,
	nomMolec VARCHAR(30) NOT NULL,
  PRIMARY KEY(nomEspece, nomMolec),
	FOREIGN KEY(nomEspece) REFERENCES Espece(nomEspece),
	FOREIGN KEY(nomMolec) REFERENCES Medicament(nomMolec)
);

CREATE TABLE Posologie(
Osvaldo Valdivia Salas's avatar
Osvaldo Valdivia Salas committed
89
	traitement INT NOT NULL REFERENCES Traitement(idTraitement),
Osvaldo Valdivia Salas's avatar
Osvaldo Valdivia Salas committed
90 91 92 93
	nomMolec VARCHAR(30) NOT NULL REFERENCES Medicament(nomMolec),
	debut DATE NOT NULL,
	duree INT NOT NULL,
	nbJourna INT NOT NULL,
Osvaldo Valdivia Salas's avatar
Osvaldo Valdivia Salas committed
94
	PRIMARY KEY(traitement, nomMolec, debut),
Osvaldo Valdivia Salas's avatar
Osvaldo Valdivia Salas committed
95 96 97
  CHECK (duree > 0),
  CHECK (nbJourna > 0)
);