SP11 CSE200 QUIZ#5

Name _____________________________ Pickup# _______   Lab=> Friday ______ 1:30pm______ 3: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. Remember to put quotes on the like and text values and pound signs around dates.

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

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

· Assume that Data Integrity is checked when appropriate and Cascade Update/Delete is checked when possible, as well.

The tables given represent the database system for the Excel-lent Animal Shelter. The following tables are a collection of information about the animals in the facility:

The ANIMAL table is a list of all the animals at the shelter, what type of animal they are, their gender, arrival date and exceptional temperament. NOTE: the temp1 field is a numeric value.

The ADOPTERS table gives a list of people who are interested in adopting animals. Each adopter can request to adopt multiple animals; and each animal might have zero or more adopters interested in adopting them.

The INFO table defines the type of service (stype) and cost an animal may need upon arrival to the shelter. There can be none, one or multiple services per animal.

The SERVICE tables shows what type of services are offered; the cost however, is still on the INFO table and is the same per service. For example, getting a bath (type 1A) is always $10 (as shown on the INFO table).

The TEMPERAMENT table defines the different temperaments assigned to each animal. NOTE: the temp# field is a numeric value.

YOU CAN ASSUME THAT ALL OF THE DATA INTEGRITY ERRORS (IF ANY) HAVE BEEN CORRECTED ONCE YOU START TO WRITE THE QUERIES

Name _____________________________ Pickup# _______   Lab=> Friday ______ 1:30pm______ 3:30pm

1. (8 pts)  Database Relationships.  Set up the relationships of this database.  Using the boxes below, fill in the primary key (if any) and foreign key (s) (if any) in the boxes given; then, for each table, draw relationship lines between tables (exactly the same as you would see in Access).

	ADOPTERS  table Primary Key Foreign Key(s)		SERVICE  table Primary Key Foreign Key(s)
ANIMAL table Primary Key Foreign Key(s)		INFO  table Primary Key Foreign Key(s)		TEMPERAMENT table Primary Key Foreign Key(s)

2. (4 pts)  Let’s pretend for just a minute that the TAG field on the ADOPTERS table for the adopter jill smart has a value of J instead of  I. The good news is that the animal with TAG A has been adopted so needs to be deleted from the ANIMAL table.  What other changes will automatically be made to the database when the TAG A animal is deleted from the ANIMAL table? NOTE: see the last instruction bullet on the first page.

The 3 records on the INFO table that have TAG A will also be deleted; however, the 3 records from the ADOPTERS table with TAG A will *not* be deleted.

The yellow solution is required. The additional part of the solution is optional, but is there to specify that answers that include the deleting of any records from the ADOPTERS table is incorrect.

3. (10 pts)  Determine, yes or no, if each of the following sets of tables can be combined correctly in a single query (circle one).   

a. ADOPTERS, INFO, SERVICE                                            yes                               no

b. ANIMAL, INFO, TEMPERAMENT, SERVICE                 yes                               no

c. ADOPTERS, ANIMAL, INFO                                             yes                               no

d. TEMPERAMENT, SERVICE                                               yes                               no

e. All 5 tables                                                                             yes                               no

4. (8 pts) For each of the names on the ADOPTERS table, create an access query (in the design view given below) to list the name of the adopter and the temperament description of the animal they are interested in adopting. REMINDER: do not put additional unnecessary fields in your query.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

5. (20 pts) Using the design views given below, create the resulting dynaset:

DESCRIPTION: Be sure to list ALL of the animals just once. Include the TAG, the animal type, the number of people who want to adopt the animal, and the total cost of adopting the animal. The total cost for adopting an animal is the sum of all of the services for that animal *and* an $20 additional overhead fee charge for other expenses (boarding, food, profit, etc). Q5C
TAG	animal type	NumAdopt	TotCost
A	dog	3	90
B	dog	1	115
C	cat	1	43
D	rabbit	0	40
E	dog	0	30
F	cat	2	20
G	cat	2	20
H	cat	0	75
I	dog	1	158

QUERY NAME: Q5A

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions

QUERY NAME: Q5B

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

 Additional room for Expressions

QUERY NAME: Q5C

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions

SCORE ________/50

Order of fields/tables does not matter

Do not grade the show line

Join type = Inner (relative to should be blank)

TOTAL POINTS = 20

1^st two queries – worth 10 points

Order of fields/tables does not matter

MUST grade the show line

Join type = Outer relative to ANIMAL

OK if ADOPTERS in Q5B and INFO in Q5A

OK if put “animal type” field on one of these two queries (then don’t need ANIMAL table in Q5C).

Could add TotCost field with EXPRESSION to INFO query instead of doing expression in last query; must use the TotCost field name here unless rename in last query.

On query with ADOPTERS table, can count the TAG field as well; but must follow that field name thru to the last query i.e. using CountofTAG instead of Countofname.

TAG field on both queries MUST be from the ANIMAL table.