DATABASE DESIGN USING THE REA DATA MODEL Chapter 15 Designing and Implementing a Database System Operation and Planning maintenance Requirements Implementation analysis Design Coding Data Modeling Data modeling is the process of defining a database so that it faithfully represents all aspects of the organization, including its interactions with the external environment. Designing and Implementing a Database System Operation and Planning maintenance Requirements Implementation analysis Data Modeling Occurs Here Design Coding Data Modeling Tools: Entity- Relationship Diagrams Entity-relationship (E-R) diagrams – graphical technique for portraying a database schema Line Enrollment Items Students Data Modeling Tools: Entity- Relationship Diagrams Enrollment Students Entity Name Attributes Enrollment Enrollment No., Enrollment Date, Enrollment Time Student Student ID No., Student Name, Student Address The REA Data Model The REA data model is a conceptual modeling tool specifically designed to provide structure for designing AIS databases. The REA data model provides structure in two ways: 1 By identifying what entities should be included in the AIS database 2 By prescribing how to structure relationships among the entities in the AIS database The REA Data Model Three Basic Types of Entities – Resources that the organization acquires and uses. – Events in which the organization engages – Agents participating in these events The REA Data Model: An Example Inventory Sales Salesperson Customer Cash Receive Cashier Accounts Cash THE REA DATA MODEL Structuring Relationships: The Basic REA Template – Rule 1: Each event is linked to at least one resource that it affects. – Rule 2: Each event is linked to at least one other event. – Rule 3: Each event is linked to at least two agents. The Basic REA Template Resource A Event A Agent A Agent B Resource B Event B Agent C The REA Data Model Rule 1: Every event entity must be linked to at least one resource entity – Some events affect the quantity of a resource: “Get” events v. “Give” events Stockflow relationships affect the quantity of a resource Commitment event represent promises to engage in some future event The REA Data Model Rule 2: Every event entity must be linked to at least one other event entity – Give and get events are linked together in an economic duality relationship The REA Data Model: A Set of Give &Get Exchanges Give Get Inventory Cash Revenue Cycle Give Get Cash Inventory Expenditure Cycle Give Get Employees’ Human Resources/ Cash Time Payroll Cycle Give Get Financing Cycle Cash Cash Give (Use) Raw Materials Give (Use) Get Finished Production Cycle Employee Time Goods Inventory Give (Use) Machinery & Equipment The REA Data Model Rule 3: Every event entity must be linked to at least two participating agents – Accountability – Monitor the status of commitments and exchanges with outside parties – Links to at least two participating agents Developing an REA Diagram STEP ONE: Identify the events about which management wants to collect information. STEP TWO: Identify the resources affected by the events and the agents who participated. STEP THREE: Determine the cardinalities between the relationships. Developing an REA Diagram Example: Typical activities in the revenue cycle include: – Take customer order – Fill customer order – Bill customer – Collect payment Developing an REA Diagram Take Order Sale Receive Cash Developing an REA Diagram Step Two: Identifying the resources affected by the events and the agents who participated Involves determining: – The resource(s) reduced by the give event. – The resource(s) increased by the get event. – The resources that are affected by a commitment event. Developing an REA Diagram Take Order Employee Inventory Customer Sale Employee Receive Cash Customer Cash Developing an REA Diagram Step Three: Determining cardinalities between relationships A cardinality describes the nature of the relationship between two entities. – It indicates how many instances of one entity can be linked to a specific instance of another entity – Cardinalities are often expressed as a pair of numbers. – The first number is the minimum, and the second number is the maximum. Developing an REA Diagram Minimum cardinality – Indicates whether a specific a instance of the entity next to the cardinality must be linked to at least one instance of the entity on the opposite side of that relationship – Can be either 0 or 1 Developing an REA Diagram Maximum cardinality – Indicates whether one instance of that entity can be linked to more than one instance of the other entity participating in that relationship – Can be either 1 or N Developing an REA Diagram Using the crow’s feet notation: – The symbol for zero is a circle: O – The symbol for one is a single stroke: | – The symbol for many is the crow’s foot: Developing an REA Diagram Sale Customer Developing an REA Diagram Three Types of Relationships – Relationships depend on the maximum cardinality on each side of a relationship. A one-to-one relationship (1:1) exists when the maximum cardinality for each entity in the relationship is 1. A one-to-many (1:N) relationship exists when the maximum cardinality on one side is 1 and the maximum on the other side is many. A many-to-many (M:N) relationship exists when the maximum on both sides is many. Developing an REA Diagram Take Order Sale Developing an REA Diagram Sale Customer Developing an REA Diagram Inventory Sale Developing an REA Diagram Sale Cash Receipt Developing an REA Diagram Sale Cash Receipt Developing an REA Diagram Sale Cash Receipt Developing an REA Diagram Sale Cash Receipt Developing an REA Diagram Take Order Employee Inventory Customer Sale Employee Receive Cash Customer Cash Developing an REA Diagram Agent – event cardinalities: – The cardinality between agent and event is typically (0:N) on the agent side Resource – event cardinalities: – The minimum cardinality is typically one – The maximum could be one or many Event – resource cardinalities: – The minimum is typically zero Developing an REA Diagram Cardinalities between events – When events occur in a sequence, the minimum cardinality between the first event and the second event is always zero – The minimum cardinality between the second event and the first event is typically one – An exception could occur if the first event is not required for the second event to occur – The maximums in the cardinalities between events can be either one or many Developing an REA Diagram Uniqueness of REA Diagrams – Each organization will have its own unique REA diagram. Data modeling is typically complex and repetitive Summary We have: – Learned the steps to follow in designing and implementing a database system; – Learned how the REA data model is used to design an AIS database; – Learned how an entity-relationship diagram of an AIS database is drawn; – Learned how to read REA diagrams; – Learned what REA diagrams reveal about the activities and policies of the organization being modeled.