RO_2.sql 3.43 KB
Newer Older
Xiang Li's avatar
Xiang Li committed
1
2
3
4
5
6
7
8
CREATE OR REPLACE TYPE TClasse AS OBJECT (
nom VARCHAR(50)
);
/
CREATE TABLE Classe OF Tclasse (
PRIMARY KEY (nom)
);
/
Evenson Jeunesse's avatar
Evenson Jeunesse committed
9

Xiang Li's avatar
Xiang Li committed
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE TYPE TEspece AS OBJECT(
nom VARCHAR(50),
classe REF TClasse
);
/
CREATE TABLE Espece OF TEspece (
PRIMARY KEY (nom),
SCOPE FOR (classe) IS Classe
);
/
CREATE OR REPLACE TYPE TRefEspece AS OBJECT(espece REF TEspece);
/
CREATE TABLE RefEspece OF TRefEspece( SCOPE FOR (espece) IS Espece);
/
CREATE OR REPLACE TYPE ListRefEspece AS TABLE OF TRefEspece;
/
Evenson Jeunesse's avatar
Evenson Jeunesse committed
26

Xiang Li's avatar
Xiang Li committed
27

Xiang Li's avatar
Xiang Li committed
28
------------------------------------------------------
Xiang Li's avatar
Xiang Li committed
29
30


Xiang Li's avatar
Xiang Li committed
31
32
33
34
CREATE OR REPLACE TYPE TMedicament AS OBJECT(
nomMolecule VARCHAR(50),
effets VARCHAR(255),
especes ListRefEspece
Evenson Jeunesse's avatar
Evenson Jeunesse committed
35
);
Xiang Li's avatar
Xiang Li committed
36
37
38
39
40
41
42
43
44
/
CREATE TABLE Medicament OF TMedicament(
PRIMARY KEY (nomMolecule),
effets NOT NULL
) NESTED TABLE especes STORE AS NT_Medicament_Especes;
/
CREATE OR REPLACE TYPE TPrescription AS OBJECT(
  medicament REF TMedicament,
  quantiteJour integer
Evenson Jeunesse's avatar
Evenson Jeunesse committed
45
);
Xiang Li's avatar
Xiang Li committed
46
47
48
49
50
/
CREATE TABLE Prescription OF TPrescription(
quantiteJour NOT NULL,
CHECK(quantiteJour > 0),
SCOPE FOR (medicament) IS Medicament
Evenson Jeunesse's avatar
Evenson Jeunesse committed
51
);
Xiang Li's avatar
Xiang Li committed
52
53
54
55
56
57
58
59
60
61
62
63
/
CREATE OR REPLACE TYPE ListPrescription AS TABLE OF TPrescription;
/

------------------------------------------------------

CREATE OR REPLACE TYPE TTraitement AS OBJECT(
id integer,
nom varchar2(255),
debut date,
duree integer,
prescriptions ListPrescription
Evenson Jeunesse's avatar
Evenson Jeunesse committed
64
);
Xiang Li's avatar
Xiang Li committed
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
/
CREATE TABLE Traitement OF TTraitement(
  PRIMARY KEY (id),
  nom NOT NULL,
  debut NOT NULL,
  duree NOT NULL,
  CHECK (duree > 0)
) NESTED TABLE prescriptions STORE AS NT_Traitement_Prescriptions;

CREATE OR REPLACE TYPE TRefTraitement AS OBJECT(traitement REF TTraitement);
/
CREATE TABLE RefTraitement OF TRefTraitement( SCOPE FOR (traitement) IS Traitement);
/
CREATE OR REPLACE TYPE ListRefTraitement AS TABLE OF TRefTraitement;
/


 ----------------------------------------------------------------
CREATE OR REPLACE TYPE TClients AS OBJECT (
id INTEGER,
telephone INTEGER,
nom VARCHAR(50),
prenom VARCHAR(50),
date_naissance DATE,
adresse VARCHAR(255)
Xiang Li's avatar
Xiang Li committed
90
91
);

Xiang Li's avatar
Xiang Li committed
92
93
94
95
96
97
98
99
CREATE TABLE Clients OF TCLients(
PRIMARY KEY (id),
telephone NOT NULL,
nom NOT NULL,
prenom NOT NULL,
date_naissance NOT NULL,
adresse NOT NULL,
CHECK (telephone > 100000000 AND telephone < 999999999)
Xiang Li's avatar
Xiang Li committed
100
);
Xiang Li's avatar
Xiang Li 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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
/


 CREATE OR REPLACE TYPE TAnimal AS OBJECT (
 id INTEGER,
 nom VARCHAR(30),
 dernierPoids INTEGER,
 derniereTaille INTEGER,
 date_naissance INTEGER,
 espece REF TEspece,
 traitements ListRefTraitement,
client REF TClients
 );
/
CREATE TABLE Animal OF TAnimal(
nom NOT NULL,
dernierPoids NOT NULL,
derniereTaille NOT NULL,
date_naissance NOT NULL,
PRIMARY KEY (id),
CHECK (dernierPoids > 0),
CHECK (derniereTaille > 0),
client NOT NULL,
SCOPE FOR (client) IS Clients,
espece NOT NULL,
SCOPE FOR (espece) IS Espece
) NESTED TABLE traitements STORE AS NT_Animal_Traitements;
/

CREATE TABLE Veterinaires(
id INTEGER,
telephone INTEGER  NOT NULL,
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50) NOT NULL,
date_naissance  DATE NOT NULL,
adresse VARCHAR(255) NOT NULL,
specialite REF TClasse NOT NULL,
traitements ListRefTraitement,
PRIMARY KEY (id),
CHECK (telephone > 100000000 AND telephone < 999999999),
SCOPE FOR (specialite) IS Classe
) NESTED TABLE traitements STORE AS NT_Veterinaire_Traitements;
/

CREATE TABLE Assistants(
id INTEGER,
telephone INTEGER  NOT NULL,
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50) NOT NULL,
date_naissance  DATE NOT NULL,
adresse VARCHAR(255) NOT NULL,
specialite REF TClasse NOT NULL,
PRIMARY KEY (id),
CHECK (telephone > 100000000 AND telephone < 999999999),
SCOPE FOR (specialite) IS Classe
Evenson Jeunesse's avatar
Evenson Jeunesse committed
156
);
Xiang Li's avatar
Xiang Li committed
157
/