CS 325 - Week 10 Lecture 1 - 2016-10-25

*   converting an ER model --
    ER diagram including the entity attribute lists --
    (and your business rules)
    into a database schema/database design;

    database design:
    *   relations
    *   relationships 
    *   domains
    *   business rules

    *   sorry, we also call the process of converting a
        database model into a database design
	the process of database design
	(yes, it is both noun and verb...!)

    *   note that you still keep database model
        around, as part of scenario's documentation;

        *   it is VERY common to basically learn more
	    about the scenario -- and thus improve
	    the model (or vice versa)
	    during the process of database design,
	    during this very conversion

	    (and during table creation,
	    and population, and etc...!)

        *   so please update/improve your model as you go,
            ****************
	    WHILE STILL LEAVING IT AS A MODEL!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            ****************

	    (relationships are still relationships,
	    M:N are still M:N,
	    (MV) attributes are still that, in the model,
	    etc.)

*   there's more than one way to depict a design's:
    *   relations
    *   relationships 
    *   domains
    *   business rules

    ...in lecture, we may give the domains short shrift at
       first, and use relation structures for the
       relations and relationships,

       BUT in the project, I'll ask you to give the
       relations in SQL-create-table form,
       so at least physical domains are included;

    (and our business rules are a list of business rules...)

*   the resulting design is largely RDBMS-independent;
    should be able to be reasonably implemented on
    any relational DBMS

*   talking about conversion, practicing it with
    the art gallery example database
   
*   each entity class will turn into ONE OR MORE
    relations in the resulting database design --

    step 1 part 1 - come up with an initial set of
        "base" tables, to then be ADDED to in
	later steps;

	start with a "base" relation for each
	entity class (understanding that more
	may be added as we go for a particular entity class)

Painter( 

Painting(

Gallery(

Art_Period(

Donor(

Curator(

    step 1, part 2 - determine reasonably primary
        keys for each of these "base" relations;
        *   might LOOK at identifying attributes
	    for that relation --
	    BUT you are not BOUND to those;

	    (and some, which we'll talk about
	    more next week -- association
	    entity classes, and weak entity
	    classes, and subtype entity classes --
	    MAY/WILL get their primary keys differently,
	    as we'll discuss a little later)
  
        *   you CAN add a more-suitable primary key
	    attribute at this stage!!!
            *   yes, they are often numeric or
	        strings containing digits...

            *   DON'T choose something that
	        isn't unique;
		AVOID choosing things that
                people in the scenario might tend to
		change later... (department
		names, for example...)

Painter(PTR_NUM, 

Painting(PTG_NUM,

Gallery(GAL_NUM,

Art_Period(PD_CODE, 

Donor(DON_NUM, 

Curator(CUR_NUM,

    *   step 1, part 3 - add the remaining attributes
        from the model to the design-thus-far
	(adding new relations as needed)

        *   is it a single-valued attribute?
	    ...add it to the "base" relation for
	    its entity class;

	*   is it a multi-valued attribute?
            *   create a NEW relation,
	        whose primary key is the combination
		of the multi-valued attribute
		and the "base" relation's primary
		key,
		(and that "base" relation's
		primary key will also be a
		foreign key back to the
		"base" relation")

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

    *   step 2 - HANDLE RELATIONSHIPS
        *   handle EACH relationship in the
	    model appropriately;

        *   *mostly* handled based on the
	    maximum cardinalities of each
	    relationship;

        *   you handle 1:1 relationships
	    one way, 1:N in another, and
	    M:N in yet another;

        *   let's start with 1:N relationships
	    (often the most common in a model)

	    *   for EACH 1:N relationship,
	        the primary key of "base" table
		of the entity class on the "1" side

		is added to the "base" table of
		the entity class on the "N" side,
		and also made a foreign key referencing
		the "base" table of the "1" side

*   first: handling the 1:N relationship Painters-paints-Painting:

Painter(PTR_NUM, ptr_lname, ptr_fname,
        ptr_dob, ptr_dod

Painting(PTG_NUM, ptg_title, ptg_cost,
         ptg_acquis_date, ptr_num
    foreign key (ptr_num) references painter

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