SP09 CSE200 QUIZ5

Name _________________________________________ Seat# __________

Lab Day/Time _______ Thursday 3:30pm _______ Friday 1:30pm

Instructions:

· Put away all books, papers, and calculators. Turn off all beepers and cell phones.

· Answers must be legible or they will be marked incorrect.

· Be sure that all answers are SYNTACTICALLY correct i.e. as you would see them prior to running the query… see the below reminders for some (but possibly not all) hints.

· REMEMBER that all the data is not shown. Be sure your queries will work with additional records.

ACCESS REMINDERS

· No quotes around yes/no field values.

· Use # signs around dates.

· Be sure to use the word Like and quotes on criteria that have wildcard characters.

· No table name is used when creating an expression.

· Use square brackets around the table/query name and square brackets around the field name with an exclamation point in between when using table/query data in an expression.

· Aggregate functions: Group By, Min, Max, Avg, Count, Sum, Where, Expression.

· The count aggregate function determines the number of records in a field so any field from that same table can be used… be sure to choose a field!

· When the total line is used (i.e. aggregate functions are used), all the fields listed must have something in the total line.

· When using a total line, sometimes the resulting dynaset field names will change (min, max, sum, count, avg). For example, when counting the number of states, the output field will likely be: CountofState.

· For Query grid problems ONLY, assume data integrity is NOT violated

SEAT#: _____________ Attendees
Id#	FName	LName	Address	City	State	Zip
1	Nancy	Davolio	507 - 20th Ave. E. Apt. 2A	Seattle	WA	98122
2	Tim	Smith	908 W. Capital Way	Tacoma	WA	98401
3	Brandon	Coake	722 Moss Bay Blvd.	Kirkland	WA	98033
4	Margaret	Peacock	4110 Old Redmond Rd.	Redmond	WA	98052
5	Matthew	Dunn	14 Garrett Hill	Seattle	WA	98105
6	Helvetius	Nagy	722 DaVinci Blvd.	Kirkland	WA	98034
7	Deborah	Peterson	305 - 14th Ave. S. Suite 3B	Seattle	WA	98128
10	Smith	John	123 Main Street	Columbus	OH	43081
11	Johnson	Mark	420 Broad Street	Columbus	OH	42001
12	Griffin	Jen	500 High Street	Columbus	OH	43081
13	King	Jennifer	400 Walnut Street	Cincinnati	OH	41098
14	Day	Karen	250 Fifth Street	Cincinnati	OH	41077
15	Bair	Jeff	125 Plum Street	Cincinnati	OH	41077
16	Beard	Nancy	10 South Wacker	Chicago	IL	60639
17	Wallace	Brad	120 South LaSalle	Chicago	IL	60639
18	Davis	Joe	15 West Washington	Chicago	IL	60221

Registration
registration#	id#	Service Code
1	4	D
2	7	A
3	7	B
4	2	B
5	1	A
6	5	D
7	6	A
8	6	C
9	9	D
10	10	E
11	14	D
12	17	A
13	17	B
14	12	B
15	16	A
16	5	D
17	13	A
18	13	C
19	11	D

Rates
Service Code	Description	Fee
A	Class Tuition	$800.00
B	Exam - I	$200.00
C	Room & Board	$400.00
D	All Inclusive	$1,250.00
E	Exam - II	$100.00

payments
id#	payment$	date	method
1	$400.00	5/16/2002	credit card
4	$250.00	6/16/2002	check
4	$250.00	5/22/2002	credit card
6	$1,200.00	6/21/2002	PO
7	$500.00	5/28/2002	credit card
11	$200.00	6/3/2002	check
11	$1,050.00	6/8/2002	PO
12	$100.00	6/8/2002	credit card
15	$1,250.00	6/12/2002	credit card
16	$800.00	6/16/2002	credit card
17	$100.00	6/2/2002	check

The Conference-Registration database above consists of the following tables:

• Attendees -list of all attendees of this annual conference. A unique id# is associated with each attendee. Note that some attendees have not yet registered for specific services but have been included since they have indicated their desire to attend.
• Rates – A list of the different conference services and rates, by unique service code. Attendees may sign up for one or more service code items or the all inclusive fee which covers all services.
• Registration – is a list of all this year’s conference registrations by attendee by service (ie: attendee #7 is signed up for both A and B – class tuition and exam).
• Payments – is a list of this year’s payments including the amount, date and payment method.

1. (6 pts ) Database Relationships. Set up the relationships for this database. Using the boxes below, fill in the primary and foreign key(s) - if any - for each table and draw relationship lines between tables.

2a. (4 pts) Why is it invalid to join the payments, attendees and registration tables?

Because a many to one to many relationship (or a pk field to two different fk field tables) creates multiple records and incorrect values

2b. (4 pts) What would happen if I decided to change the id# 1 on the Attendees table from 1 to 21? How would that change the database?

The id# 1 on the payments table would change to a 21 as long as referential data integrity is “on”.

The id# 1 on the registration table would NOT change because it has a data integrity violation

2c. (4 pts) What is the difference between an inner join between two tables and an outer join relative to the foreign key table of the same two tables?

The difference is that the outer join relative to the fk table will show the inner join plus any data violation errors i.e. fk values that do not exist in the pk field.

3. (6 pts) List the full name of all attendees planning on taking any of the exams during the conference. Sort the list by last name then first name. All exams are listed as such by their description. Make sure your query will work even if another exam is later added to the table.

Query Name: Q3 Tables Required: Attendees, Registration, Rates

Join Type: Inner Relative to

Field

LName

FName

Description

Table

Attendees

Attendees

Rates

Total

Show

Sort

Ascending

Ascending

Criteria

Like “*Exam*”

OR

“All Inclusive”

Additional Room (if needed)

4. (6 pts) Summarize the payments made for all Ohio Attendees. List the total amount of their payments, the attendee’s id number and full name.

Query Name: Q4 Tables Required: Attendees, Payments

Join Type: Outer Relative to: Attendees

Field

ID#

LName

FName

Payment$

State

Table

Attendees

Attendees

Attendees

Payments

Attendees

Total

GroupBy

GroupBy

GroupBy

Sum

Where

Show

Sort

Criteria

“OH”

OR

Additional Room (if needed)

5. (5 pts) Create a Registration Fee list which includes each registration record and the associated fee for services. List the registration#, registrant’s id# and the associated fee.

Query Name: Q5 Tables Required: Registration, Rates

Join Type : Inner or Outer Relative to: if choose outer, must Registration table here

Field

Registration#

Id#

Fee

Table

Registration

Registration

Rates

Total

Show

Sort

Criteria

OR

Additional Room (if needed)

MUST show FEE field – error if do not show it since need to use it for the next query

6. (15 pts) Using Q5 as a starting point, create a list by attendee (id#, last name) that details the number of payments made and the total amount still owed by the attendee based on their registration charges. Be sure to list ALL attendees.

Query Name: Q6A Tables Required: Attendees, Q5

Join Type: Outer Relative to: Attendees

Field

Id#

Fee

Table

Attendees

Q5

Total

GroupBy

Sum

Show

Sort

Criteria

OR

Additional Room (if needed)

MUST show both fields – error for each if don’t

Query Name: Q6B Tables Req’d Attendees, Payments

Join Types Outer Relative to: Attendees

Field

Id#

Payment$

Payment$

Table

Attendees

Payments

Payments

Total

GroupBy

Sum

Count

Show

Sort

Criteria

OR

Additional Room (if needed)

Query Name Q6C Tables Req’d Attendees, Q6A, Q6B

Join Types Inner Relative to:

Field

Id#

LName

CountOfPayment$

Owed:

Table

Any table

Attendees

Q6B

Total

Show

Sort

Criteria

OR

Additional Room (if needed)

Owed : NZ([Q6A]![SumOfFee], 0) – NZ([Q6B]![SumOfPayment$], 0)

Ok if also used outer join relative to any table for Q6C.

OK if did not put ,0 on the NZ functions as this is the default.

OK if did not put query names on above equation.

Do not have to use the Attendees table in Q6C if put the last name on another query (Q6A or Q6B).

MUST show all the fields