CREATE TABLE PERSON ( -- SUPERTYPE 
    p_id NUMBER(2) CONSTRAINT c1 PRIMARY KEY, 
    name CHAR(15), 
    dob DATE 
)

CREATE TABLE STUDENT ( -- SUBTYPE 
    s_id NUMBER(2) CONSTRAINT c2 PRIMARY KEY, 
    p_id_fk, 
    grade CHAR(1), 
    FOREIGN KEY (p_id_fk) REFERENCING PERSON (p_id) 
)

CREATE TABLE TEACHER( -- SUBTYPE 
    t_id NUMBER(4) CONSTRAINT c3 PRIMARY KEY, 
    p_id_fk, 
    tel CHAR(8), 
    FOREIGN KEY (p_id_fk) REFERENCING PERSON (p_id) 
)

CREATE MATERIALIZED VIEW LOG ON STUDENT WITH PRIMARY KEY, ROWID

CREATE MATERIALIZED VIEW LOG ON TEACHER WITH PRIMARY KEY, ROWID

CREATE MATERIALIZED VIEW PERSON_HACK (p_id_fk, marker, rid) 
BUILD IMMEDIATE 
REFRESH ON COMMIT AS 
SELECT p_id_fk, 1, ROWID FROM STUDENT 
UNION ALL 
SELECT p_id_fk, 2, ROWID FROM TEACHER

ALTER MATERIALIZED VIEW PERSON_HACK 
ADD CONSTRAINT PERSON_HACK_PK PRIMARY KEY (p_id_fk)

INSERT INTO PERSON VALUES (11, 'John', to_date('12/12/12', 'dd/mm/yy'))

INSERT INTO PERSON VALUES (22, 'Maria', to_date('01/01/01', 'dd/mm/yy'))

INSERT INTO PERSON VALUES (33, 'Philip', to_date('02/02/02', 'dd/mm/yy'))

INSERT INTO STUDENT VALUES (98, 11, 'A')

INSERT INTO TEACHER VALUES (1234, 11, 14809510)

COMMIT

ROLLBACK

DROP MATERIALIZED VIEW PERSON_HACK

DROP TABLE TEACHER

DROP TABLE STUDENT

DROP TABLE PERSON

PURGE RECYCLEBIN