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 PERSON_MV 
  REFRESH COMPLETE 
  AS SELECT p.P_ID, 
            s.S_ID, 
            t.T_ID 
       FROM PERSON p 
       LEFT OUTER JOIN STUDENT s 
         ON s.P_ID_FK = p.P_ID 
       LEFT OUTER JOIN TEACHER t 
         ON t.P_ID_FK = p.P_ID

ALTER MATERIALIZED VIEW PERSON_MV 
  ADD CONSTRAINT PERSON_MV_CK1 
    CHECK((S_ID IS NULL AND       -- either both are NULL 
           T_ID IS NULL) OR 
          ( (S_ID IS NULL OR      -- or only one is NULL 
             T_ID IS NULL) AND 
            (S_ID IS NOT NULL OR 
             T_ID IS NOT NULL)))

INSERT ALL 
  INTO PERSON (P_ID, NAME, DOB) VALUES (11, 'John', to_date('12/12/2012', 'dd/mm/yyyy')) 
  INTO PERSON (P_ID, NAME, DOB) VALUES (22, 'Maria', to_date('01/01/2001', 'dd/mm/yyyy')) 
  INTO PERSON (P_ID, NAME, DOB) VALUES (33, 'Philip', to_date('02/02/2002', 'dd/mm/yyyy')) 
SELECT * FROM DUAL

COMMIT

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

COMMIT

INSERT INTO TEACHER VALUES (1234, 11, 14809510)

COMMIT

BEGIN 
  DBMS_MVIEW.REFRESH('PERSON_MV', 'C', '', TRUE, FALSE, 0, 0, 0, FALSE, FALSE); 
END; 

SELECT * 
  FROM PERSON_MV