=====
CS 325 - Week 11 Asynchronous Material
=====
=====
"TODAY" WE WILL
=====
* announcements
* continuing converting model into a design/schema, part 2
* SQL: mention the ANSI syntax for joins
* SQL: demo of some more-than-two-table-joins
* SQL: intro to views
* [IF time:] SQL: intro to sequences
* prep for next class
* Reading:
* DB Reading Packet 8 - Database Design, Part 2
* SQL Reading Packet 7 - has the view material
(but Sequences are in SQL Reading Packet 6,
and where are those more-than-2-table join examples...?)
* Should be working on Homework 8, due by 11:59 pm on Friday
* Should be working on converting your Project model draft into
a Project design draft (due November 19th)
* (and have your talked to your academic advisor about
Spring 2022 registration? 8-) )
=====
MORE odds-and-ends that you might encounter in
converting a database model into a database design/schema
=====
FIRST: handling so-called ASSOCIATION entity classes
=====
* sometimes you find, in a model, an entity class that ASSOCIATES
two or more other entity classes in a transaction that
is pretty important to the scenario,
* AND has attributes of its own,
* BUT does not have reasonable/apparent identifying attributes.
* Consider the Flight model we showed on the recording --
We might get "base" tables for entity classes Flight and Customer
of:
Flight(FLIGHT_NUM, flight_date, exp_departure, exp_arrival,
actl_departure, actl_arrival)
Customer(CUST_NUM, cust_lname, cust_fname)
* Flight_reservation, here, is an example of an
association entity class;
we'll start a "base" table for it:
Flight_Reservation(
...but what should its primary key be?
* what MIGHT be different here? you sometimes have TWO reasonable
choices for the primary key of association entity class
Flight_reservation;
* one choice: to introduce a primary key for that association
in the flight model, you might introduce a reserv_num as a primary
key for Flight_reservation:
Flight_Reservation(RESERV_NUM, reserv_date_made, flight_num,
cust_num)
foreign key(flight_num) references Flight,
foreign key(cust_num) references Customer
* another choice: OR it might "feel" more appropriate,
for many association entity classes, to consider
thre association entity to be identified by the
identifiers of the entities it is associating;
that is, maybe I'd rather identify a flight reservation
by the customer reserving for the flight and the flight that
customer is making a reservation for;
In this approach, the foreign keys, combined, are made the
primary key of the association entity class' "base" table:
Flight_Reservation(reserv_date_made, FLIGHT_NUM,
CUST_NUM)
foreign key(flight_num) references Flight,
foreign key(cust_num) references Customer
* another classic association entity class:
sale_item_quant, associating a sale and a sale_item
with JUST the attribute quantity, the quantity of THAT
sale item in THAT sale
This is a pretty classic case where you might choose
not to intro a separate primary key for sale_item_quant's
"base" table --
Sale_item_quant(SALE_NUM, SKU_NUM, quantity)
foreign key(sale_num) references Sale,
foreign key(sku_num) references Sale_item
* what's another case where you might use a foreign key
from a relationship line as part or all of another
table's primary key?
you MIGHT do so for handling weak entity classes
(since they are so dependent on another entity class,
and are usually related to that entity class as the child
in a 1:N relationship or in 1:1 relationship where that
relationship is REQUIRED on its end)
* consider a scenario with multiple apartment buildings,
they want to keep track of, for each room in one,
its room number, num of bedrooms, and num of baths, but
maybe that's it;
Room_num cannot be the Room entity class "base" table's primary
key, because there might be, say, a room 101 in more than one
apartment building;
* You know you WILL be adding bldg_num to Room's "base" table
because 1:N relationship contains;
Room(ROOM_NUM, num_bedrooms, num_baths, BLDG_NUM)
foreign key(bldg_num) references Apt_Bldg
...so the weak entity class' "base" table's primary key
may be a COMBO of one of its attributes AND the primary
key of the "base" table of the entity class it is dependent
upon;
=====
What about RECURSIVE relationships?
=====
* consider Employee-manages-Employee -- and it is 1:N;
still put the "parent's" "base" table's primary key into
the "child's", as a foreign key, BUT they're the same "base" table
(!!!), so the foreign key needs a different name, suggesting the
relationship:
Employee(EMPL_NUM, mgr_num
foreign key(mgr_num) references Employee(empl_num)
* populating this can be fun!
if you have an employee with NO manager, insert their
row first! (With NULL for mgr_num)!
THEN you can insert employees managed by THAT employee,
THEN you can insert employees managed by any of THOSE employees,
and so on!
(typically only annoying for the FIRST insertions...!)
* by the way: what if the foreign key cannot be null?
...you might need to create the table with a foreign key
that can be null, insert a first row with mgr_num as null,
THEN add a second row that manages them, and so on,
THEN use update commands to UPDATE their foreign keys to
now-existing rows,
THEN use an ALTER command to make the foreign key column
NOT NULL...!
a kluge, but it can work!!
=====
MULTIPLE relationships between entity classes
=====
* still handle each individually!
BUT!! you may need to be creative/descriptive with the table names,
OR you may need a renamed foreign key,
depending on the cardinalities of the relationships involved;
* if 2 M:N relationships? JUST give them different intersection table
names!
Member(MEM_NUM,....
Talk(TALK_NUM, ....
In_charge(MEM_NUM, TALK_NUM)
foreign key(mem_num) references Member,
foreign key(talk_num) references Talk
Sign_Ups(MEM_NUM, TALK_NUM)
foreign key(mem_num) references Member,
foreign key(talk_num) references Talk
* BUT -- what about
the music prof advises and gives lesson to students situation?
* Student needs Music_prof's foreign key TWICE --
so NAME both foreign keys to make CLEAR which relationship they
are for;
Student(STU_NUM, advisor_num, private_tch_num,
foreign key(advisor_num) references Music_prof(prof_num),
foreign key(private_tch_num) references Music_prof(prof_num)
========
SQL "ANSI" join syntax
========
from https://en.wikipedia.org/wiki/SQL:
* "SQL became a standard of the American National Standards Institute
(ANSI) in 1986, and of the International Organization for
Standardization (ISO) in 1987."
* ANSI SQL/99 included some additional ways to express different
join operations;
...here's one of those syntaxes:
select expr1, expr2, ..., exprN
from tbl1 JOIN tbl2 ON (tbl1.attrib1 = tbl2.attrib2);
this, then, is a reasonable alternative to saying:
select expr1, expr2, ..., exprN
from tbl1, tbl2
where tbl1.attrib1 = tbl2.attrib2;
* AND maybe you'll be less likely to forget the JOIN
condition using the ANSI join syntax!! 8-)
* FEEL free to use the above -- it is FINE, good style!!!!
(BUT the ANSI "natural join" syntax does NOT meet class
style standards -- that's a DIFFERENT syntax than either one
above!!)
======
joining MORE than TWO tables!
======
* JUST REMEMBER:
when you join N tables,
you need to have (N-1) appropriate join conditions!!!!!!!!
what if you want:
the name of a customer,
the name of the employee who represents them,
and the name of the department that employee works for?
select cust_lname, empl_last_name, dept_name
from customer c, empl e, dept d
where c.empl_rep = e.empl_num
and e.dept_num = d.dept_num;
HERE is that query in ANSI join notation:
select cust_lname, empl_last_name, dept_name
from customer c
JOIN empl e ON c.empl_rep = e.empl_num
JOIN dept d on e.dept_num = d.dept_num;
======
intro to SQL views
======
* a view is a "derived" table -- it is NOT actually stored in rows
in the database (or in a data structure containing the actual data) --
instead, a view in a DBMS JUST contains HOW to generate
the desired information when the view is used!
(the user can USE it like it was a table,
but whenever it is used, it is generated at that point!)
* you can have a view for a "tabular" beastie users want to refer
to but that would be problematic in terms of
modification anomalies -- or just makes certain other queries
more convenient
AND you can have a view when you want to restrict someone's
access to just SOME of the rows of some table (you can grant
access to a view instead of the whole table the view is based on)
* here's the basic syntax:
create view view_name as
desired_select_statement;
* NOW a few more nuances here:
* you can rename the columns in the view in a couple of
ways, and sometimes you HAVE to...!
* that is -- the view "looks" like a table to users and programmers,
who can use it like a table in select statements,
SO its column names HAVE to meet SQL's column name syntax;
(so, for a view based on an aggregate function call or
computation, you HAVE to rename those columns!
METHOD ONE:
* use column aliases in the select statement!
create view short_empl2 as
select empl_last_name last_name, job_title job
from empl;
* (note: now, using the view short_empl2, ONLY the column
names last_name and job are known!
drop view salary_avgs;
create view salary_avgs as
select job_title, avg(salary) job_avg_salary
from empl
group by job_title;
METHOD TWO:
* put the desired column names IN a comma-separated list IN
parentheses AFTER the view's name in the create view statement
(but you must give a name for EACH column in this version):
drop view salary_avgs2;
create view salary_avgs2(job_title, job_avg_salary) as
select job_title, avg(salary)
from empl
group by job_title;
...and that should be enough for you to try out views
in Friday's lab exercise and the next homework assignment!