=====
CS 325 - Week 10 Asynchronous Material
=====
=====
TODAY WE WILL:
=====
* announcements
* continue part 1 of converting an E-R model into a database design/schema
* SQL: continue intro to set-theoretic operations
* SQL: more on update and delete
* IF time: a few other SQL topics
* prep for next class
* Current reading:
* DB Reading Packet 7 - Database Design, part 1
* DB Reading Packet 8 - Database Design, part 2 - will discuss
next week, but you want to read it sooner and get started
converting your PROJECT MODEL draft into a DATABASE DESIGN draft!
* SQL Reading Packet 6 - Set-theoretic operations, more on modifying
data, and intro to sequences
* Should be working on Homework 7 - due 11:59 pm on Friday
* Project database design draft: Friday, November 19th
* Have you made a Spring 2022 registration advising appointment yet
with your advisor?
...please do so by November 5!
...so you can register for Spring 2022 as SOON as you registration
window opens during November 8 - November 19
* our Art-Galleries-scenario database design/schema SO FAR,
by the end of last Monday's class:
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: we need to handle EACH relationship in the E-R model,
making the APPROPRIATE additions to our design for each!
* three main types/kinds/categories of relationships:
1:N, M:N, 1:1
=====
handling 1:N relationships
=====
* for EACH 1:N relationship:
* add an attribute to the "child" entity class' "base" table;
(the "child" here is the N side of the 1:N relationship)
you add the primary key of the "parent" entity class' "base"
table (the "parent" here is the 1 side of the 1:N relationship)
TO "child" entity class' "base" table, and make it a
foreign key REFERENCING the "parent" entity class' "base" table;
(it is just a new foreign key, it is not affecting the "child"
table's primary key)
* after handling all 4 1:N relationships in this model, we have:
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) referenced 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_CERTIFICATION)
foreign key (cur_num) references Curator
=====
handling M:N relationships
=====
* for EACH M:N relationship, add an INTERSECTION table!
* give it a descriptive name -- might be based on the relationship
name, it might be based on the names of the two entity classes
involved, or some reasonable combination of those
* contains the primary key of the "base" table of
each of the involved entity class, EACH a foreign key as well,
referencing those "base" tables
* the PAIR of these primary keys is the primary key of
this intersection table!
so, after the handling the only M:N in this model, our schema is:
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) referenced Gallery
Gallery(GAL_NUM, Gal_street_addr, Gal_name
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
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
=====
handling 1:1 relationships
=====
* basically a special case/variation of how we handle 1:N relationships
* there needs to be a foreign key involved, to represent the
relationship -- but WHICH entity class' "base" table gets it?
* sometimes, you can go EITHER way! (just don't try adding
a foreign key to BOTH entity class' "base" table!!)
* one case where the choice is clearer:
when 1 side of the relationship has a minimum cardinality of 0,
and the other has a minimum cardinality of 1;
...then, the best choice is to put the primary key of
the "required" entity class' "base" table into
the "base" table of the entity class that requires
that relationship;
* HERE are the completed set of relation structures that could be
reasonably used for a database design/schema for this model:
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) referenced Gallery
Gallery(GAL_NUM, Gal_street_addr, Gal_name)
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
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
Curator_Phone(CUR_NUM, CUR_PHONE)
foreign key (cur_num) references Curator
Curator_Certif(CUR_NUM, CUR_CERTIFICATION)
foreign key (cur_num) references Curator