CS 200
Lecture 09 FileMaker vs SQL & Reports
CS 200 Spring 2020 1 09-Advanced Databases
Miscellaneous Notes
Abbreviations
• aka also known as
• DBMS DataBase Management System
• mutatis mutantis with the necessary changes having been made
CS 200 Spring 2020
2
09 – Advanced Databases
Administrivia
Please read and highlight BEFORE lab
• Assignment9
• 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!
CS 200 Spring 2020
3
09 – Advanced Databases
Databases–FileMaker
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!)
Albums
Album_ID
Title
Group
Medium
Category
Price
Purchase Date
Copyright
Label
Sample
Songs
Song_ID
Side
Track
Title
Mins
Secs
Album_ID
CS 200 Spring 2020
4
09 – Advanced Databases
FileMaker Terminology (1)
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” CS 200 Spring 2020
5
09 – Advanced Databases
FileMaker Terminology (2)
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.)
CS 200 Spring 2020
6
09 – Advanced Databases
Creating A Database
Creation of a new database is similar to other applications File: New Database
CS 200 Spring 2020
7
09 – Advanced Databases
Creating a Table within our Database
CS 200 Spring 2020
8
09 – Advanced Databases
Defining Fields
CS 200 Spring 2020
9
09 – Advanced Databases
Data Validation in FileMaker (1)
You can’t mistype something that’s entered for you!
CS 200 Spring 2020
10
09 – Advanced Databases
Uniquely Identifying Records
Primary Key
CS 200 Spring 2020
11
09 – Advanced Databases
Data Validation in FileMaker
An Important Principle:
It’s easier to catch & correct errors
when data are entered
than it is to find and correct them later
CS 200 Spring 2020
12
09 – Advanced Databases
Modes in FileMaker
Browse
Find
Layout
Preview
CS 200 Spring 2020
13
09 – Advanced Databases
Browse Mode
CS 200 Spring 2020
14
09 – Advanced Databases
Find Mode
CS 200 Spring 2020
15
09 – Advanced Databases
Preview Mode
CS 200 Spring 2020
16
09 – Advanced Databases
Layout Mode
CS 200 Spring 2020
17
09 – Advanced Databases
Creating a New Layout
CS 200 Spring 2020
18
09 – Advanced Databases
New Layout 2
CS 200 Spring 2020
19
09 – Advanced Databases
Data Validation (3) — Pop-up Menus / Drop-down Lists
A drop-down list…
T
And there are radio buttons and checkboxes…
which also allow users to choose from a value list but display the choice differently
CS 200 Spring 2020
20
09 – Advanced Databases
A pop-up menu…
.
Simple One-Table Queries (2)
In SQL:
• select Title, Artist from Albums where Price < 10.00
In FileMaker:
• make another new layout
• choose, position, label, & format fields
• enter find mode & type the condition (“query by example”) • then perform the find
In each vs the other ...
• what’s different? better? worse? easier? harder? can’t be done?
CS 200 Spring 2020
21
09 – Advanced Databases
Organization of a Report Layout
At the top of every page
Header
printed once on each page
Repeated for Every Record
BODY
printed once for each group
At the bottom of every page
Footer
printed once on each page
CS 200 Spring 2020
22
09 – Advanced Databases
Simple One-Table Queries (1)
In SQL:
• selectTitle,Artist,PricefromAlbums
In FileMaker:
• make a new layout
• choose, position, label and format fields • enter browse mode
In each vs the other ...
• what’s different? • what’s better?
• what’s worse?
• what’s easier?
• what’s harder?
• what can’t be done?
CS 200 Spring 2020
23
09 – Advanced Databases
One-Table Queries (and)
How about...
select Title, Artist from Albums
where (Price < 14.00) and (Artist = ‘Mozart’) ?
T
v
Hmm ... what’s the “Omit” check box do?
(Read and Remember...)
CS 200 Spring 2020
24
09 – Advanced Databases
One-Table Queries (or)
How about
select Title, Artist from Albums
where (Price < 10.00) or (Artist = ‘Mozart’) ?
FileMaker calls each line of a Find a
“request”
In SQL I didn’t have to display Price to use it in a query; must I in FileMaker?
CS 200 Spring 2020
25
09 – Advanced Databases
One-Table Queries (Using a Field Twice – or)
How about
select Title, Artist from Albums
where (Price < 10.00) or (Price > 15) ?
Are there any SQL queries that FileMaker
can’t do?
How about (a < b) or [ (c < d) and (e < f) ]?
Roughly speaking, there’s a theorem that you can express all boolean expressions with all or’s or with all and’s
Intheaboveexample, (a 10.00) and (Price < 15)
order by Price ?
T
CS 200 Spring 2020
27
09 – Advanced Databases
Two Table Queries/Joins in SQL
Select
From
Where
Order By Albums.Album_ID, Song_ID
Albums.Title as "Album Title", Songs.Title as "Song Title" Albums, Songs
Albums.Album_ID = Songs.Album_ID
CS 200 Spring 2020
28
09 – Advanced Databases
Two-Table Queries/Joins in FileMaker (From the Songs Table)
From the Songs Table
... where Songs.Album ID = Albums.Album ID
Songs is the “master table” in FileMaker-speak
This is a “many-to-one” relationship from Song to Album in FileMaker-speak
CS 200 Spring 2020
29
09 – Advanced Databases
The Result (Made to Look Like SQL Output)
Of course, you’d almost never actually DO this in FileMaker, since you can make much more readable and attractive layouts.
CS 200 Spring 2020
30
09 – Advanced Databases
Two-Table Queries/Joins in FileMaker
(From the Albums Table)
But from the Album Table’s point of view (it’s now the “master table”) ...where Albums.Album ID = Songs.Album ID
This is a “one-to-many” relationship from Albums to Songs Could I create the preceding layout in the Albums table?
CS 200 Spring 2020
31
09 – Advanced Databases
The Result — First Formatted to Look Like SQL
One to Many
Album fields. Song fields.
But notice the use of a portal.
A layout that’s more natural for FileMaker, and easier to read.
CS 200 Spring 2020
32
09 – Advanced Databases
Multi-Key (Two-Table) Queries/Joins
From last week’s University sample database
Mark
Assignme
StudNum
Course
AssignNum
Mark
nt
Course
AssignNum
Weighting Description Date Due
List of assignment due dates for student
99001122
Notice how the structure
of this dialog reflects the natural syntax of a where.
select from
where
M.StudNum, M.Course, M.AssignNum,
Date Due
Mark M, Assignment A
( M.StudNum = 99001122 )
and ( M.Course = A.Course ) and ( M.AssignNum= A.AssignNum)
How is this done in FileMaker?
grab a field in one table, drag it to a field
in another or use buttons, selected fields,
and popups
CS 200 Spring 2020
33
09 – Advanced Databases
Referential Integrity
For every foreign key
• there is a record in the referenced table having that primary key
• eg for every Album_ID value appearing in a Song record there is an Album with that
Album_ID.
A — “Cascading deletes”
• if you delete an album,
the related songs are
also automatically deleted
B — auto-creation of related song records
• just by typing into related fields
on a layout
• the foreign key is automatically set, too
• be smart: auto-enter the song’s pKey
C — like B, but from Songs to Albums D — like A, but from Songs to Albums
• would not be smart here ... CS 200 Spring 2020
34
09 – Advanced Databases
FileMaker vs SQL — Two-Table Joins
Are there 2-table SQL queries that can’t be done in FileMaker ?
Can these?
select * from Albums, Songs
select * from Albums, Songs where Album_ID < Songs_ID
How about using a table twice in an SQL join (eg “select ... from Song as S1, Song as S2”)?
In Filemaker, create “copies” of a table in FileMaker’s relationships dialog
when you would list the table > once in the corresponding SQL where clause.
Are there 2-table FileMaker queries that can’t be done in SQL ?
CS 200 Spring 2020
35
09 – Advanced Databases
Three-Table Queries/Joins in FileMaker
From Last Week’s Lecture
T
Students
Registe
Courses
IDN
r IDN
Name
Name
Course
Room
Term
Time
Mark
Description
List the rooms in which students
have lectures
select S.IDN, S.Name, R.Course, C.Room
from Students S, Register R, Courses C
where (S.IDN = R.IDN)
and (R.Course = C.Name)
order by S.Name, C.Course
CS 200 Spring 2020
36
09 – Advanced Databases
Three Query Search (2)
Here’s what we want for each student…
with Courses as the master table (+ a query for Aaron)
with Students as the master table
CS 200 Spring 2020
37
09 – Advanced Databases
Hmm … in a Students layout, how do you access Courses fields?
Thus
you can relate (link) A to B, relate B to C,
then on a layout in A use fields from C as well as B
If you position the field in a portal, FileMaker will show all the related values
If you position the field outside a portal, FileMaker will show you the first related value it finds
which is just what you want if the relationship is many-to-one
but “wrong” if the relationship is one-to-many
however, FileMaker doesn’t care — it just does what you tell it to…
CS 200 Spring 2020
38
09 – Advanced Databases
Study Questions
What about 4-table joins? 5-table joins? etc Which is more powerful: SQL or FileMaker? Which is easier to use: SQL or FileMaker?
If SQL is the definition of a (truly) “relational database” …
is FileMaker a relational database?
if it isn’t, why do so many people buy FileMaker?
CS 200 Spring 2020
39
09 – Advanced Databases
Reports in FileMaker
Reports are something SQL barely does
• apossiblereasonforpreferringFileMaker
•
• iefieldvaluesineachofthoserecords
• andpossiblyinlinkedrecordsfromlinkedtables
You lay out how you want those fields arranged
• FileMakerprintsthatlayoutrepeatedly,
once for each record in the “found set”
• usuallyyoudon’twanttoreportoneveryrecord,
so you do a query first; what’s printed is data from records in the “found set”
But usually you’d like to
• ordertherecords
• and/ortogroupthem
• andprintsummaryinformationabouteachgroup,eg
the number of records in each group the average / total / etc of some field
(though note that you can purchase “report generators” that hook to SQL DBMSs) A report is just a list of records
CS 200 Spring 2020
40
09 – Advanced Databases
Groups
And usually some of the information is the same within a group
• itwouldbenicetoonlyprintitonce
• (egtheAlbumTitleisthesameforallthesongsonanalbum)
So…
We’d like a way to “group” records and
“do something” once for each group
What’s a “group” in FileMaker?
• sortonsomefield’svalue
• eachsequenceofrecordswiththesamefieldvalueisagroup
What does it mean to “do something” for each group?
print once
the fields that don’t change and/or summary (“aggregate”) information
between the records for one group
and the records for the next group
CS 200 Spring 2020
41
09 – Advanced Databases
Organization of a Report Layout
At the top of every page
Header
printed once on each page
Precedes the first leading subsummary
Leading Grand Summary
printed once
Precedes every change in the sorted field’s value
Leading SubSummary
Repeated for Every Record
BODY
printed once for each group
Follows every change the sorted field’s value
Trailing SubSummary
Follows the last trailing subsummary
Trailing Grand Summary
printed once
At the bottom of every page
Footer
printed once on each page
CS 200 Spring 2020
42
09 – Advanced Databases
Warning
In FileMaker . . .
• Each sub-summary part is associated with a “break field”
whose distinct values define groups
But using the sub-summary does not cause a sort to be done on the break field
so as to create the groups!
— you must do that yourself, separately
• Browse mode only shows sub-summaries if it’s sorted by the break field And you must have sorted by the relevant break field
Remember “Group By” in SQL?
• it told SQL what field(s) to group records by
when using an aggregate function [eg count() and sum()]
• it’s the same idea in FileMaker
except that sorting serves as both Order By & Group By
• SQL aggregate functions = FileMaker summary fields
CS 200 Spring 2020
43
09 – Advanced Databases
Example of a Report Layout
CS 200 Spring 2020
44
09 – Advanced Databases
The Resulting Report
CS 200 Spring 2020
45
09 – Advanced Databases
Multi-Level Reports – Subgroups
Suppose you want to break a group into subgroups
and print some summary information for each subgroup? eg separately for side 1 and side 2 of an album
It works just as you’d expect
sort first by the outer group, then by the inner
put a (another) sub-summary part between the
outer sub-summary part & the body part, and
set its break field to be the second sort key
CS 200 Spring 2020
46
09 – Advanced Databases
The Resulting Two-Level Report
At the top of every page
Precedes the first leading subsummary
Precedes every change in the
primary sort field’s value
Precedes every change in the secondary sort field’s value
Repeated for every record
Follows every change in the secondary sort field’s
value
Follows every change in the primary sort field’s value
Follows the last trailing subsummary
At the bottom of every page
Header
Leading Grand Summary
Leading Outer SubSummary
Leading Inner SubSummary
BODY
Trailing Inner SubSummary
Trailing Outer SubSummary
Trailing Grand Summary
Footer
CS 200 Spring 2020
47
09 – Advanced Databases
Reports — Some Details
Use the Format menu to control the appearance of data values
If a field appears in a Header
the value for the first record on the page is used similarly for the footer, mutatis mutandis
Use the Define Fields dialog box
to create a summary field (SQL aggregate function)
that you place in a grand summary or sub-summary part
CS 200 Spring 2020
48
09 – Advanced Databases
Generalizing
If I select a field on a layout
• FileMaker grays out inappropriate Format menu items • selecting one of the active menu items
lets me change the appearance of the selected field
What happens if nothing is selected & I select a Format menu item? what does it mean?
Why is this an important question?
CS 200 Spring 2020
49
09 – Advanced Databases
Indexing in FileMaker
Remember SQL’s Create Index command?
Here’s how FileMaker does it
FileMaker will automatically index fields
• to implement “Unique” or “Existing” data validation
on the matched field in a related table
when the field is used to create a value list
provided you haven’t manually forced indexing off
Otherwise, by default:
• “Automatically turn indexing on if needed” In FileMaker
• it’s unlikely you’ll ever need to manually turn indexing on
but you may want to turn it off to save file space
because FileMaker never will!
• Other databases make up their own rules
(By the way, FileMaker doesn’t use indices for sorting)
And while we’re here
T
CS 200 Spring 2020
50
09 – Advanced Databases
Formats in FileMaker
Note especially the difference between
• how a data value appears on a form eg 3.14
• and what’s stored in the database
The Format menu…
eg 3.14159265358979323846264338327950288419716939937510582097494459230 7816406286208998628034825342117067982148086513282306647093844609550 582
& note the menu items that control the appearance of
each data type
CS 200 Spring 2020
51
09 – Advanced Databases
The Inspector
CS 200 Spring 2020
52
09 – Advanced Databases
Things That Might Confuse You in FileMaker
You can define as many layouts at you want
• thesearealsocalled“views”ofthedata(SQL-speak) • givethemmeaningfulnames!!!
You don’t have to include all fields on all layouts
• defininganewfielddoesn’tcauseittoappearonall
layouts
• bydefault,anewfieldisplacedonthe“currentlayout,”
although there’s a preference to prevent that
In FileMaker
• dataentryoptionsareassociatedwithafield’sdefinition
• theappearanceofafieldisspecifiedonthelayout,andcanvary • otherdatabasesmaychoosedifferentconventions
You can only see sub-summaries
• wheninPreviewModeorBrowseMode
and if you have sorted by the relevant break field(s)
• doingboth—andrememberingtodoboth—isapain
• FileMakermacrosarehandyforautomatingthis(nextweek…)
(Incidentally, FileMaker allows you to userid-/password-protect a table, layouts, etc, and to access a database over the internet (there’s a more expensive version tuned for this.)
CS 200 Spring 2020
53
09 – Advanced Databases
Summing Up
SQL joins vs FileMaker joins (“relationships”)
• FileMakerisnowbuiltontopofanSQLengine
• butdoesFileMaker’sGUIprovideawaytogenerateanddisplayallpossibleSQLselects?
FileMaker features typical of GUI-flavoured DBMS’s
• formswith
formatting of displayed values
data validation
input widgets (radio buttons, check boxes, pop-ups, etc)
point-and-click-to-open (next week)
query by example
• reportsand
formatting of displayed values
sorting
summaries, sub-summaries, aggregate functions
Why might someone prefer SQL?
Why might someone prefer FileMaker?
Another study question: compare & contrast database management with Excel & FileMaker
CS 200 Spring 2020
54
09 – Advanced Databases