CS 325 - Week 10 Lecture 2 - 2016-10-26

*   at end of last class, our database-design-in-progress was:

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
    foreign key (ptr_num) references painter,
    foreign key (pd_code) references art_period

Gallery(GAL_NUM, gal_street_addr, gal_name

Art_Period(PD_CODE, pd_name, pd_begin, pd_end

Donor(DON_NUM, don_lname, don_fname, don_street_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_CERT)
    foreign key (cur_num) references Curator

*   Now let's handle the rest of the 1:N relationships:

*   helpful terminology:
    *   let's call the "base" table for the
        entity class on the "1" side of
	a 1:N relationship the parent table,

	and let's call the "base" table for
	the entity class on the "N" side
	the child table;

	THEN we can say that we handle each
	1:N relationship by putting the
	primary key of the parent table
        into the child table, and make it
	a foreign key (in the child table)
	referencing the parent 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_street_addr, gal_name

Art_Period(PD_CODE, pd_name, pd_begin, pd_end

Donor(DON_NUM, don_lname, don_fname, don_street_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_CERT)
    foreign key (cur_num) references Curator

*   N:M relationships

    *   EACH N:M relationships should result in
        adding an additional table,
	called an INTERSECTION table

    *   the intersection table is "built" as follows:
        *   include the primary key of each
	    of the "base" tables of the two
	    entity classes involved

        *   the PAIR/combo of these primary
	    keys is the multi-attribute primary
	    key of the new intersection table

        *   and each of the primary keys is
	    also made a foreign key back
	    to its respective "base" table

        *   how is it named?      
	    ...often based on the relationship name,
	       sometimes on the names of the
	       two entity classes involved,
	       and sometimes some combination thereof


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

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_street_addr, gal_name

Donor(DON_NUM, don_lname, don_fname, don_street_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_CERT)
    foreign key (cur_num) references Curator

*   1:1 relationships

    *   basic rule: put the primary key of one of the entity
        class's "base" table into the other entity class's
	"base" table, and make it a foreign key referencing
	the "base" table it is from;;

	DON'T DO THIS WITH BOTH PRIMARY KEYS!
	...choose ONE or the OTHER...!

    *   HOW do you choose?
        *   DO look at the minimum cardinalities, here;
	    if they are 1 and 0, 
	    so that an instance of one of the entity classes
	    MUST be related to an instance of the other,
	    (but not vice versa),
	    it can be better in that case to put the
	    foreign key in the "base" table of the
	    entity class that NEEEEEEDS that relationship

	    because then you can make that attribute
	    NOT NULL, and thus have the DBMS help support
	    that this relationship is required;

        *   if both are 1,
	    or both are 0,
	    it's likely OK either way (just don't do both!)

            *   sometimes you might try to guess if
	        one placement might be more convenient
		for future queries...


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

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
    -- ptr_num should be NOT NULL
    -- pd_code should be NOT NULL

Gallery(GAL_NUM, gal_street_addr, gal_name

Donor(DON_NUM, don_lname, don_fname, don_street_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, gal_num
    foreign key(gal_num) references Gallery  
    -- and I'll make gal_num a NOT NULL attribute here 

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

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

*   a few more comments on minimum cardinalities'
    impact on the design...

    *   we saw how they might influence WHICH "base"
        table gets the foreign key in 1:1;

    *   how about 1:N?
        *   if a child instance is required to have
	    a relationship with a parent instance,
	    can you see that it could be good
	    to make that foreign key in the
	    child NOT NULL?

	    ...and vice versa, if a child instance is
	    NOT required to, allow that foreign
	    key to be null?


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

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_street_addr, gal_name

Donor(DON_NUM, don_lname, don_fname, don_street_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, gal_num
    foreign key(gal_num) references Gallery  
    -- and I'll make gal_num a NOT NULL attribute here 

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

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