lab1.1

CREATE TABLE competition (
    id                INTEGER NOT NULL,
    start_date_time   DATE NOT NULL,
    location          VARCHAR2(4000) NOT NULL,
    laps_number       INTEGER NOT NULL,
    name              VARCHAR2(4000)
);

ALTER TABLE competition ADD CONSTRAINT competition_pk PRIMARY KEY ( id );

CREATE TABLE horse (
    id           INTEGER NOT NULL,
    name         VARCHAR2(4000) NOT NULL,
    birth_date   DATE NOT NULL,
    owner_id     INTEGER NOT NULL,
    jockey_id    INTEGER
);

ALTER TABLE horse ADD CONSTRAINT horse_pk PRIMARY KEY ( id );

CREATE TABLE jockey (
    id           INTEGER NOT NULL,
    name         VARCHAR2(4000) NOT NULL,
    address      VARCHAR2(4000) NOT NULL,
    birth_date   DATE NOT NULL,
    height       NUMBER NOT NULL,
    weight       NUMBER NOT NULL
);

ALTER TABLE jockey ADD CONSTRAINT jockey_pk PRIMARY KEY ( id );

CREATE TABLE laps (
    id               INTEGER NOT NULL,
    laps_count       INTEGER NOT NULL,
    place            VARCHAR2(4000) NOT NULL,
    competition_id   INTEGER NOT NULL,
    horse_id         INTEGER NOT NULL
);

ALTER TABLE laps ADD CONSTRAINT laps_pk PRIMARY KEY ( id );

CREATE TABLE owner (
    id        INTEGER NOT NULL,
    name      VARCHAR2(4000) NOT NULL,
    address   VARCHAR2(4000) NOT NULL
);

ALTER TABLE owner ADD CONSTRAINT owner_pk PRIMARY KEY ( id );

ALTER TABLE horse
    ADD CONSTRAINT horse_jockey_fk FOREIGN KEY ( jockey_id )
        REFERENCES jockey ( id );

ALTER TABLE horse
    ADD CONSTRAINT horse_owner_fk FOREIGN KEY ( owner_id )
        REFERENCES owner ( id );

ALTER TABLE laps
    ADD CONSTRAINT laps_competition_fk FOREIGN KEY ( competition_id )
        REFERENCES competition ( id );

ALTER TABLE laps
    ADD CONSTRAINT laps_horse_fk FOREIGN KEY ( horse_id )
        REFERENCES horse ( id );

CREATE OR REPLACE TRIGGER fkntm_horse BEFORE
    UPDATE OF owner_id ON horse
BEGIN
    raise_application_error(-20225,'Non Transferable FK constraint  on table HORSE is violated');
END;
/