1 / 40

Chapter 18

Chapter 18. Implementing an REA Model in a Relational Database. REA Diagram—Revenue Cycle. Fred’s Train Shop. REA Diagram—Expenditure Cycle. Fred’s Train Shop. REA Diagram—Payroll Cycle. Fred’s Train Shop. REA Redundancies.

ksena
Download Presentation

Chapter 18

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 18 Implementing an REA Model in a Relational Database

  2. REA Diagram—Revenue Cycle Fred’s Train Shop

  3. REA Diagram—Expenditure Cycle Fred’s Train Shop

  4. REA Diagram—Payroll Cycle Fred’s Train Shop

  5. REA Redundancies • Difference between merging redundant events and merging redundant resources: • Merging redundant resources does not affect any cardinalities. • LINKAGE BETWEEN CYCLES • Merging redundant events alters minimum cardinalities associated with the other events that are related to the merged event. • May be linked to either an event that is part of one business cycle or to an event that is part of another cycle but cannot be linked to both events • The minimum cardinality associated with the other events must be 0 in the integrated REA diagram

  6. Intergraded REA Diagram

  7. The revenue cycle • This model assumes customers are the only source of cash receipts. • Usually there will be also be cash receipts from other sources/cycles. • This would change the indicated minimum cardinalities to 0

  8. The Expenditure cycle • This model assumes customers are the only source of cash receipts. • Usually there will be also be cash receipts from other sources/cycles. • This would change the indicated minimum cardinalities to 0

  9. This model assumes customers are the only source of cash receipts. • Usually there will be also be cash receipts from other sources/cycles. • This would change the indicated minimum cardinalities to 0 • The payroll cycle

  10. This model assumes customers are the only source of cash receipts. • Usually there will be also be cash receipts from other sources/cycles. • This would change the indicated minimum cardinalities to 0 • The integrated diagram merges multiple copies of resource and event entities but retains multiple copies of agent entities.

  11. Cardinality Effect of Merging Resources Cardinalities between resource and entities remain the same.

  12. Cardinality Effect of Merging Events The cardinality between Disburse Cash and Supplier and Employee (as payee) is now 0 to 1, that is, a disbursement can be made to the supplier or the employee but not both!

  13. Rules for Creating Integrated REA Diagram • Every event must be linked to at least one resource. • Every event must be linked to two agents who participate in that event. • Every event that involves the disposition of a resource must be linked to an event that involves the acquisition of a resource. • Every resource must be linked to at least one event that increments that resource and to at least one event that decrements that resource. • If event A can be linked to more than one other event, but cannot be linked simultaneously to all of those other events, then the REA diagram should show that event A is linked to a minimum of 0 of each of those other events.

  14. Using REA Diagram to Create Relational Database • Advantage: • Creating a set of tables from an REA diagram automatically results in a well-structured relational database • Ensures the elimination of anomalies: • Update • Insert • Delete

  15. REA to Database Steps • Create a table for each distinct entity in the diagram and for each many-to-many relationship. • Assign attributes to appropriate tables. • Identify primary keys: • Attributes that uniquely identifies each record. • For M:N relationships the primary key consists of two attributes that represent the primary keys of each entity linked in that relationship. • Identify remaining attributes for table. • Use foreign keys to implement one-to-one and one-to-many relationships. • An attribute of one entity that is itself the primary key of another entity.

  16. 5 1 6 2 7 3 4 • Our integrated diagram has seven event entities.

  17. 1 2 • Our integrated diagram has two resource entities.

  18. 2 1 3 3 1 2 3 3 • There are three distinct agent entities. • The first is the customer. • The second agent entity is the supplier. • 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. 3 3

  19. IMPLEMENTING AN REA DIAGRAM IN A RELATIONAL DATABASE • Total distinct entities to be represented in separate tables:

  20. IMPLEMENTING AN REA DIAGRAM IN A RELATIONAL DATABASE • Create a separate table to represent each many-to-many relationship in the conceptual model, I.e., for the following participation cardinality patterns (0,N)-(0,N) (0,N)-(1,N) (1,N)-(0,N) (1,N)-(1,N) • You must create a separate table to represent the relationship • The primary keys of the related entity tables are posted into the relationship table to form its primary key. This kind of primary key is called a composite or concatenated primary key • There are no exceptions to this rule!!! • If you post a foreign key in either direction, redundancy will be a problem for many-to-many relationships

  21. Example: Many-Many Relationships(from DCH) Note: Each record field must be single valued(no repeating groups)

  22. Example: Many-Many Relationship(from DCH) Student-Course Relationship Table

  23. 3 1 2 4 5 • the many-to-many relationships.

  24. IMPLEMENTING AN REA DIAGRAM IN A RELATIONAL DATABASE • Total number of tables in database:

  25. IMPLEMENTING AN REA DIAGRAM IN A RELATIONAL DATABASE • Identify primary keys • Every table in a relational database must have a primary key. • The primary key is an attribute or combination of attributes that uniquely identifies each row in a table. • It is typically a numeric identifier. • 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. • These multiple-attribute primary keys are called concatenated keys.

  26. Attribute assignment

  27. Use foreign keys to implement one-to-one and one-to-many relationships. • Minimum cardinalities may suggest which choice is more efficient. • 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. • The primary key of the entity that can linked to multiple instances of the other entity must become the foreign key in that other entity. • Example: The primary key for customer (which can occur only once per sale) is a foreign key in the sales table (which can occur many times for a particular customer).

  28. SaleID CR-ID Amount Amount Date Date Sale Cash Receipt (1,1) (1,1) yields Example: (1,1)-(1,1) Could do either one but because the entities represent sequential events, authors follow the practice of placing the primary key of the event that occurs first (sale) as a foreign key in the event that occurs second (cash receipt).

  29. SaleID CR-ID Amount Amount Date Date Sale Cash Receipt (0,1) (1,1) yields Example 2: Posting into a (1,1)-(0,1) This eliminates null values

  30. SaleID CustID City Amount Date Name Sale Customer (0,N) (1,1) Is-to S-ID* Cust-ID Name Address S1, S3 C1 Heather Walnut Creek S2 C2 Steven Cincinnati Example : Posting into a one to many

  31. Use foreign keys to implement one-to-one and one-to-many relationships

  32. Use foreign keys to implement one-to-one and one-to-many relationships

  33. Foreign key (field property required or not required) • If minimum cardinality for foreign key posting is 1(mandatory), field property attribute should be set to required • The six circled foreign key attributes field property should be set to required in the database table to avoid possibility of null value

  34. Foreign keys in bold red should have property attribute set to required to avoid null value.

  35. Foreign key (required or not required) Cardinalities indicate that Customer participation in relationship is mandatory. Since it is mandatory, to avoid possible null value, make this attribute required in the sales table

  36. There are 4 relationship attributes. • If relationship becomes a separate table, then relationship attributes are placed in that table • If relationship established via foreign key, attribute placed in table with foreign key

  37. Relationship attributes

  38. Retrieving Information from REA Database • Journals • Information contained in event tables • Ledgers • Information contained in resource tables • Financial statements • Information contained in resources and • Information on imbalances • Accounts receivable • Sales transactions for which customer payments have not yet been received • Accounts payable • Purchases from suppliers that have not yet been paid for

More Related