Chapter 9 Page 99
CHAPTER 9 DATABASE MANAGEMENT SYSTEMS REVIEW QUESTIONS 1.
database planning, design, implementation, operation and maintenance, and change and growth.
2.
the users, the database management system, the database administrator, and the physical database structures.
3.
The primary difference between the network and hierarchical models is in the parent-child relationship. The network model allows a child file to have multiple parents. The hierarchical model permits a child to be associated with only one parent record.
4.
a.
No data redundancy
b. Single update of data c.
Current values for all user applications
d. Task-data independence.
Chapter 9 Page 100
5.
program development, backup and recovery, database usage reporting, and database access.
6.
One level is the schema, which is the conceptual view of the data. The schema describes the entire database, and it represents the database logically. The second level is the internal view which is the physical arrangement of the records. At this level, the physical data records are described as well as linkages between files. The next level is the subschema, which is the external view of the database that specific users have authorization to use. This is also called the user view. This is the level that users find of most interest.
7.
The primary key is a table attribute the value of which is unique to the record. It is a unique identifier.
8.
Logically related tables need to be physically connected to achieve the associations described in the data model. This is accomplished by embedding the primary key of one table into the related table as a foreign key.
9.
The data dictionary describes every data element in the database. The data dictionary enables all users (and programmers) to share a common view of the data resource, thus facilitating greatly the analysis of user needs.
10.
An organization has three diverse operating units: tractors, sewing machines, and computer chips. These units have different customers, suppliers, and production facilities. Operating data are consolidated for financial reporting only.
Chapter 9 Page 101
11.
An entity is anything about which the organization wishes to capture data. Entities may be physical, such as “Inventory”, “Customer”, or “Employee”. They may also be conceptual, such as “Sale” (to a customer), “Accounts Receivable”, or “Accounts Payable”.
12.
The XYZ company is a geographically distributed organization with several sites around the country. Users at these sites need rapid access to common data for read-only purposes.
13.
(1:0,1) Every occurrence (record) is for one entity (Employee), there is a possibility of zero or one occurrence in the related entity (Company Car). (1:1) Describes a situation in which each record in one entity is always associated with one (and only one) record in the associated entity. For example, a company laptop computer is assigned to only one manager, and every manager is assigned only one laptop. (1:M) An example of this situation is that each item of Inventory (entity) is supplied by one and only one Vendor (related entity), but each Vendor supplies one or many different Inventory items to the company. (M:M) A company with a policy of purchasing the same types of inventory from multiple suppliers would have a M:M association between the Vendor and Inventory entities.
Chapter 9 Page 102
14.
The term association pertains to the nature of the relationship between two entities. This is represented by a verb such as shipped, requests, or receives. Cardinality is the degree of association between two entities. Simply stated, cardinality describes the number of possible occurrences in one table that are associated with a single occurrence in a related table.
15.
In a many-to-many association, a link table with a combined (composite) key consisting of the primary keys of the two related tables is created in order to link the related tables.
16. a.
All occurrences at the intersection of a row and column are a single value. No
multiple values (repeating groups), partial dependencies, or transitive dependencies are allowed. b. The attribute values in any column must all be of the same class. c.
Each column in a given table must be uniquely named.
d. Each row in the table must be unique in at least one attribute that is considered to be the primary key. 17. a.
Restrict—Extracts rows that satisfy the given condition from a specified table
and places these rows into a new table. b. Project—Extracts columns from a specified table and places these attributes (columns) into a new table. c.
Join—Builds a new table from two tables consisting of all concatenated pairs of rows, one from each table.
18.
A table normalized to 3NF meets the following conditions:
1. All nonkey attributes in the table are dependent on the primary key.
Chapter 9 Page 103
2. All nonkey attributes are independent of the other nonkey attributes. In other words, the primary key of a table wholly and uniquely defines each attribute in the table, and none of the table attributes are defined by an attribute other than the primary key.
Chapter 9 Page 104
19.
The user may restrict the fields of data to view with the SELECT command. Further, the user may restrict the rows or records of data to be viewed with the WHERE command. The WHERE command allows the user to view only those records that have values which fall within a certain range for one or more fields of data.
20.
A data model is the blueprint for creating the physical database. Database designers identify system entities and prepare a model of them using a graphical representation technique called an entity relationship (ER) diagram.
21.
The deletion anomaly may cause records to be deleted unintentionally and may occur for some time before the problem is noticed. A deletion anomaly occurs when an item in one file is legitimately deleted. The problem occurs when this file is linked to another file, which may also have a record deleted, due to its link. If the second record should not be deleted, then an update anomaly has occurred.
22.
A user view is the set of data that a particular user sees. Examples of user views are computer screens for entering or viewing data, management reports, or source documents, such as an invoice.
23.
User views derive from underlying database tables. Simple views may be constructed from a single table, while more complex views may require several tables. Furthermore, a single table may contribute data to many different views.
24.
Valid entities meet the two conditions below: Condition 1.
An entity must have two or more occurrences.
Condition 2.
An entity must contribute at least one attribute that is not provided through other entities.
Chapter 9 Page 105
25.
Because attributes are the logical and relevant characteristics of an entity, they are unique to it. Therefore, the same attributes should not be used to define two different entities.
DISCUSSION QUESTIONS
Chapter 9 Page 106
1.
In the traditional data management environment, applications are developed with data and program dependency. Typically, these programs are application specific. Thus, the users of the application data tend to be proprietary about the data in “their” application and may not be amenable to sharing such data.
2.
If your university were to use different databases for the registrar, library, parking, food services, and computing services, then the number of forms you would have to fill out, if any of your personal data changes, would be plentiful. For example, if you were to move during the semester to a different apartment, the university should be notified. In this situation, a couple of things could happen. You could be required to go to each service individually and fill out an address form, or you could go to one central location and fill out a form that has multiple copies, which are sent to the various areas on campus for update. In any case, your address could be keyed in correctly by the registrar. You might receive some correspondence from the registrar and assume that the address correction was made. However, a keypunch error might have occurred by the library staff, and you may not receive notification that you have a library book which you forgot about past due. After the end of the semester, you may not receive your final grade report. When you call the registrar, you may find out that the library has reported that you have an overdue book and that your grades should be held until the book is returned, and the fine is paid.
Chapter 9 Page 107
3.
Under the database concept, the data becomes centrally stored with many different users accessing the database. However, each user should not have access to the whole database. Under the traditional data management approach, where the data are limited to a single application, the user access problem was not as much of a threat. The DBMS is a special software system that is programmed to know which data each user is authorized to access. This controlled authorization is crucial in centrally stored DBMSs.
4.
User views are derived database tables. A single table may contribute data to several different views. On the other hand, simple views may be constructed from a single table.
5.
Tables that are logically related in the data model need to be physically linked. The degree of association between the tables (i.e., 1:1, 1:M, or M:M) determines how the linking occurs. The key-assignment rules for linking tables are discussed below:
Where a true 1:1 association exists between tables, either (or both) primary keys may be embedded as foreign keys in the related table.
Where a 1:M (or 1:0,M) association exists, the primary key of the 1 side is embedded in the table of the M-side.
To represent the M:M association between tables, a link table needs to be created which has a combined (composite) key consisting of the primary keys of two related tables.
Chapter 9 Page 108
6.
The entity relationship (ER) diagram is the graphical representation technique used to depict a data model. Each entity in an ER diagram is named in the singular noun form, such as Customer rather than Customers. The labeled line connecting two entities describes the nature of the association between them. This association is represented with a verb, such as shipped, requests, or receives. The ER diagram also represents cardinality (the degree of association between two entities). Four basic forms of cardinality are possible: zero or one (0,1), one and only one (1,1), zero or many (0,M), and one or many (1,M). These are combined to represent logical associations between entities such as 1:1, 1:0,M, and M:M.
7.
SQL allows users to retrieve data from many different files without the assistance of programmer professionals. Thus, if the user has access to data files and knows SQL, which is very user friendly, the user may retrieve the data instantaneously.
8.
The data was not centrally stored for many different applications to use in the traditional data management environment; therefore, a database administrator was not needed. Because it is centrally stored and shared by many users in a database environment, the need arose for an individual to care for and control these files. The database administrator is responsible for database planning, developing the data requirements and data dictionary, database design and controls, database implementation and access controls, operation and maintenance, and establishing and reviewing the standards and procedures.
Chapter 9 Page 109
9.
The system programmer’s program should not have access to the data, except perhaps temporarily to test the programs. The database administrator controls the access to the data. If one person has the authority to both write programs and access data, then control issues become a concern. The potential to commit fraud or embezzlement or to destroy or alter the company’s records increases.
10.
Neither table can donate an embedded key to the other, because both are on the “many” side. The only solution, therefore, is to create a new link table containing the key fields of both tables.
11.
Tables that are not normalized contain anomalies which require excessive updates to tables, prevent data from being stored properly, and may cause unintentional deletion of data. Accountants need to be familiar with normalization issues, because these anomalies threaten the integrity of the financial data of the organization.
12.
A database lockout prevents multiple users from accessing the same table simultaneously and making changes to data values while they are temporarily inconsistent. Lockouts force changes to be made sequentially to ensure data accuracy.
13.
Database concurrency controls ensure the completeness and accuracy of a distributed database at remote sites where the same beginning data balances are updated by different transactions. This is accomplished by serializing and timestamping transactions. Depending on the need for data currency, the time-stamped data will be reconciled and posted to all distributed databases.
Chapter 9 Page 110
14.
Database accounting systems are transaction-based rather than account-based. The focus is on capturing important details of transactions that may be lost when they are forced into the structure of traditional accounting records. The transaction tables are then to be used to reconstruct traditional accounting records, such as Accounts Receivable and Accounts Payable.
15.
Where a true 1:1 association exists between tables, either (or both) primary keys may be embedded as foreign keys in the related table. On the other hand, when the lower cardinality value is zero (1:0,1), a more efficient table structure can be achieved by placing the 1-side (1:) table’s primary key in the zero-or-one (:0,1) table as a foreign key. Assume that a company has 1000 employees, but only 100 of them are sales staff. Also assume that each sales person is assigned a company car. Therefore, every occurrence in the Employee entity is associated with either zero or one occurrence in the Company Car entity. If we assigned the Company Car (:0,1) side primary to the Employee (:1) table as a foreign key, then most of the foreign keys will have null (blank) values. While this approach would work, it could cause some technical problems during table searches. Correctly applying the keyassignment rule solves this problem, because all Company Car records will have an employee assigned, and no null values will occur.
Chapter 9 Page 111
16.
The insertion and update anomalies would create record keeping and operational problems for the firm. However, flawed databases design that prevents the insertion of records, or requires the user to perform excessive updates, would attract attention quickly. The presence of the deletion anomaly is less conspicuous, but potentially more serious from an accounting perspective. Because the deletion anomaly may go undetected, the user may be unaware of the loss of important data until it is too late. This anomaly can result in the unintentional loss of critical accounting records and the destruction of the audit trail.
17.
The organization’s business rules directly impact the structure of the database tables. If the database is to function properly, its designers need to understand the organization’s business rules, as well as the specific needs of individual users. For example: 1. When an organization decides to purchase the same items of inventory from different suppliers, the cardinality between the Supplier and Inventory tables is M:M. 2. When a the company purchases all items of a certain type from only one supplier, the cardinality between Supplier and Inventory tables is 1:M respectively. 3. A policy that a separate receiving report is prepared for the receipt of goods specified on a single purchase order will result in a 1:1 cardinality between the receiving report and purchase order tables. If, however, multiple purchase orders are combined on a single receiving report, then the cardinality between these tables will be 1: M respectively.
Chapter 9 Page 112
18.
The partitioned approach works best for organizations that require minimal data sharing among users at remote sites. To the extent that remote users share common data, the problems associated with the centralized approach will apply. The primary user must now manage requests for data from other sites. Selecting the optimum host location for the partitions, to minimize data access problems, requires an indepth analysis of end-user data needs.
19.
To achieve data currency, simultaneous access to individual data elements or records by multiple users needs to be prevented. The solution to this problem is a database lockout, which is a software control that prevents multiple simultaneous accesses to data. A deadlock occurs when multiple users seeking access to the same set of records lockout each other. As a result, the transactions of all users assume a “wait” state until the locks are removed. A deadlock is a permanent condition that must be resolved by special software that analyzes each deadlock condition to determine the best solution.
Chapter 9 Page 113
20.
The primary justification for a replicated database is to support read-only queries in situations involving a high degree of data sharing, but no primary user exists. With data replicated at every site, data access for query purposes is ensured, and lockouts and delays due to network traffic are minimized. A potential problem arises, however, when replicated databases need to be updated by transactions. Since each site processes only local transactions, the common data attributes that are replicated at each site will be updated by different transactions and thus, at any point in time, will have uniquely different values. System designers need to employ currency control techniques to ensure that transactions processed at different locations are accurately reflected in all the database copies.
MULTIPLE CHOICES 1.
D
2.
C
3.
D
4.
E
5.
D
6.
D
7.
A
8.
C
9.
B
10.
C
11.
D
12.
C
Chapter 9 Page 114
13.
D
14.
C
15.
A
16.
D
17.
A
18.
D
PROBLEMS 1.
August 23, 199X MEMO TO:
Al Brindifi, VP Operations Carla Glasser, VP Finance James Closter, VP Marketing Julia Tinner, Controller Beth Clark, Manager of Information Systems
FROM:
Solutions Consultants
SUBJ:
Conversion of data processing systems After examining the operations of your organization and deriving a rough-cut estimate of your needs, this consulting team feels that your organization would benefit greatly from a database management system. A database management system will allow data to be shared amongst the departments, thus facilitating communication. One of the problems that has been brought repeatedly to our attention, is the need for information by user groups, which is currently kept by the
Chapter 9 Page 115
organization. These user groups are having trouble gaining access to the data, because it does not reside in that user groups’ application. A database management system will provide a central data source, whereby each authorized user group may gain access to the data it needs. Further, we see many inefficiencies in processing data due to data duplication by various applications. An even greater problem is that the data is oftentimes not consistent from one application to another. The DBMS will reduce data duplication and data redundancy. We propose that you begin a conversion to a DBMS. This conversion will require that a database administrator be appointed or hired. A DBMS requires that the central data store be diligently
planned,
managed,
and
maintained. The
database
administrator would perform these functions, as well as control user access to the data. 2.
a.
indexed sequential or indexed random; least optimal: sequential
b. indexed random or hashing; least optimal: sequential c.
sequential; least optimal: indexed random
d. sequential or pointer; least optimal: indexed random e. indexed random or hashing; least optimal: sequential f.
indexed random or hashing; least optimal: sequential
g. indexed random; least optimal: hashing
Chapter 9 Page 116
3.
Supplier Table Supplier ID Supplier
Supplier
(PK)
Address
Name
Terms
Balance
Supplier Phone #
Supplier/DVD Table Supplier ID DVD # (PK)
(PK)
DVD Table DVD #
DVD
DVD
Year of
(PK)
Name
Genre
Release
Address
Credit
Copy Table Copy ID
DVD #
(PK)
(FK)
Customer Table Customer
Name of
Phone
Primary
#(PK)
Customer
Household Member Table
Card #
Cost
Rental Time
Chapter 9 Page 117
4.
Member #
Customer
Name of
(PK)
Phone #
Household
(FK)
Member
Age
Restrictions
Mail order catalogues are beginning to be customized based upon the preferences and purchasing patterns of the customers. If data is stored regarding the customer preferences and their buying patterns, then catalogues containing only the items which are believed to be of value to the customer may be contained in the catalogue. Printing costs and postage costs would be saved. Also, more items could be included which fit the pattern of the customer. Further, the customer does not have to weed through the catalogue to find the items that he or she likes. The technology is changing such that customized or semi-customized (for groups of people) catalogues are a reality. Listed below are some files and some fields in the files which would be necessary in addition to the traditional accounts receivable, cash receipts, vendor, and general ledger files.
Customer Master File Customer Number Name
Sales File Customer Number Sales Order
Inventory Master File Item Number Item
Inventory Type File Product Type Product
Number $ Amount of Items Taxes
Description Product Type Quantity on
Classification
Address Phone Credit Card Type Credit Card Number Credit Card Expiration Last Purchase Date Cumulative Purchase Amount Purchases in Last 12 months Purchases in Last 3 months
Freight Total Amount
Sales Order Detail File
Hand Selling Price
Chapter 9 Page 118 $ Purchases of Type A $ Purchases of Type B $ Purchases of Type C $ Purchases of Type D Request Type A catalogue Request Type B catalogue Request Type C catalogue Request Type D catalogue
5.
Sales Order Number Item Number Quantity Discount
Lockout. The example below illustrates how two different transactions are being processed against the same AR control account within the same time frame. The individual logic steps are shown in their actual sequence of execution. If no database lockout were in place, the AR Control value of 20,000 is retrieved by both users of the system. One user is posting a payment to Sub Account 1 of $500 and the other is posting a payment to Sub Account 2 for $800.
AR Control Time IPU Instruction Sub Acct 1 Sub Account 2 Account 1:00:001 A Read sub acct 1 1,000 1:00:001 B Read sub acct 2 3,000 1:00:002 A Update sub acct 1 500 1:00:002 B Update sub acct 2 2,200 1:00:003 A Read control acct 20,000 1:00:003 B Read control acct 20,000 1:00:004 A Update control acct 19,500 1:00:005 B Update control acct 19,200 The update process does not reflect the second to last instruction executed. The AP Control Account should reflect payments received of $1,300, but only $800 of payments are accounted for. Thus, a transaction is lost, and the control and subsidiary ledgers are out of balance. Deadlock. A deadlock occurs when multiple sites lock out each other. Take for example, a mail order company in which two customers are
Chapter 9 Page 119
processing transactions simultaneously. Customer 1 wants to order 2 items—Item A and Item B. Customer 2 is purchasing Item B and Item A. Customer 1 informs the phone clerk that he or she wants Item A, and the record for Item A is locked until the order is complete. Meanwhile, Customer 2 orders Item B from another phone clerk who locks it. Customer 1 then requests Item B, which is locked by Customer 2’s order. The phone clerk apologizes for the delay and says the system is slow today. Customer 2 then requests Item A, which is locked by Customer 1’s order. The phone clerk helping Customer 2 apologizes for the delay and says the system is slow today. Unfortunately, neither transaction can be completed, resulting in deadlock. This condition will not be resolved unless some type of intervention occurs. 6.
SELECT Customer Name, Inventory Number, Item Description, Due Date FROM Customer Table, Rental Line Items Table WHERE Due Date < Today
7.
The best distributed approach is a replicated database. Reasoning
The users are distributed around the country and need rapid access to data. A centralized model may result in long delays because of network traffic and database lockout.
User data needs are common not unique. Since there are no identifiable primary
Chapter 9 Page 120
users, partitioning the database will accomplish nothing.
Because usage is read-only, changes will not occur and database concurrency is not a problem.
8.
The best distributed approach is a partitioned database. Reasoning
The users are distributed around the country and need rapid access to data. A centralized model may result in long delays because of network traffic and database lockout.
User data needs are unique with identifiable primary users. There is no need to replicate the entire database.
Since users are unique, changes to the database will not cause database concurrency problem.
9.
The first step is to remove repeating groups. The removal of repeating groups results in the following two tables. Due Date has been removed because it is a calculated field. Student ID Number is the primary key for the Student table. Student Id Number and Book Call No. together are the primary key for the Library table. Student table is now in 3NF. Library table requires further normalization. Student Table Student ID
Student First
Student Last
No. of Books
Number
Name
Name
Out
Book Call No.
Book Title
Date Out
Library Table Student ID
Chapter 9 Page 121
Number The second step is to remove partial dependencies from Library table. Book Title is partially dependent on the Book Call No. component of the primary key. This assumes that the call number is unique to the book, and not to the event of borrowing the book. It also assumes that more than one copy of the book may exist in the library and that all copies have the same call number. Removing the partial dependency results in the tables below: Transaction Table Student ID
Book Call No.
Date Out
Number Book Table Book Call No.
Book Title
Book Call No. is the primary key for the new table Book. Since no transitive dependencies exist, all three tables (Student, Transaction, and Book) are now in 3NF. 10.
Table Textbook ISBN #
Title
(PK)
Publisher (FK)
Table Publisher Publisher (PK) Table Class
Address
Phone #
Cost
Selling
Quantity
Price
on Hand
Chapter 9 Page 122
Course #
Course
Professor
(PK)
Name
(Employee
Days
Room #
(FK)
Number) (FK) Table Semester Term
Year
Semester
Name
Address
#(PK) Table Professor Employee # (PK)
Term #
Phone
Title
Chapter 9 Page 123
11.
See diagram below. PK
Inventory Table
FK
Chapter 9 Page 124
Part # Description QOH Reorder Point EOQ Unit Cost Vendor #
Chapter 9 Page 125
PK
Vendor Table
Chapter 9 Page 126
Vendor # Vendor Name Vendor Address
Telephone
Chapter 9 Page 127
12. See diagram below. PK
Inventory Table
Chapter 9 Page 128
Part # Description QOH Reorder Point EOQ
Part # Vendor # Unit cost
Vendor # Vendor Name Vendor Address
Telephone
Note: This is an example of a many-to-many relation between the inventory and vendor tables. The solution requires a link table, which also contains Unit Cost data. A composite key of Part # and Vendor # is needed to define the Unit Cost attribute, because there are many prices for each item carried, depending on which vendor supplies the part.
13. See diagram below. Customer Table Customer # Customer Name Address * Customer Total Invoice Table Invoice # Date *Invoice Total
Customer #
Line Item Table Invoice # Part # Quantity *Extended Price Inventory Table Part Number Unit Price * Could be a calculated field 14.
Table Employee Employee #
Name
Address
Date Hired
Exemptions Marital
Chapter 9 Page 129
(PK) Table PO PO # (PK)
Status Date
Employee # Vendor # (FK) (FK)
Table PO/Item PO # (PK) Item # (PK) Quantity
Table Item Item # (PK)
Description On Hand
Table Vendor Vendor # (PK) Name
Address
Cost
Price
Location
Contact
Terms
Balance
Chapter 9 Page 130
15.
16.
Defining Entities and Data Modeling - Payroll
Response:
Chapter 9 Page 131
1 and 2
Rejected Entities Payroll clerk
(a)Reason
cash disbursements clerk
Violates rule 1 and 2 – Wording suggests only one clerk (rule 1) and no evidence of attributes unique to this entity (rule 2) Violates Rule 1 – the company is a single occurrence Violates rule 1 and 2
supervisor check for the total payroll paycheck payroll summary
Violates rule 1 and 2 This is a view - Violates rule 2 This is a view - Violates rule 2 This is a view - Violates rule 2
Sagerod manufacturing company
Valid entities Employee Paycheck register Cash disbursement journal Time card Employee earnings File
Chapter 9 Page 132
17.
a. and b. Rejected Entities Accounts Payable
Reason Violates rule 1 and 2––Wording suggests only one
Department Clerk
clerk (rule 1) and no evidence of attributes unique to
Safe Buy Grocery
this entity (rule 2) Violates Rule 1––The company is a single
Stores Receiving Report
occurrence This is a view––It derives entirely from receiving
Summary Part-Time Employees
report and thus violates rule 2 Violates rule 2––Assumption: no employee specific
Chapter 9 Page 133
Unloading Personnel
data need to be captured by this system Violates rule 2––Assumption: no employee specific
Sales
data need to be captured by this system Violates rule 2––Assumption: The company does not
Representatives Obsolescence
capture Sales Rep data unique to each transaction. This is a view––Derived from inventory records
Reports Invoice (physical)
Violates rule 2 This is a view––Used to create Invoice Record
Check (physical)
Violates rule 2 This is a view––Derived from Check register records.
Payment Summary
Violates rule 2 This is a view––Derived from Check register records
Purchase Requisition
Violates rule 2 This is a view––Used to create Purchase Order
Customers
Violates rule 2 Not relevant to this system
Valid Entities Purchase Manager
Reason Assumption: all store purchase managers will use the system. This entity will consist of multiple occurrences and provide manager/store-specific
Supplier Inventory Purchase Order Receiving Report Invoice (record) Payment Obligation Check (register)
data not contained in other entities. Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2
Chapter 9 Page 134
Chapter 9 Page 135
18.
Solution a. and b. Rejected Entities Accounts Payable
Reason Violates rule 1 and 2—Wording suggests only one
Department Clerk
clerk (rule 1) and no evidence of attributes unique to
Safe Buy Grocery
this entity (rule 2) Violates Rule 1—the company is a single occurrence
Stores FA Receiving Report
This is a view—It derives entirely from receiving
Summary
report and thus violates rule 2
Chapter 9 Page 136
Receiving Clerk
Violates rule 2—Assumption: no employee specific
Accounts Payable
data need to be captured by this system Violates rule 2—Assumption: no employee specific
Manager Depreciation
data need to be captured by this system Assumption: This is a view—Derived from data in
Schedule Invoice (physical)
inventory records Violates rule 2 This is a view—Used to create Invoice Record
Check (physical)
Violates rule 2 This is a view—Derived from Check register records.
Payment Summary
Violates rule 2 This is a view—Derived from Check register records.
Purchase Requisition
Violates rule 2 This is a view—Used to create Purchase Order.
Store Manager
Violates rule 2 Assumption: manager/store-specific data will be contained in other entities i.e. Purchase Order
Valid Entities Supplier Fixed Assets Inventory Purchase Order Receiving Report Invoice (record) Payment Obligation Check (register)
Reason Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2
Chapter 9 Page 137
Chapter 9 Page 138
Chapter 9 Page 139
19. Response: 1 and 2
Rejected Entities Lotus Tea Importer Company and all departments such as Sales, warehouse, shipping department, general ledger department, etc. Various clerks such as Sales representative, accounting department clerk, AR Clerk, etc. stock release document Mail room employees packing slip. Invoice (physical) Customer Check (physical) inventory account summary remittance advices AR account summery price list
(a)Reason Violates Rule 1 – the company and these departments are single occurrences
Violates rule 1 and 2 – Wording suggests only one clerk (rule 1) and no evidence of attributes unique to this entity (rule 2) This is a view – It derives entirely from sales order and thus violates rule 2 Violates rule 2 – Assumption: no employee specific data need to be captured by this system This is a view – Derived from sales order. Violates rule 2 This is a view – derived from sales order Violates rule 2 This is a view – used to create record in cash receipts journal Violates rule 2 This is a view – Derived from inventory records. Violates rule 2 This is a view that is derived from the sales order and sent to the customer to facilitate posting payments to the correct customer account This a view – derived from the AR subsidiary ledger or the total of all unpaid sales orders Given the limited information in the problem, this entity may be represented as either a separate table or more simply as a field in the inventory record. We assume the latter in this solution.
Chapter 9 Page 140
Valid entities Customer Sales order inventory (product) Cash receipts record (CR journal) bill of lading carrier deposit slip Shipping Notice
Reason Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2
The ER diagrams in the student solutions probably contain the accounting-record entities in the table below. Technically, however, these entities are not necessary in a modern database system. This issue is an aspect of REA modeling that is examined in detail in chapter 10 of the text. The table below illustrates how each of these accounting records can be derived from other transactional database tables. The solutions to parts C and D of this problem do not include these unnecessary entities Unnecessary Accounting Records
May be derived as follows
Sales Journal
This is equivalent to sales order records
AR Subsidiary ledger
cost of goods sold account (GL)
This is the sum of all sales order records organized by customer that are still open (unpaid) at period end. This is the sum total of all sales order records that are still open (unpaid) at the period end. Calculated as the quantity sold (sales Order) X the cost of the item taken from the inventory record
inventory control (GL)
Sum of all inventory records
sales account (GL) journal voucher
This is sum total sales order records This is the sum of the transaction detail captured by the cash receipts records and/or the c
AR control accounts (GL)
Chapter 9 Page 141
Chapter 9 Page 142
Chapter 9 Page 143
20
Chapter 9 Page 144
21.
Chapter 9 Page 145
22.
Solution: a. and b. Rejected Entities Sales Representative
Reason Assumption: Sales representative specific data will
D&F Music Club
be contained in customer record. Violates rule 2 Violates Rule 1—the company is a single
John, Billing Clerk
occurrence Violates rule 2—Assumption: no employee specific
Chris, Warehouse
data need to be captured by this system Violates rule 2—Assumption: no employee specific
Clerk Sandy, AR Clerk
data need to be captured by this system Violates rule 2—Assumption: no employee specific
Scott, Mailroom Clerk
data need to be captured by this system Violates rule 2—Assumption: no employee specific
Customer Invoice
data need to be captured by this system This is a view—created from Customer Invoice
(physical) Customer Check
record. Violates rule 2 This is a view—Derived from Check register
(physical) Remittance Advise
records. Violates rule 2 This is a view—Derived from Customer Invoice
Remittance List
record at time of billing. Violates rule 2 This is a view—Used to create Cash Receipt
Laura, Cash Receipts
record. Violates rule 2 Violates rule 2—Assumption: no employee specific
Clerk Deposit Slip
data need to be captured by this system. This is a view—Created from Cash Receipt record.
Warehouse Manager
Violates rule 2 Violates rule 2—Assumption: no employee specific
Demand Forecast Purchase Requisition
data need to be captured by this system. View derived from marketing system Assumption: This is a View derived from Inventory records.
Chapter 9 Page 146
Rejected Entities Sara, Purchasing
Reason Violates rule 2—Assumption: no employee specific
Clerk PO Blind Copy
data need to be captured by this system. This is a view—derived from Purchase Order
Cash Disbursement
record. Violates rule 2 Violates rule 2—Assumption: no employee specific
Clerk Payment Check
data need to be captured by this system. This is a view—Created from Check Register
(Physical) Packing Slip
record. Violates rule 2 This is a view—Created from Sales Order record. Violates rule 2
Chapter 9 Page 147
Valid entities Customer Sales Order Sales Journal Inventory Sub Ledger Bill of Lading General Ledger Cash Receipts Journal Return Record Purchase Order Supplier Account Payable Receiving Report Supplier Invoice
Reason Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2 Meets conditions of Rules 1 and 2
(record) Check (register)
Meets conditions of Rules 1 and 2
Chapter 9 Page 148
22, part c.
22, part c.
Data Model of Expenditure Procedures
Data Model for Revenue Cycle Procedures
Chapter 9 Page 149
22, part d.
Fully Attributed Model Expenditure Procedures
Chapter 9 Page 150
22 part d.
Fully Attributed Model Revenue Cycle Procedures
22, part e. DFD of Revenue Cycle Procedures
Chapter 9 Page 151
Chapter 9 Page 152
22, part e. DFD for Expenditure Cycle Procedures
