1 / 30

Chapter 18

Chapter 18. Implementing an REA Model in a Relational Database. Intergraded REA Diagram. 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.

novia
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. Intergraded REA Diagram

  3. 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.

  4. 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

  5. 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.

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

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

  8. 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

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

  10. 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

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

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

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

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

  15. 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.

  16. Attribute assignment

  17. Tables for Fred’s Train Shop Revenue cycle

  18. 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).

  19. 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).

  20. 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

  21. 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

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

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

  24. 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

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

  26. 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

  27. 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

  28. Relationship attributes

  29. 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