Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    isa-realtionship

    Hello!

    i want to display the following isa-relationship:

    see ISA.jpg

    Now i think that this code is wrong:

    CREATE TABLE T(ID_T INTEGER, NAME VARCHAR2(10), CATEGORY VARCHAR(10));
    ALTER TABLE T ADD (CONSTRAINT PK_T PRIMARY KEY (ID_T));

    CREATE TABLE A(ID_T INTEGER, ID_A INTEGER, NAME VARCHAR2(10));
    ALTER TABLE A ADD (CONSTRAINT PK_A PRIMARY KEY (ID_T, ID_A));
    ALTER TABLE A ADD (CONSTRAINT FK_A FOREIGN KEY (ID_T) REFERENCES T (ID_T));

    CREATE TABLE B(ID_T INTEGER, ID_B INTEGER, NAME VARCHAR2(10));
    ALTER TABLE B ADD (CONSTRAINT PK_B PRIMARY KEY (ID_T, ID_B));
    ALTER TABLE B ADD (CONSTRAINT FK_B FOREIGN KEY (ID_T) REFERENCES T (ID_T));


    CREATE TABLE C(ID_T INTEGER, ID_C INTEGER, NAME VARCHAR2(10));
    ALTER TABLE C ADD (CONSTRAINT PK_C PRIMARY KEY (ID_T, ID_C));
    ALTER TABLE C ADD (CONSTRAINT FK_C FOREIGN KEY (ID_T) REFERENCES T (ID_T));



    It would be great if anybody could help me!
    Attached Thumbnails Attached Thumbnails isa.jpg  
    Regards,
    Julia

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: isa-realtionship

    It should be:

    CREATE TABLE T(ID_T INTEGER, NAME VARCHAR2(10), CATEGORY VARCHAR(10));
    ALTER TABLE T ADD (CONSTRAINT PK_T PRIMARY KEY (ID_T));

    CREATE TABLE A(ID_T INTEGER, {a_specific_columns});
    ALTER TABLE A ADD (CONSTRAINT PK_A PRIMARY KEY (ID_T));
    ALTER TABLE A ADD (CONSTRAINT FK_A FOREIGN KEY (ID_T) REFERENCES T (ID_T));

    CREATE TABLE B(ID_T INTEGER, {b_specific_columns});
    ALTER TABLE B ADD (CONSTRAINT PK_B PRIMARY KEY (ID_T));
    ALTER TABLE B ADD (CONSTRAINT FK_B FOREIGN KEY (ID_T) REFERENCES T (ID_T));

    CREATE TABLE C(ID_T INTEGER, {c_specific_columns});
    ALTER TABLE C ADD (CONSTRAINT PK_C PRIMARY KEY (ID_T));
    ALTER TABLE C ADD (CONSTRAINT FK_C FOREIGN KEY (ID_T) REFERENCES T (ID_T));

    i.e. the ID for the supertype is also the ID for the subtype.
    Last edited by andrewst; 03-18-04 at 06:13.

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thank You!
    But what can i do if i need the following construction later:
    Attached Thumbnails Attached Thumbnails isa1.jpg  
    Regards,
    Julia

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    That would require an intersection table:

    create table ab (a_id_t references a, b_id_t references b,
    constraint pk_ab primary key ( a_id_t, b_id_t));

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thank You!
    Regards,
    Julia

  6. #6
    Join Date
    Mar 2003
    Location
    Australia
    Posts
    59
    Be sure to enforce a constraint that prohibits the supertype to be more than one subtype (If that is desired)...

    ie: With the current schema this is possible.....

    insert into T values (1)
    insert into A Values (1)
    insert into B Values (1)

  7. #7
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    New question

    Hello!
    Now i have an advanced question.

    ###################################

    table T
    ==============================
    ID integer, (e.g. 1, 2, 3)
    CATEGORY (e.g., A, B, C)
    ==============================

    table A
    ===========
    ID integer,
    ...

    relat_table AB
    ==============
    ID_A integer
    ID_B integer


    table B
    ============
    ID integer,
    ...

    table C
    ===========
    ID integer,
    ...

    ###################################


    Now i want to create the following trigger:

    if (insert into T ( CATEGORY) values ('A') )
    then (insert the same ID into table A)

    if ................ 'B'..............
    then ................ 'B'.........

    and so on.

    These operations must be atomic.

    How can i realize that?

    ################################

    CREATE OR REPLACE TRIGGER TRG_T_BEF_INS
    BEFORE INSERT ON T
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    tmpVar1 NUMBER;

    BEGIN
    tmpVar1 := 0;

    SELECT SEQ_T.NEXTVAL INTO tmpVar1 FROM dual;
    :NEW.ID := tmpVar1;
    END;


    It would be great if anybody could help me!
    Regards,
    Julia

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: New question

    You would want an AFTER INSERT trigger something like this:
    PHP Code:
    create or replace trigger trg_t_aft_ins
    after insert on t
    for each row
    begin
      
    if :new.type 'A' then
        insert into a 
    (idvalues (:new.id);
      
    elsif :new.type 'B' then
        insert into b 
    (idvalues (:new.id);
      ...
      
    end if;
    end
    However, this only works if all the non-key columns in A, B, ... are defined to allow NULLs. I would more likely go for an approach like this:

    PHP Code:
    create or replace view a_view as
    select a.ida..., t...
    from at
    where a
    .id t.id;

    create or replace trigger a_view_ins_trg 
    instead of insert on a_view
    begin
      insert into t
    (id, ...) values (seq_t.nextval, ...);
      
    insert into a(id, ...) values (seq_t.currval, ...);
    end
    Now the user inserts into view_a, and a record gets created in T and A with all columns populated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •