Question

B. Credit Tracking We shall be giving customers store credit for the money they "saved" by the reassignments of purchases from above. In order to do this, we need to keep track of the reassigned purchases. This will be done with two things: a table in which to record each reassignment; and an after trigger that records each reassignment in the table. 1. credit. Make a table called Credit with columns cid title, year, when, old_club,new_club and done (that match the domain types as used in table Purchase). 2. club change. Write an after trigger that adds a tuple to table Credit whenever a purchase tuple's club is reassigned that records the change (resulting from the update to Purchase). Name this trigger ClubChange. Column old_club stores the value of the club for the offer originally used. Column new_club stores the value of the new club for the offer now being used. And done is a timestamp that records the time when this change was made.

-- Schema definition

create table Customer (

    cid   smallint not null,

    name varchar(20),

    city varchar(15),

    constraint customer_pk

        primary key (cid)

);

create table Club (

    club varchar(15) not null,

    desc varchar(50),

    constraint club_pk

        primary key (club)

);

create table Member (

    club varchar(15) not null,

    cid   smallint     not null,

    constraint member_pk

        primary key (club, cid),

    constraint mem_fk_club

        foreign key (club) references Club,

    constraint mem_fk_cust

        foreign key (cid) references Customer

);

create table Category (

    cat varchar(10) not null,

    constraint category_pk

        primary key (cat)

);

create table Book (

    title     varchar(25) not null,

    year      smallint     not null,

    language varchar(10),

    cat       varchar(10) not null,

    weight    smallint     not null,

    constraint book_pk

        primary key (title, year),

   constraint book_fk_cat

        foreign key (cat) references Category,

    constraint book_weight

        check (weight > 0)

);

create table Offer (

    club   varchar(15) not null,

    title varchar(25) not null,

    year   smallint     not null,

    price decimal(5,2) not null,

    constraint off_pk

        primary key (club, title, year),

    constraint off_fk_club

        foreign key (club) references Club,

    constraint off_fk_book

        foreign key (title, year) references Book,

    constraint off_price

        check (price > 0)

);

create table Purchase (

    cid    smallint     not null,

    club   varchar(15) not null,

    title varchar(25) not null,

    year   smallint     not null,

    when   timestamp    not null,

    qnty   smallint     not null,

    constraint pur_pk

        primary key (cid, club, title, year, when),

    constraint pur_fk_offer

        foreign key (club, title, year) references Offer,

    constraint pur_fk_mem

        foreign key (club, cid) references Member,

    constraint pur_qnty

        check (qnty > 0)

);

-- A prettier way to view the PURCHASE table.

create view Pretty_Purchase as

    (select cast (cid as decimal(2)) as cid,

            title,

           club,

            year,

            cast (when as date) as day,

            cast (when as time) as time

        from purchase);

create table Shipping (

    weight smallint     not null,

    cost    decimal(5,2) not null,

    constraint shipping_pk

        primary key (weight),

    constraint ship_uni_cost

        unique (cost)

);

Answer 1

Based on the snapshot pasted and the 2 questions mentioned under that, we can answer them as follows:

1. CREATE TABLE CREDIT

(

cid smallint not null, title varchar(25) not null, year   smallint     not null,  when   timestamp    not null, old_club   varchar(15) not null, new_club varchar(15) not null, done timestamp    not null

);

2.After Trigger

CREATE OR REPLACE TRIGGER ClubChange

AFTER UPDATE OF CLUB ON PURCHASE

FOR EACH ROW ENABLE

BEGIN

INSERT INTO CREDIT VALUES(:OLD.CID, :OLD.TITLE, :OLD.YEAR, :OLD.WHEN, :OLD.CLUB, :NEW.CLUB, SYSDATE);

END;

/

Explanation: Triggers are named blocks that can execute whenever a condition is met. Here, we are executing an insert based on update on a particular column.

The syntax of trigger: first line defining the creation with name, second line defining when the trigger is to run (After an update is done on column CLUB of PURCHASE table), third line stating that it has to be in Enabled state, and execute for each row.

Here, during insertion you will see :OLD , :NEW predictes being used. Basically, the pseudorecord is present in a trigger, so, the whole old record updated is available to use (predefined) in :OLD clause, and the whole new record is available to us using the predefined :NEW clause, through which we can access the older / newer values.