=====
CS 325 - Week 5 Asyvnchronous Material
=====
=====
"TODAY" WE WILL
=====
* announcements
* continue - intro to E-R modeling, part 1
* SQL - more clauses for the select statement's where clause
* SQL - column and table aliases
* SQL - projecting computed columns and aggregate functions
* prep for next class
-----
ANNOUNCEMENTS
-----
* Reading for this week:
* DB Reading Packet 4 - intro to E-R modeling, part 1
* SQL Reading Packet 3 - more where clause options and aggregate
functions (and more...)
* You should be working on Homework 3! Due this FRIDAY at 11:59 pm
=====
CONTINUING our INTRO to E-R modeling!
=====
* our development approach is:
* FIRST: develop an E-R model of a scenario/setting
* ONLY AFTER THAT: convert that model into an appropriate
database design (that includes appropriate tables!)
* One definition of a database design (Kroenke):
* a database design (or database schema) defines a database' structure:
* its tables,
* relationships,
* domains, and
* business rules
* Key elements in an E-R model (Kroenke):
* entities,
* attributes,
* identifiers, and
* relationships
* we discussed entities (entity classes and entity instances) on
Monday;
* and we started talking about attributes;
=====
a bit MORE about attributes in the E-R model
=====
* reminder: attributes are properties or characteristics of entity
instances of an entity class that users in that scenario care about;
* attributes have domains - the set of permitted values for
that attribute;
* an attribute for a single entity instance may be multi-valued
or single-valued
* CS 325 class style:
* to give the attributes of an entity class as a vertical
"list" on or near the E-R model diagram
Painting
--------
Ptg_title
Ptg_date_purchased
* attributes are assumed to be single-valued unless
(MV) is written next to them in this list
Employee
--------
Empl_lname
Empl_email (MV)
Empl_salary
=====
IDENTIFIERS
=====
* how do people in a scenario tell instances of an entity class apart?
...at the modeling level, the attributes they use for that
are called identifiers
* SOME ENTITY CLASSES DO NOT HAVE IDENTIFIER ATTRIBUTES.
(we'll talk about why this can be useful later!)
* NOT necessarily primary keys! Because at the model stage,
entity classes ARE NOT RELATIONS
(each entity class will EVENTUALLY be turned into ONE OR MORE
relations IN a database design LATER!!!!!!!)
* BUT we do like to indicate these identifying attributes (when they
exist) in our models,
and CS 325 CLASS STANDARD will be to write these identifying
attribute names in ALL-UPPERCASE
Painting
--------
PTG_TITLE
Ptg_date_purchased
Employee
--------
EMPL_LNAME
Empl_email (MV)
Empl_salary
=====
RELATIONSHIPS
=====
* the R in the E-R model!!!!!
* a relationship class is an association that can be between
entity classes (and is significant in the users' scenario)
a relationship instance is an association that can be between
entity instances
* a Customer might be able to have an Account;
Customer Harris has Account 23657
* in the E-R model, we want to give a name describing each
relationship;
* might be "verb"-y, describing the nature of the relationship;
* might be a combo of the names of the entity classes involved;
* might be a combo of the above...!
* How can you indicate a relationship in an E-R diagram
depicting an E-R model?
* it is pretty typical to draw a LINE between entity class
rectangles to indicate a relationship within an E-R model;
* CS 325 Class Standard:
to draw a line with a DIAMOND between the two related entity
classes,
and we'll LABEL that diamond (on or near that diamond,
either is fine) with its descriptive name
* it is NOT required that the name be reasonably-readable
in BOTH directions!
=====
* DEGREE of a relationship
=====
* the number of entities involved in that relationship
a relationship of degree 2, or a binary relationship,
is between TWO entities;
* if a relationship being modeled is of degree more than two,
we will "break" that into 2 or more binary relationships;
(possibly adding small entity classes to make this work
and to possibly be where characteristics of such a relationship
would be placed;)
=====
MODELING can be ... an INTERESTING process!
=====
* it CAN be tricky sometimes to distinguish between attributes
and entity classes!
* it CAN be tricky to distinguish between attributes and relationships
between entity classes!
* remember: the attributes in an E-R MODEL should be characteristics
of ONE instance of that entity class --
be careful that you don't treat a relationship as an attribute
* avoid "burying" a relationship in your attributes...
********************
* LINES are used to indicate relationships between entity
classes at the modeling stage!!!!!!!!!!!!!
********************
======
CARDINALITIES of relationships
=====
* maximum cardinalities
* minimum cardinalities
* maximum cardinalities:
* for a given relationship, for an entity instance in one of the
entity classes involved, HOW MANY instances of the OTHER class
CAN this entity instance be related to (in this relationship)?
* the numbers we really care about:
One or Many!
1 or (N or M)
* the 4 possible maximujm cardinality possibilities we care about:
1:1 - one-to-one
1:N (some people also include N:1) - one-to-many (sometimes also
many-to-one)
N:M - many-to-many
* 1:1 - in a relationship R between entity class A and entity class
B, R has a maximum cardinality of 1:1 if
* an entity instance in A can be related to AT MOST
one entity instance in B,
AND
* an entity instance in B can be related to AT MOST
one entity instance in A
* not all that common, actually!
* 1:N - in a relationship R between entity class A and entity class
B, R has a maximum cardinality of 1:N if
* an entity instance in A can be related to MORE THAN
ONE entity instance in B,
BUT
* an entity instance in B can be related to AT MOST
one entity instance in A
* REALLY common! (arguably the MOST common) in most scenarios;
* N:M - in a relationship R between entity class A and entity class
B, R has a maximum cardinality of N:M if
* an entity instance in A can be related to MORE THAN
ONE entity instance in B,
AND
* an entity instance in B can be related to MORE THAN
ONE entity instance in A
* more common than 1:1, less common (usually) than 1:N
* THIS IS IMPORTANT to NOTE in an E-R MODEL!
(REALLY affects the tables in the eventual design!)
* SO many different ways to SHOW this!
* CS 325 Class Standard:
* put a 1 or M or N NEAR the relationship line NEAR the
entity class rectangle involved;
* but you read these "across" the relationship line;
|------------| 1 primary-teacher-of N |--------|
| Instructor |-------------/\----------------| Course |
|------------| \/ |--------|
* this means an instructor instance can be the primary-teacher-of
MORE than one course,
BUT a course instance can only have ONE instructor instance
who is its primary-teacher;
* this is what these instances CAN have;
========
MINIMUM CARDINALITIES
========
* minimum cardinalities
* for a given relationship, for an entity instance in one of the
entity classes involved, HOW MANY instances of the OTHER class
MUST this entity instance be related to (in this relationship)?
* the numbers we really care about:
One or Zero!
MUST the relationship exist for an entity instance,
or may it NOT exist?
* SO MANY ways to indicate this!
* CS 325 class style standard:
* put an OVAL or a LINE NEAR the relationship line NEAR the
entity class rectangle involved;
* but you read these "across" the relationship line;
|------------| 1 primary-teacher-of N |--------|
| Instructor |-|-----------/\--------------O-| Course |
|------------| \/ |--------|
* this means an instructor instance does not have to
be the primary-teacher-of any course
BUT a course instance MUST have an instructor instance
who is its primary-teacher;
* this is what these instances MUST have; (or may not be required to have)
* end of E-R Modeling, part 1!
=====
NOW -- how about some SQL?
=====
* DEMO some of the features discussed in SQL Reading Packet 3,
WITH the understanding that you WILL read this packet
and it has MORE details about these features;
* a few points about SQL:
* it is NOT case-sensitve EXCEPT within quotes!
* I tend to type SQL in (mostly) lowercase, but I am fine if you use upper-case
or mixed case
* it is nice if you follow a consistent pattern... 8-)
* NOW: some more BASIC select statement SYNTAX and SEMANTICS:
(we'll be ADDING to this as we ADD select CLAUSES in the next few
weeks!)
SYNTAX:
* angle and square brackets below are NOT part of the syntax,
* angle brackets are just surrounding a description
* square brackets are just surrounding OPTIONAL parts
select [distinct] <one or more expressions, sep'd by commas>
from <1 or more table expressions, sep'd by commas>
[where <boolean expr>];
SEMANTICS: [conceptually!! actual algorithms try to be more efficient!]
* STEP 1: take the CARTESIAN PRODUCT of the table expressions in the FROM
clause
* STEP 2: take a relation SELECTION of the rows resulting from STEP 1
for which the WHERE clause <boolean expr> is TRUE
* STEP 3: take a projection of the columns/expressions in the SELECT
clause, in the order they appear, JUST from the rows resulting from
STEP 2
...and only remove duplicate rows from the result IF DISTINCT is
in the SELECT clause
* see 325lect05-2.sql for examples of some more WHERE clause options,
and more;
* but ran out of time, so moving table aliases, column aliases,
computed columns, and aggregate functions to
NEXT WEEK's asynchronous material
* (you can certainly still practice with them in the meantime
based on your reading of SQL Reading Packet 3, however! 8-) )