CS 325 - Week 11 Lecture 1 - 2016-11-01

*   converting supertypes/subtypes into appropriate tables
    in one's database design...

*   steps for this:

    *   create a "base" table for each entity class
        ...yes, for EACH supertype AND for EACH subtype;

    *   like discussed last week, determine an appropriate
        primary key for the supertype's "base" table;

        FOR DISJOINT and OVERLAPPING subtypes,
        *   then, make the primary key of each subtype's "base"
	    table the primary key of its supertype's "base" table,
	    AND also a foreign key referencing the supertype's "base"
	    table

        (it'll be different for unions, we'll discuss those a
	bit later...)

        *   then, for single-valued attributes, put them in
	    that entity class' "base" table,

	    and for multi-valued attributes, create a SEPARATE
	    table as described last week

        *   last piece:
	    to make certain queries easier:
	    *   for DISJOINT subtypes,
	        add to the supertype's "base" table an ADDITIONAL
		attribute, *_type, whose domain is some code or
		value indicating WHICH subtype that row is

            *   for OVERLAPPING subtypes,
                since a supertype's instance CAN be MORE than one
		   of the subtypes,
		add to the supertype's "base" table ADDITIONAL attributeS,
                is_*, one for each possible subtype,
		whose domain indicates yes/no, true/false,
		indicating IF that supertype instance is ALSO
		that subtype;

		*   sadly, Oracle SQL does not have a boolean type... 8-(
		    (Orace PL/SQL does -- arrgh...)

             *   handle relationships (1:N, 1:1, M:N) as usual

*   account example:

    *   "base" table for EACH entity class, supertype OR subtype
        (or NEITHER)

    Account(
    Savings_Account(
    Checking_Account(

    *   primary keys?

    Account(ACCT_NUM, 

    Savings_Account(ACCT_NUM, 
        foreign key (acct_num) references Account

    Checking_Account(ACCT_NUM, 
        foreign key (acct_num) references Account

    *   and these are disjoint, so add to Account:
   
    Account(ACCT_NUM, acct_type, 
        -- acct_type is char(1), 'S' for savings, 'C' for checking

    Savings_Account(ACCT_NUM, 
        foreign key (acct_num) references Account

    Checking_Account(ACCT_NUM, 
        foreign key (acct_num) references Account

    *   handle rest of attributes "normally"

    Account(ACCT_NUM, acct_type, date_opened, balance) 
        -- acct_type is char(1), 'S' for savings, 'C' for checking

    Savings_Account(ACCT_NUM, sav_int_rate)
        foreign key (acct_num) references Account

    Sav_Bonus(ACCT_NUM, BONUS_FEAT)
        foreign key(acct_num) references Savings_Account

    Checking_Account(ACCT_NUM, ckg_min_bal, per_ck_charge)
        foreign key (acct_num) references Account

    (and since there are no other relationships here, I'm done;)

*   university person example:
    
    *   "base" tables:

    Univ_person(
    Student(
    Instructor(

    *   adding in appropriate primary keys:

    Univ_person(UNIV_ID, 

    Student(UNIV_ID,
        foreign key(univ_id) references Univ_person

    Instructor(UNIV_ID,
        foreign key(univ_id) references Univ_person

*   since these are OVERLAPPING subtypes,
    add an is_* attribute to the "base" table of the supertype
    for each subtype:

    Univ_person(UNIV_ID, is_student, is_instructor, univ_p_last_name,
                univ_p_first_name, campus_email,
        -- is_student, is_instructor are char(1), 'y' or 'n'

    Student(UNIV_ID, gpa, sem_matricd, sem_graduated,
        foreign key(univ_id) references Univ_person

    Instructor(UNIV_ID, is_W4_on_file, date_first_hired,
        foreign key(univ_id) references Univ_person

*   what about UNION supertype/subtypes?
    *   because of their nature,
        their subtype entity class' "base" tables
	will tend to have their "own" primary keys!

        *   but how do they relate the union supertype's
	    "base" table?

	    you WILL add the primary key of the union
	    supertype's "base" table TO the subtype
	    "base" tables, but JUST as a foreign key
	    (NOT as part of subtype's "base" table'd
	    primary key)

        *   and because (typically) union supertypes cannot
	    be more than one of their subtypes,
	    (typically) add a *_type attribute to the
	    union supertype's "base" table

Sponsor(SPONS_NUM, spons_type, 

Club(CLUB_NUM, spons_num,
   foreign key (spons_num) references Sponsor

Team(TEAM_CODE, spons_num,
   foreign key (spons_num) references Sponsor

Dept(DEPT_CODE, spons_num,
   foreign key (spons_num) references Sponsor

Event(EVENT_NUM,

*   and now continue normally from there, resulting in:

Sponsor(SPONS_NUM, spons_type)

Club(CLUB_NUM, spons_num, club_name, is_active)
   foreign key (spons_num) references Sponsor

Team(TEAM_CODE, spons_num, sport, season)
   foreign key (spons_num) references Sponsor

Dept(DEPT_CODE, spons_num, dept_title, office_num)
   foreign key (spons_num) references Sponsor

Event(EVENT_NUM, event_title, event_date, spons_num)
   foreign key(spons_num) references Sponsor