Clinique_RO.sql

CREATE OR REPLACE TYPE traitement AS OBJECT (
    id_traitement NUMBER,
    debut DATE,
    duree NUMBER,
    quantite NUMBER,
    veterinaire VARCHAR(30),
    medicament VARCHAR(30)
);
/


CREATE OR REPLACE TYPE numero AS OBJECT(
    numero VARCHAR(12)
);
/

CREATE OR REPLACE TYPE listNumeros AS TABLE OF numero;
/

CREATE OR REPLACE TYPE listTraitements AS TABLE OF traitement;
/


CREATE OR REPLACE TABLE client (
    id_client NUMBER NOT NULL,
    nom VARCHAR(20) NOT NULL,
    prenom VARCHAR(20) NOT NULL,
    dn DATE NOT NULL,
    adresse VARCHAR(50) NOT NULL,
    numeros listNumeros NOT NULL,
    PRIMARY KEY(id_client)
)NESTED TABLE numeros STORE AS l_numeros;
/

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)
);
/

CREATE OR REPLACE TABLE animal(
    id_animal NUMBER NOT NULL,
    id_client NUMBER NOT NULL,
    espece VARCHAR(20),
    nom VARCHAR(20),
    poid FLOAT,
    TAILLE FLOAT,
    dn DATE,
    traitements listTraitement NOT NULL,
    PRIMARY KEY (id_animal),
    FOREIGN KEY (id_client) REFERENCES client(id_client),
    FOREIGN KEY (espece) REFERENCES espece(espece)
) NESTED TABLE traitements STORE AS l_traitements;
/