Transcript CSc-340 01b
The Relational Model
Chapter 2
Database Schema
Keys
Schema Diagrams
Relational Query Languages
Relational Operations
CSc340 1b
1
Go Over Homework/Project
E-Mail with your Background
Anyone feeling underwhelmed or overwhelmed
with course?
Name & General Description of Project
e.g. "Hannay Reels"
Manufactures Steel & Aluminum Reels for Hose & Cable
in Industrial Applications
Applications: Customer Relations Management, Order
Entry, Accounts Receivable, Purchasing, Accounts
Payable, Inventory, Manufacturing Control, Payroll, etc.
Sample Projects: http://cs.union.edu/csc340
CSc340 1b
2
Example of a Relation
attributes
(or columns)
tuples
(or rows)
Attribute Types
The set of allowed values for each attribute
is called the domain of the attribute
Attribute values are (normally) required to
be atomic; that is, indivisible
The special value null is a member of
every domain
The null value causes complications in the
definition of many operations
Relation Schema and Instance
A1, A2, …, An are attributes
R = (A1, A2, …, An ) is a relation schema
Example:
instructor = (ID, name, dept_name, salary)
Formally, given sets D1, D2, …. Dn a relation r is a subset of
D1 x D2 x … x Dn
Thus, a relation is a set of n-tuples (a1, a2, …, an) where each ai
Di
The current values (relation instance) of a relation are specified
by a table
An element t of r is a tuple, represented by a row in a table
Relations are Unordered
Example: instructor relation with unordered tuples
Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
Database
A database consists of multiple relations
Information about an enterprise is broken up into parts
instructor
student
advisor
Bad design:
univ (instructor -ID, name, dept_name, salary, student_Id, ..)
results in
repetition of information (e.g., two students have the same instructor)
the need for null values (e.g., represent an student with no advisor)
Normalization theory (Chapter 7) deals with how to design “good”
relational schemas
Keys
Let K R
K is a superkey of R if values for K are sufficient to identify a
unique tuple of each possible relation r(R)
Superkey K is a candidate key if K is minimal
Example: {ID} is a candidate key for Instructor
One of the candidate keys is selected to be the primary key.
Example: {ID} and {ID,name} are both superkeys of instructor.
which one?
Foreign key constraint: Value in one relation must appear in
another
Referencing relation
Referenced relation
Schema Diagram for University Database
Relational Query Languages
Procedural vs.non-procedural, or declarative
“Pure” languages:
Relational algebra
Tuple relational calculus
Domain relational calculus
Relational operators
Selection of tuples
Relation r
Select tuples with A=B
and D > 5
σ
A=B and D > 5
(r)
Selection of Columns (Attributes)
Relation
r
Select A and C
Projection
Π
A, C
(r)
Joining two relations – Cartesian Product
Relations r, s
r xs
Union of two relations
Relations r, s
r s
“Reel” Life Example of “Union” [1 of 2]
CSc340 1b
15
“Reel” Life Example of “Union” [2 of 2]
The Query
(UPR00100=PayrollNameAddr; UPR00400=PayMaster; UPR00500=DeductionMaster)
SELECT
FROM
WHERE
UPR00100.EMPLOYID, UPR00100.EMPLCLAS, UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME,
UPR00100.BRTHDATE, UPR00100.GENDER, UPR00100.STRTDATE, UPR00400.PAYRCORD, UPR00400.PAYRTAMT,
UPR00500.DEDUCTON
UPR00100 INNER JOIN
UPR00400 ON UPR00100.EMPLOYID = UPR00400.EMPLOYID LEFT OUTER JOIN
UPR00500 ON UPR00100.EMPLOYID = UPR00500.EMPLOYID
(UPR00400.PAYRCORD = 'WEEKLY' OR
UPR00400.PAYRCORD = 'HOURLY') AND (UPR00100.EMPLCLAS IN (@emplClass)) AND
(UPR00500.DEDUCTON IN (@wchMED))
UNION
SELECT
FROM
WHERE
UPR00100_1.EMPLOYID, UPR00100_1.EMPLCLAS, UPR00100_1.LASTNAME, UPR00100_1.FRSTNAME, UPR00100_1.MIDLNAME,
UPR00100_1.BRTHDATE, UPR00100_1.GENDER, UPR00100_1.STRTDATE, UPR00400_1.PAYRCORD, UPR00400_1.PAYRTAMT,
'NONE' AS DEDUCTON
UPR00100 AS UPR00100_1 INNER JOIN
UPR00400 AS UPR00400_1 ON UPR00100_1.EMPLOYID = UPR00400_1.EMPLOYID
(UPR00400_1.PAYRCORD = 'WEEKLY' OR
UPR00400_1.PAYRCORD = 'HOURLY') AND (UPR00100_1.EMPLCLAS IN (@emplClass))
AND (NOT EXISTS
(SELECT
EMPLOYID
FROM
UPR00500 AS UPR00500_1
WHERE
(EMPLOYID = UPR00100_1.EMPLOYID) AND (DEDUCTON LIKE 'MED%')))
CSc340 1b
16
Set difference of two relations
Relations r, s
r –s
Set Intersection of two relations
Relation r, s
rs
Joining two relations – Natural Join
Let r and s be relations on schemas R and S
respectively.
Then, the “natural join” of relations R and S
is a relation on schema R S obtained as
follows:
Consider each pair of tuples tr from r and ts from s.
If tr and ts have the same value on each of the
attributes in R S, add a tuple t to the result,
where
t has the same value as
t has the same value as
tr on r
ts on s
Natural Join Example
Relations r, s
Natural Join
r
s
Figure in-2.1
MySQL Workbench
Handout
Relationships between tables in
HannayReels sample Customer Tables
CSc340 1b
22
"Live" "Reel"
Database Example
email automatically sent today
Draw relation between Invoices table and
Payments table on board
Show Query in Reporting Services
FIX problem on Tuesday using SQL
CSc340 1b
23
Homework/Project
Homework due Next Class:
Homework due in One Week:
1.1, 1.3, 1.5, 1.6
2.1, 2.2, 2.4, 2.6, 2.7, 2.8
Project due Next Class:
Name of Your Enterprise
General Description
CSc340 1b
24
To the Computers
“All your base are belong to us”
http://en.wikipedia.org/wiki/All_your_base_are_belong_to_us
Examining Relations (Customers)
In OpenOffice “BASE”
In MS Access
Tools Menu Relationships
“Database Tools” Tab “Relationships” button
Start Creating a few Tables for Your DB
In MySQL Workbench (see HannayReels.mwb)
If you are unsure on project, try a few
CSc340 1b
25