lecture 06 database introduction - student.cs.uwaterloo.cacs200/lectures/18_a_winter/0… · album...
TRANSCRIPT
![Page 1: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/1.jpg)
CS 200
CS 200 Winter 2018 Database Introduction1
Lecture 06Database Introduction
CS 200 Winter 2018 Database Introduction
Abbreviations• aka also known as• DBMS DataBase Management System• mutatis mutantis with the necessary changes having been made
2
Miscellaneous Notes
![Page 2: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/2.jpg)
CS 200 Winter 2018 Database Introduction
Please read and highlight BEFORE lab• Assignment 6• This week’s lecture slides
Reading • Database Design (on Learn > Database Resources > Database Design)
Adapted from Access Database Design and Programming by Steven Roman
Today• Database basics and Design• Creation of a database• Reports and Queries• Relationships
Please ask questions!
3
Administrativia
CS 200 Winter 2018 Database Introduction
AssumptionsYou have used a database before
You understand basic data management
4
![Page 3: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/3.jpg)
CS 200 Winter 2018 Database Introduction
Things to think about• What are the data objects in FileMaker?• How does the interface differ from other applications we have seen so far?• What are the efficiencies and deficiencies of the interface?• What are different databases you use?• How are the databases you are part of used?• Is the way data is displayed important?
5
CS 200 Winter 2018 Database Introduction
This is your first pass at FileMaker. By the end of this section you will be able to:• design a simple database• create queries and reports• understand database terminology and• create relationships with a database
Our objectives are• to introduce you to database fundamentals• Expose you to an application specific to database – FileMaker
6
Databases–FileMaker
![Page 4: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/4.jpg)
CS 200 Winter 2018 Database Introduction
A collection of related data
Data consists of information on a specific topic
7
What is a Database?
CS 200 Winter 2018 Database Introduction
Use of DatabasesWhat databases are you a part of?
What are some of the benefits of belonging to a database?
What are some of the drawbacks?
Why/when do I use a database?
How are the databases I am part of used?
8
![Page 5: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/5.jpg)
CS 200 Winter 2018 Database Introduction
File
Table (Entity)
Field (Attribute)
Record
9
FileMaker Terminology (1)
CS 200 Winter 2018 Database Introduction
FileMaker Terminology (2)Layout
Report
Query
10
![Page 6: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/6.jpg)
CS 200 Winter 2018 Database Introduction
Creation of a TableConsider the following example:
You would like to create a database with all of the Albums you have downloaded onto your iPod.
What info would be relevant to have in a table?
What types of fields will each of these be?
11
CS 200 Winter 2018 Database Introduction
Creating a Database
12
![Page 7: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/7.jpg)
CS 200 Winter 2018 Database Introduction
Album Table Fields
13
Field NameAlbum TitleAlbum Type
MediumRecording Year
LabelPurchase PriceAlbum Artist
Purchase Date
CS 200 Winter 2018 Database Introduction
Album Table Field Types
14
Field Name Field TypeAlbum TitleAlbum Type
MediumRecording Year
LabelPurchase PriceAlbum Artist
Purchase Date
TextTextText
NumberText
NumberTextDate
![Page 8: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/8.jpg)
CS 200 Winter 2018 Database Introduction
Album Table Field Definitions
15
Field Name Field Type OptionsAlbum TitleAlbum Type
MediumRecording Year
LabelPurchase PriceAlbum Artist
Purchase Date
TextTextText
NumberText
NumberTextDate
Value List (TP, CD, LP, MP3)Value List (Jazz, Blues, Rock, )
Range (1921-2010), Number
Number
Date
CS 200 Winter 2018 Database Introduction
Creating A DatabaseCreation of a new database is similar to other applications
File: New Database
16
![Page 9: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/9.jpg)
CS 200 Winter 2018 Database Introduction
Creating a Table within our Database
17
CS 200 Winter 2018 Database Introduction
Defining Fields
18
![Page 10: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/10.jpg)
CS 200 Winter 2018 Database Introduction
Data Validation
19
CS 200 Winter 2018 Database Introduction
You can’t mistype something that’s entered for you!
20
Data Validation in FileMaker (1)
![Page 11: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/11.jpg)
CS 200 Winter 2018 Database Introduction
An Important Principle:
21
Data Validation in FileMaker (2)
It’s easier to catch & correct errorswhen data are entered than it is to find and correct them later
CS 200 Winter 2018 Database Introduction
Uniquely Identifying RecordsPrimary Key
22
![Page 12: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/12.jpg)
CS 200 Winter 2018 Database Introduction
Browse
Find
Layout
Preview
23
Modes in FileMaker
CS 200 Winter 2018 Database Introduction
Browse Mode
24
![Page 13: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/13.jpg)
CS 200 Winter 2018 Database Introduction
Find Mode
25
CS 200 Winter 2018 Database Introduction
Preview Mode
26
![Page 14: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/14.jpg)
CS 200 Winter 2018 Database Introduction
Layout Mode
27
CS 200 Winter 2018 Database Introduction
Creating a New Layout
28
![Page 15: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/15.jpg)
CS 200 Winter 2018 Database Introduction
New Layout 2
29
CS 200 Winter 2018 Database Introduction
New Layout 3
30
![Page 16: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/16.jpg)
CS 200 Winter 2018 Database Introduction31
At the top of every page Headerprinted once on each page
Repeated for Every Record BODYprinted once for each group
At the bottom of every page Footerprinted once on each page
Organization of a Report Layout
CS 200 Winter 2018 Database Introduction
Note especially the difference between• how a data value appears on a form
eg 3.14• and what’s stored in the database
eg 3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117067982148086513282306647093844609550582
& note the menu items that control the appearance of each data type
32
The Format menu...
Formats in FileMaker
![Page 17: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/17.jpg)
CS 200 Winter 2018 Database Introduction
More than one Table in a DatabaseSongs Table
33
Song Table Album TableSong ID
Song TitleSideTrackMinsSecs
Song Album ID
Album TitleAlbum Type
MediumRecording Year
LabelPurchase PriceAlbum Artist
Purchase Date
Album IDPrimary Keys
Foreign Key
CS 200 Winter 2018 Database Introduction
Creating a Relationship in FileMaker
34
![Page 18: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/18.jpg)
CS 200 Winter 2018 Database Introduction
Types of RelationshipsOne to One
One to Many
Many to One
Many to Many
35
CS 200 Winter 2018 SQL
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
36
Terminology
![Page 19: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/19.jpg)
CS 200 Winter 2018 Database Introduction
Displaying Info from a Many to One Relationship
37
Field Tool in Layout Mode
CS 200 Winter 2018 Database Introduction
Many to One (2)
38
![Page 20: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/20.jpg)
CS 200 Winter 2018 Database Introduction
One to Many
39
Portal Tool
CS 200 Winter 2018 Database Introduction
One to Many (2)
40
![Page 21: Lecture 06 Database Introduction - student.cs.uwaterloo.cacs200/Lectures/18_A_Winter/0… · Album Table Field Definitions 15 Field Name Field Type Options Album Title Album Type](https://reader035.vdocuments.us/reader035/viewer/2022071000/5fbc5925e950797b7b2bedea/html5/thumbnails/21.jpg)
CS 200 Winter 2018 Database Introduction
Creating Enhanced, Stylized Layouts
41
CS 200 Winter 2018 Database Introduction
You can define as many layouts as you want• these are also called “views” of the data (SQL-speak)• give them meaningful names!!!
You don’t have to include all fields on all layouts• defining a new field doesn’t cause it to appear on all layouts• by default, a new field is placed on the “current layout,”
although there’s a preference to prevent that
In FileMaker• data entry options are associated with a field’s definition• the appearance of a field is specified on the layout, and can vary• other databases may choose different conventions
42
Things That Might Confuse You in FileMaker