Chapter 4 Solutions(1)

Ch. 4: Relational Databases Accounting Information Systems Chapter Solutions Chapter 4 Q4.1 The 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.1 a. 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 · 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: · item number · description · quantity on hand · cost. P4.2 a. DDL - this is the language used to define the database. b DQL - this is an example of a query. c DML - 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.3 Below 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 Name Primary Key Foreign Keys Other Attributes Inventory Item Number Description Quantity on Hand Purchases Purchase order number Vendor number Purchasing Agent (employee number) Date of purchase Total amount of purchase Purchases-Inventory Item number Purchase order number Quantity purchased Unit cost (actual) Extended amount Vendor Vendor number Vendor name Vendor address Employees Employee number Employee name Note: 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.6 The necessary tables, with their attendant primary and foreign keys, are as follows: Table Name Primary Key Foreign Keys Other Attributes Employee Employee Number Supervisor number (another employee number) Employee name Pay rate Date hired Date of birth Skills Skill number Skill name Employees-Skills Skill number Employee number Date skill acquired There is no solution to part c as students will select different software packages and enter different data in the tables. 4.7 The 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 table Table Name Primary Key Foreign Keys Other Attributes Cash Receipts Cash Receipt Number Customer number Employee processing payment (employee number) Date of receipt Total amount received Cash Receipts-Sales Invoice payment applies to (Invoice number) Cash Receipt number Amount applied to a specific invoice There 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# Date OrderDate CustomerID CustomerName Item# Quantity 52 6-19-05 5-25-05 201 Johnson 103 5 52 6-19-05 5-25-05 201 Johnson 122 8 52 6-19-05 5-25-05 201 Johnson 10 11 52 6-19-05 5-25-05 201 Johnson 71 12 57 6-20-05 6-01-05 305 Henry 535 18 57 6-20-05 6-01-05 305 Henry 115 15 57 6-20-05 6-01-05 305 Henry 122 5 INVOICE TABLE Invoice# (PK) Date OrderDate CustomerID (FK) 52 6-19-05 5-25-05 201 57 6-20-05 6-01-05 305 INVOICE-INVENTORY TABLE Invoice# (FK) Item# (FK) Quantity 52 103 5 52 122 8 52 10 11 52 71 12 57 535 18 57 115 15 57 122 5 CUSTOMER TABLE CustomerID (PK) CustomerName 201 Johnson 305 Henry Note: PK-Primary Key, FK – Foreign Key 4.9 TABLE 4-18 Purchase Order # Purchase Order Date Part # Description Unit Price Quantity Ordered Vendor # Vendor Name Vendor Address 2 3/9/05 334 XYZ $30 3 504 KL Supply 75 Stevens Dr. 2 3/9/05 231 PDQ $50 5 504 KL Supply 75 Stevens Dr. 2 3/9/05 444 YYM $80 6 504 KL Supply 75 Stevens Dr. 3 4/5/05 231 PDQ $50 2 889 Oscan Inc 55 Cougar Cir. PART TABLE Part #(PK) Description Unit Price 334 XYZ 30 231 PDQ 50 444 YYM 80 PURCHASE ORDER TABLE Purchase Order # (PK) Vendor #(FK) Purchase Order Date 2 504 3/9/05 3 889 4/5/05 VENDOR TABLE Vendor #(PK) Vendor Name Vendor Address 504 KL Supply 75 Stevens Dr. 889 Oscan Inc. 55 Cougar Cir. PURCHASE-PART TABLE Purchase Order # (FK) Part # (FK) Quantity Ordered 2 334 3 2 231 5 2 444 6 3 231 2 Note: PK-Primary Key, FK – Foreign Key 4-2 4-1