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

09_AdvancedDatabases.pdf - CS 200 Lecture 09 FileMaker vs...

This preview shows page 1 - 13 out of 54 pages.

CS 200 CS 200 Spring 2020 09-Advanced Databases 1 Lecture 09 FileMaker vs SQL & Reports
Image of page 1
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
Image of page 2
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
Image of page 3
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
Image of page 4
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)
Image of page 5
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)
Image of page 6
CS 200 Spring 2020 09 – Advanced Databases Creating A Database Creation of a new database is similar to other applications File: New Database 7
Image of page 7
CS 200 Spring 2020 09 – Advanced Databases Creating a Table within our Database 8
Image of page 8
CS 200 Spring 2020 09 – Advanced Databases Defining Fields 9
Image of page 9
CS 200 Spring 2020 09 – Advanced Databases You can’t mistype something that’s entered for you! 10 Data Validation in FileMaker (1)
Image of page 10
CS 200 Spring 2020 09 – Advanced Databases Uniquely Identifying Records Primary Key 11
Image of page 11
CS 200 Spring 2020
Image of page 12
Image of page 13

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture