CS 200
CS 200 Spring 2020
09-Advanced Databases
1
Lecture 09
FileMaker vs SQL
& Reports
CS 200 Spring 2020
09 – Advanced Databases
Abbreviations
•
aka
also known as
•
DBMS
DataBase Management System
•
mutatis mutantis
with the necessary changes having been made
2
Miscellaneous Notes
CS 200 Spring 2020
09 – Advanced Databases
Please read and highlight BEFORE lab
•
Assignment 9
•
This week’s lecture slides
Reading
•
Database Design
(on learn.uwaterloo.ca)
Adapted from Access Database Design and Programming by Steven Roman
Today
•
SQL vs FileMaker
•
Reports in FileMaker
•
Read and Reason Pearl
Please ask questions!
3
Administrivia
CS 200 Spring 2020
09 – Advanced Databases
This is your second pass at databases
•
The first time around, you got a rough idea of how they work
•
This time round we’ll
fill in some important details
evaluate the power & flexibility of databases
and a GUI DBMS
Our strategy
•
SQL as an application model for FileMaker
•
so we’ll “compare and contrast...” the two
Recall the Albums and Songs database
Arrows go FROM a foreign key TO a corresponding
primary key (they DO NOT have to go between fields
with the same name!)
4
Albums
Album_ID
Title
Group
Medium
Category
Price
Purchase Date
Copyright
Label
Sample
Songs
Song_ID
Side
Track
Title
Mins
Secs
Album_ID
Databases–FileMaker
CS 200 Spring 2020
09 – Advanced Databases
FileMaker File or Document
a collection of one or more database tables,
including field definitions
+ layouts (aka “views”)
+ scripts
Master Table
the file that accesses & displays data from
another file
one or more records in a
“related file” that is/are identified “via a relationship”
Related Table
a file containing related data you want to access in the master file
Match Fields
a field in the master file and a field in the related file
that contain values you want to use
to find matching records
aka “link fields”
5
FileMaker Terminology (1)
CS 200 Spring 2020
09 – Advanced Databases
Master Record
A record in the master table
for which you wish to find matching records
in a related table.
Related Record
A record in the related file whose link field
(according to the relationship used)
contains a value equal to that of the link field
of the master record.
Related Field
A field in a related record.
May be placed directly on a layout of the master table,
or in the first row of a portal (also in a layout of the master table).
Portal
A layout object in the master file in which you place related fields.
Use a portal when the relation involved is one-to-many.
(You don’t need it when the relation involved is many to one.)
6
FileMaker Terminology (2)
CS 200 Spring 2020
09 – Advanced Databases
Creating
A Database
Creation of a new database is similar to other applications
File: New Database
7
CS 200 Spring 2020
09 – Advanced Databases
Creating a Table within our Database
8
CS 200 Spring 2020
09 – Advanced Databases
Defining Fields
9
CS 200 Spring 2020
09 – Advanced Databases
You can’t mistype something that’s entered for you!
10
Data Validation in FileMaker (1)
CS 200 Spring 2020
09 – Advanced Databases
Uniquely Identifying Records
Primary Key
11
CS 200 Spring 2020
