ACC 340 Module 2

The final step in drawing an REA diagram for one transaction cycle is to add information about relationship cardinality. Define cardinality.

- 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. - In relational database terms, cardinality provides information about how many rows in the other table can be linked to each row in this table. - For example, the cardinality between the event Sales and the agent Customer answers the question: for each sale a company makes, how many customers are associated with that sale? - Cardinalities describe the nature of the relationship between two entities. - No universal standard exists for representing information about cardinalities in REA diagrams. - The choice of cardinalities is not arbitrary. It reflects facts / organization's business policy about the organization that are obtained during the requirements definition stage of the database design process.

Describe the three basic rules that apply to the REA model pattern.

1. Every event entity must be linked to at least one resource entity (that it affects). 2. Every event entity must be linked to at least one other event entity. 3. Every event entity must be linked to at least two participating agents.

What are the basic requirements when logically designing a relational database model?

1. Every row in every table must have a unique key known as a primary key. - ENTITY INTEGRITY RULE: A non-null primary key ensures that every row in a table represents something and that it can be identified. o Reason: A primary key cannot uniquely identify a row in a table if it is null (blank). 2. Tables may also have a key known as a foreign key; such a key will have a value corresponding to the primary key in another table. - REFERENTIAL INTEGRITY RULE: Foreign keys which link rows in one table to rows in another table must have values that correspond to the value of a primary key in another table. o Foreign keys link rows in one table to rows in another table. This constraint, called the referential integrity rule, ensures database consistency. 3. Each column in a table must be single-valued (the same data type) and describe an attribute of the entity identified by the primary key (PK) 4. All nonkey attributes in a table must describe a characteristic of the object identified by the primary key (PK)

Describe the steps in developing an REA diagram.

1. First, identify the basic events of interest - Any activity about which management wants to collect information in order to plan, control, and evaluate performance). 2. Identify the resources affected by and the agents who participate in those events. 3. Use knowledge about the organization's business practices to add relationship cardinality information to the diagram.

Explain the types of attributes that tables possess in a relational database.

1. Primary key - the attribute, or combination of attributes, that uniquely identify a specific row in a table. 2. Foreign key - an attribute appearing in one table that is a primary key in another table. 3. Nonkey attributes found in tables - For example, an inventory table may contain information about the description, quantity on hand, and list price of each item a company sells.

Redundancy has been called an enemy of relational databases. Discuss the problems associated with redundancy embedded in the database design.

1. The database becomes larger than it needs to be, since duplicate facts are being stored within it. 2. Inconsistency may occur where only one instance of redundant data is updated or purged. - The result is that the accuracy and integrity of the database suffers. 3. Make file maintenance unnecessarily time consuming and error-prone. 4. There are three specific anomalies connected with redundancy: - The update anomaly is an improper database organization where a non-primary key item is stored multiple times. Updating the item in one location and not the others causes data inconsistencies. - The insert anomaly is an improper database organization the results in the inability to add records to a database. - The delete anomaly is an improper organization of a database that results in the loss of all information about an entity when a row is deleted.

Identify the prerequisite of developing an REA data model.

1. Understand business process / transaction cycle 2. Document business process / transaction cycle - Narratives - Flowcharting transaction cycle to document our understanding 3. Develop an REA diagram

Describe a data dictionary. Describe how accountants may support the development of a database dictionary.

A data dictionary is a means by which information about the structure of a database is maintained. The data dictionary contains information about the structure of the database. For each data element stored in the database, there is a corresponding record in the data dictionary that describes it. Accountants should participate in the development of the data dictionary because they have a good understanding of the data elements in a business organization • Data element Name • Data description • Data / field type - where those data elements originate and • Data source = Source documents - how the data elements are used. • Programs in which used = transaction cycles • Output in which contained = reports and authorized readers • Authorized users = internal controls A data dictionary contains information about the structure of the database. For each data element stored in the database, there is a corresponding record in the data dictionary that describes its input source, records that contain the data, programs that use the data, output/reports that contain the data, and authorized users that can access the data. Accountants support the development of a data dictionary because they understand the audit trail. For example, accountants know how the data is captured, how it is processed, what information or reports they need to generate, etc. The data dictionary is ultimately the responsibility of the accountants.

Describe the minimum cardinality and the maximum cardinality.

A minimum cardinality indicates the number of instances of that entity that must be associated with at least one instance of the other entity. - Minimum cardinalities can be 0 or 1. A maximum cardinality indicates the number of instances of that entity that can be linked to at most one instance of the other entity. - Maximum cardinalities can be 1 or N (many).

Describe the possible relationships between entities, in terms of cardinalities.

A one-to-one relationship (1:1) exists when the maximum cardinality of each entity is 1. A one-to-many relationship (1:N) exists when the maximum cardinality on one entity is 1 and maximum cardinality of the other entity is N. A many-to-many relationship (M:N) exists when the maximum cardinality of both entities is N.

Define database.

A set of interrelated, centrally coordinated data files that are stored with as little data redundancy as possible

Describe what will be the main impact of database technology in your career.

Acquire essential job skills: - assist users to have quicker access to and greater use of accounting information in decision-making through queries - work directly with databases and will enter, process, and query databases. = activities - audit or work for companies that use database technology to store, process, and report accounting transactions. = tools - develop and evaluate internal controls necessary to ensure database integrity. = skills

Describe business intelligence.

Business intelligence analyzes large amounts of data for strategic decision making

The second basic rule that applies to the REA data modeling is: Each event is linked to at least one other event. Explain what is a commitment event.

Commitment events are linked to other events to reflect sequential cause-effect relationships (time differential) Commitment events do not directly affect the quantity of a resource Some events do NOT directly alter the quantity of a resource. If a customer orders goods but has not paid and has not received goods, this activity is called a commitment event =TAKE CUSTOMER ORDER event in Figure 17-6, p.509 Not every relationship between two events represents a give-to-get economic duality. Commitment events are linked to other events to reflect sequential cause-effect relationships. Organizations need to track the effects of commitments to provide better service and for planning purposes. Especially important to the service organizations o Booking flights, hotel rooms, tour o Appointment with CPA / Doctor / lawyer Summary : REA model value-added activities only.

Describe advantages of database systems over file-oriented transaction processing systems.

Data independence: - One of the benefits of using database is to allow an organization to change its data in one location without affecting its data in another location. - Because data and the programs (that use data) are independent of each other, each can be changed without having to change the other. This makes programming easier and simplifies data management - Database systems separate logical and physical views. This separation is referred to as program-data independence. - Such separation facilitates developing new applications because programmers can concentrate on coding the application logic (what the program will do) and do not need to focus on how and where the various data items are stored or accessed. - In the file-oriented transaction systems, programmers need to know physical location and layout of records which adds another layer of complexity to programming. Data integration: - Integration is achieved by combining master files into larger "pools" of data (in unlimited ways to combine information data lakes, data warehouse, data sea) that many application programs can access - An example is an employee database that consolidates data formerly contained in payroll, personnel, and job skills master files. This makes it easier for information to be combined in unlimited ways Reporting flexibility: - Reports can be revised easily and generated / customized as needed and the database can be easily browsed to research a problem or obtain detailed information underlying a summary report File-oriented transaction processing systems have problems such as data redundancy, data inconsistencies, lack of compatibility, lack of data sharing, lack of data integration, substantial program-data dependence. Data is also trapped within a single functional area. Database systems, however, alleviate these problems by providing data integration, data sharing, minimal data redundancy and data inconsistencies, data independence, and cross-functional analysis. - In a database system, data is only entered once, which minimizes errors, and this data is shared across the entire organization, rather than only with specific departments. - Data integration: files are logically combined and made accessible to various systems - Data Sharing: with data in one place it is more easily accessed by authorized users - Minimizing data redundancy and data inconsistency: eliminates the same data being stored in multiple files, thus reducing inconsistency in multiple versions of the same data - Data Independence: data is separate from the programs that access it; changes can be made to the data without necessitating a change in the programs and vice versa - Cross-Functional Analysis: relationships between data from various organizational departments can be more easily combined

Mary has been selling cupcakes for the past few years. She keeps meticulous record of every cupcake that she has ever made and sold. She has also kept records of customers' comments on all of her cupcakes. These have been entered into a computerized accounting information system that her accountant refers to as a "data warehouse." Mary is considering rebranding her cupcake and has asked her accountant to identify the top sellers for the past few years. Describe the type of activity does Mary wants her accountant to engage in using the database.

Data mining

Describe data modeling.

Data modeling is the process of defining a database so that it faithfully represents all aspects of the organization, including its interaction with the external environment. The objective is to capture and store data about every business activity the organization wishes to plan, control, and evaluate.

Describe the ways in which a well-designed DBMS will facilitate the basic functions of creating, changing and querying data.

Every DBMS must provide a means of performing the three basic functions of creating a database, changing a database, and querying a database. The set of commands used to create the database is known as data definition language (DDL). - The DBMS language that builds the data dictionary - DDL is used to build the data dictionary, initialize or CREATE the database, describe the logical views for each individual user or programmer, and specify any limitations or constraints on security imposed on database records or fields. - DDL builds the data dictionary, creates the database, describes the subschema, and specifies record or field security constraints The set of commands used to change the database is known as data manipulation language (DML). - DML is used for maintaining/UPDATE the data, including UPDATING data, inserting data, and deleting portions of the database. - DML changes the content in the database through updates, insertions, and deletions. The set of commands used to query the database is known as data query language (DQL). - DQL is used to interrogate the database, including retrieving records, sorting records, ordering records, and presenting subsets of the database. - READ - The DQL usually contains easy-to-use, powerful commands that enable users to satisfy their own information needs. - DQL enables the retrieval, sorting, and display of data from the database.

Your friend Mary is a sales associate for Drug Co. Mary uses her company issued computer to access Drug Co.'s accounting information system when she travels across the country. She has access to view customer and product information. She also has access that allows her to enter and cancel customer orders. Mary's right to access Drug Co.'s AIS represents a(n) ________ in the company's database management system.

External-level schema

The third basic rule that applies to the REA data modeling is: Each event is linked to at least two participating agents. Identify who are internal agent and external agent.

For accountability / internal control purposes, organizations need to be able to track actions of employees. They also need to monitor the status of commitments and exchanges with outside parties. Therefore, each event links to at least two participating agents. For events that involve transactions with external parties (customers or vendors): - the internal agent is the employee responsible for the affected resource, and - the external agent is the outside party to the transaction. For internal events (within the company), such as transferring raw materials to the production floor: - the internal agent is the employee who gives up responsibility or custody for the resource, and - the external agent is the one who receives it.

Explain the difference between file-oriented transaction processing systems and relational database systems.

In file-oriented approaches, different users (or departments, units, etc.) maintain their own data and use different application programs. This results in a significant increase in number of master files stored by an organization. The various disadvantages of file-oriented organization include: - a large number of data files - data redundancy, - data inconsistencies, - lack of data integration, - substantial program-data dependence, - lack of compatibility, and - lack of data sharing. The database approach views data as an organizational resource that should be used and managed for the entire organization. The program that manages and controls the data and the interfaces between data and application programs is called the database management system (DBMS).

Model the cardinalities of the following business policies. The Sale-Inventory relationship for a custom homebuilder.

Inventory (1, 1) Sale (1, 0) Inventory --- Sale (1, 1)

Describe the difference in logical view and physical view of a database system.

Logical view is an external level. It is how people organize, view, and describe relationships among data items. Information output is based on the user's perspective, or the logical view. Physical view is an internal level. It depends on explicitly knowing how the data is actually arranged in a file and where the data is stored on the computer.

The first basic rule that applies to the REA data modeling is: Each event is linked to at least one resource that it affects. Explain what is a stockflow relationship.

Relationships that affect the quantity of a resource are referred to as stockflow relationships. Events affect the quantity of a resource = represent an inflow or outflow of the resource If they increase the quantity of a resource, they are called a "get" event inflow o Receive goods from suppliers o Receive payments from customers If they decrease the quantity of a resource they are called a "give" event outflow o Pay suppliers o Sell merchandises

Create relational tables that solves the update, insert, and delete anomalies in the table below.

Resource table: Part# PK Description NK Unit price NK Event table: Purchase Order# PK Purchase Order Date Agent table: Vendor# PK Vendor Name Vendor Address Linking table: Purchase Order# PK Part# PK Quantity Ordered

Model the cardinalities of the following business policies. The relationship between the Sale and Receive Cash events at a convenience store

Sales (1, 1) Receive Cash (1, 0) Sales --- Receive Cash (1, 1)

Model the cardinalities of the following business policies. The relationship between the Sale and Receive Cash events for installment sales

Sales (1, 1) Receive Cash (N, 0) Sales --- Receive Cash (1, N)

What is schema? Describe the different levels of schemas involved in a database structure. What is the role of accountants in development of schemas?

Schema: description of data elements in a database, relationships among them, and logical model used to organize and describe data A schema describes the logical structure of a database (logical view) Conceptual-level schema: an organization-wide view of the entire database listing all data elements and relationships between them (40,000 feet view) External-level schema: a set of individual user views of portions of the database, each of which is referred to as a subschema Internal-level schema: provides a low-level view of the database includes descriptions about pointers, indexes, record lengths, etc. Accountants are primarily involved in the development of conceptual- and external-level schemas

Model the cardinalities of the following business policies. The Take Customer Order-Sale relationship in a situation when occasionally several shipments are required to fill an order because some items were out of stock.

Take Customer Order (1, 1) Sale (N, 0) Take Customer Order --- Sale (1, N)

Describe an REA data model.

The REA data model is a conceptual modeling tool specifically designed to provide structure for designing an AIS database. The REA data model identifies what entities should be included in the AIS database and prescribes how to structure relationships among the entities in the AIS database. Focus on an organization's value-chain activities REA stands for resources, events, and agents. Resources are defined as those things that have economic value to the organization. Events are the various business activities about which management wants to collect information for planning and control purposes. Agents are the people and organizations that participate in events and about which information is desired for planning, control, and evaluation purposes.

Most DBMS packages contain a data definition language (DDL), a data manipulation language (DML), and a data query language (DQL). For each of the following examples, indicate which language would be used and why. A database administrator defines the logical structure of the database. The controller requests a cost accounting report containing a list of all employees being paid for more than 10 hours overtime in a given week. A programmer develops a program to update the fixed- assets records stored in the database. The human resources manager requests a report noting all employees who are retiring within five years. The inventory serial number field is extended in the inventory records to allow for recognition of additional inventory items with serial numbers containing more than 10 digits. A user develops a program to print out all purchases made during the past two weeks. An additional field is added to the fixed- asset records to record the estimated salvage value of each asset.

a. DDL - this is the language used to define the database. b DQL - this is an example of a query. c DML - this is the language used to actually process transaction data and update the database. d. DQL - another example of a task that involves querying the database. e. DDL to alter the structure + DML to make the change. f. DQL - such a listing can be produced by a query. g. DDL to add the field + DML to enter data in it.

Describe the cardinality using the REA diagrams in Figure 17-7.

a. One to one: Sales (1, 1) Receive Cash (1, 0) Sales --- Receive Cash (1, 1) b. One to many: Sales (1, 1) Receive Cash (N, 0) Sales --- Receive Cash (1, N) c. One to many (opposite) Sales (N, 1) Receive Cash (1, 0) Sales --- Receive Cash (N, 1) d. Many to many: Sales (N, 1) Receive Cash (N, 0) Sales --- Receive Cash (M, N)