WI09 CSE200 QUIZ#5

Name ______________________________________________________ Seat# __________

Lab Day/Time _______ Friday 1:30pm _______ Friday 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.

· Prior to starting to write the queries, assume you have corrected all the data integrity issues, if any.

DESCRIPTION

The tables given represent the database system for the Excel-lence Ice Cream Company. The following tables are being used to collect information about the orders taken from customers:

The FLAVORS table is a list of ice cream, frozen yogurt and sorbet flavors offered to customers with the calories per one single scoop also given.

The CONES table defines the type and size of the cone options as well as the calories per cone type. There are 3 different types of cones – sugar, bowl and waffle – and there are three sizes for each cone type – small, medium and large. The unique cone identification value, that is, the field name “cid”, is created from the first letter of the cone type followed by the first letter of the cone size. This field will always be length 2.

The ORDERS table is a list of orders taken from customers with the cone type designated.

The SCOOPS table specifies the order and the scoop information including the number of scoops as well as the flavor. Notice that there are duplicate order numbers. This is so that a person can order different flavored scoops on the same cone (i.e. for the same order). For instance, order#1 has 2 scoops, one is black cherry swirl (flavor#1) and the other is choc chip cookie dough (flavor#2).

The COMPANIES table specifies who supplies the flavors and how many gallons of each. The companynum field says that company number one supplies flavors 1, 3, and 7. Not included but important is yet another table to specify the name of each company and where they are located, etc.

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) of each table and draw relationship lines between tables.

2. A. (3 pts) The given database has data integrity for all its relationships. Define what this means.

All the foreign key values exist in the primary key field.

B. (3 pts) Give an example of cascade delete using the ORDERS and SCOOPS tables.

A PK value is deleted from ORDERS and the associated record(s) that match on the SCOOPS are also automatically deleted. They should give you an example

3. (4 pts) What is the difference between an inner join and the outer join relative to the foreign key table? In Access, this is the difference between option 1 and option 3 when you are determining the join properties between two tables on a query.

Since this database has data integrity, they are the same. The difference is that option 3 will show inner join records plus data integrity errors, if any, but since there are no data integrity errors, they are the same.

4. (6 pts) Using the query design view below, construct a query to list only the flavor number and the cone type (sugar, waffle or bowl i.e. see the CONES table) for all the orders taken.

TABLE(S): ORDERS CONES SCOOPS
JOIN TYPE: INNER			RELATIVE TO: blank
Field	Type	flavor
Table	Cones	scoops
Total
Sort
Show	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

5. (5 pts) Using the query design view below, construct a query to summarize by the two-letter cone identification value, how many of ALL the possible cone-type/size combinations are ordered. NOTE: Some of the cone-type/size combinations could have a result of zero if no one ordered that cone-type/size combination.

TABLE(S): CONES ORDERS
JOIN TYPE: OUTER			RELATIVE TO: CONES
Field	cid	Order#/Coneid
Table	Cones	Orders
Total	Group by	Count
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

6. (3 pts) Will either an inner or outer join work for the above query (yes/no)? Explain your answer.

YES, but you will not get the same information i.e. NO the inner join won’t list ALL the cone/size combos, only the ones that were ordered i.e the ones that match.

NO, since you only get the records that match with the inner and ALL the cone/size combo with the outer. Only if all the PK values exist in the FK field will both outputs actually be the same, but you don’t know ahead of time that that wil be the case.

7. (18 pts total à 5, 5 and 8) Using the query design views given below, construct a set of queries to determine how many gallons are left for each and EVERY flavor on the database. Your solution should result in the dynaset given/shown on the table worksheet. NOTE: There are 15 scoops/gallon.

Query Name: Q7A

TABLE(S): COMPANIES FLAVORS
JOIN TYPE: OUTER			RELATIVE TO: FLAVORS
Field	Flavor#	numgal
Table	flavors	companies
Total	Group by	sum
Sort
Show	X	X
Criteria
OR

Additional room for Expressions, if necessary

The flavor# must come from the FLAVORS table

Both fields must have the show line checked

Query Name: Q7B

TABLE(S): FLAVORS SCOOPS
JOIN TYPE: OUTER			RELATIVE TO: FLAVORS
Field	Flavor#	#scoops
Table	Flavors	Scoops
Total	Group by	sum
Sort
Show	X	X
Criteria
OR

Additional room for Expressions, if necessary

The flavor# must come from the FLAVORS table

Both fields must have the show line checked

Query Name: Q7C

TABLE(S): QUERY7A QUERY7B FLAVORS
JOIN TYPE: INNER				RELATIVE TO: if inner join, leave blank
Field	Flavor#	Flavor	Numgal:
Table	Can be from any table listed above	flavors	Leave blank
Total
Sort
Show	X	X	X
Criteria
OR

Additional room for Expressions, if necessary

numgal: nz([sumofnumgal])-nz([sumof#scoops])/15

optional table name allowed as well as optional ,0 for second argument of NZ function.

be sure the NZ functions are PER FIELD.

Actually any join type will work for this last query. Be sure if they put OUTER that they put a table name in the RELATIVE TO field… doesn’t matter which one.

Order matters as well as the name of the calculated field and the show line… since I give the resulting dynaset.

Can add flavor field to one of the previous queries and not include the FLAVORS table here.

Order of first two queries doesn’t matter i.e. can do FLAVORS/SCOOPS first then FLAVORS/COMPANIES.