CS 200
Lecture 09 FileMaker Introduction
CS 200 Winter 2019 1 FileMaker Introduction
Miscellaneous Notes
Abbreviations
• aka alsoknownas
• DBMS DataBaseManagementSystem
• mutatismutantiswiththenecessarychangeshavingbeenmade
CS 200 Winter 2019 2
FileMaker Introduction
Administrativia
Please read and highlight BEFORE lab
• Assignment8
• Thisweek’slectureslides
Reading
• DatabaseDesign(onLearn>DatabaseResources>DatabaseDesign) Adapted from Access Database Design and Programming by Steven Roman
Today
• DatabasebasicsandDesign • Creationofadatabase
• ReportsandQueries
• Relationships
Please ask questions!
CS 200 Winter 2019 3
FileMaker Introduction
Assumptions
You have used a database before
You understand basic data management
CS 200 Winter 2019 4
FileMaker Introduction
Things to think about
• WhatarethedataobjectsinFileMaker?
• Howdoestheinterfacedifferfromotherapplicationswehaveseensofar? • Whataretheefficienciesanddeficienciesoftheinterface?
• Whataredifferentdatabasesyouuse?
• Howarethedatabasesyouarepartofused?
• Isthewaydataisdisplayedimportant?
CS 200 Winter 2019 5
FileMaker Introduction
Databases–FileMaker
This is your first pass at FileMaker. By the end of this section you will be able to:
• designasimpledatabase
• createqueriesandreports
• understanddatabaseterminologyand • createrelationshipswithadatabase
Our objectives are
• to introduce you to database fundamentals
• Exposeyoutoanapplicationspecifictodatabase–FileMaker
CS 200 Winter 2019 6
FileMaker Introduction
What is a Database?
A collection of related data
Data consists of information on a specific topic CS 200 Winter 2019 7
FileMaker Introduction
File
Table (Entity) Field (Attribute) Record
CS 200 Winter 2019 8
FileMaker Introduction
FileMaker Terminology (1)
Layout Report Query
CS 200 Winter 2019 9
FileMaker Introduction
FileMaker Terminology (2)
Creating A Database
Creation of a new database is similar to other applications File: New Database
CS 200 Winter 2019 10
FileMaker Introduction
Creating a Table within our Database
CS 200 Winter 2019 11
FileMaker Introduction
Defining Fields
CS 200 Winter 2019 12
FileMaker Introduction
Data Validation
CS 200 Winter 2019 13
FileMaker Introduction
Data Validation in FileMaker (1)
You can’t mistype something that’s entered
for you!
CS 200 Winter 2019 14
FileMaker Introduction
Data Validation in FileMaker (2)
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 Winter 2019 15
FileMaker Introduction
Primary Key
Uniquely Identifying Records
CS 200 Winter 2019 16
FileMaker Introduction
Browse
Find
Layout
Preview
CS 200 Winter 2019 17
FileMaker Introduction
Modes in FileMaker
CS 200 Winter 2019 18
FileMaker Introduction
Browse Mode
Find Mode
CS 200 Winter 2019 19
FileMaker Introduction
CS 200 Winter 2019 20
FileMaker Introduction
Preview Mode
CS 200 Winter 2019 21
FileMaker Introduction
Layout Mode
Creating a New Layout
CS 200 Winter 2019 22
FileMaker Introduction
New Layout 2
CS 200 Winter 2019 23
FileMaker Introduction
New Layout 3
CS 200 Winter 2019 24
FileMaker Introduction
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 Winter 2019 25 FileMaker Introduction
Formats in FileMaker
Note especially the difference between
• howadatavalueappearsonaform eg 3.14
• andwhat’sstoredinthedatabase
eg 3.1415926535897932384626433832795028841971693 99375105820974944592307816406286208998628034825 342117067982148086513282306647093844609550582
& note the menu items that control the appearance of
each data type
The Format menu…
CS 200 Winter 2019 26
FileMaker Introduction
Songs Table
More than one Table in a Database
SongTable
Album Table
Song ID
Primary Keys
Album ID
Song Title
Album Title
Side
AlbumType
Track
Medium
Mins
Recording Year
Secs
Label
Song Album ID
Foreign Key
Purchase Price
Album Artist
Purchase Date
CS 200 Winter 2019 27
FileMaker Introduction
Creating a Relationship in FileMaker
CS 200 Winter 2019 28
FileMaker Introduction
One to One One to Many Many to One Many to Many
Types of Relationships
CS 200 Winter 2019 29
FileMaker Introduction
Album ID
• is a “primary key” for the Album Table because it uniquely identifies an album
• is a “foreign key” of the Songs Table
because it contains a primary key of the Album Table
and thus links a Song record to a unique Album record
“One-to-many” and “many-to-one”
• wrt Album ID
Albums is the “one table”
• Songs is the “many table” because for a given Album ID there is only ONE Album record
— but are (usually) MANY Song records
• “many-to-many” can happen, too
— though not by matching a primary key in each of two tables! — it is often useful
• we’ll see an example next week
CS 200 Winter 2019
30
SQL
Terminology
Displaying Info from a Many to One Relationship
Field Tool in Layout Mode
CS 200 Winter 2019 31
FileMaker Introduction
Many to One (2)
CS 200 Winter 2019 32
FileMaker Introduction
Portal Tool
One to Many
CS 200 Winter 2019
33
FileMaker Introduction
One to Many (2)
CS 200 Winter 2019 34
FileMaker Introduction
Creating Enhanced, Stylized Layouts
CS 200 Winter 2019 35
FileMaker Introduction
Things That Might Confuse You in FileMaker
You can define as many layouts as you want
• thesearealsocalled“views”ofthedata(SQL-speak) • givethemmeaningfulnames!!!
You don’t have to include all fields on all layouts
• defininganewfielddoesn’tcauseittoappearonalllayouts
• bydefault,anewfieldisplacedonthe“currentlayout,”
although there’s a preference to prevent that
In FileMaker
• dataentryoptionsareassociatedwithafield’sdefinition
• theappearanceofafieldisspecifiedonthelayout,andcanvary • otherdatabasesmaychoosedifferentconventions
CS 200 Winter 2019 36
FileMaker Introduction