1. Homepage
  2. Homework
  3. CS200 Concepts for Advanced Computer Usage - Assignment 9 - FileMaker
This question has been solved

CS200 Concepts for Advanced Computer Usage - Assignment 9 - FileMaker

Engage in a Conversation
WaterlooCS200Concepts for Advanced Computer UsageFileMakerSQLDatabase

CS200 Assignment 9 – FileMaker Due Friday July 14, 2023, 5:00 pm CourseNana.COM

Readings and Resources CourseNana.COM

On Learn: Database Resources > Database Design Files CourseNana.COM

The following are located in Assignment 9 Files on Learn. CourseNana.COM

Favourite Colour: FavouriteColourDemo: CourseNana.COM

University Starter: UniversityDemo: CourseNana.COM

Assignment Objectives CourseNana.COM

This is a text file to import into the database you create in question 1 CourseNana.COM

This file shows you how your question 1 will look and work in the end. CourseNana.COM

You will use this file for question 2 of the assignment. CourseNana.COM

This file shows you how your question 2 will look and work in the end. CourseNana.COM

  • 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. CourseNana.COM

  • An understanding of how to implement (some) three-table joins in FileMaker. CourseNana.COM

  • Be aware, however, that more complicated select statements than we have CourseNana.COM

    considered can be far harder to work out than the simple cases we have considered. CourseNana.COM

  • 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. CourseNana.COM

  • 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. CourseNana.COM

    Assignment Strategy CourseNana.COM

    This assignment may be done independently or with a partner. If you do work with a partner, please add both or your names, usernames and Student ID numbers to the footer of each layout in the files and name the files with both usernames (username1_username2). CourseNana.COM

CS200 Assignment 9 Page 1 of 6 CourseNana.COM

Your Tasks CourseNana.COM

[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.) CourseNana.COM

a) Create a FileMaker table named username_FavouriteColour with an integer primary key field and two text fields called Name and Colour. CourseNana.COM

FavouriteColour CourseNana.COM

pKey Name Colour CourseNana.COM

• • CourseNana.COM

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 shown above left). CourseNana.COM

Import the data contained in the file FavouriteColour.txt, which you can find on Learn. CourseNana.COM

  1. b)  Createadataentrylayoutcalled“Input”(showntothe
    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.
    CourseNana.COM

  2. c)  Create a layout called “MyFriends” 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. CourseNana.COM

    • 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. CourseNana.COM

    • Name the duplicate table “FindSameFavouriteColour”. CourseNana.COM

    • Since you don’t, however, want to list yourself as a friend, you will also need CourseNana.COM

      to require that matching records not have the same value of pKey.1 CourseNana.COM

    • Prevent users from editing ANY of the fields displayed in this layout, CourseNana.COM

including related fields within the portal. CourseNana.COM

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
CourseNana.COM

where (A.colour = B.colour) and (A.pKey <> B.pKey) CS200 Assignment 9 CourseNana.COM

Page 2 of 6 CourseNana.COM

  • 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
    CourseNana.COM

    Count(FindSameFavouriteColour::pKey) 2. CourseNana.COM

  • Your MyFriends layout should look like the example shown to the right. (Please put your name(s) and username(s) in the header.) CourseNana.COM

d) Create a layout called “ToPrint” (like that appearing below). This layout will show the names of others having the same CourseNana.COM

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 CourseNana.COM

a portal that has more rows than you ever expect to actually show (try counting the records and make an estimation). CourseNana.COM

  • 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. CourseNana.COM

  • 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). CourseNana.COM

    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). CourseNana.COM

    2The SQL Select statements for this look like select count(*) CourseNana.COM

    from FavouriteColour as A, FavouriteColour as B
    where A.pKey = 15 and (A.colour = B.colour) and (A.pKey
    B.pKey) CourseNana.COM

CS200 Assignment 9 CourseNana.COM

Page 3 of 6 CourseNana.COM

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): CourseNana.COM

Download the file UniversityStarter.fmp12 from Learn. Then create a layout in the CourseNana.COM

Students CourseNana.COM

IDnumber CourseNana.COM

Surname Initials Street City Province BirthDate Gender CourseNana.COM

Marks CourseNana.COM

IDnumber Course AssignNum Mark CourseNana.COM

Assignments CourseNana.COM

Course AssignNum Weight Description CourseNana.COM

Register CourseNana.COM

ID Number Course Section CourseNana.COM

Students table that looks like the following and behaves like the sample solution to this question. CourseNana.COM

Read all instructions before starting. CourseNana.COM

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 CourseNana.COM

There are lots of interesting things about this layout. CourseNana.COM

  • How do we specify which course we’re interested in? Which is to say, what is the CourseNana.COM

    List Marks for Which Course? popup connected to? CourseNana.COM

  • One possibility is to create an auxiliary 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. CourseNana.COM

    3 This layout implements questions 1(g) and 1(h) in the SQL Assignment, though more generally.
    CS200 Assignment 9 Page 4 of 6 CourseNana.COM

That would work, but would require allocating extra space for the field in every record, which is unnecessary. CourseNana.COM

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..) CourseNana.COM

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 we want to show in the portal 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. CourseNana.COM

  1. b)  Thecourse,assignmentnumber,andmarkcolumnsoftheportalarejustfields from the Marks table, which are readily accessible once you have connected the Students and Marks table. Place these fields into your portal. CourseNana.COM

  2. 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. CourseNana.COM

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. CourseNana.COM

d) Finally,thecoursegradeshownbottomright.Seeifyoucanfigureouthowthat’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... CourseNana.COM

Password-protected solutions to these questions are in Learn. (Use a blank password.) CourseNana.COM

CS200 Assignment 9 Page 5 of 6 CourseNana.COM

CourseNana.COM

Bonus CourseNana.COM

[10 marks] The following are some examples of what you can do in SQL’s WHERE clause: CourseNana.COM

compare a field value with a literal value example: name = ‘Tony’ CourseNana.COM

compare two field values —often used in a relationship condition example: Students.IDN = Register.IDN CourseNana.COM

combine comparisons using an OR example: (Course = ‘Biol’) OR (Course = ‘Phys’) CourseNana.COM

combine comparisons using an AND
example: (Surname = ‘Tony’) AND (Students.IDN = Marks.IDN) AND (Course = CourseNana.COM

‘Biol’) CourseNana.COM

other assorted operators can be used
example: (Mark > 80) AND (Mark < 90) AND NOT (Studnum = 8912345) CourseNana.COM

  • The above are different examples of what you can do with an SQL where clause as opposed to a FileMaker Find. Explain how to do each of the above queries in both SQL and in FileMaker’s Find Mode (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. CourseNana.COM

  • 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). CourseNana.COM

  • Assume that your reader is an experienced user of both SQL and FileMaker. CourseNana.COM

  • Please enter your answer in a properly (but not elaborately) formatted MS Word document named username_a9bonus (or username1_username2_a9bonus) of at most one page showing your name and username (or codes, if you are working with a partner) at the beginning of your document. CourseNana.COM

  • 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. CourseNana.COM

    Submission Instructions CourseNana.COM

  • Create a folder called username_Assign9 CourseNana.COM

  • Move each of the assignments files (username_A9Q1 or username1_username2_A9Q1, username_A9Q2 or username1_username2_A9Q2 and username_a9bonus or username1_username2_a9bonus) to this folder. CourseNana.COM

  • Compress this folder and name it username_Assign9.zip and submit it to the Assignment 9 Dropbox on Learn. CourseNana.COM

CS200 Assignment 9 Page 6 of 6  CourseNana.COM

Get in Touch with Our Experts

WeChat (微信) WeChat (微信)
Whatsapp WhatsApp
Waterloo代写,CS200代写,Concepts for Advanced Computer Usage代写,FileMaker代写,SQL代写,Database代写,Waterloo代编,CS200代编,Concepts for Advanced Computer Usage代编,FileMaker代编,SQL代编,Database代编,Waterloo代考,CS200代考,Concepts for Advanced Computer Usage代考,FileMaker代考,SQL代考,Database代考,Waterloohelp,CS200help,Concepts for Advanced Computer Usagehelp,FileMakerhelp,SQLhelp,Databasehelp,Waterloo作业代写,CS200作业代写,Concepts for Advanced Computer Usage作业代写,FileMaker作业代写,SQL作业代写,Database作业代写,Waterloo编程代写,CS200编程代写,Concepts for Advanced Computer Usage编程代写,FileMaker编程代写,SQL编程代写,Database编程代写,Waterlooprogramming help,CS200programming help,Concepts for Advanced Computer Usageprogramming help,FileMakerprogramming help,SQLprogramming help,Databaseprogramming help,Waterlooassignment help,CS200assignment help,Concepts for Advanced Computer Usageassignment help,FileMakerassignment help,SQLassignment help,Databaseassignment help,Waterloosolution,CS200solution,Concepts for Advanced Computer Usagesolution,FileMakersolution,SQLsolution,Databasesolution,