going from a MODEL to a DESIGN:

*   going from an ER database model to a relational database design/schema

*   we're going through this process for an Art Gallery example (whose
    ER diagram is posted along with these notes)

step 1, part 1:
*   create a "base", first table for each entity
    class...

Painter(

Painting(

Gallery(

Art_Period(

Donor(

Curator(

Step 1, Part 2: determine a reasonable primary
key for each "base" table

*   look at the identifying attributes, and determine
    if they might indeed be suitable primary keys
    for the "base" tables, or if you want to
    introduce a more suitable primary key
    attribute...

    *   are the identifying attributes really superkeys?
    *   ...are they minimal/candidate keys?
    *   ...do I WANT them as a primary key?

*   (if you do introduce a new attribute to serve
    as primary key -- you don't have to change your
    model, as this isn't an error in your model,
    it's just part of the design process;

    ...if you discover a missing piece of information
    the user cares about day-to-day, THEN correct
    the model accordingly;)

Painter(PTR_NUM, Ptr_lname, 

Painting(PTG_NUM, 

Gallery(GAL_NUM,

Art_Period(PD_CODE, Pd_name, 

Donor(DON_NUM,

Curator(CUR_NUM, Cur_lname,

STEP 1, PART 3 - appropriately add the remaining
attributes from the ER model to the design

*   look at each non-identifying attribute in each
    entity class' attribute list:
    *   is it single-valued? If so, add it to
        the "base" table for that entity class;

    *   is it multi-valued? If so,
        *   create a new table with an appropriate
	    name (avoiding having a table name with
	    the same name as an attribute, as
	    that would be confusing),

       *   it contains the primary key of the "base"
           table as a foreign key back to the "base"
	   table

       *   it also has the multi-valued attribute

       *   its primary key is BOTH the "base"
           table primary key AND the multi-valued
	   attribute;

(so, we are trying to create relations in 1NF as
     part of the design process -- so we don't
     have to go back and normalize to 1NF later...)

Painter(PTR_NUM, Ptr_lname, Ptr_fname, ptr_dob, ptr_dod,

Painting(PTG_NUM, Ptg_title, Ptg_cost, Ptg_acquis_date,

Gallery(GAL_NUM, gal_str_addr, gal_name,

Art_Period(PD_CODE, Pd_name, Pd_begin, Pd_end,

Donor(DON_NUM, Don_lname, Don_fname, Don_str_addr, 
      Don_city, Don_state, Don_zip, 

Donor_email(DON_NUM, DON_EMAIL)
foreign key (don_num) references Donor

Curator(CUR_NUM, Cur_lname, Cur_email, 

Curator_phone(CUR_NUM, CUR_PHONE)
foreign key(cur_num) references Curator

Curator_certif(CUR_NUM, CUR_CERTIF)
foreign key(cur_num) references Curator

STEP 2 - HANDLING RELATIONSHIPS

*   for EACH relationship line with a diamond
    on it (because supertype/subtype will be
    handled/discussed next week)...

    ...introduce controlled redundancy to relate
    the tables as indicated in the ER model

3 MAIN possibilities:
*   the relationship is 1:N
    ...is M:N
    ...is 1:1

*   FOR EACH 1:N relationship:
    *   often, the entity near the 1 is called the
        "parent", and the entity near the N is
	called the "child"

    *   then, for each 1:N relationship, the primary
        key of the parent's "base" table is placed
	in the child's "base" table as a foreign
	key back to the parent's "base" table;

Painter(PTR_NUM, Ptr_lname, Ptr_fname, ptr_dob, ptr_dod,


Painting(PTG_NUM, Ptg_title, Ptg_cost, Ptg_acquis_date,
         ptr_num, pd_code, don_num, gal_num
foreign key(ptr_num) references Painter,
foreign key(pd_code) references Art_period,
foreign key(don_num) references Donor,
foreign key(gal_num) references Gallery,

Gallery(GAL_NUM, gal_str_addr, gal_name,

Art_Period(PD_CODE, Pd_name, Pd_begin, Pd_end,

Donor(DON_NUM, Don_lname, Don_fname, Don_str_addr, 
      Don_city, Don_state, Don_zip, 

Donor_email(DON_NUM, DON_EMAIL)
foreign key (don_num) references Donor

Curator(CUR_NUM, Cur_lname, Cur_email, 

Curator_phone(CUR_NUM, CUR_PHONE)
foreign key(cur_num) references Curator

Curator_certif(CUR_NUM, CUR_CERTIF)
foreign key(cur_num) references Curator

*   HANDLING N:M RELATIONSHIPS

*   EACH N:M relationship results in
    the creation of an INTERSECTION table:
    *   it contains the primary key from
        each associated "base" table
	for each entity,
    *   each of those is a foreign key
        back to that "base" table, and
    *   BOTH keys, together, make up the
        primary key for the new intersection
	table (it is a multi-attribute
	primary key)

Painter_in_Period(PTR_NUM, PD_CODE)
foreign key(ptr_num) references Painter,
foreign key(pd_code) references Art_period

*   HANDLING 1:1 RELATIONSHIPS

*   since an instance of one is related to
    at most one instance of the other,
    in GENERAL you can safely choose to
    put the primary key of either one
    into the other as a foreign key  --

    BUT!! you HAVE to choose one --
    DON'T put foreign keys to each
    other in both!!!

*   sometimes one of the choices might
    be preferable to the other:
    *   if one side is optional and the other
        is mandatory, it can be advantageous
	to put the foreign key on the side
	that must be related to an instance of
	the other --
   for example,

Gallery(GAL_NUM, Gal_str_addr, Gal_name,

Curator(CUR_NUM, Cur_lname, Cur_email, gal_num,
foreign key (gal_num) references Gallery

(and I'd probably make gal_num NOT NULL)

SO: here is the almost-final database design from this
model: (just add domain information and business rules to 
complete it...)
-------------------------------------------------------

Painter(PTR_NUM, Ptr_lname, Ptr_fname, ptr_dob, ptr_dod)

Art_Period(PD_CODE, Pd_name, Pd_begin, Pd_end)

Painter_in_Period(PTR_NUM, PD_CODE)
foreign key(ptr_num) references Painter,
foreign key(pd_code) references Art_period

Gallery(GAL_NUM, gal_str_addr, gal_name)

Curator(CUR_NUM, Cur_lname, Cur_email, Gal_num)
foreign key (Gal_num) references Gallery

Curator_phone(CUR_NUM, CUR_PHONE)
foreign key(cur_num) references Curator

Curator_certif(CUR_NUM, CUR_CERTIF)
foreign key(cur_num) references Curator

Donor(DON_NUM, Don_lname, Don_fname, Don_str_addr, 
      Don_city, Don_state, Don_zip)

Donor_email(DON_NUM, DON_EMAIL)
foreign key (don_num) references Donor

Painting(PTG_NUM, Ptg_title, Ptg_cost, Ptg_acquis_date,
         ptr_num, pd_code, don_num, gal_num)
foreign key(ptr_num) references Painter,
foreign key(pd_code) references Art_period,
foreign key(don_num) references Donor,
foreign key(gal_num) references Gallery