Lecture 8 Notes - CS 200 Lecture 08 Relational Databases SQL(Structured Query Language CS 200 Fall 2013 1 SQL Administrivia BEFORE lab please read and

Lecture 8 Notes - CS 200 Lecture 08 Relational Databases...

This preview shows page 1 - 5 out of 29 pages.

CS 200 CS 200 Fall 2013 SQL 1 Lecture 08 Relational Databases – SQL (Structured Query Language) CS 200 Fall 2013 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
Image of page 1
CS 200 Fall 2013 SQL CS 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 terms database table record (aka row) eld (aka column) If this wasn’t true at the beginning of the term, by now you’ve worked through the Filemaker Intro 3 Assumptions for Today’s Lecture CS 200 Fall 2013 SQL It’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 are based on SQL OS 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 can import data from an SQL database into software with a nice GUI (eg FileMaker) by crafting an appropriate “SQL select statement” 4 Why SQL?
Image of page 2
CS 200 Fall 2013 SQL Why use a database at all? Structuring data allows us to do things we can’t do e ciently, or can’t feasibly do, with unstructured data The added power & exibility aren’t free it takes time and e ort to create (and maintain) the structure You have to decide if that e ort is worthwhile Obvious questions: What do I mean by “structure” in a database? What’s the payo? 5 Databases – Structured Data CS 200 Fall 2013 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 “ eld”) we could have used FileMaker though for what we wanted to do, it wouldn’t have been worth the e ort of learning FileMaker indeed, we could have placed the data in a Word table or 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 later 6 “Structure”
Image of page 3
CS 200 Fall 2013 SQL 7 The Excel Assignment — Keeping Track of VISA Charges CS 200 Fall 2013 SQL Data entry “validation” Sophisticated searching (aka “queries” ) Sophisticated summarizing and reporting Safe simultaneous updates by multiple users
Image of page 4
Image of page 5

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture