Courses/Computer Science/CPSC 203/CPSC 203 Template/Labs Template/TA Examples for Access

TA Examples for Access

TAs please upload your examples of access documents that explain concepts or help with skills. Also provide the following information:

1. Your name
2. What concept the access file helps to explain

---

Access Examples

• Navneet: Week 1 - Lab 1
  • Concepts: tables, candidate key, primary key, schema, foreign key

• Tuan Vu: Week 1 - Lab 1
  • Concepts: Basic parts of a Database, Entity Relationship Diagrams

• Mina: Week 1 - Lab 2
  • Concepts: Database, tables, records, forms, queries, reports

• Mohammad: Week 2 - Lab 1
  • Concepts: Multi-Table Queries, Custom Calculations, Aggregate Queries, Constraints

• Kambiz : Week 2 - Lab 2
  • Concepts: Crosstab queries and Custom Calculations

Example Assignment (King)

See Media:CPSC203-Mock_Assignment_2.pdf‎

Navneet: Week 1 - Lab 1

EXAMPLE. The two images below correspond to two tables, Student and Instrument, in university database. Referring to these two tables, answer the following questions:

• Questions:
  • What is the candidate key for the Student table? Is there more than one candidate key?
  • What is the candidate key for the Instrument table?
  • What is the primary key for the Student table?
  • What is the primary key for the Instrument table?
  • What is the schema of the Student table?
  • What is the schema of the Instrument table?
  • What is the foreign key in the Instrument table?

Student Table

Instrument Table

Tuan Vu: Week 1 - Lab 1

An example of the database that an online store would have:

1) A table that stores customer information.

Q: What kind of information do we want to keep for each customer?

A: Customer ID, Name, Email, Address, Phone number…

2) A table that stores item information.

Q: What kind of information that we want to keep for each item?

A: Item ID, Name, Price, Current quantity in stock…

3) A table that stores order information.

Q: What kind of information should we have?

A: Order ID, Customer ID, Item ID, Quantity, Date, Status…

Questions:

- What kind of relationship between the tables do we have here?

- Why don’t we just have the complete customer and item information in each order instead of using the reference?

- If so, what would happen if some customer changes his profile?

Mina: Week 1 - Lab 2

Example. The database below (students.accdb) contains the students information. It has a table called Grades. Each record in Grades table consist of the following fields: Student ID, Last Name, First Name, Gender, Birth Date, CPSC grade, Art grade, Math grade. Download the database and complete the following tasks, as well as answer the following questions:

Download: Media:student.accdb

• Tasks:
  • Open Grades table. It has the following records:

• Use Datasheet View to add the records shown in below to the Grades table:

• Add a new table, name it Newgrades, consists of following fields: Student ID, Course Name, Grade.
• Create a form based on the table created in the previous step and fill the new table with the same information provided in Grades table using that form. The form will look like this:

• When you are finished you should have a similar table to the one depicted below:

• Use Query Wizard, to create two simple queries on Grades table:
  • Compute Average, Min, Max and Sum of the CPSC grade field.
  • Show the First Name, Last Name and Math grade of all students.
• Use Report Wisard to create a report on the Grades table, showing the Math grades of all student, grouped by gender, sorted by Student ID. The final report will be like this:

Media:student.accdb

• Questions:
  • Is there any prime key in second table? why not?
  • What is the relationship between two tables?
  • After adding the second table, which fiels are not necessary in the first table?
  • Why this is important that we set the types of each field?

Mohammad: Week 2 - Lab 1

The database file can be downloaded here.

The model contains two simple tables Student and School with the following relationship. This simply shows students and the schools they are studying at.

Exercise 1: Design a multi-table query that shows "First Name", "Last Name", "Date of Birth", "Phone Number", "Address", "Average" and "School Name" for each student. Try to sort the result based on student's last name.

Examine the SQL view of the query and try to understand it. The result should look like this:

Exercise 2: Design a query to extract all top students (with average grater than %80) who study at University of Calgary and show their first name, last name and average.

Exercise 3: Remove the "Average" field from the previous query and replace it with a simple letter GPA (Since all average are above 80, you need only to show "A+" where average is above 90 and "A" in case average is less than 90).

Use Expression Builder and IIf function to make the calculated field. Notice how we specify the name of the new field we are building:

The result should look like the following:

Exercise 4: Design a query to show names of schools and the number of students in each of them. In the design view, press on button to enable "total row" in the query designer:

Try to use the "First Name" field from the "Student" table, instead of "ID" and examine the result. Explain why "ID" is the best choice to use in this case.

Exercise 5: Design a query that shows school names and the number of students with an average between %80 and %90 in each of them.

The result should look like the following:

Kambiz: Week 2 - Lab 2

Download file from here. Example: Tables tblItem and tblCountry have relationship as shown below:

tblItem is the main table that contains some information about different appliances. Their name, price and country that has made the product.

Now as mentioned in the Database: Week2 - Lab2 material, in order to create a crosstab query you need to select query wizard from the create tab.

Then select crosstab query wizard. Now from the list of tables select tblItem and click next.

Now select countryID for the row heading and click next.

Now select ItemName for the column heading and click next.

Now select price and sum from the list of fields and functions and click next.

Finaly click next and finish. Now you have your crosstab query that shows how much is the price of each item and categorizes them based on the country of origin.

Custom Calculation

Now lets say we want to creat a query that shows item's name, price and country of origin. Also we want to have a custom calculation that if the price of an item is greater than or equal 100 dollars it types expensive otherwise types cheap.

If you remember from the IF THEN statement in Microsoft Excel the formula would be something like this:

If([tblItem].[Price]>=100,"expensive","cheap")

Here, instead of IF we can type IIF.

And since this calculation should appear in a column of the query we type something like price check: before the actual formula as shown below.

After running the query here is your result:

Try to come up with a different condition that uses Nested IF.

Practice TBA 2

Here is an example of TBA 2 you can practice with to get more familiar with queries and tables. Media:TBA2-pretest.mdb

Navneet: Practice TBA2

The following is an example to help you prepare for TBA2. We will go over this during tutorial (19/10/2008, MS236, 9:00 - 10:00 am). The example is based on the two tables, shown below. You can also download an Access database with the two tables already created.

Media:Navneet-TBA2-Practice.mdb

Ryan: Helpful Suggestions for Asst 2

The following is a set of helpful hints to do assignment 2.

This assignment is all about the queries the better queries you have, the better the mark you will get.

• Table “LanguageRankings” properly loaded – you need to create a query that ranks the languages obtained, look at how the website example is done. same concept different data.
• once you have created a query that has ranked each of the languages for all the students, create an append query to add this information into the "LanguageRankings" table.

This file has an example of the website rankings, an append query for website rankings, and a few other examples of queries done with Website rankings, use this file as a guide for your assignment.

Media:F08 Assignment2.mdb

good luck!