CS 200CS 200 Fall 2013SQL1Lecture 08Relational Databases – SQL(Structured Query Language)CS 200 Fall 2013SQLBEFORE 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 databasesPlease ask questions!2Administrivia
CS 200 Fall 2013SQLCS 100 or equivalent experience with a relational DBMS (DataBase Management System) •most usefully, with FileMaker •you’ve seen a two-table relational database •you’ve been exposed to forms, reports, queries, sorting, & data validation •you’re familiar with the termsdatabasetablerecord (aka row)eld (aka column)If this wasn’t true at the beginning of the term, •by now you’ve worked through the Filemaker Intro3Assumptions for Today’s LectureCS 200 Fall 2013SQLIt’s an excellent MODEL for how relational DBMS’s work Modern “big” DBMS’s are SQL-based Many PC databases are not •but can often be used as “front-ends” to mainframe SQL systems •though FileMaker Pro 7, 8, 9 and MS Access arebased on SQLOS X actually comes with two SQLs!•“SQL Lite,” which is designed to be embedded in programs (including the O/S)•“My SQL,” a (very) popular open source SQL server (used for the Math Faculty’s inventory database)Often you canimport data from an SQL databaseinto software with a nice GUI (eg FileMaker)by crafting an appropriate “SQL select statement”4Why SQL?
CS 200 Fall 2013SQLWhy use a database at all?Structuring data allows us to do things we can’t do eciently, or can’t feasibly do, with unstructured data •The added power & exibility aren’t free it takes time and eort to create (and maintain) the structure •You have to decide if that eort is worthwhile Obvious questions: •What do I mean by “structure” in a database? •What’s the payo?5Databases – Structured DataCS 200 Fall 2013SQLThe 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 “eld”) •we couldhave used FileMaker though for what we wanted to do,it wouldn’t have been worth the eort of learning FileMaker •indeed, we could have placed the data in a Word tableor even in a text processor (eg BBEdit)...separate elds 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!We’ll look at databases in Excel later6“Structure”
CS 200 Fall 2013SQL7The Excel Assignment — Keeping Track of VISA Charges CS 200 Fall 2013SQLData entry “validation”Sophisticated searching (aka “queries”) Sophisticated summarizing and reporting Safe simultaneous updates by multiple users