SP07 CSE200 QUIZ#5

Name ______________________________________________________ Seat# __________

Lab Day/Time

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.

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

The tables given represent the database system for the Smith Banking Company. The ACCOUNTS table is a list of customer accounts and specific customer information. The TYPES table specifies the types of accounts that the Smith Banking Company has available for each customer to choose from. The DEPOSIT table shows all the deposit transactions for the given account numbers. The WITHDRAWALS table shows all the withdrawals for the given account numbers. Notice that the transaction$ field on the WITHDRAWALS table is a negative value.

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. (6 pts) The acct# field on the ACCOUNTS table was updated from 456 to 466. What changes, if any, were made to the other tables? Explain in detail i.e. tell what was changed on each table stating “nothing” if nothing was changed on a table; also explain WHY the data was changed or why it was not changed.

No changes were made on any other table besides the updated 456 to 466 on the ACCOUNTS table.

Data integrity was violated on both relationships from the ACCOUNTS to the DEPOSITS table and from the ACCOUNTS to the WITHDRAWALS table, so Cascade Update is not available i.e. nothing changed.

No changed to TYPES table since the account field is not connected to this table

3. (4 pts) What is the difference between an inner join and the outer join relative to the foreign key table?

Both will include the records that match, however records with data integrity errors will also show up on the outer join relative to the foreign key table.

4. (6 pts) Using the query design view below, construct a query to summarize, in this order, by type and description (show both) the total amount of deposit transactions (i.e. not the total number of deposit transaction per type, but the total amount of transactions per type).

Table(s) Used _deposits, accounts, types___ Join Type __inner_______ relative to _____________________

Field	Type	Description	Transaction$
Table	types	types	Deposits
Total	Group By	Group By	Sum
Show
Sort
Criteria
OR

5. (5 pts) Using the query design view below, construct a query to determine the account number and type of account description (not the letter, but the description) for all cities except Columbus.

Table(s) Used ___accounts, types___________ Join Type ___inner__________ relative to ________________

Field	Acct#	description	city
Table	accounts	types	accounts
Total
Show
Sort
Criteria			not “Columbus”
OR

6. (3 pts) If I want to summarize the above query by acct# so that the accounts are not listed multiple times, what would be put in the TOTAL line, if anything, for the city field?

Where… although GroupBy will also work

7. (18 pts total à 5, 5 and 8) Using the query design views given below, construct a set of queries to list, in this order, the account number, the balance and total number of transactions for ALL the account numbers.

Query Name: Q7A

Table(s) Used __accounts, deposits_____ Join Type ____outer_________ relative to __accounts_________

Field	Acct#	Transaction$	Transaction$ (any field)
Table	Accounts	Deposits	Deposits
Total	Group by	Sum	Count
Show
Sort
Criteria
OR

Query Name: Q7B

Table(s) Used ___accounts, withdrawals_____ Join Type ___outer_________ relative to ____accounts_______

Field	Acct#	Transaction$	Transaction$ (any field)
Table	Accounts	Withdrawals	withdrawals
Total	Group By	Sum	Count
Show
Sort
Criteria
OR

Query Name: Q7C

Table(s) Used __Q7A, Q7B___________ Join Type __inner_______ relative to _____________________

Field	Acct#	Balance:	Numtrans:
Table	Q7A or Q7B
Total
Show
Sort
Criteria
OR

Balance: nz([Q7A]![SumofTransaction$]) + nz([Q7B]![SumofTransaction$])

Must use query name

Must use NZ per field

Numtrans: [Q7A]![CountofAnyField] + [Q7B]![CountofAnyField]

Only have to use table name if chose same field (i.e. if anyfield is same for each query)

Optional use NZ for each individual field; incorrect if using one NZ function around entire expression