=====
CS 325 - Week 4 Lecture 1 - 2021-09-13
=====
=====
TODAY WE WILL
=====
* announcements
* intro to the relational model
* prep for next class
* Reading for this week:
* DB Reading Packet 3 - introducing the relational model
* SQL Reading Packet 2 - talking about how to represent
some major relational operations using SQL
* Homework 3 is delayed! More on that when I figure it out.
======
intro to the RELATIONAL model
======
* recall: E.F. Codd developed this while at IBM in 1970
* based on a branch of math: relational algebra
* the DBMS supporting a relational mode is creating
an abstraction that your data is in the form of relational
tables (relations, or tables that meet the relational criteria)
* powerful and elegant!
* relational model allows for the ability to create
DBMS-independent database designs
^ includes, for a relational database, what tables?
what columns in those tables? how are they
related? <-- we'll need a bit more, also...
=====
* a relational databases is a collection of relations
* and, what is a relation?
* being formal:
Ulmann, 2nd ed., p. 19:
a subset of the Cartesian product of a list of domains
* (pulling from Sunderraman here...)
* relation scheme/schema:
* (in math terms) a finite sequence of unique attribute names
(attribute is kind of like the math equivalent of a column)
You can give this relation scheme a name -- so, for example:
employees = (empl_id, empl_last_name, empl_str_addr, empl_salary)
* an attribute name A is associated with a domain, dom(A), a set of
values, which includes the special value null
* ex: dom(empl_id) could be the set of integers between 1000 and
9999 and the special value null
* given a relation scheme R = (A1, A2, ... An),
a relation r on the scheme R is defined as any finite subset
of the Cartesian product
dom(A1) x dom(A2) x ... x dom(An)
* reminder: Cartesian product of sets A and B
A x B
the set of all ordered pairs of ONE element from A and
ONE element from B
* happily, a relation is a FINITE subset of this Cartesian
product!
* and note that ONE element of this relation is the ordered
set of ONE value of A1, ONE value of A2, ... ONE value of An
...sounds kind of like a ROW, doesn't it?
...in Math, that's also called a tuple
* (And in practice, we'll tend to pick subsets of this
Cartesian product that we find "useful" or have some sort
of meaning)
SO, for relation scheme
employees = (empl_id, empl_last_name, empl_str_addr, empl_salary)
a relation might be:
{(1111, 'Jones', '111 Ash Str', 20000), <-- 1 element of this set, a tuple!
(2222, 'Nguyen', '123 Elm', 25000)} <-- another element of this set, another tuple!
* a relational database scheme D
is a finite set of relation schemes {R1, R2, ... Rm}
and a relational database on a relational database scheme D
is a set of relations {r1, r2, ... rm} where each ri is a relation on the corresponding
relation scheme Ri
* if you think of a relation as a tabular thing,
a tuple in a relation is like a row in that tabular thing,
and an attribute of the relation is like a column in that tabular thing
We'd like a tuple/row to hold data that pertains to some "thing" or portion of a "thing"
* a cell of a relation/table is the intersection of a row and a column,
or a single attribute's value within a particular tuple
* NOW -- not everything a person calls a "table" MEETS the relation definition
(but we are going to try to make sure the tables we create for a relational database DO meet
the definition!)
* if you follow the above definitions, you NEVER have a multi-valued cell!
EACH cell contains either NO value (the special value null) or ONE value
(it has ONE from the domain of that attribute, since null might BE in that domain!)
* if you recall your basic rules of sets,
a thing is either a member of a set or it is not --
NO concept of being in a set "multiple times"
THAT's why a relation (or a table meeting its rules) cannot have
duplicate ROWS (cannot have duplicate TUPLES)
* if you recall your basic rules of sets,
we don't really care about the order of the elements in a set --
or, the order of the elements is not SIGNIFICANT
...so, the order of the rows or tuples in a relation is not supposed to
be significant.
* each attribute within a relation must have a unique name
(that's straight from the relation scheme definition above
* all of the values for a particular attribute within a relation/table
must be from that attribute's domain