SQL Lecture CS200.pdf - CS 200 Lecture 08 Relational Databases \u2013 SQL(Structured Query Language CS 200 Winter 2020 1 SQL Administrivia BEFORE lab

SQL Lecture CS200.pdf - CS 200 Lecture 08 Relational...

This preview shows page 1 - 10 out of 59 pages.

CS 200 CS 200 Winter 2020 SQL 1 Lecture 08 Relational Databases – SQL (Structured Query Language)
Image of page 1
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
Image of page 2
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
Image of page 3
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
Image of page 4
CS 200 Winter 2020 SQL 5
Image of page 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
Image of page 6
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”
Image of page 7
CS 200 Winter 2020 SQL 8 The Excel Assignment — Keeping Track of VISA Charges
Image of page 8
CS 200 Winter 2020 SQL Data entry “validation” Sophisticated searching (aka “queries” )
Image of page 9
Image of page 10

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture