Accounting Information Systems Chapters 5, 6, and 8
Given the conceptual model of the association between Alpha and Omega, if 74% of alphas are related to omegas and 46% of omegas are related to alphas, what is the best relational table implementation .
Create a table for Alpha, a table for Omega, and a separate table for the association with a composite primary key consisting of the primary keys from Alpha and Omega.
In a Microsoft Access database, suppose you have a sale table, a customer table, an inventory table, and a stockflow-sale-inventory table. Customer ID is posted into the sale table as a foreign key. Referential integrity is enforced on all relationships between tables. Describe the data entry into these tables. When will data be entered into each table? Will data be entered into every table when a sale is made? Or is some of it entered at other times? Will the order in which data is entered into the tables matter? Explain.
Customer data will be entered into the customer table as customers are identified and approved by the company. Inventory data will be entered into the inventory table as the company identifies and approves items to be included in its portfolio of offerings. As sales are made, first the data that applies to the sale as a whole (e.g. sale number, date, and customer to whom the sale is made) will be entered into the sale table. Then data for each line item (e.g. quantity sold and actual unit selling price) will be entered into the stockflow-sale-inventory table. The order does matter. If sale data is entered before a related customer is entered in the customer table, the record cannot be saved because referential integrity will be violated. If data is entered into the stockflow-sale-inventory table before either the related sale data or the inventory data is entered, similarly, the record cannot be saved because referential integrity will be violated.
What type of data does each record in an agent table contain?
Each agent data table provides information about an internal or external person or entity category. Each record in an agent table contains data about one instance of agent.
What type of data does each record in a resource table contain?
Each resource data table includes data describing a corporate resource. Each record in a resource table contains data about one instance of a resource.
Give some examples of nonkey attributes that would describe a customer class.
Event data tables are much like expanded information age journals; they do not require the summarization and loss of detail required to record traditional accounting transactions. All the detailed documentation of business activity data is available in the event data stores. Each record in an event data table contains documentation of a particular business event. Unless the event happened at multiple times, in multiple places, and with multiple agents, typically the event data table will include the time, location, and who was involved.
What is the purpose of a foreign key
Foreign keys are key attributes of an object that are posted into another object table. The sole purpose of foreign keys is to link tables so the tables can be used collectively to store business data and to generate useful information.
How do you establish a concatenated primary key in Microsoft Access?
In table design view, click on the grey box next to the first key attribute, then Ctrl-click on the grey boxes next to any additional key attributes. Once all key attributes are selected, click on the Primary Key icon in the ribbon menu.
How do you determine the scope of a business process level REA model?
In your model, you should define the scope to the business processes and events that an organization wants to plan, execute (control), and/or evaluate.
Given the tables below and assuming entered data conforms to the multiplicities, what must have been the underlying conceptual model?
Look @ book
How are MAXIMUM multiplicities used when designing enterprise systems?
Maximum multiplicities are used to communicate the maximum number of times a class can participate in an association
What are the multiplicities for a class that has mandatory participation in an association and can be related to at most one instance of the associated class? Where should these multiplicities be placed on the UML class diagram?
Minimum = 1; maximum = 1; the 1..1 should be placed next to the associated class on the UML class diagram (i.e., on the opposite side of the association from the class whose participation they describe).
How are MINIMUM multiplicities used when designing enterprise systems?
Minimum multiplicities are used to represent and model business rules; they indicate whether a class's participation in an association is optional or mandatory
Multiplicities aka (Cardinalities)
Minimum: expresses the minimum number of times one instance of a class must participate in an association with the related class. 0 = optional participation 1 = mandatory participation Maximum: expresses the maximum number of times one instance of a class may participate in an association with the related class 1 = one-time only participation * = as many times as needed, no restrictions
Does every table in a relational database contain a foreign key? Explain
No. Foreign keys can only be posted into tables for which the class' participation in a relationship has a maximum of 1. Most resource and agent tables will not contain foreign keys because they can participate many times in the associations with related events. Sometimes they may have foreign keys representing associations with other agents or resources (e.g. customer may have a salesperson identifier posted as a foreign key to represent the one salesperson that is assigned responsibility for the customer).
List the five questions typically asked in Journalism 101 and explain how they are typically answered in a business process level model.
Question How Typically Answered Who was involved? Participation associations between event class and internal and external agent classes What happened? Event class What was affected (and by how much)? Stockflow associations between resource or resource-type classes and event class When? Date attribute of event class Where? Location attribute of event class Why? Duality association between event class and causally related event class
What is referential integrity and how can one tell if a relational database has referential integrity?
Referential integrity is a principle in the relational database model that requires a value for a foreign key attribute to either be null (blank) or to match exactly one of the data values in the table in which the attribute is a primary key. Referential integrity may be enforced in Microsoft Access by marking the appropriate relationship property checkbox in the relationship layout.
What is referential integrity and how can referential integrity be enforced in Microsoft Access?
Referential integrity is a principle in the relational database model that requires a value for a foreign key attribute to either be null (blank) or to match exactly one of the data values in the table in which the attribute is a primary key. Referential integrity may be enforced in Microsoft Access by marking the appropriate relationship property checkbox in the relationship layout.
Associations
Relationships between classes
Give three examples of different types of revenue cycles.
Selling products, wherein title to the products transfers to the customer Renting products, wherein customer gets temporary custody and use of products Selling services, such as accounting, advertising, or lawn care
Classes
Sets of real world objects - things that have a separate existence, either physical or conceptual
In Microsoft Access, what does it mean to set a field property to require data entry? Is that the same thing as enforcing referential integrity? If not, what is the purpose for setting a field property to require data entry?
Setting a field property to require data entry prohibits a record from being added to a database table if that field is left blank. Any field may be set to required data entry, including foreign keys and descriptor attributes. Primary keys by definition require data entry (because of the entity integrity principle) so there is no need to set primary keys to require data entry. Required data entry is not the same as referential integrity. Referential integrity allows the value of a foreign key to be null (blank). To ensure a user must enter a valid foreign key value and not allow a null value, one must enforce referential integrity and set the field property to require data entry.
Derivable attributes
Static - will not change if new data is entered into system vs. Volatile - will change if new data is entered into system
Multiplicities describing associations
min..1 --------- min..1 = a 1-to-1 association (one-to-one) min..1 --------- min..* = a 1-to-n association (one-to-many) min..* ---------- min..1 = an n-to-1 association (many-to-one) min..* ---------- min..* = an m-to-n association (many-to-many)
Explain the difference between conceptual, logical, and physical database models.
A conceptual model attempts to represent reality as faithfully as possible and is independent of any particular type of database model, software or hardware. A logical model represents reality in a format compatible with a specific type of database, such as relational or object-oriented. A logical model is independent of any particular type of hardware and may be implemented in any software that conforms to the logical model type. For example, a relational logical model may be implemented using a number of different relational database software packages but it may not be implemented using object-oriented database software. A physical database model represents the actual implementation of the logical model in conformance with a specific software package's requirements. For example, a model that portrays the specifications of a database implemented in Microsoft Access is a physical database model.
What is the purpose of a primary key attribute?
A primary key uniquely and universally identifies each instance of a class.
Give three examples of different types of acquisition cycles.
Buying products, wherein title to the products transfers to the enterprise Renting products, wherein enterprise gets temporary custody and use of products Buying services, such as accounting, advertising, or lawn care
How to interpret multiplicities
Can one instance of Class A exist in the database without a related instance of Class B? If Yes, minA = 0 (optional) If No, minA = 1 (mandatory) Can one instance of Class A in the database be associated with more than one instance of Class B? If Yes, maxA = * (many) If No, maxA = 1 (one) Can one instance of Class B exist in the database without a related instance of Class A? If Yes, minB = 0 (optional) If No, minB = 1 (mandatory) Can one instance of Class B in the database be associated with more than one instance of Class A? If Yes, maxB = * (many) If No, maxA = 1 (one)
Attributes
Characteristics or elementary properties of classes and/or associations Primary key attribute uniquely and universally identifies each instance of a class or association Simple versus composite attributes Derivable attributes
UML
Unified Modeling Language
Steps to Create a REA Business Process Level *Core* Model
Step 1: Identify Economic Exchange Events Create each economic exchange event as a class and create a duality association between them Step 2: Attach Resources to the Economic Events Create each resource as a class and create a stockflow association between it and the related economic event Step 3: Attach External Agents to Economic Events Create as a class the external agent from whom resources are obtained in each economic increment event and create a participation association between the event and agent Create as a class the external agent to whom resources are transferred in each economic decrement event and create a participation association between the event and agent Step 4: Attach internal agents to economic events Create as classes the internal agents who process, accomplish, or authorize each economic increment event and create participation associations between the events and agents Create as classes the internal agents who process, accomplish, or authorize each economic decrement event and create participation associations between the events and agents Step 5: Assign attributes to classes and associations Step 6: Assign multiplicities Step 7: Validate model with client
List and describe the steps in REA business process level modeling.
Step 1: Identify Economic Exchange Events Examine value chain to identify the core of the business process - the causally related economic increment and decrement events Step 2: Attach Resources to Economic Events Examine value chain to identify the resource flows involved in the economic increment and decrement events that were identified in step 1; create stockflow associations to connect the resources to the events Step 3: Attach External Agents to Economic Events Determine the external business partners involved in the economic increment and decrement events; create participation associations to connect the external agents to the events Step 4: Attach Internal Agents to Economic Events Determine which enterprise employee categories are involved in the economic increment and decrement events; create participation associations to connect the internal agents to the events Step 5: Attribute Assignment Determine the characteristics an enterprise needs to track for each class and association in the model; connect each attribute to the appropriate class or association Step 6: Multiplicity Assignment For each association, ask the four questions about the minimum and maximum number of times each class participates in the association; indicate the answers to the questions using the appropriate notation Step 7: Validate Model Discuss the model with a knowledgeable enterprise representative; review the model in detail, explaining each component in laymen's terms; make any necessary changes
When you create a relationship layout in Microsoft Access that includes relationships between tables for which referential integrity is enforced, Access marks the relationships with notations of 1 and ∞. What do those notations indicate?
The 1 and ∞ symbols on the Relationship Layout resemble multiplicities, however, they are not exactly the same concept. The 1 and ∞ reveal how many times the same data value can be stored in that field of the table to which the symbol is connected. For example, a 1 next to SalespersonID in the Salesperson table says that the same data value for SalespersonID can exist in the Salesperson table only one time. That is, there can be no duplicate values, which makes sense because SalespersonID is the primary key and must be unique. The ∞ next to SalespersonID in the Sale table indicates that the same data value for that attribute may exist multiple times in the Sale table. That also makes sense given that there is no restriction on foreign key fields to be unique. For example, Salesperson 654321 can only exist in the Salesperson table (in which it is the primary key) once, but can exist in the Sale table (in which it is a foreign key) as many times as that salesperson makes sales.
Describe the acquisition cycle. What main activities make up this process?
The acquisition/payment process includes business events involved in acquiring, paying for, and maintaining the goods and services needed by an organization. Objectives of this process include (1) acquiring only those goods or services that an organization needs and can afford, (2) receiving only those goods and services actually ordered or requested, (3) paying only for goods and services actually received, and (4) making sure that the goods and services acquired are properly maintained and available when needed. Organizations can acquire a wide variety of goods and services including supplies, inventory, property, plant, and equipment, new ideas (e.g., research and development), and miscellaneous services (e.g., utilities, telephone, protection, legal services, medical, financial, or custodial services). Organizations can also acquire human resources (e.g., people's time and skills), and financial resources. Most enterprises separate those types of acquisitions into separate human resources/payroll processes and financing processes. Regardless of the type of good or service being acquired, the following are typical events in the acquisition/payment process: Identify need and request the good or service to fill that need Select a supplier and order the good or service, Receive and inspect the good or service, Pay for the good or service If necessary, return or seek warranty satisfaction on goods or services If you analyze a variety of acquisition/payment business processes, you will notice that some organizations may order the events differently, may use a subset of the events, or may add more detailed events. Nonetheless, the basic nature of the process is fairly stable across organizations and resources.
Each class is drawn with a three compartment box.
The first compartment contains the class name (and stereotype, if any) A stereotype is a generalized type of class to which other classes may belong The second compartment contains the attributes of the class Varying levels of detail regarding those attributes may be included, such as data types The third compartment contains the operations the class may perform (we will leave these blank)
Describe the sales/collection process (revenue cycle). What main activities make up this process?
The sales/collection process includes the sequence of events involved in delivering goods and services to customers for payment. Essentially, the sales/collection process is the mirror image of the acquisition/payment process. Whenever one organization or individual acquires and pays for goods and services, someone else is selling the goods or services and receiving payment. Although there is some diversity across the types of goods and services sold, the basic process typically involves the following events: Present products or services available for sale or rent Receive an order for goods or services, Select and prepare the good or service for delivery, Deliver the good or service, Receive payment for the good or service, Provide after-sale service or accept returns. As with the acquisition/payment process, for some organizations the events may occur in different order, may not all occur, or may be accompanied by additional tasks. Nonetheless, from an event perspective, the basic nature of the sales/collection process remains fairly constant across organizations and industries.
