AU10 CSE200 QUIZ#5

Name _____________________________  Seat# ________   Lab=> Thur ______ 9:30am______ 11:30am

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.

The COE table is a list of people in the college of engineering (well a portion of them anyway), who will be  responsible for checking the purchase orders submitted by all of the departments in the college. Three dates are given for each purchase order number; the date it was created (i.e. a purchase order number is requested by the department then created by the college); the date received is when the department returns the completed purchase order with all of the pertinent information for ordering supplies; and the approved date is the approver signed off on the department request for supplies. Notice that some of the fields are null; that is the purchase order has not yet been received from the department or not yet approved by the college. NOTE: all purchase orders are started by the college.

The csedept table defines the details about each of the different supplies that are ordered by the cse department as well as how many of each supply is needed per purchase order.

The ecedept table defines the details about each of the different supplies that are ordered by the ece department as well as how many of each supply is needed per purchase order.

The supplies table is the list of supplies, each of which includes the description of the supply as well as the cost (i.e. dollar amount) to purchase each supply.

AU10 CSE200 QUIZ#5

Name ______________________________ Seat# ________   Lab=> Thurs ______ 9:30am______ 11:30am

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) for each table AND draw relationship lines between tables (exactly the same as you would see in Access).

		COE table Primary Key Foreign Key(s)
CSEDEPT table Primary Key Foreign Key(s)			ECEDEPT table Primary Key Foreign Key(s)
	SUPPLIES table Primary Key Foreign Key(s)

2. (4 pts)   Give an example, using the data given on the table, to introduce a Referential Data Integrity violation between the SUPPLIES and the ECEdept tables.

1) Can either delete a record from the SUPPLIES table OR

2) add a record to the ECEdept table that does not have a matching supply field to the supval field on the SUPPLIES table OR

3) change a value on either table,  such that…

there is a FK value that does not exist in the PK field

3. (4 pts)  Assuming that if Data Integrity exists, the Data Integrity box is checked, and that the Cascade Update and Cascade Delete boxes are also checked; and assuming you have introduced a Data Integrity error per the above problem, what happens if I change the purchase order value cse1 in the COE table to cse11?

All of the cse1 values on the CSEdept table will change to cse11.

4. (6 pts) Can each of the following group of tables be joined correctly in one and only one query?

a. CSEdept, COE, ECEdept? YES_______              NO_______

b. All 4 tables?                                              YES_______              NO_______

5.

(10 pts) Create an Access query (in the design view given below) to list the CSE purchase order numbers that have been approved as well as the total amount (cost*quantity) for all of the supplies ordered. Be sure to write your query so that it gives the resulting dynaset as shown.

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

Additional room for Expressions, if necessary

6.

Query Name: Q6A

(18 pts; 4, 4, 10) Create a set of Access queries to create the table given above which contains ALL of the supplies, each description, the total quantity of that supply that the CSE department ordered, the total quantity of that supply that the ECE department ordered, and the total quantity ordered of that supply by all COE departments.

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

Query Name: Q6B

Additional room for Expressions, if necessary

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

Additional room for Expressions, if necessary

Query Name: Q6C

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

 Additional room for Expressions, if necessary

SCORE ________/50

Grade show line and order of fields since given resulting dynaset

Must name the middle field as specified in the resulting dynaset

Must NOT show the date approved field

Must leave table blank for expression field

Optional table name on qty and cost fields for the expression

The PO# field can also be the POnum field from the COE table (make sure correct table listed)

REMINDERs: if using a TOTAL line, must have something in the total line for every field listed; if using an outer join, must have a relative to value; if using an inner join, relative to should be blank.

OK if switch Q6A and Q6B i.e. ECEdept in Q6A and CSEdept in Q6B

Grade show line for all 3 queries

Only grade order of fields for 3^rd query

1^st two queries:

Reminder: the “relative to” field should be the SUPPLIES table

Can include description field from supply table in one of these queries with a GroupBy for the total line.

Can rename the field here as it pertains to the 3^rd query i.e. instead of the qty field as shown, could put the following expression:

CSEqty: sum([qty]) with Expression in the total line; cannot put table in the table field but can put it in the expression

MUST use the supplies table for the supval (cannot use the supply field on the dept table)

3^rd query:

Optional table name on qty and cost fields for the expression

MUST leave table blank for expression field

OK if put the description on one of the first two queries, so wouldn’t need the SUPPLIES table on the 3^rd query

OK if put Q6A/Q6B in the expression and not in the table field

MUST put the table name and the NZ per field on the expression for 3^rd query

OK if use CSEqty in TotVal exptression instead of [Q6A]![SumOfqty]; same for ece value

FYI: there are additional solutions of inner, inner, outer but it makes the last query much more complicated. If you did this and have questions, let me know.