=====
CS 325 - Week 5 Lecture - 2021-09-20
=====
=====
TODAY WE WILL
=====
* announcements
* review clicker questions
* intro to entity-relationship modeling, PART 1
* prep for next class
-----
Announcements:
-----
* Reading for this week:
* DB Reading Packet 4 - intro to
entity-relationship modeling, PART 1
* SQL Reading Packet 3 - More where clause options and aggregate
functions
* Should be working on Homework 3! due Friday;
* The Project Handout *should* come out this week;
=====
* ENTITY-RELATIONSHIP MODELING!
also often abbreviated as E-R modeling, ER modeling...
* NOTE: if you do additional reading on this,
that's great! BUT you are expected to use the CS 325 class
style standards for E-R models that I have to look at... 8-)
* data modeling: the process of creating a representation of the
users' view of the data (in some setting or scenario)
* can have a LARGE effect on the usability of an eventual database!
* IMPORTANT PHILOSOPHY HERE:
* you will have a much better chance of building a robust, usable
database for a scenario if you MODEL it BEFORE deciding on
its relations/tables!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
* so that's the idea here:
* FIRST you model the scenario,
* THEN you convert that model into appropriate corresponding
relations/tables
* IN entity-relationship modeling,
you are trying to think of a scenario in terms of ENTITY (classes)
and the RELATIONSHIPS (relationship classes) BETWEEN them;
while an entity (class) will EVENTUALLY be converted to ONE or MORE
tables in an eventual database DESIGN,
IN THE MODELING PHASE, YOU SHOULD NOT BE THINKING IN TERMS OF
TABLES YET!!
* if you are E-R modeling, you should be trying very hard to think
in terms of ENTITY (classes)!
======
* [quick definition: AFTER the modeling, THEN we will convert the E-R model
we come up with into a Database DESIGN/database schema:
* database design? database schema?
the STRUCTURE of the relations in a database and their relationships;
* here's a slightly more pragmatic definition than the one from
last week:
* [from Kroenke's Database Design test]
* its relations/tables,
relationships, <-- we'll define those via foreign keys
domains, <-- of its attributes
business rules <-- day-to-day "rules"-of-thumb within the
scenario
* (you can think of business rules as being one way to
state some of the constraints on allowable data values...)
* depending on how you express this, it CAN be pretty darn
DBMS-software independent;
=====
* E-R models!
* introduced by Peter Chen in a 1976 paper;
* a model created via E-R modeling is often called an E-R model,
and is often depicted using an E-R diagram!
* "base" objects in an E-R model are:
* entities (entity classes)
* attributes
* identifiers
* relationships
=====
ENTITIES and entity classes!
=====
* entity: a thing that exists and is distinguishable from other things
...it needs to be something that can be identified within a scenario
or a setting; that the users within may want to track
* an entity can be concrete or abstract
* A particular student at a University
* A particular course at that University
* an entity CLASS is a SET of entities of the same type
* the SET of students at a University
* the SET of courses at that University
* in an E-R model,
we want to determine the entity classes that are significant
in a scenario;
* entity classes CAN be disjoint, but they don't have to be;
* In an E-R diagram (depicting an E-R model),
an entity class is typically depicted as a labeled rectangle
(this part IS common!)
-----------
| Student |
-----------
----------
| Course |
----------
=====
ATTRIBUTES
=====
* attributes or properties describe entity class CHARACTERISTICS that
are important or significant to the users in a scenario
* really, in an E-R model, an entity instance is represented
by a set of attributes;
* consider:
* what are significant or important characteristics of
an instance of this entity class to those in the scenario?
* what is ethical or legal to keep track of?
* what is practical to keep track of?
* how are these depicted in an E-R model, in an E-R diagram
* you'll see them depicted in ovals attached to the entity
class rectangle -- but that's clunky unless you have
special software to deal with that;
* BUT our CS 325 class approach to this will be to make
a list of such attributes labeled by the entity class
name, placed "nearby" or on the next page
-----------
| Student |
-----------
----------
| Course |
----------
Student Course
-------- -------
St_last_name Course_name
St_first_name Course_length
St_email
St_str_addr
St_date_enrolled
St_date_of_birth
* and notice that each of these attributes has a DOMAIN,
a set of possible values....
we will be a little loose about indicating this in our
E-R diagrams;
but some approaches have you create and document a data dictionary
giving the domains for each attribute;
We WILL, however, note when attributes are MULTI-VALUED --
when ONE entity instance might REASONABLY have multiple values
for that attribute;
* we'll (CS 325 style approach) mark multi-valued attributes
with (MV) after the attribute's name
-----------
| Student |
-----------
----------
| Course |
----------
Student Course
-------- -------
St_last_name Course_name
St_first_name Course_length
St_email (MV)
St_str_addr
St_date_enrolled
St_date_of_birth
* here, saying that a *single* student entity instance might
have more than one e-mail address;
* (and the others are assumed to be single-valued!)
* note: BUSINESS RULES can help explain/decide why some attributes
might or might not be allowed to be multi-valued;
And a scenario might ALSO have business rules that
note why some attributes for an entity class might or
might not be allowed to have null in their domains...
* (Are all students always assigned an e-mail address by
the scenario, for example? Then it might be that St_email
is not permitted to be null...)
* we are not formally noting that in our E-R model,
*but* it SHOULD be included in the list of BUSINESS
RULES you should START building during the E-R modeling
process;
* relationships: will be where we start in Week 5 Asynchronous
materials;