Converting an ER Model to a Database Design/Schema - Part 2

*   FIRST: converting supertypes/subtypes

*   the basic steps:
    *   create a "base" table for each supertype entity class
        and each subtype entity class

    *   FOR THE SUPERTYPE entity class's "base" table,
        figure out an appropriate primary key for
	that table;

    *   now, for EACH subtype entity class's "base" table,
        add the supertype "base" table's primary key;
        it should be the primary key for each subtype
	"base" table,
	and ALSO a foreign key referencing the supertype
        "base" table;

    *   FOR DISJOINT SUPERTYPE/SUBTYPES:
        *   to make certain queries easier, add a <blah>_type
            attribute to the supertype "base" table, whose
	    domain is some code or value indicating
            of WHICH subtype a supertype instance is;

    *   FOR OVERLAPPING SUPERTYPE/SUBTYPES:
        *   again, to make certain queries easier,
            add an is_<blah> attribute to the supertype
            "base" table for EACH subtype entity class,
	    indicating with a domain of yes/no, 1/0,
	    boolean-ish-domain of your choice whether
	    that supertype instance is also of that
	    subtype;

*   handle the remaining attributes pretty much as
    normal -- single-valued attributes go in their
    respective "base" tables, and each multi-valued
    attribute gets its own table, referencing the
    appropriate "base" table (yes, even if it is a
    subtype!) as discussed last week;

*   (see examples in PDF alternate-notes... (reading
    packet))

*   How about UNION situations?
    *   (look like supertype/subtype, but with u on
        the circle -- and the "subtypes" are really
	independent, with their own attributes, very
	few if any shared,
	BUT they share a relationship...)

    *   still give each entity class involved
        its own "base" table;

    *   BUT! each "subtype" "base" table gets its "own" primary
        key (not the union "base" table's primary
        key);

    *   still put the union's primary key into the
        "subtype" "base" tables, BUT just as a foreign
	key referencing the union "base" table;
	(NOT as the "subtype" "base" table's primary key)

    *   SINCE in a union situation it is USUALLY the
        case that a union instance is exactly one of
	subtype instances, add a <blah>_type attribute
        to the union "base" table to indicate of what
	subtype that instance is;

*   Assocation entities
    *   an entity that associates two or more entities
        often involving some significant transaction;

        typically has NO identifying attributes of its own
        (usually because, really, it gets its identity
        from the two or more entities related to it...)

        typically it is child -- the N side -- on two
	1:N relationships

*   When you are in position of giving this assocation
    entity's "base" table its primary key, there are
    two options:
    1.  (more typical) make the primary key a multi-attribute
        primary key, consisting of the foreign keys that are
	the parents in those 1:N relationships;

    2.  (when you realize now that this is a more
        significant transaction than you maybe initially
	realized) then you can give it its own primary key;

...in option 1 above, the more typical case, the resulting
   "base" table looks a lot like an intersection table
   with additional attributes...!

*   Weak entities

*   really, basically handled like a normal 1:1 or 1:N
    situation (which is usually how the weak entity 
    is related to what it depends on) --

    BUT, the weak entity "base" table usually gets	
    a foreign key -- from the "base" table of what it
    depends on;
    it is common to make the weak entity's primary
    key include that foreign key;

    ...often ends up being a multi-attribute primary
    key, with PART of the primary key being the foreign
    key from what it depends on;

*   Recursive relationships

    *   pretty much handle like you would ANY 1:1, 1:N,
        M:N relationship, EXCEPT, to avoid multiple
	attributes with the same name (!) in a "base"
	table,
	you'd give the foreign key for this a DIFFERENT
        name, hopefully related to the recursive
	relationship;

        for Employee-manages-Employee, for example,

        Empl(EMPL_NUM, managed_by, 
         foreign key (managed_by) references Empl(empl_num)

*   and when you have multiple relationships between the
    same entities, it also turns out to be a good idea to
    name introduced foreign keys based on the particular
    relation, instead of just using the referenced
    primary key's name;

    see the Reading Packet/alternate PDF for today;