CS200 Assignment 10 – FileMaker
Due Monday March 25th 2019, 11:59 pm
Readings and Resources
On Learn: Database Resources > Database Design
Files
The following are located in Assignment 10 Files on Learn.
Favourite Colour: FavouriteColourDemo: University Starter: UniversityDemo:
This is a text file to import into the database you create in question 1 This file shows you how your question 1 will look and work in the end. You will use this file for question 2 of the assignment.
This file shows you how your question 2 will look and work in the end.
Assignment Objectives
• The idea of a “self-join,” (and how you write a self-join in SQL), which in FileMaker is a relationship in which a single table is linked to itself.
• An understanding of how to implement (some) three-table joins in FileMaker.
• Be aware, however, that more complicated select statements than we have considered can be
far harder to work out than the simple cases we have considered.
• The notion of a global field in FileMaker. In many respects, global fields serve as variables in
FileMaker. As we’ll see when we discuss scripting in FileMaker, you can create a layout with which users give global fields values, with buttons that execute scripts that use those values while manipulating data.
• The notion of creating a value list from the values stored in a given field of some table, and requiring the user to select from that list while entering a value.
Assignment Strategy
This assignment may be done independently or with a partner. If you do work with a partner, please add both or your names, hand in codes and Student ID numbers to the footer of each layout in the files and name the files with both usernames (username1_username2).
CS200 Assignment 10 Page 1 of 6
Your Tasks
1.
[50%] Sometimes it is useful to form the join of a table with itself (self-join). Consider a simple table (shown on the right) that records each person’s favourite colour. (Because a person’s name is not necessarily unique, there is also a unique sequence number called pKey for each record.)
a) Create a new FileMaker database called username_A10Q1, and add a table named username_FavouriteColour with an integer primary key field and two text fields called Name and Colour.
• In defining the table’s fields, specify that all three fields must have a value, that the primary key be unique, and that a sequence of serial numbers be automatically assigned (“auto- entered”) to pKey (as to right).
• Import the data contained in the file FavouriteColour.txt, which you can find on Learn.
b) Create a data entry layout called “Input” (shown to the right)
that allows you to enter new records. For the Colour field, users should be forced to select a person’s favourite colour from a popup menu of the colours already entered for this field in another record. You do not need to type the list of colours. Also allow users to enter a previously unused colour.
c) Create a layout called “MyFriends” (like shown on the next page) showing the current record’s fields and a 10-line scrolling portal listing the names of all those records having the same favourite colour as that of the current record.
• To create this portal you will have to define a relationship from FavouriteColour to FavouriteColour in which the Colour fields are required to be identical.
• One cannot define a relationship directly from a table to itself, so a duplicate must be created. Name the duplicate table “FindSameFavouriteColour”.
• Since you don’t, however, want to list yourself as a friend, you will also need to require that matching records not have the same value of pKey.1
• Prevent users from editing ANY of the fields displayed in this layout, including related fields within the portal.
1 The SQL Select statement for this is
select A.pKey, A.name, A.colour, B.pKey, B.name, B.colour
from FavouriteColour as A, FavouriteColour as B where (A.colour = B.colour) and (A.pKey <> B.pKey)
CS200 Assignment 10
Page 2 of 6
FavouriteColour
pKey
Name Colour
• Add a fourth field having the name “HowManyFriends” to FavouriteColour and display it on the MyFriends layout. HowManyFriends
should be a calculated field; its
computation should be
Count(FindSameFavouriteColour::pKey) 2.
• Your MyFriends layout should look like the example shown to the right. (Please put your name(s) and username(s) in the header.)
d) Create a layout called “ToPrint” (like that
appearing below). As it is “ToPrint” this layout
should be created with Preview Mode in mind. This layout will show the names of others having the same favourite colour as the person listed on the left.3 The thing to particularly notice here is that the list of people is of variable length. This is accomplished by creating a portal that has more rows than you ever expect to actually show (try counting the records and make an estimation).
• Use the inspector’s “Position → Sliding & Invisibility” to instruct FileMaker that the portal (and the part containing it) should collapse vertically (“slide up”) to eliminate unneeded space. This collapsing will not show in Browse, only Preview. Why?
• The header and footer of ToPrint should contain the name of the first and last people whose records appear on the page (as shown above and in the demo).
Please put your name(s) and username(s) in the header of all layouts, and the page number in the footer (refer to FileMaker’s online help, or the Poke Pearl for inserting special variables into layouts).
2The SQL Select statements for this looks like select count(*)
from FavouriteColour as A, FavouriteColour as B where (A.colour = B.colour) and (A.pKey≠B.pKey)
CS200 Assignment 10
Page 3 of 6
2. [50%] This question has you create a 3-table join of the Students, Marks and Assignments tables you will find in the University Starter file. These are slightly simplified versions of the tables by the same name that you met in the SQL assignment. They contain the following fields (fields comprising the primary key for each table are shown in bold):
Students
IDnumber
Surname Initials Street City Province BirthDate Gender
Marks
IDnumber Course AssignNum Mark
Assignments
Course AssignNum Weight Description
Register
ID Number Course Section
• Download and open the file UniversityStarter.fmp12 from Learn. The goal of this question is to create a layout in the Students table that looks like the following (on the next page) and behaves like the sample solution (UniversityDemo). Save your work as username_A10Q2.
Read all instructions, and test the demo before starting.
In the layout below, for a given student, the portal lists the marks for the student in the class selected by the List Marks for Which Course? popup in the upper right corner. It also shows the student’s mark in that course, which is computed by multiplying the student’s mark for each assignment times the weight for that assignment.3
There are lots of interesting things about this layout.
• How do we specify which course we’re interested in? Which is to say, what is the List Marks for
Which Course? popup connected to?
• One possibility is to create a text field (call it WhichCourse), and require the user to enter a value into it by selecting from a popup menu connected to a value list whose values are taken from the Course field of the Assignments table. This field is not part of the “data” per se, but for the purpose of the FileMaker layout.
3 This layout implements questions 1(g) and 1(h) in the SQL Assignment, though more generally.
CS200 Assignment 10 Page 4 of 6
• That would work, but would require allocating extra space for the field in every record, which is unnecessary.
a) Instead, WhichCourse should be created as a “Global” field. These are “pseudo-fields” whose values are shared by all records. Only one value is stored in the table, separate from the records in a table, and that value is seen and shared by all records in the table. You can still format this as a pop-up. (Be sure to declare that WhichCourse is of type Text, then click the Options button for WhichCourse and find the checkbox that makes it a (shared) Global field..)
It would be easy to list ALL the marks for a student in the portal — you’d just define a relationship using the IDnumber field as the link. But in the portal, we want to show records from the Marks table for a particular IDnumber and Course, so you want to match the values in two fields as you create a one-to-many relationship from Students to Marks.
b) The course, assignment number, and mark columns of the portal are just fields from the Marks table, which are readily accessible once you have connected the Students and Marks table. Place these fields into your portal.
c) What about the portal columns containing a textual description of each assignment and the assignment weight? Those textual descriptions are stored in the Assignments table.
• It seems we need to connect the Marks table to the Assignments table. What is the primary key for the Assignments table? The Course and AssignNum fields. Once again you need to link two tables by matching the values of two fields.
d) Finally, the course grade shown bottom right. See if you can figure out how that’s done. You’ll need to create a calculated field in the Marks table, and a calculated field in the Students table, but nothing more. Details left to the reader…
Password-protected solutions to these questions are in Learn. (Use a blank password.)
CS200 Assignment 10 Page 5 of 6
Bonus
[10 marks] The following are some examples of what you can do in SQL’s WHERE clause:
• compare a field value with a literal value example: name = ‘Tony’
• compare two field values —often used in a relationship condition example: Students.IDN = Register.IDN
• combine comparisons using an OR
example: (Course = ‘Biol’) OR (Course = ‘Phys’)
• combine comparisons using an AND
example: (Surname = ‘Tony’) AND (Students.IDN = Marks.IDN) AND (Course = ‘Biol’)
• other assorted operators can be used
example: (Mark > 80) AND (Mark < 90) AND NOT (Studnum = 8912345)
The above are different examples of what you can do with an SQL where clause. Explain how to do each of the above queries in FileMaker’s Find Mode and draw contrast to how it is done in SQL (or, when necessary, explain how to do the query in one application and note that it is not possible to do in the other). Note: This is essentially a compare and contrast question.
This question is not asking how you would do the particular SQL example given in FileMaker, but how you would do the bolded statements in both applications (i.e. do not explain how to find “Tony” specifically).
Assume that your reader is an experienced user of both SQL and FileMaker.
Please enter your answer in a properly (but not elaborately) formatted MS Word document named username_a10bonus (or username1_username2_a10bonus) of at most one page showing your name and username(s) at the beginning of your document.
Comment: this is the sort of question you should habitually ask yourself when you have available two applications, either of which might be suitable for the same task. It is also a typical CS 200 examination question, for which this is good practice.
Submission Instructions
• Create a folder called username_Assign10
• Move each of the assignments files (username_A10Q1 or username1_username2_A10Q1, username_A10Q2 or username1_username2_A10Q2 and username_a10bonus or username1_username2_a10bonus) to this folder.
• Compress this folder and name it username_Assign10.zip and submit it to the Assignment 10 Dropbox on Learn.
CS200 Assignment 10 Page 6 of 6