Memorandum / Department of Accountancy
East Tennessee State University

To:4310 Students

From:Gary G. Berg

Date:February 8, 2017

Re:Assignment 3 – Extending Database

Your client is in the process of creating a database for the revenue cycle. On the following pages are 6 tables that have been created. Relationships between tables have not been established. Your client wants you want to extend the schema shown on the following pages to include information about customer payments. Some customers make installment payments on each invoice. Others write a check to pay for several different invoices. You client wants to store the following information: (note the format instructions on 3 of the fields): Do not add any additional fields.

  • $ amount applied to a specific invoice (format this field as currency)
  • cash receipt number
  • customer number
  • date of receipt (format this field as short date)
  • Employee Cashier # for cashieremployee processing payment
  • Invoice# payment applies to
  • total amount received (format this field as currency)

Required

  1. Download the existing database file.
  2. Create the two additional tables neededto store the information your client wants.
  3. These two tables will not contain any data for this assignment
  4. Identify the primary key(s) for each new table you create.
  5. Establish all necessary table relationships. Make sure you enforce referential integrity.
  6. Submit the completed file via the D2L dropbox.:
  7. Files submitted to the wrong dropbox will receive a grade of 0
  8. If you have an issue and need me to take a look at what you have done, send the file as an email attachment.
  9. I will not tell you the answer, but I will try to point you in the right direction
  10. Due on02-20-2017, 11:30 p.m. (missing this deadline will result in grade of zero)
  11. Remember, file must be virus free. (assignment received containing a Virus receives a grade of 0)

tblCustomer
Customer# / Name / Street / City / State / Zip Code / Credit Limit / Account Balance
11255 / G. Hwang / 2993 Main / Mesa / AZ / 85281 / $4,000.00 / $875.00
12971 / J. Jackson / 466 West Oak / Tempe / AZ / 35280 / $5,000.00 / $2,588.00
13629 / P. Szabo / 246 East Palm / Mesa / AZ / 35281 / $6,000.00 / $3,955.00
15637 / S. Martinez / 2366 Spring / Tempe / AZ / 35297 / $5,000.00 / $250.00
18229 / S. Martinez / 1744 Apache / Tempe / AZ / 85237 / $3,000.00 / $1,675.00
tblEmployeeCategory
Category# / CategoryType
1 / Sales Rep
2 / Cashier
tblEmployee
Employee # / EmployeeName / EmployeeCategory
22 / Jon / 1
23 / Jill / 1
24 / Jane / 1
25 / Bill / 1
26 / Wanda / 2
27 / Robert / 2
tblInventory
Item# / Description / QOH / MSRP
10573 / 19" Monitor / 13 / $495.00
10574 / 21" Monitor / 8 / $949.00
10622 / Laser Printer / 22 / $395.00
10623 / Color Laser Printer / 5 / $699.00
10624 / Multi-functional Printer / 12 / $799.00
tblSale
Invoice# / Date / Sales Rep Employee # / Customer Number / Amount
1 / 9/8/2015 / 25 / 15637 / $5,399.00
2 / 9/10/2015 / 22 / 12971 / $1,748.00
3 / 9/25/2015 / 24 / 13629 / $1,185.00
4 / 10/2/215 / 25 / 11255 / $399.00
5 / 10/11/2015 / 22 / 15637 / $1,098.00
6 / 10/25/2015 / 25 / 18229 / $990.00
tblSale-Inventory
Invoice# / Item# / QuantitySold / ActualSalePrice
1 / 10573 / 1 / $495.00
2 / 10574 / 1 / $949.00
2 / 10624 / 1 / $799.00
3 / 10622 / 3 / $395.00
4 / 10573 / 1 / $495.00
5 / 10573 / 1 / $495.00
5 / 10623 / 1 / $699.00
6 / 10573 / 2 / $495.00

1