Clinique_RO.sql 1.36 KB
Newer Older
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
1
CREATE OR REPLACE TYPE traitement AS OBJECT (
2
3
4
5
6
7
    id_traitement NUMBER,
    debut DATE,
    duree NUMBER,
    quantite NUMBER,
    veterinaire VARCHAR(30),
    medicament VARCHAR(30)
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
8
9
10
11
);
/


12
13
14
15
16
17
18
19
CREATE OR REPLACE TYPE numero AS OBJECT(
    numero VARCHAR(12)
);
/

CREATE OR REPLACE TYPE listNumeros AS TABLE OF numero;
/

Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
20
21
22
CREATE OR REPLACE TYPE listTraitements AS TABLE OF traitement;
/

23

24
CREATE OR REPLACE TABLE client (
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
25
26
27
28
29
    id_client NUMBER NOT NULL,
    nom VARCHAR(20) NOT NULL,
    prenom VARCHAR(20) NOT NULL,
    dn DATE NOT NULL,
    adresse VARCHAR(50) NOT NULL,
30
    numeros listNumeros NOT NULL,
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
31
    PRIMARY KEY(id_client)
32
)NESTED TABLE numeros STORE AS l_numeros;
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
33
34
35
36
37
38
39
40
41
42
43
44
45
/

CREATE OR REPLACE TABLE classeEspece(
    classeEscpece VARCHAR(30) NOT NULL PRIMARY KEY
);
/

CREATE OR REPLACE TABLE espece(
    classeEspece VARCHAR(30) NOT NULL,
    espece VARCHAR(20) NOT NULL,
    PRIMARY KEY(espece),
    FOREIGN KEY (classeEspece) REFERENCES classeEspece(classeEspece)
);
46
47
48
49
50
/

CREATE OR REPLACE TABLE animal(
    id_animal NUMBER NOT NULL,
    id_client NUMBER NOT NULL,
51
    espece VARCHAR(20),
52
53
54
55
56
57
    nom VARCHAR(20),
    poid FLOAT,
    TAILLE FLOAT,
    dn DATE,
    traitements listTraitement NOT NULL,
    PRIMARY KEY (id_animal),
58
59
    FOREIGN KEY (id_client) REFERENCES client(id_client),
    FOREIGN KEY (espece) REFERENCES espece(espece)
60
) NESTED TABLE traitements STORE AS l_traitements;
61
/