=====
CS 325 - Week 10 Lecture 1 - 2021-10-25
=====

=====
TODAY WE WILL
=====
*   announcements
*   converting an E-R model into a relational design/schema
*   prep for next class

*   reading for this week:
    *   DB Reading Packet 7 - Database Design, Part 1
    *   SQL Reading Packet 6 - "Set-theoretic operations, more on modifying
        data, intro to sequences"

*   you should be starting Homework 7 - final versions of its problems
    are due 11:59 pm on FRIDAY

*   be sure to read DB Reading Packets 7 and 8 - Database Design, Parts 1 and 2
    before you start to convert your Project Model draft into
    relations!

=======
CONVERTING an E-R MODEL into a RELATIONAL schema, a relational database
    design!
=======
*   how shall we depict a resulting relational schema/design?
    *   reminder:
        a schema/design consists of:
	*   relations
	*   relationships
	*   domains
	*   business rules

    *   relation-structure form is convenient, but it does not
        include domains of the attributes;

        we'll still use it informally/for homework design problems/
	to make a useful reference for writing queries

    *   create-table form is nice for also including domains!
        (and being very close to executable...)

=====
my suggested step 1: use each entity class to create your first
    initial "base" tables

    (more will VERY likely be added later!)
=====
*   give the "base" table for each entity class a SQL-friendly name

Painter(

Painting(

Gallery(

Art_period(

Donor(

Curator(

=====
hey, make sure to give each of the "base" tables
    a suitable primary key
=====
*   if you add an attribute to be a "better" primary key,
    you DON'T have to add that to the E-R model --
    this is not a model correction, it is part of conversion to
    a design

Painter(PTR_NUM, Ptr_lname, 

Painting(PTG_NUM, 

Gallery(GAL_NUM,

Art_period(PD_CODE, 

Donor(DON_NUM, 

Curator(CUR_NUM,

=====
then could add in the remaining attributes from the model
    into the design
=====
*   IF single-valued attributes,
    might as well add them to the "base" table for
    the entity class they are in;

    BUT what if they are multi-valued?
    AH! You create a NEW table for EACH multi-valued attribute,
    *   it has a suitable name that is descriptive but NOT
        exactly the same as an attribute name, please!

    *   its primary key of the new table is the COMBINATION of
        the primary key of the entity class' "base" table and the
        AND the multi-valued attribute

    *   and the primary key of the "base" table that is in this new
        table should also be a foreign key referencing the "base" table

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_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_CERTIFICATION)
    foreign key (cur_num) references Curator

=======
NOW!!! handle RELATIONSHIPS!
=======
*   EACH relationship line in your model
    results in an addition to your design!!!!!!!!!!!!!!!

    *   for each 1:N relationship:
        *   the primary of the "base" table of the entity class on
	    the "1" side of the relationship -- often also called the
	    parent
	    is added to the "base" table of the entity class on the
	    "N" side of the relationship -- often called the child

    *   for each N:M relationship:
        *    you add an INTERSECTION TABLE for each N:M relationship,
	     they have the primary key of EACH entity class' "base"
	     table,
	     BOTH, together, make up the primary key of the new table,
	     and EACH is a foreign key referencing its entity class'
	     "base" table

    *   for each 1:1 relationship:
        ...special case of 1:N, see Week 10 Asynchronous Material
	   and of course the DB Reading Packet 7

...I'll finish this example in the Week 10 Asynchronous Material

SO: where we'll START in Week 10 Asynchronous Material:
=====
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_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_CERTIFICATION)
    foreign key (cur_num) references Curator