CS 200
CS 200 Winter 2020
SQL
1
Lecture 08
Relational Databases – SQL
(Structured Query Language)
CS 200 Winter 2020
SQL
BEFORE lab, please read and highlight
•
Assignment 8
•
Database Design (on learn.uwaterloo.ca)
Adapted from Access Database Design & Programming
by Steven Roman
•
The SQL tutorial notes (on learn.uwaterloo.ca)
•
Slides for this lecture
START the lab this week by doing the SQL tutorial
Topics for today
•
relational database design
•
when you need another table, and why
•
SQL (Structured Query Language)
•
a model for relational databases
Please ask questions!
2
Administrivia
CS 200 Winter 2020
SQL
You’ve seen a two-table relational database
•
you’ve been exposed to
forms, reports, queries, sorting, & data validation
•
you’re familiar with the terms
database
table
record (aka row)
field (aka column)
If this wasn’t true at the beginning of the term,
•
by now you’ve completed the Filemaker Intro
3
Assumptions for Today’s Lecture
CS 200 Winter 2020
SQL
Things to Think aboutHow does a DBMS differ from a spreadsheet?Why would I choose to use a DBMS?How does SQL differ from FileMaker?What are the reasons for needing more than one database table?
4
CS 200 Winter 2020
SQL
5
CS 200 Winter 2020
SQL
Why use a database at all?
•
You have to decide if that effort is worthwhile
Obvious questions:
•What do I mean by “structure” in a database? •What’s the payoff?
6
Databases – Structured Data
CS 200 Winter 2020
SQL
The visa worksheet in the Excel assignment
•
is an example of a 1–table database,
although we built it in Excel, not with a DBMS
•
each ROW
(“record”) holds data for a particular transaction
•
each COLUMN holds a particular piece of data about that transaction (a “field”)
•
we could have used FileMaker
though for what we wanted to do,
it wouldn’t have been worth the effort of learning FileMaker
•
indeed, we could have placed the data in a Word table
or even in a text processor (eg BBEdit)...
separate fields by tabs, separate records by ¶
but working with the data would have been MUCH harder
— think about implementing the Actual Balance and Statement Balance columns!
7
“Structure”
CS 200 Winter 2020
SQL
8
The Excel Assignment — Keeping Track of
VISA Charges
CS 200 Winter 2020
SQL
Data entry
“validation”
Sophisticated searching (aka
“queries”
)
