Lecture Notes
CPSC 421 (Fall 2013)
Today:
ER Diagrams (cont)
Homework:
hw7 out (due Thurs)
proj 1 out (due Thurs)
S. Bowers
1 of 9
Lecture Notes
CPSC 421 (Fall 2013)
Weak Entity Sets
ssn
name
lot
Employee
name
policy
dob
Dependent
In th
Lecture Notes
CPSC 421 (Fall 2013)
Today:
ER Diagrams
Homework:
hw6 due
hw7 out
proj 1 out
S. Bowers
1 of 11
Lecture Notes
CPSC 421 (Fall 2013)
Conceptual Data Modeling Approaches
Similar to software design .
gather and analyse requirements
design
Lecture Notes
CPSC 421 (Fall 2013)
Today:
Embedded SQL (cont)
S. Bowers
1 of 11
Lecture Notes
CPSC 421 (Fall 2013)
Cursors
Cursor features vary from one DBMS to another
Move forward only
Move forward or backward one row at a time
Move to arbitrary loc
Lecture Notes
CPSC 421 (Fall 2013)
Today:
Quiz 4
More joins
Embedded SQL (intro)
Homework:
HW 6 out
S. Bowers
1 of 8
Lecture Notes
CPSC 421 (Fall 2013)
Outer Joins
The basic join is called an inner join
An inner join is the default . the plain JOIN k
Lecture Notes
CPSC 421 (Fall 2013)
Today:
Subqueries (cont)
Set operators
S. Bowers
1 of 12
Lecture Notes
CPSC 421 (Fall 2013)
Subqueries (Review)
Single-valued comparisons
Q: What does this query do?
SELECT c1.number, c1.name
FROM
customer c1
WHERE c1.
Lecture Notes
CPSC 421 (Fall 2013)
Today:
Group By, Having, Order by (cont.)
Subqueries (intro)
Homework:
HW 5 out
Ch 6: 6.3 up to 6.3.5
S. Bowers
1 of 8
Lecture Notes
CPSC 421 (Fall 2013)
Group By attributes
What can appear in a SELECT clause under G
Lecture Notes
CPSC 421 (Fall 2013)
Today:
Quiz 3
Null values (cont.)
Group By, Having, Order by
Reminders:
hw4 out
Exam 1 next Thursday
S. Bowers
1 of 15
Lecture Notes
CPSC 421 (Fall 2013)
SQL Null values
NULL is a special value in SQL
Indicates the
Lecture Notes
CPSC 421 (Fall 2013)
Today:
More MySQL
SQL Join Syntax
Null values
Group By
Homework:
hw4 out
hw3 due
S. Bowers
1 of 17
Lecture Notes
CPSC 421 (Fall 2013)
More on MySQL
Creating Views
Views provide a way to name a query
The query can
Lecture Notes
CPSC 421 (Fall 2013)
Today:
Quiz 2
More SQL queries
Homework:
hw2 due
hw3 out
Reminder:
No class on Thursday
S. Bowers
1 of 15
Lecture Notes
CPSC 421 (Fall 2013)
Example Tables
accounts
number
101
102
103
104
105
owner
J. Smith
W. Wei
J
Lecture Notes
CPSC 421 (Fall 2013)
Today:
Quiz 1
SQL: creating tables
Homework:
Hw1 due
Hw2 out (see webpage)
S. Bowers
1 of 9
Lecture Notes
CPSC 421 (Fall 2013)
Declaring Tables in SQL (MySQL)
Recall that schema attributes have domains data types
The
Lecture Notes
CPSC 421 (Fall 2013)
Todays Topics .
Relations (cont.)
SQL intro
S. Bowers
1 of 9
Lecture Notes
CPSC 421 (Fall 2013)
Anatomy of a Relation (review)
Account
Number
101
102
103
104
105
Owner
J. Smith
W. Wei
J. Smith
M. Jones
H. Martin
Balanc