create.sql 3.91 KB
Newer Older
Gaetan Carabetta's avatar
Gaetan Carabetta committed
1 2 3 4 5
CREATE TABLE ClasseEspece(
	nomClasse VARCHAR(30) NOT NULL,
	PRIMARY KEY(nomClasse)
); 

Gaetan Carabetta's avatar
Gaetan Carabetta committed
6 7 8 9 10 11
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,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
12 13 14
	numero INT NOT NULL,
	PRIMARY KEY(idClient),
  CHECK (numero >= 0100000000 AND numero <= 0799999999)
Gaetan Carabetta's avatar
Gaetan Carabetta committed
15 16 17 18 19 20 21 22
);

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,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
23
	numero INT NOT NULL,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
24
	specialite VARCHAR(30),
25
	PRIMARY KEY(idAssistant),
Gaetan Carabetta's avatar
Gaetan Carabetta committed
26 27
  FOREIGN KEY(specialite) REFERENCES ClasseEspece(nomClasse),
  CHECK (numero >= 0100000000 AND numero <= 0799999999)
Gaetan Carabetta's avatar
Gaetan Carabetta committed
28 29 30 31 32 33 34 35
); 

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,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
36
	numero INT NOT NULL,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
37
	specialite VARCHAR(30),
38
	PRIMARY KEY(idVeterinaire),
Gaetan Carabetta's avatar
Gaetan Carabetta committed
39 40
  FOREIGN KEY(specialite) REFERENCES ClasseEspece(nomClasse),
  CHECK (numero >= 0100000000 AND numero <= 0799999999)
Gaetan Carabetta's avatar
Gaetan Carabetta committed
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
); 

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 INT 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),
60 61
	FOREIGN KEY (especeNom) REFERENCES Espece(nomEspece),
	CHECK (POIDS > 0),
62
  CHECK (TAILLE > 0)
Gaetan Carabetta's avatar
Gaetan Carabetta committed
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
); 

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

CREATE TABLE Traitement(
	idTraitement INT NOT NULL,
	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,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
83
  PRIMARY KEY(nomEspece, nomMolec),
Gaetan Carabetta's avatar
Gaetan Carabetta committed
84
	FOREIGN KEY(nomEspece) REFERENCES Espece(nomEspece),
Gaetan Carabetta's avatar
Gaetan Carabetta committed
85
	FOREIGN KEY(nomMolec) REFERENCES Medicament(nomMolec)
Gaetan Carabetta's avatar
Gaetan Carabetta committed
86 87 88 89 90 91 92 93
); 

CREATE TABLE Posologie(
	idAnimal INT NOT NULL REFERENCES Animal(idAnimal),
	nomMolec VARCHAR(30) NOT NULL REFERENCES Medicament(nomMolec),
	debut DATE NOT NULL,
	duree INT NOT NULL,
	nbJourna INT NOT NULL,
94
	PRIMARY KEY(idAnimal, nomMolec, debut),
95 96
  CHECK (duree > 0),
  CHECK (nbJourna > 0)
Gaetan Carabetta's avatar
Gaetan Carabetta committed
97 98
); 

Gaetan Carabetta's avatar
Gaetan Carabetta committed
99 100
/* Methodes */
/* Non fonctionnelles */
Gaetan Carabetta's avatar
Gaetan Carabetta committed
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132
/*
CREATE FUNCTION naissance (Naissance date)
returns date
as
begin
  declare(@nais date)
  set @nais =
  case Naissance
    when Naissance then Naissance
    when NOT(Naissance) then NULL
  end--case
  return @nais
end;

CREATE FUNCTION espece_autoriser (Animal string, nomMolec string)
  return (
  SELECT CASE WHEN EXISTS (
  SELECT *
  FROM Espece_Med E JOIN Animal A
  ON E.nomEspece = A.especeNom
  AND A.idAnimal = Animal
  WHERE  E.nomMolec = nomMolec
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END -- CASE
)
END;

*/

/* Vues
Gaetan Carabetta's avatar
Gaetan Carabetta committed
133
vPersonne(Union(Projection(Client, nom, prenom, ddn, adresse, numero), Union(Projection(Assistant, nom, prenom, ddn, adresse, numero, specialite), Projection(Veterinaire, nom, prenom, ddn, adresse, numero, specialite))))
Gaetan Carabetta's avatar
Gaetan Carabetta committed
134 135
vPersonnel(Union(Projection(Assistant, nom, prenom, ddn, adresse, numero, specialite), Projection(Veterinaire, nom, prenom, ddn, adresse, numero, specialite)))
*/
Gaetan Carabetta's avatar
Gaetan Carabetta committed
136 137

/* à tester */
Gaetan Carabetta's avatar
Gaetan Carabetta committed
138 139 140

/*

141 142 143 144 145 146
CREATE OR REPLACE VIEW Personne AS
SELECT C.nom, C.prenom, C.ddn, C.adresse, C.numero
FROM Client C
UNION ALL
(
SELECT A.nom, A.prenom, A.ddn, A.adresse, A.numero
Gaetan Carabetta's avatar
Gaetan Carabetta committed
147 148
  FROM Assistant A
  UNION ALL
149
  SELECT V.nom, V.prenom, V.ddn, V.adresse, V.numero
Gaetan Carabetta's avatar
Gaetan Carabetta committed
150 151
  FROM Veterinaire V);

152 153
CREATE OR REPLACE VIEW Personnel AS
  SELECT A.nom, A.prenom, A.ddn, A.adresse, A.numero, A.specialite
Gaetan Carabetta's avatar
Gaetan Carabetta committed
154 155
  FROM Assistant A
  UNION ALL
156
  SELECT V.nom, V.prenom, V.ddn, V.adresse, V.numero, V.specialite
Gaetan Carabetta's avatar
Gaetan Carabetta committed
157 158 159
  FROM Veterinaire V;

  */