09_AdvancedDatabases.pdf - CS 200 Lecture 09 FileMaker vs SQL Reports CS 200 Spring 2020 1 09-Advanced Databases Miscellaneous Notes Abbreviations \u2022

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)