=====
CS 325 - Week 11 Lecture 1 - 2021-11-01
=====
=====
TODAY WE WILL
=====
* announcements
* converting a model into a database design/schema, part 2
* prep for next class
* Should start working on Homework 8, due Friday November 5th, 11:59 pm
* Especially after today (maybe Wednesday), it would be a good time
to start converting your project model draft into a project design draft,
since you should have all the tools for that, having gone over
DB Reading Packets 7 and 8;
=====
CONVERTING supertype/subtype entity classes
into appropriate relations
=====
* the STEPS...
* again, the order of these can definitely vary -- this is
an ordering I've found myself leaning toward;
* you'll still have a "base" table for each of the entity classes
involved year -- yes, for each supertype entity class and for each
subtype entity class
Account(
Savings_Acct(
Checking_Acct(
* for the supertype's "base" table, you'll determine an
appropriate primary key
Account(ACCT_NUM,
Savings_Acct(
Checking_Acct(
* NOW: for disjoint and overlapping subtypes,
(that is, NOT for union subtypes!!!),
you will put the primary key of the supertype
entity class' "base" table
into EACH of the subtypes' entity class's "base"
tables as THEIR primary key,
and also as a FOREIGN key referencing the
supertype entity class' "base" table
Account(ACCT_NUM,
Savings_Acct(ACCT_NUM,
foreign key(acct_num) references Account
Checking_Acct(ACCT_NUM,
foreign key(acct_num) references Account
* To make certain queries easier, it is common (and
we'll practice that here, for the experience,
and so it's a class style standard) to add some
additional attribute(s) also:
for disjoint subtypes: add an attribute to the
supertype entity class' "base" table whose domain
appropriately indicates WHICH subtype that supertype's
entity is an instance of;
for Account/Savings/Checking model, perhaps:
Account(ACCT_NUM, acct_type
(and acct_type's domain could be ('Savings', 'Checking'),
or ('S', 'C'), etc.)
Savings_Acct(
Checking_Acct(
for overlapping subtypes, add a somewhat-binary attribute
to the supertype entity class' "base" table
for each subtype, indicating if it IS that subtype or
not (we'll demo that in a little bit)
* then you can proceed handling the rest of the design
probably as we've discussed earlier;
* SO: finishing the Account/Savings/Checking model conversion,
we can add in the single-valued attributes into their
appropriate "base" tables:
Account(ACCT_NUM, acct_type, date_opened, balance,
Savings_Acct(ACCT_NUM, int_rate,
foreign key(acct_num) references Account
Checking_Acct(ACCT_NUM, min_balance, per_ck_charge,
foreign key(acct_num) references Account
we can add a NEW table for each MULTI-valued attribute,
following the same approach as discussed last week:
Account(ACCT_NUM, acct_type, date_opened, balance,
Savings_Acct(ACCT_NUM, int_rate,
foreign key(acct_num) references Account
Savings_Bonuses(ACCT_NUM, BONUS_FEATURE)
foreign key(acct_num) references Savings_Acct
Checking_Acct(ACCT_NUM, min_balance, per_ck_charge,
foreign key(acct_num) references Account
* (yes, it is fine for a foreign key to reference
a subtype entity class' "base" table, for a multi-valued
attribute for that subtype's entity class)
* and handle any other relationships -- 1:1, 1:N, N:M --
with each of these entity classes, supertype or
subtype, as discussed last week
(also remembering that, in handling those, it is also
fine to have a foreign key referencing a subtype's entity
class' "base" table if the relationship calls for that)
* let's handle the Univ-person model:
* "base" table for each entity class:
Univ_person(
Student(
Instructor(
* determine appropriate primary key for the supertype
entity class' "base" table,
and make it the primary key of the subtype entity classes'
"base" tables also, as well as a foreign key
referencing the supertype entity class' "base" table
Univ_person(UNIV_ID,
Student(UNIV_ID,
foreign key(univ_id) references Univ_person
Instructor(UNIV_ID,
foreign key(univ_id) references Univ_person
* and since these are overlapping subtypes,
add an "is_blank" attribute for each subtype to
the supertype entity class' "base" table:
Univ_person(UNIV_ID, is_student, is_instr,
Student(UNIV_ID,
foreign key(univ_id) references Univ_person
Instructor(UNIV_ID,
foreign key(univ_id) references Univ_person
* Now handle the rest of attributes as discussed
previously:
Univ_person(UNIV_ID, is_student, is_instr, last_name,
first_name, campus_email)
Student(UNIV_ID, gpa, semester_matricd, semester_graduated)
foreign key(univ_id) references Univ_person
Instructor(UNIV_ID, is_w4_on_file, date_1st_hired)
foreign key(univ_id) references Univ_person
* what about UNION supertype subtypes?
* how is converting that into a design different?
* you still make a "base" table for each entity class;
(considering the Sponsor model:
Sponsor(
Club(
Team(
Dept(
* BUT -- here, each subtype entity class' "base" table
gets its own appropriate primary key --
and you'll introduce an appropriate primary key to
supertype entity class' "base" table as well;
Sponsor(SPONS_ID,
Club(CLUB_NUM,
Team(TEAM_CODE,
Dept(DEPT_CODE,
* I still need the supertype entity class' "base" table's
primary key in the suptype entity class' "base" tables,
but JUST as a foreign key, NOT as any part of the primary key;
Sponsor(SPONS_ID,
Club(CLUB_NUM, spons_id,
foreign key(spond_id) references Sponsor
Team(TEAM_CODE, spons_id,
foreign key(spond_id) references Sponsor
Dept(DEPT_CODE, spons_id,
foreign key(spond_id) references Sponsor
* and, like a disjoint superclass, you add a blah_type
attribute to the supertype entity class' "base" table
to make certain queries easier:
Sponsor(SPONS_ID, spons_type,
(maybe spons_type's domain is ('club', 'team', 'dept'))
Club(CLUB_NUM, spons_id,
foreign key(spond_id) references Sponsor
Team(TEAM_CODE, spons_id,
foreign key(spond_id) references Sponsor
Dept(DEPT_CODE, spons_id,
foreign key(spond_id) references Sponsor
* (and now handle remaining attributes,
and other entity classes, and other relationships...)
Sponsor(SPONS_ID, spons_type)
(maybe spons_type's domain is ('club', 'team', 'dept'))
Club(CLUB_NUM, spons_id, club_name, is_active)
foreign key(spond_id) references Sponsor
Team(TEAM_CODE, spons_id, sport, season)
foreign key(spond_id) references Sponsor
Dept(DEPT_CODE, spons_id, dept_title, office_num)
foreign key(spond_id) references Sponsor
Event(EVENT_NUM, event_title, event_date, spons_id)
foreign key(spons_id) references Sponsor