=====
Week 6 Asynchronous Materials
=====
=====
"TODAY" WE WILL
=====
* announcements
* continue with supertype/subtype entity classes
* SQL: computed columns and column aliases
* SQL: table aliases
* SQL: joins of MORE than two tables [but did not get to today]
* SQL: aggregate functions
* SQL: intro to sub-selects [but did not get to today]
* prep for next class
=====
Reading
=====
* reading for this week:
* DB Reading Packet 5 - E-R modeling, part 2
* SQL Reading Packet 4 - sub-selects (and more)
(also some more from SQL Reading Packet 3)
=====
OTHER Announcements
=====
* You should be working on Homework 4, due this FRIDAY, October 1st
* EXAM 1 is coming up!
* Monday, October 11th
* we'll review for it during class on Monday, October 4th
* this week (Week 6) is pretty normal!
* a Lab Exercise this Friday, October 1st
* Homework 4 due Friday, October 1st
* Homework 5 coming out this weekend
* NEXT week is more unusual!
* We'll review for Exam 1 in class on Monday, October 4th
* Week 7 Asynchronous materials posted Wednesday
* NO LABS on Friday, October 8, because I'll be at a conference
* AND: Homework 5 is DUE on FRiday, October 8th
* BUT Homework 6 will not come out until AFTER Exam 1
* ONE MORE NOTE:
* Recall: in syllabus, it notes that once example solutions
for a Homework are posted, you can no longer submit work
for that homework
* To give you a chance to look over those example solutions
BEFORE Exam 1,
I will make these available EARLY on SATURDAY, OCTOBER 9th
SO -- you may still submit HOMEWORK PIECES for CREDIT
through 11:59 pm on FRIDAY, OCTOBER 8th
=====
CONTINUING with supertype and subtype entity classes!
=====
* better choices for modeling so-called IS-A relationships --
a Savings_Account IS An Account,
etc.
* these are also sometimes called
GENERALIZATION/SPECIALIZATION relationships
* how do you know when these would be useful for your model?
* sometimes you just realize it -- hey, inventory items
include potion items and battle items
* sometimes you notice DISTINCT subsets of seemingly "optional"
attributes --
that is, some attributes all the items the items
in that entity class have,
BUT also some distinct subsets that seem to be "optional",
but make more sense when you think of them as characteristics
of a subtype entity class instead;
* sometimes the relationships give you clue --
you might have some relationships all instances of an entity
class can have,
but some you really want to limit to only some members of an
entity class --
and you realize these make more sense if you think of
some relationships being with a supertype entity class
and some being with a subtype entity class
* we have some different notation these relationships in
an E-R model
...and you will be using our CS 325 standard format for these:
* an entity class rectangle for EACH supertype and EACH subtype
entity class
* BUT the relationship lines are different here --
draw a line from each EACH supertype and EACH subtype
entity class TO a small circle,
labeled with d if the subclass entity classes are DISJOINT
and labeled with o if the subclass entity classes are OVERLAPPING
and labeled with u if the subclass entity classes are part of
a UNION supertype-subtype situation
* put a small u-shaped curve on the line connecting each
subtype entity class to that circle,
* put a hash or oval on the line connecting the supertype
entity class to that circle, indicating whether a
supertype instance MUST be one of subtype instances, also,
or not;
======
handling ATTRIBUTES for supertype/subtype entity classes
======
* generally, we'd like a characteristic or attribute in a model
to only appear ONCE, in ONE entity class' attribute list
* if an attribute is meant to be for ALL instances of that
supertype entity class, it goes in the supertype entity
class' attribute list
* SO -- for the Account scenario:
Account Savings_acct Checking_acct
-------- ------------- --------------
ACCT_NUM int_rate per_ck_charge
acct_date_opened min_balance monthly_fee
acct_balance
* NOTICE these distinctive things here:
* there aren't repeated attributes here!
* it is QUITE common that the subtype entity classes
do not have identifying attributes! (those tend
to be in the supertype entity class' attribute lists)
* SO -- for the Univ_person scenario:
Univ_person Student Instructor
----------- -------- ----------
UNIV_ID curr_gpa salary_per_course
Last_name
First_name
Campus_email
=======
what about those UNION supertype/subtypes?
=======
* odd beasties!
* when you have a business rule that says "certain
<really distinct categories> can do <certain important thing>",
and so it turns out that making a UNION supertype entity class
for "those that can do this thing" can be very useful in a model;
(see the Sponsor-of-Events model example in DB Reading Packet 5)
Sponsor Club Team Dept Event
------- -------- -------- --------- -----
CLUB_NUM TEAM_CODE DEPT_CODE EVENT_NUM
Club_Name Sport Dept_title Event_title
Is_active Season Office_num Event_date
* it is QUITE common if the union supertype has few or even
no attributes at the model stage!
* it is QUITE common if the union subtypes all have VERY
different and distinct attributes, and distinct
identifying attributes!
* BUT: IF there ARE common attributes for union supertypes,
put those in the union supertype's entity attribute list...
========
SQL time!!!
========
--------
computed columns and column aliases!
...now moving to 325lect06-2.sql...