Attendees - Ohio State University



Attendees | |

|Id# |

|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 | |D |

| |5 | |

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

Draw the relationship diagram and indicate the primary and foreign keys.

[pic]

Using the QBE grids provided, write a query or set of queries to answer the following questions. Name the query by the question number. If more than one query is required for a specific question, name each query by the question number plus A, B, etc. Assume that the data provided is only a part of the data base and the Referential Data Integrity is enforced for all relationships.

1. 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.

2. Summarize the payments made for all Ohio Attendees. Include the total amount of their payments, the attendee’s id number and full name.

3. Summarize the payments made by payment method. Include the total value of the payments, the average payment value and the minimum payment value.

4. 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.

5. 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. Please list all attendees.

6. A 5% late fee is being assessed on the outstanding balances (amount owed calculated in query 5). Please list, by attendee (id#, last name), the original amount owed, the late fee and the new balance.

7. Summarize the registration fees for the current registration list by the attendee’s state of residence.

Query Name Query1 Tables Req’d Attendees, Registration, Rates

Join on Id#, Service Code Join Types Inner

|Field |LName |FName |Description | | |

|Table |Attendees |Attendees |Rates | | |

|Total | | | | | |

|Show | | | | | |

|Sort |Ascending |Ascending | | | |

|Criteria | | |Like “*Exam*” | | |

|OR | | |“All Inclusive” | | |

|OR | | | | | |

Additional Room (if needed)

Query Name Query2 Tables Req’d Attendees, Payments

Join on Id# Join Types Outer

|Field |ID# |LName |FName |Payment$ |State |

|Table |Attendees |Attendees |Attendees |Payments |Attendees |

|Total |GroupBy |GroupBy |GroupBy |Sum |Where |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | |“OH” |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

Query Name Query3 Tables Req’d Payments

Join on - Join Types -

|Field |Method |Payment$ |Payment$ |Payment$ | |

|Table |Payments |Payments |Payments |Payments | |

|Total |GroupBy |Sum |Min |Avg | |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

Query Name Query4 Tables Req’d Registration, Rates

Join on Service Code Join Types Inner

|Field |Registration# |Id# |Fee | | |

|Table |Registration |Registration |Rates | | |

|Total | | | | | |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

Query Name Query5A Tables Req’d Attendees, Query4

Join on id# Join Types Outer

|Field |Id# |Fee | | | |

|Table |Attendees |Query4 | | | |

|Total |GroupBy |Sum | | | |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

Query Name Query5B Tables Req’d Attendees, Payments

Join on Id# Join Types Outer

|Field |Id# |Payment$ |Payment$ | | |

|Table |Attendees |Payments |Payments | | |

|Total |GroupBy |Sum |Count | | |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

Query Name Query5 Tables Req’d Attendees, Query5A, Query5B

Join on Id#, Id# Join Types Inner, Inner

|Field |Id# |LName |CountOfPayment$ |Owed: |

|Table |Attendees |Attendees |Query5B | |

|Total | | | | |

|Show | | | | |

|Sort | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

Additional Room (if needed)

Owed : NZ([Query5A]![SumOfFee], 0) – NZ([Query5B]![SumOfPayment$], 0)

Query Name Query6 Tables Req’d Query5

Join on Join Types

|Field |Id# |LName |Owed |LateFee: |NewBalance: |

|Table |Query5 |Query5 |Query5 | | |

|Total | | | | | |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

LateFee:[owed] * 0.05

NewBalance:[owed] + [LateFee] can also use [owed]*1.05

Query Name Query7 Tables Req’d Attendees, Registration, Rates

Join on Id#, Service Code Join Types Inner, Inner

|Field |State |Fee | | | |

|Table |Attendees |Rates | | | |

|Total |GroupBy |Sum | | | |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

Can also be done using some of the previously created queries.

Query Name _________________________ Tables Req’d ________________________

Join on _____________________________ Join Types _______________________

|Field | | | | | |

|Table | | | | | |

|Total | | | | | |

|Show | | | | | |

|Sort | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

Additional Room (if needed)

-----------------------

Conference Registration Database

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

∞

[pic]

[pic]

[pic]

[pic]

Id#

Table:

Rates

Primary Key:

Service code

Table:

Registration

Primary Key:

Regi؀ࠁࠊࠋࠌࠏࠐࠕࠖࠛࠜࠣࠤࠨstration#

Table:

Attendees

Primary Key:

Id#

Table:

Payments

Primary Key:

none

Id#

Service Code

∞

∞

1

1

1

Can include depending on how you interpret the question

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download