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;

  */