SQL_RO.sql 1.19 KB
Newer Older
Gaetan Carabetta's avatar
Gaetan Carabetta committed
1 2 3 4 5 6 7
drop table animal;
drop table espece;
drop table client;
drop type listTraitements;
drop type listNumeros;
drop type traitement;

8
CREATE TYPE traitement AS OBJECT (
9 10 11 12 13 14
    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
15 16 17 18
);
/


19
CREATE TYPE listNumeros AS TABLE OF VARCHAR(10);
20 21
/

22
CREATE TYPE listTraitements AS TABLE OF traitement;
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
23 24
/

25

26
CREATE TABLE client (
Gaetan Carabetta's avatar
Gaetan Carabetta committed
27
    id_client NUMBER,
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
28 29 30 31
    nom VARCHAR(20) NOT NULL,
    prenom VARCHAR(20) NOT NULL,
    dn DATE NOT NULL,
    adresse VARCHAR(50) NOT NULL,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
32
    numeros listNumeros,
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
33
    PRIMARY KEY(id_client)
34
)NESTED TABLE numeros STORE AS l_numeros;
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
35 36
/

37
CREATE  TABLE espece(
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
38
    classeEspece VARCHAR(30) NOT NULL,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
39
    espece VARCHAR(20),
40
    PRIMARY KEY(espece)
Osvaldo Valdivia Salas's avatar
RO  
Osvaldo Valdivia Salas committed
41
);
42 43
/

44
CREATE  TABLE animal(
Gaetan Carabetta's avatar
Gaetan Carabetta committed
45 46 47
    id_animal NUMBER,
    id_client NUMBER,
    espece VARCHAR(20),
48 49 50 51
    nom VARCHAR(20),
    poid FLOAT,
    TAILLE FLOAT,
    dn DATE,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
52
    traitements listTraitements,
Gaetan Carabetta's avatar
Gaetan Carabetta committed
53 54
    CHECK (poid > 0),
    CHECK (TAILLE > 0),
55
    PRIMARY KEY (id_animal),
56 57
    FOREIGN KEY (id_client) REFERENCES client(id_client),
    FOREIGN KEY (espece) REFERENCES espece(espece)
58
) NESTED TABLE traitements STORE AS l_traitements;
Gaetan Carabetta's avatar
Gaetan Carabetta committed
59
/