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
You've reached the end of your free preview.
Want to read all 54 pages?
- Spring '14
- Relational model, FileMaker