Rais11_ch16 - C Implementing an REA Model in a Relational...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: C Implementing an REA Model in a Relational Database uestions to be addressed in this chapter: How are REA diagrams for individual transaction cycles integrated into a single comprehensive organization-wide REA diagram? How are tables constructed from the REA model of an AIS in a relational database? n the previous chapter, you learned how to develop an REA diagram for an individual transaction cycle. his chapter demonstrates how to implement an REA diagram in a database. e focus on relational databases because: They are commonly used to support transaction processing systems. They are familiar to most business students. n Chapter 15, we looked at REA diagrams for the revenue and expenditure cycles. efore we integrate these diagrams with the payroll cycle, let’s take a look at the HR/payroll cycle activities. Employee (Supervisor) Time Worked Employee Time Employees Employee (Payroll Clerk) Disburse Cash Cash • Employee (Supervisor) The basic economic exchange: – Get employee time and skills – Give a paycheck Employee Time Time Worked Employees Employee (Payroll Clerk) Disburse Cash Cash • The time worked event must be linked to a particular employee and supervisor for a (1,1) cardinality. Employee (Supervisor) Time Worked Employee Time Employees Employee (Payroll Clerk) Disburse Cash Cash • Employee (Supervisor) However, each agent can be linked to zero or many time worked events. The zero minimum allows for inclusion of a new employee or supervisor who has not yet been involved in a time recording. Employee Time Time Worked Employees Employee (Payroll Clerk) Disburse Cash Cash • A similar situation exists with the disburse cash event. (We regard each individual paycheck as a separate cash disbursement.) Employee (Supervisor) Time Worked Employee Time Employees Employee (Payroll Clerk) Disburse Cash Cash • • The assumption is made that employees record time worked on a daily basis. Time worked is therefore linked to a maximum of one cash disbursement, since employees aren’t paid for half a day on one paycheck and the other half of the day on another check. Employee (Supervisor) Time Worked Employee Time Employees Employee (Payroll Clerk) Disburse Cash Cash • • Employee (Supervisor) For each cash disbursement, however, there are one-to-many time worked events. In other words, a paycheck could pay an employee for anywhere from one day’s work to many. Employee Time Time Worked Employees Employee (Payroll Clerk) Disburse Cash Cash The employee time entity requires some explanation. The resource being acquired by the time worked event is the use of an employee’s skills and knowledge for a particular period of time. Employee (Supervisor) Time Worked Employee Time • • Employees Employee (Payroll Clerk) Disburse Cash Cash • • Time is different from inventory and other assets in that it cannot be Employee stored. (Supervisor) There are only a few relevant attributes about employee time: – Hours worked – How the time was used Employees Time Worked Employee Time Employee (Payroll Clerk) Disburse Cash Cash • • The time worked and disburse cash events capture all the information about employee time that it is practical to collect and monitor. Employee Consequently, the (Supervisor) employee time resource entity is almost never implemented in an actual database, which is why it is depicted with dotted lines.mployees E Time Worked Employee Time Employee (Payroll Clerk) Disburse Cash Cash • In the relationship between cash disbursement and the cash resource: – This relationship is identical to the expenditure cycle. – Each check or EFT must be linked to at least one cash account (and usually only one), leading to a (1:1) cardinality. – Each cash account can be linked to: Employee • As few as (Supervisor) zero cash disbursements (e.g., a new account). • And up to many. Employee • Means a (0,N) cardinality. Time Worked Time Employees Employee (Payroll Clerk) Disburse Cash Cash Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) In this integrated diagram, we see three separate cycles. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time Employees (Supervisor) • The revenue cycle appears in yellow. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The expenditure cycle appears in blue. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time Employees (Supervisor) • The payroll cycle appears in pink. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The integrated diagram merges multiple copies of resource and event entities but retains multiple copies of agent entities. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) Inventory has been shown in green here, because it is increased by the expenditure cycle and decreased by the revenue cycle. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) Cash is increased by the revenue cycle and decreased by both the expenditure and payroll cycles. • Our integrated diagram shows the disburse cash event Call on (shown in purple) is linked to both receive inventoryustomer C (in the expenditure cycle) and time worked (from payroll cycle). Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time Employees (Supervisor) Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time Employees (Supervisor) • Cardinalities between inventory and each of the five events to which it is related are the same as before. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time Employees (Supervisor) • Cardinality between the cash disbursement event and other events with which it is linked are different. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The cardinality between disburse cash and receive inventory is now (0,N) instead of (1,N) as it was in the expenditure cycle. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The cardinality between disburse cash and record hours worked is now (0,N) instead of (1,N) as it was in the payroll cycle. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The cardinality between disburse cash and suppliers is now (0,N) instead of (1,N) as it was in the expenditure cycle. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The cardinality between disburse cash and employees (payees) is now (0,N) instead of (1,N) as it was in the payroll cycle. nce an REA diagram has been developed, it can be used to design a well-structured relational database. reating a set of tables from an REA diagram automatically results in a wellstructured relational database. he three steps to implementing an REA diagram in a relational database are: Create a table for: Assign attributes to appropriate tables. Use foreign keys to implement one-to-one and one-tomany relationships. Each distinct entity in the diagram. Each many-to-many relationship. s discussed previously, REA diagrams will differ across organizations because of differences in business policies. he three steps to implementing an REA diagram in a relational database are: Create a table for: Assign attributes to appropriate tables. Use foreign keys to implement one-to-one and one-tomany relationships. Each distinct entity in the diagram. Each many-to-many relationship. s discussed previously, REA diagrams will differ across organizations because of differences in business policies. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) Our integrated diagram has eight event entities. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time Employees (Supervisor) • • There are three distinct agent entities. The first is the customer. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The second agent entity is the supplier. Call on Customer Suppliers Inventory Take Cust. Order Order Inventory Employees (Salesperson) Customer Employees Employees (Salesperson) Suppliers Receive Inventory Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time • Employees (Supervisor) The third agent entity is the employee. We label the types of employees to make the diagram more understandable, but they all go in one table. otal entities to be represented in separate tables: Events Resources Agents 8 2 3 13 he three steps to implementing an REA diagram in a relational database are: Create a table for: Assign attributes to appropriate tables. Use foreign keys to implement one-to-one and one-tomany relationships. Each distinct entity in the diagram. Each many-to-many relationship. s discussed previously, REA diagrams will differ across organizations because of differences in business policies. Call on Customer 4 Employees (Salesperson) Suppliers 2 Take Cust. Order Inventory 5 Customer Employees Order Inventory 3 6 Employees (Salesperson) Suppliers Receive Inventory 1 Sales 7 Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) Employees (as Payees) Time Worked Employee Time Employees (Supervisor) • Let’s count the many-to-many relationships. otal number of tables in database: Events Resources Agents Plus: Many-to-Many Relationships 8 2 3 13 7 20 able names for these 20 entities correspond to the names of the entities in the REA diagram. The tables for M:N relationships are hyphenated concatenations of the entities involved in the relationship. Makes it easier: To verify that all necessary tables have been created. To use the REA diagram as a guide when querying the database. able names for our integrated diagram: • • • • • • • • • • Call on Customer Take Customer Order Sales Receive Cash Order Inventory Receive Inventory Disburse Cash Time Worked Inventory Cash • • • • • • • • • • Customer Supplier Employee Call on Customer-Inventory Take Order-Inventory Sales-Inventory Sales-Receive Cash Order Inventory-Inventory Receive Inventory-Inventory Receive Inventory-Disburse Cash he three steps to implementing an REA diagram in a relational database are: Create a table for: Assign attributes to appropriate tables. Use foreign keys to implement one-to-one and one-tomany relationships. Each distinct entity in the diagram. Each many-to-many relationship. s discussed previously, REA diagrams will differ across organizations because of differences in business policies. tep 2: Assign attributes to each table The next step is to determine which attributes should be included in each table. The designer needs to interview users and management to identify which facts need to be included in the database. Should use the REA diagram to determine in which tables those facts should be placed. Depends on whether the fact is a primary key or just a descriptive attribute. dentify primary keys Every table in a relational database must have a primary key. The primary key is usually a single attribute. However for M:N relationship tables, it consists of two attributes that represent the primary key of each linked entity. Example: The primary key for a sales-inventory table might be Invoice No-Item No. These multiple-attribute primary keys are called concatenated keys. The primary key is an attribute or combination of attributes that uniquely identifies each row in a table. It is typically a numeric identifier. eys for the entity tables we’ve identified might be specified as follows: CALL ON CUSTOMER—Call No. TAKE CUSTOMER ORDER—Sales Order No. SALES—Invoice No. RECEIVE CASH—Cash Receipt No. RECEIVE INVENTORY—Receiving Report No. DISBURSE CASH—Check No. TIME WORKED—Timecard No. INVENTORY—Item No. CASH—Account No. • The M:N relationship CUSTOMER—Customer No. SUPPLIER—Supplier No. tables would have keys EMPLOYEE—Employee No. that are combinations of the keys for the two related tables. eys for the entity tables we’ve identified might be specified as follows: CALL ON CUSTOMER—Call No. TAKE CUSTOMER ORDER—Sales Order No. SALES—Invoice No. RECEIVE CASH—Cash Receipt No. RECEIVE INVENTORY—Receiving Report No. DISBURSE CASH—Check No. TIME WORKED—Timecard No. INVENTORY—Item No. CASH—Account No. • Example: The primary CUSTOMER—Customer No. SUPPLIER—Supplier No. key for the sales-receive EMPLOYEE—Employee No. cash table would be invoice no.-cash receipt no. ssign other attributes to appropriate tables Attributes other than the primary key are also included in tables: To provide for accurate transaction processing and the production of financial statements; or To facilitate effective management of the entity’s resources, events, and agents. Any attribute in a table must be a fact about the object represented by the primary key. Example: Information about the customer, such as his address or phone number, should be included in the customer table, not the sales table. ome non-key attributes even need to be stored in M:N tables. xample: The inventory-sales table may include a “quantity sold” attribute. The quantity sold can’t be placed in the inventory table, because there can be many sales of any particular inventory item, and each sale produces a different quantity ordered. The quantity sold can’t be placed in the sales table, because an individual sale can include several inventory items. The quantity sold is placed in the sales-inventory table so that you can determine how much of EACH inventory item was ordered with EACH sale. rice and cost data Information about prices and costs are stored as attributes in several different tables. The inventory table stores the suggested list price, which is generally constant for the fiscal period. The sales-inventory table stores the actual sales price, which can vary during the year. Just like sales prices, the standard and actual purchase costs of each item are stored in different tables. General rule: Time-independent data (such as standard costs or list prices) should be stored as an attribute of a resource or agent. Data that vary across time (such as actual costs and prices) should be stored with event entities or in M:N relationships that involve at least one event. umulative Data Attributes like “quantity on hand” or “account balance” are cumulative data. Quantity on hand is calculated as: Customer balance: Sum of quantities purchased from the table linking inventory to the receive inventory event. LESS: Sum of quantity sold from the sales-inventory table. Sum of all sales to the customer. LESS: Sum of all cash receipts from customer. he three steps to implementing an REA diagram in a relational database are: Create a table for: Assign attributes to appropriate tables. Use foreign keys to implement one-to-one and oneto-many relationships. Each distinct entity in the diagram. Each many-to-many relationship. s discussed previously, REA diagrams will differ across organizations because of differences in business policies. tep 3: Use foreign keys to implement 1:1 and 1:N relationships. Many-to-many relationships have been implemented by the creation of separate tables. One-to-one and one-to-many relationships still need to be implemented in the database. But it is usually more efficient to implement them by the creation of foreign keys. A foreign key is an attribute of one entity that is the primary key of another entity. Customer number might appear in the customer table as a primary key and in the sales table as a foreign key. sing foreign keys to implement one-to-one relationships Can be implemented by including the primary key of one entity as a foreign key in the other. Minimum cardinalities may suggest which choice is more efficient. Usually, best to insert the primary key of the entity that can occur a minimum of one time as a foreign key in the entity that can occur a minimum of zero times. When there are two sequential events, the primary key of the event that occurs first is usually the foreign key in the event that occurs second. Provides better control, as the employee who updates the table for the second event does not have to access the table for the event that occurred first. Call on Customer Suppliers Employees (Salesperson) Call on Customer Order Inventory Take Cust. Order Customer Inventory Employees (Salesperson) Employees Suppliers Receive Inventory Take Cust. Order Cash Sales Customer Employees (Cashier) Disburse Cash Receive Cash Employees (Cashier) • Employees (as Payees) • Employees (Supervisor) This relationship is a 1:1 relationship, but the minimum on both sides is zero. Employee Time Worked Time Because the entities represent sequential events, we will follow the practice of placing the primary key of the event that occurs • on customer) as a foreign key in the event that occurs first (call Let’s zoom in on the relationship between call on customer and order). second (take customer take customer order. IMPLEMENTING AN REA DIAGRAM IN A RELATIONAL DATABASE T able Name Call on Customer Take Customer Order Primary Key Call No. Order No. Foreign Key Call No. Other Attributes Date, Time Date, Time, Total Amount Call on Customer Suppliers Take Cust. Order Employees (Salesperson) Employees Order Inventory Take Customer Inventory Order Customer Employees (Salesperson) Suppliers Receive Inventory Sales Sales Customer Employees (Cashier) Disburse Cash Cash Receive Cash Employees (Cashier) • Employees (as Payees) The same situation exists in the relationship between take customer order and sEmployee the primary key for take customer ales, so Time Worked order will be placed as Time a foreign key in the sales table. Employees (Supervisor) IMPLEMENTING AN REA DIAGRAM IN A RELATIONAL DATABASE Primary Foreign Key Key Other Attributes Call No. Date, Time Order No. Call No. Date, Time, Total Amount Invoice No. Order No. Date, Time, Total Amount, Invoice Sent (Y/N) T able Name Call on Customer Take Customer Order Sales sing foreign keys to implement one-to-many relationships Place the primary key of the entity that can occur only once as a foreign key in the entity that can occur many times. Example: The primary key for salesperson (which can occur only once per sale) is a foreign key in the sales table (which can occur many times for a particular salesperson). If you tried to do the opposite, you would not have flat tables. t would be useful to step through a complete process of converting an REA diagram into a database model. he integrated diagram is too extensive to provide a good, short example. herefore, let’s use a simple, individual transaction cycle for purposes of this example only. Below is a sample REA diagram for a very simple revenue cycle. Customer Inventory Sale Employee Receive Cash Cash Customer Our first step is to create a table for each event, resource, agent, and many-to-many relationship. Customer Inventory Sale Employee Receive Cash Cash Customer There are two events. Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Primary Key Foreign Key Other Attributes There are two resources. Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Inventory Cash Primary Key Foreign Key Other Attributes There are two types of agents: customers and employees. Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Inventory Cash Customer Employee Primary Key Foreign Key Other Attributes There is one many-to-many relationship. Customer Inventory Sale Employee Receive Cash Cash Customer Table Name Sale Receive Cash Inventory Cash Customer Employee Sales-Inventory Primary Key Foreign Key Other Attributes he next step is to assign attributes to each table. hese attributes include the assignment of primary keys. T able Name Sale Receive Cash Inventory Cash Customer Employee Sales-Inventory Primary Key Sale No. Cash Rect. No. Item No. Account No. Customer No. Employee No. Sale No.-Item No. Foreign Key Other Attributes he final step involves using foreign keys to implement the 1:1 and 1:N relationships. he relationship between customer and sales is a 1:N relationship. We make the primary key for the entity that occurs only once (customer) serve as a foreign key in the entity that can occur many times (sale). Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Primary Key Sale No. Cash Rect. No. Foreign Key Customer No. Other Attributes Date of Sale, Time of Sale, Total Amount of Sale Receipt Date, Receipt Time, Total Amount of Receipt Description, List Price Bank, Type of Account Customer Name, Customer Address, Customer Phone Employee Name, Employee Address, Employee Phone, Job Title Quantity Sold, Actual Price Inventory Cash Customer Employee Item No. Account No. Customer No. Employee No. Sales-Inventory Sale No.-Item No. ikewise, the primary key for employee should be a foreign key in the sales table. Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Primary Key Sale No. Cash Rect. No. Foreign Key Customer No., Employee No. Other Attributes Date of Sale, Time of Sale, Total Amount of Sale Receipt Date, Receipt Time, Total Amount of Receipt Description, List Price Bank, Type of Account Customer Name, Customer Address, Customer Phone Employee Name, Employee Address, Employee Phone, Job Title Quantity Sold, Actual Price Inventory Cash Customer Employee Item No. Account No. Customer No. Employee No. Sales-Inventory Sale No.-Item No. he primary key for employee should also be a foreign key in the receive cash table. Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Primary Key Sale No. Cash Rect. No. Foreign Key Customer No., Employee No. Employee No. Other Attributes Date of Sale, Time of Sale, Total Amount of Sale Receipt Date, Receipt Time, Total Amount of Receipt Description, List Price Bank, Type of Account Customer Name, Customer Address, Customer Phone Employee Name, Employee Address, Employee Phone, Job Title Quantity Sold, Actual Price Inventory Cash Customer Employee Item No. Account No. Customer No. Employee No. Sales-Inventory Sale No.-Item No. he primary key for customer should also be a foreign key in the receive cash table. Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Primary Key Sale No. Cash Rect. No. Foreign Key Customer No., Employee No. Employee No., Customer No. Other Attributes Date of Sale, Time of Sale, Total Amount of Sale Receipt Date, Receipt Time, Total Amount of Receipt Description, List Price Bank, Type of Account Customer Name, Customer Address, Customer Phone Employee Name, Employee Address, Employee Phone, Job Title Quantity Sold, Actual Price Inventory Cash Customer Employee Item No. Account No. Customer No. Employee No. Sales-Inventory Sale No.-Item No. he relationship between sales and receive cash is 1:1. Two guidelines will produce the same result. Put the primary key of the event with the minimum of one (sales) as a foreign key in the event with the minimum of zero (receive cash); or Put the primary key of the event that occurs first (sales) as a foreign key in the event that occurs second (receive cash). Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Inventory Cash Customer Employee Primary Key Sale No. Cash Rect. No. Item No. Account No. Customer No. Employee No. Foreign Key Customer No., Employee No. Employee No., Customer No., Sale No. Sales-Inventory Sale No.-Item No. Other Attributes Date of Sale, Time of Sale, Total Amount of Sale Receipt Date, Receipt Time, Total Amount of Receipt Description, List Price Bank, Type of Account Customer Name, Customer Address, Customer Phone Employee Name, Employee Address, Employee Phone, Job Title Quantity Sold, Actual Price he relationship between sales and inventory is a many-tomany relationship and was already implemented by the creation of a separate table. Customer Inventory Sale Employee Receive Cash Cash Customer n the relationship between cash and receive cash, the primary key for the event that occurs once (cash) should be a foreign key in the event that occurs many times (receive cash). Customer Inventory Sale Employee Receive Cash Cash Customer T able Name Sale Receive Cash Inventory Cash Customer Employee Primary Key Sale No. Cash Rect. No. Item No. Account No. Customer No. Employee No. Foreign Key Customer No., Employee No. Employee No., Customer No., Sale No., Account No. Sales-Inventory Sale No.-Item No. Other Attributes Date of Sale, Time of Sale, Total Amount of Sale Receipt Date, Receipt Time, Total Amount of Receipt Description, List Price Bank, Type of Account Customer Name, Customer Address, Customer Phone Employee Name, Employee Address, Employee Phone, Job Title Quantity Sold, Actual Price n this chapter, you’ve learned: How REA diagrams for individual transaction cycles are integrated into a single comprehensive organization-wide REA diagram. How tables are constructed from the REA model of an AIS in a relational database. ...
View Full Document

Ask a homework question - tutors are online