Deck 13: Acquiring Information Systems and Applications

Question 1

The steering committee of the organization you work for has decided to develop a relational database for a new accounting system. The committee has asked you to explain the different approaches to developing the database. How would you compare the system output approach and the REA approach to the committee?

Answer 1

Different approaches to develop the database are listed below:
1. System output approach - It is one of the approaches to develop a relational database for the new accounting system. In this approach, system outputs are identified which are required by the user then the data element is identified which is used to produce the output. In this approach, tables and data element of the tables are also determined. At last, table is documented with a data access diagram.
2. REA framework approach - This is another approach to develop a relational database for the new accounting system. REA stands for Resource Event Agent. It is a framework for an organization's resources, events, and agents. It is useful in increasing the productivity because it eliminates the non-value added activities.
Comparison between system output approach and REA approach is shown below:
• System output approach identifies system outputs. On the other hand, REA approach identifies business events and activities.
• System output approach identifies the data elements that are needed to produce output. REA approach identifies relationships among resources, events, and agents.
• System output approach determines the type of tables that are created by data normalization. REA approach determines the type of tables that are created by E-R diagram.
• Both approaches document the table with data access diagram.

Question 2

Using a system output approach to developing a database, you have determined that the accounting system needs to produce a purchase order for each purchase the organization places. The desired format of the purchase order is as follows:

Required:
a. What data elements need to be stored to produce the purchase order?
b. What are the three steps that should be taken to normalize the data you identified in Part a with respect to 3NF?
c. What tables should be in the database so the accounting system can produce a purchase order? Use data normalization to develop your tables.

Answer 2

a. Data elements that are needed to be stored to produce the purchase order are following:
• Purchase order number
• Purchase order date
• Vendor code
• Vendor name
• Vendor address
• Product number
• Quantity purchased
• Product description
b. Three steps that should be taken to normalize the data with respect to 3NF are following:
• All the repeating data elements should be moved to a new table and the primary keys should be assigned.
• All non-key data element should be moved to a new table and the primary keys should be assigned.
• Transitive dependencies should be moved to the new table and primary keys should be assigned.
c. To produce a purchase order in the accounting system, the following elements should be there in the database:
• Invoice table
• Invoice line table
• Vendor table
• Inventory table

Question 3

What does REA stand for and what are the definitions of each term?

Answer 3

Abbreviation of Resource Event Agent is REA. It can be understand in the following way:
R - Resource
E - Event
A - Agent
Definition of the each term is given below:
1. Resource - It is an economic factor required to attain the desired result. Three most basic resources are land, labor, and capital. A resource is always used in an event.
2. Event - An event is a business activity. Anything that takes place or a combination of things that happen is known as event.
3. Agent - Agents are those who take part in the event. A person or a thing authorized to act on behalf of another person or thing is known as agent.

Question 4

Refer to the tables you created for the purchase order in the previous question.
Required:
Draw a data access diagram and discuss briefly how the tables and relationships would be involved in printing a purchase order.

Question 5

What are some examples of relationships between entities?

Question 6

A new sales order processing system is being designed for a corporate client. During the initial design process, the specifications shown below have been proposed for the sales transaction file and the open sales order file.

Transaction records are to be retrieved by invoice number, whereas open sales order records are to be retrieved by sales order number. The transaction file is to be purged once a month. The open sales order file is permanent, although individual records will be purged after the order has been filled. Customer billing addresses are more or less permanent, but shipping addresses can vary from sale to sale because customers may request shipment to branch offices or field sites.
Required:
a. Analyze these file specifications to provide a normalized logical data structure defining the schema of a relational database management system. Provide tables of data elements describing your final normalized form.
b. Provide a data access diagram showing how the data records would be retrieved.

Question 7

What was Chris Hampton's mission, and what was her strategy for fulfilling this mission?

Question 8

A new purchase order processing system is being designed for a corporate client. The specifications that follow have been proposed for the purchases transaction file and the open purchase order file.

Transaction records are to be retrieved by transaction number, whereas open purchase order records are to be retrieved by purchase order number. The transaction file is to be purged once a month. The open purchase order file is permanent, although individual records will be purged after the order has been filled.
Required:
a. Analyze these file specifications and provide a normalized logical data structure defining the schema of a relational database management system. Provide tables of data elements describing your final normalized form.
b. Provide a data access diagram showing how the data records would be retrieved.

Question 9

A project manager in the organization where you are the controller wants to get a weekly report on each project she supervises. After spending some time with the manager to determine what data need to be on the report, you and the manager have agreed on the following format for the report:

Required:
a. What data elements need to be stored to produce each project report?
b. What are the three steps that should be taken to normalize the data you identified in Part a with respect to 3NF?
c. What tables should be in the database so the accounting system can produce a project report? Use data normalization to develop your tables.

Question 10

Refer to the tables you created for the project report in the previous question.
Required:
Draw a data access diagram and discuss briefly how the tables and relationships would be involved in printing a project report.

Question 11

The data access diagram and corresponding tables for part of a job cost system are shown below. Keys are shown in capital letters, and foreign keys are shown in italicized letters.

Required:
a. Determine the materials and labor costs of each of the active jobs.
b. Comment on the mechanism of retrieving records from Tables 2 through 5.
Tables for Problem 7:

Question 12

Using the REA approach to developing a database for an accounting system, you have identified one event, or activity, that needs to be included in the database. The event is a purchase from a vendor. The organization makes over-the-counter purchases in which the purchasing agent makes a purchase and immediately receives the goods purchased. All purchases are on account, and the company uses a voucher number to uniquely identify all purchases.
Required:
a. Identify any resources and agents that are related to the purchase event. Document the event and any resources and agents with an E-R diagram.
b. Identify the relationship among the events, resources, and agents. Add these relationships to your E-R diagram. Indicate on your E-R diagram the type of each relationship-that is, 1:1,1:*, *:1, or *:*.
c. Identify and list the attributes that are related to the resources, events, agents, and relationships.
d. Create the appropriate database tables by referring to the E-R diagram.

Question 13

Document the tables you developed in the previous question with a data access diagram.