Accounting Information Systems Homework - Database Design and Security

This assignment solution addresses an accounting information systems homework problem. Part A requires an analysis of a flat file database used by Northeast Seasonal Jobs International (NSJI), identifying its shortcomings from a reviewer's perspective and suggesting improvements. It also involves transforming the flat file to 3NF (third normal form) based on provided business rules. Part B includes discussion questions about internal controls in a movie theater and a restaurant, along with problems that explore violations of the principle of separation of duties, proposing procedures to mitigate the risks associated with each scenario. The solution provides detailed explanations and recommendations for data management, database design, and internal control practices.

Accounting Information Systems Homework
Part A:
You have been hired to review the accuracy of the books of Northeast Seasonal Jobs
International (NSJI), a job broker. NSJI matches employers whose business is seasonal (like ski
resorts) with people looking for part time positions at such places. Employers are located in the
Northeast (New England), but recently expanded to include parts of Canada. NSJI maintains all
of this information in a flat file spreadsheet.
For the spreadsheet structure see the Excel file "Student_NSJI_Flat_File.xlsx" attached.
Requirements
1) From a reviewer’s perspective, explain what is wrong with NSJI’s approach to manage its
data. Do you think these problems are serious from the company perspective? Please be specific.
2) To reduce the possible impact of these potential problems, what advice would you give to the
company? 3) Using the spreadsheet structure provided, transform the flat file to 3NF (third
normal form).
Business Rules
You have ascertained from an interview with your client (NSJI):
● Contact position is fixed – does not change (i.e. it is a field of Employer) ● “Comments” is a
field of Employer, not Position
● “NAICS” stands for “North American Industry Classification System”
● “Openings” is a field of Position, not Employer
● “Phone” is a field of Employer
● “Position ID” is unique for all employers
Part B:
Discussion Questions:
7.5. When you go to a movie theater, you buy a prenumbered ticket from the cashier. This ticket
is handed to another person at the entrance to the movie. What kinds of irregularities is the
theater trying to prevent? What controls is it using to prevent these irregularities? What
remaining risks or exposures can you identify?
7.6. Some restaurants use customer checks with prenumbered sequence codes. Each food server
uses these checks to write up customer orders. Food servers are told not to destroy any customer
checks; if a mistake is made, they are to void that check and write a new one. All voided checks
are to be turned in to the manager daily. How does this policy help the restaurant control cash
receipts?

Problems:
7.2. Explain how the principle of separation of duties is violated in each of the following
situations. Also, suggest one or more procedures to reduce the risk and exposure highlighted in
each example.
a. A payroll clerk recorded a 40-hour work-week for an employee who had quit the previous
week. He then prepared a paycheck for this employee, forged her signature, and cashed the
check.
b. While opening the mail, a cashier set aside, and subsequently cashed, two checks payable to
the company on account.
c. A cashier prepared a fictitious invoice from a company using his brother-in-law’s name. He
wrote a check in payment of the invoice, which the brother-in-law later cashed.
d. An employee of the finishing department walked off with several parts from the storeroom
and recorded the items in the inventory ledger as having been issued to the assembly
department.
e. A cashier cashed a check from a customer in payment of an account receivable, pocketed the
cash, and concealed the theft by properly posting the receipt to the customer’s account in the
accounts receivable ledger.
f. Several customers returned clothing purchases. Instead of putting the clothes into a return bin
to be put back on the rack, a clerk put the clothing in a separate bin under some cleaning
rags. After her shift, she transferred the clothes to a gym bag and took them home.
g. A receiving clerk noticed that four cases of MP3 players were included in a shipment when
only three were ordered. The clerk put the extra case aside and took it home after his shift
ended.
h. An insurance claims adjuster had check-signing authority of up to $6,000. The adjuster
created three businesses that billed the insurance company for work not performed on valid
claims. The adjuster wrote and signed checks to pay for the invoices, none of which
exceeded $6,000.
i. An accounts payable clerk recorded invoices received from a company that he and his wife
owned and authorized their payment.
j. A cashier created false purchase return vouchers to hide his theft of several thousand dollars
from his cash register.
k. A purchasing agent received a 10% kickback of the invoice amount for all purchases made
from a specific vendor.