You are here

Problems

19 January, 2016 - 12:35

Notes regarding Problems 1 through 5: These problems should be completed with a databasesoftware package, such as Microsoft Access. For Problems 1 through 3, you may use datathat you (or your instructor) downloaded from a database. Problem 4 provides an alternativeto Problems 1 through 3 by using the database structure and sample data from Figure 3.19. This problem may also be completed using the software of your choosing.

1.

Before starting this problem, you should consult the customer master file record layout in Figure 3.6.

 

REQUIRED: Using the database software indicated by your instructor:

 

a.

Create the “structure” for the records in the customer file. Use Figure 3.6 as a general guide to the data elements to be included in the customer records. However, observe the following specific requirements:

   

(1)

Devise your own coding scheme for the “customer number.”

   

(2)

For the customer address, provide three separate fields, one each for street address, state, and ZIP code.

 
media/image54.JPG
Figure 3.19 Relational Database for Discussion Question 3-4 and Problems 3-4 and 3-5 
 
   

(3)

Provide for two additional data elements that are not shown in Figure 3.6 (because they normally would be accessed from other files)—open sales orders and accounts receivable balance.

 

b.

If the software package supports a function to design input screens, create the screen format to be used for entering customer data.

 

c.

Create hypothetical customer records and key the data into the database. The only design constraint is to use a variety of names, street addresses, states/ZIP codes, open sales order amounts, accounts receivable balances, and credit limits. (The number of records will be indicated by your instructor.)

 

d.

Obtain a printout of the database records.

2.

NOTE: This problem is a continuation of Problem 1.

 

REQUIRED:

 

a.

“Search” the database for all customers with a ZIP code of ZZZZZ (choose a code that is common to at least two, but not to all, of your customers). Obtain a printout of your search algorithm and a list of customers whose records met the search parameter.

 

b.

“Sort” the database in the descending order of credit limit amounts. Obtain both a printout of your sort algorithm and the sorted list of customers.

 

c.

Create a “Customer Status Report” (the report title). Observe the following specific requirements:

   

(1)

Provide column headings, in left-to-right order, for customer name, credit limit, accounts receivable balance, and open orders.

   

(2)

For each state, print subtotals of the accounts receivable balance and open orders columns.

3.

NOTE: This problem is a continuation of Problem 1.

 

REQUIRED:

 

a.

Write a “program” to enter customer order amounts into the system and to have the system either warn the user if the new order places the customer over his or her credit limit or advise the user if the credit limit is not exceeded. Store the program in the system, and obtain a hardcopy printout of the program.

 

b.

Test the program developed in (a) by entering the amounts of customer order transactions (use a variety of order amounts and different customers, such that you test all possible combinations of variables involved in the credit-checking algorithm). (The number of order transactions will be indicated by your instructor.) Obtain hardcopy evidence of the results of your testing.

4.

Using the database structure and sample data in Figure 3.19 as a starting point (rather than Figure 3.6), complete the requirements of Problems 1 through 3 (or whatever portions of those problems your instructor may indicate).

5.

Use the database structure and sample data in Figure 3.19 to:

 

a.

Combine the tables to obtain a complete record of the order and shipment. Obtain both a printout of the algorithm(s) used to combine the tables and the list of these records.

 

b.

Select the inventory items for which there is no order. Obtain both a printout of the algorithm(s) used to select the items and the list of the selected records.

 

c.

Select those orders that have not yet been shipped (i.e., open orders). Obtain both a printout of the algorithm(s) used to select the open orders and the list of the selected records.

 

d.

Calculate the total value (price) of the inventory items that are on hand. Sort the items in descending order of value. Obtain both a printout of the algorithm(s) used to perform the calculations and to sort the records, and a list of the sorted records.

6.

This problem asks you to research the literature for controls that apply to database management software.

 

REQUIRED: Develop a paper that discusses control plans for single user PC database management systems (Microsoft Access or another of your choosing). Your paper should explain how each plan operates (with illustrations where appropriate) and how the plan helps to achieve the information process control goals discussed in Chapter 8 (see Table 8.1). (The number of pages will be indicated by your instructor.) Note: Limit yourself to controls that apply only to database application software. Do not discuss PC pervasive control plans.

7.

Write a short paper describing the database underlying a small company enterprise system (e.g., Quickbooks®, Peachtree®, MYOB®, or another you have access to).

 

a.

Does it appear to be integrated? When you change an item of data in one application, does it carry through to others? (For example, if a customer’s billing address is changed, do all existing invoices reflect the change?)

 

b.

How easy is it to set up the data relationships for the database?

 

c.

Can you view the schema? Are subschemas supported?

 

d.

Are there any controls in place to ensure that data relationships make sense? (For example, is referential integrity supported?)

8.

REQUIRED: Develop an entity-relationship diagram of the Information System that supports the purchasing process of Proware Company described below. Include the cardinality constraints.

The individual departments of Proware Company refer to various vendor catalogs when they need to purchase items. They then complete a purchase request form over the company intranet for each vendor. Once completed, the form is forwarded to the Purchasing department, where it is validated and checked against the department’s budget to ensure there are still funds available in its supply budget. Each form is assigned a unique serial number, referred to as the PO number. Purchasing transmits the validated form to the vendor, or faxes it if the vendor is not online.

When the vendor fills the order, it sends an invoice to purchasing and enclose a copy of the invoice with the shipment when it is delivered to the department. The department verifies that the contents match the PO, attaches a note approving payment, and forwards the approved invoice to purchasing. An invoice may only be partially approved, if an item was missing or incorrect. A second payment authorization may be submitted at a later time when the correct item is received.

Each week purchasing generates vendor checks based on the approved authorizations from departments, and maintains a copy of the check in the computer file. Vendors may be paid for multiple orders (e.g. for several departments) in any given week.

Each month, purchasing generates a budget report for each department, which itemizes the amount paid for each invoice, the amount allocated for outstanding purchase orders, and the remaining available funds for the department.

9.

REQUIRED: Develop an entity-relationship diagram of the information system that supports the hiring process of Proware Company described below. Include cardinality and participation constraints.

When a manager needs to hire an employee, he or she first completes an Employee Requistion form over the company intranet, which indicates the position open, the rate of pay, hours, skills needed, and whether the requisition is for a replacement or additional employee.

Once submitted, Human Resources recruits as needed to fill the position. When applications arrive, Human Resources is responsible for prescreening the applicants. Anyone who appears suitable is scheduled for an interview with the hiring manager. The interview date and time is noted on the application, and forwarded to the hiring manager.

The hiring manager completes an interview form after each interview and attaches it to the application. When all of the interviews are complete, the hiring manager gives the name of the top candidate to Human Resources, who prepares an offer letter to be sent to the applicant. The applicant signs the letter to indicate acceptance and returns it to Human Resources. An employee file for the newest member of Proware is created, and the original application, the interview form, and the offer letter are included in it.