Accouting Ination System Chapter 4 Solutions(1).doc

Accounting Information Systems Chapter SolutionsChapter 4Q4.1The database approach is possible because of the database management system (DBMS). As shown in Figure 4.2, the DBMS is a software program that sits between the actual data stored in the system and the application programs that use the data. As shown in Figure 4.4, this allows users to separate the way they view the data (called the logical view) from the way the data is actually stored (the physical view). The DBMS interprets the users requests and retrieves, manipulates, or stores the data as needed. The two distinct views separate the applications from the physical information, providing increased flexibility in applications, improved data security, and ease of use.In a database system the manager will rarely, if ever, need to understand or be familiar with the physical view of the data. In most instances, the internal auditor and the programmer will not need to understand or be familiar with the physical view of the data. Most everything they do requires only an understanding of the logical view of the data.If accountants understand logical data structures and the logical view of the data they are better able to draw the information they need from the database.P4.1a. Note that the problem does not say what the date is (date ordered, date shipped, etc) so different assumptions can be made about the need date data. To fill out a sales order, the sales order entry clerk needs access to the following data stored in the data base 4-1- item number- description- quantity-on-hand- price- customer name- customer name- shipping address- credit limit- account balance To create and mail a bill (invoice), the billing clerk needs access to the following data stored in the data base:- customer name- customer number- billing address- item numbers- quantity sold- price- terms To manage inventory, the inventory control department needs access to the following data stored in the data base:- item number- description,- quantity on hand. To purchase inventory, the purchasing department needs access to the following data stored in the data base:Accounting Information Systems- item number- description- quantity on hand- cost.P4.2a.DDL - this is the language used to define the database.bDQL - this is an example of a query.cDML - this is the language used to actually process transaction data and update the database.d.DQL - another example of a task that involves querying the database.e.DDL and DML - the former to alter the structure, the latter to make the change.f.DQL - such a listing can be produced by a query.g.DDL and DML - the former to add the field, the latter to enter data in it.P4.3Below is a set of tables that answers parts a, b, and c. There is no solution to parts d through f as students will select different software packages and come up with different queries.Table NamePrimary KeyForeign KeysOther AttributesInventoryItem NumberDescriptionQuantity on HandPurchasesPurchase order numberVendor numberPurchasing Agent (employee number)Date of purchaseTotal amount of purchasePurchases-InventoryItem numberPurchase order numberQuantity purchasedUnit cost (actual)Extended amountVendorVendor numberVendor nameVendor addressEmployees Employee number Employee nameNote: Extended amount and Total amount of purchase do not have to be stored in the data base as they can be calculated from other values. Extended amount is Quantity purchased x Unit cost. Total amount of purchase is the sum of all the extended amounts for all items on a particular purchase order.4.6The necessary tables, with their attendant primary and foreign keys, are as follows:Table NamePrimary KeyForeign KeysOther AttributesEmployeeEmployee NumberSupervisor number (another employee number)Employee namePay rateDate hiredDate of birthSkillsSkill numberSkill nameEmployees-SkillsSkill numberEmployee numberDate skill acquiredThere is no solution to part c as students will select different software packages and enter different data in the tables.4.7The following additional tables, with their attendant primary keys, are needed to store the other new attributes. Note that customer name is already stored in the customer tableTable NamePrimary KeyForeign KeysOther AttributesCash ReceiptsCash Receipt NumberCustomer numberEmployee processing payment (employee number)Date of receiptTotal amount receivedCash Receipts-SalesInvoice payment applies to (Invoice number)Cash Receipt numberAmount applied to a specific invoiceThere is no solution to part c as students will select different software packages and enter different data in the tables.4.8 To avoid the update, insert, and delete anomalies in Table 4-17 (shown below), three separate relational tables (Invoice Table, Invoice-Inventory Table, and Customer Table) are created.TABLE 4-17 Invoice#DateOrderDateCustomerIDCustomerNameItem#Quantity526-19-055-25-05201Johnson1035526-19-055-25-05201Johnson1228526-19-055-25-05201Johnson1011526-19-055-25-05201Johnson7112576-20-056-01-05305Henry53518576-20-056-01-05305Henry11515576-20-056-01-05305Henry1225INVOICE TABLE Invoice# (PK)DateOrderDateCustomerID (FK)526-19-055-25-05201576-20-056-01-05305INVOICE-INVENTORY TABLE Invoice# (FK)Item# (FK)Quantity52103552122852101152711257535185711515571225CUSTOMER TABLE CustomerID(PK)CustomerName201Johnson305HenryNote: PK-Primary Key, FK Foreign Key4.9TABLE 4-18PurchaseOrder #Purchase Order DatePart #DescriptionUnitPriceQuant