ACC 341 Test 2 (4,17,18)

REA Rules

1) Each event is linked to at least one resource that it affects. 2) Each event is linked to at least one other event. 3) Each event is linked to at least two participating agents

Validating Integrated REAs

1) Every event must be linked to at least one resource. 2) Every event must be linked to two agents who participate in that event. 3) Every event that involves the disposition of a resource must be linked to an event that involves the acquisition of a resource. (This reflects the economic duality underlying "give-to-get" economic exchanges.) 4) Every resource must be linked to at least one event that increments that resource and to at least one event that decrements that resource. 5) 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. 6) If an event can be linked to any one of a set of agents, but cannot be simultaneously linked to all those agents, then the REA diagram should show that event is linked to a minimum of 0 of each of those agents.

Steps in REA development

1) Identify the events about which management wants to collect information. 2) Identify the resources affected by each event and the agents who participate in those events. 3) Determine the cardinalities of each relationship.

Database design process steps

1) systems analysis 2) conceptual design 3) physical design 4) Implementation and conversion 5) operation and maintenance

Basic Requirements of a Relational Database

1. Every column in a row must be single valued 2. Primary keys cannot be null 3. Foreign keys, if not null, must have values that correspond to the value of a primary key in another table 4. All nonkey attributes in a table must describe a characteristic of the object identified by the primary key

REA data model (Resource Events Agents)

A data model used to design AIS databases. It contains information about three fundamental types of entities: resources, events, and agents.

relational database

A database built using the relational data model.

Schema

A description of the data elements in a database, the relationships among them, and the logical model used to organize and describe the data.

entity-relationship (E-R) diagram

A graphical depiction of a database's contents showing the various entities being modeled and the important relationships among the

entity integrity rule

A non-null primary key ensures that every row in a table represents something and that it can be identified

2. Primary keys cannot be null

A primary key cannot uniquely identify a row in a table if it is null (blank). A nonnull primary key ensures that every row in a table represents something and that it can be identified. This is referred to as the entity integrity rule. In the Sales-Inventory table in Table 4-5, no single field uniquely identifies each row. However, the first two columns, taken together, do uniquely identify each row and constitute the primary key.

one-to-one (1:1) relationship

A relationship between two entities where the maximum cardinality for each entity is 1

one-to-many (1:N) relationship

A relationship between two entities where the maximum cardinality for one of the entities is 1 but the other entity has a maximum cardinality of many.

many-to-many (M:N) relationship

A relationship between two entities where the maximum cardinality of both entities is many.

relational data model

A two-dimensional table representation of data; each row represents a unique entity (record) and each column is a field where record attributes are stored.

With respect to database systems, which of the following statement(s) is (are) true? A) A subschema can prevent access to those portions of the database that do not apply to it. B) They're boring. C) DBMS software links the way data are physically stored with each user's logical view of the data. D) An individual user's view of portions of a database is referred to as an internal-level schema. (external to be correct)

A) A subschema can prevent access to those portions of the database that do not apply to it. C) DBMS software links the way data are physically stored with each user's logical view of the data.

Which of the following statements about the REA data model is true? A) Every event must be linked to at least two agents. B) Every resource must be linked to at least one agent. C) Every event must be linked to at least two resources. D) Every agent must be linked to at least two events.

A) Every event must be linked to at least two agents. Read REA Rules

Which of the following steps in the expenditure cycle would appear as event entities in an REA diagram? A) Ordering B) Receiving C) Approve Supplier Invoices D) Cash Disbursements

A) Ordering B) Receiving D) Cash Disbursements Approve Supplier Invoices would not be an event because it is just a data processing activity that does not add any new information to the database.

Which of the following steps in the revenue cycle would appear as event entities in an REA diagram? A) Sales Order Entry B) Shipping C) Billing D) Cash Collections

A) Sales Order Entry B) Shipping D) Cash Collections Billing would not be an event because it is just a data processing activity that does not add any new information to the database

Helge Autoparts sells new and refurbished auto parts exclusively to auto dealers. Helge purchases inventory from distributors at trade conventions. Each time Helge makes a purchase, the company records the supplier's name, address, date, specific items purchased, price paid for each, and the total amount spent. Which of the following would not be an attribute in the Purchases table? A) Supplier address B) Person's name or company name from whom the purchase was made. C) Date of purchase. D) Total amount of purchase.

A) Supplier address Supplier address does not provide information relating to purchase information. Doesn't really matter what the address is of the company you purchased the good from. When buying from amazon address is unknown.

When implementing a revenue cycle REA diagram in a relational database, the relationship between customers and sales would be implemented by . A) placing the customer number attribute as a foreign key in the sales table B) placing the sales invoice number as a foreign key in the customer table C) either approach is equally acceptable

A) placing the customer number attribute as a foreign key in the sales table A sale can be linked to only one customer, so customer number could be a foreign key in the sale table. However, a customer can be linked to many different sales, so invoice number cannot be a foreign key in the customer table.

CH4 Question 9: The constraint that all foreign keys must have either null values or the value of a primary key in another table is referred to as which of the following? A) referential integrity rule B) entity integrity rule C) foreign key value rule D) null value rule

A) referential integrity rule

When merging REA diagrams from two different cycles, it is usually not necessary to change the minimum cardinality for __________ that appear in both of the separate REA diagrams. A) resources B) agents C) events

A) resources Merging two REA diagrams that share common resources typically does not require making any changes to minimum cardinalities of those resources. However, merging two REA diagrams that share a common event often necessitates changing the minimum cardinalities of agents in different cycles that are linked to that common event to zero because the event will be linked to only one of those agents.

data model

An abstract representation of database contents

Foreign Key

An attribute in a table that is also a primary key in another table; used to link the two tables.

Entity

Anything about which an organization wants to collect and store information.

ABC company has a checking account, savings account, and payroll account with the XYZ bank. ABC company deposits all customer payments into its checking account. The relationship between Cash and Receive Cash would be modeled as being . A) 1:1 B) 1:N C) N:1 D) M:N

B) 1:N Each Cash account can be linked to multiple Receive Cash events, but each Receive Cash event is deposited into one, and only one, Cash account.

CH4 Question 7: Which of the following is a software program that runs a database system? A)DQL B) DBMS C) DML D) DDL

B) DBMS

With respect to data warehouses, databases, and files, which of the following statements is true? A) Analyzing large amounts of data for strategic decision making is often referred to as strategic processing. B) Databases were developed to address the proliferation of master files, which were created each time a need for information arose. C) Since data warehouses are only used for transaction processing, they are updated in real time rather than periodically. D) Few mainframes and servers use database technology, and database use in personal computers is growing slowly

B) Databases were developed to address the proliferation of master files, which were created each time a need for information arose.

CH4 Question 8: The constraint that all primary keys must have nonnull data values is referred to as which of the following? A) referential integrity rule B) entity integrity rule C) normalization rule D) relational data model rule

B) entity integrity rule

CH4 Question 4: Which of the following is an individual user's view of the database? A) conceptual-level schema B) external-level schema C) internal-level schema D) logical-level schema

B) external-level schema

CH4 Question 2: How a user conceptually organizes and understands data is referred to as the? A) physical view B) logical view C) data model view D) data organization view

B) logical view

CH4 Question 6: Which of the following attributes would most likely be a primary key? A) supplier name B) supplier number C) supplier zip code D) supplier account balance

B) supplier number Primary key must be unique so account balance, zip, and name cannot be used. (some companies may have the same name)

CH4 Question 1: relational data model portrays data as being stored in? A) hierarchies B) tables C) objects D)files

B) tables

events

Business activities about which management wants to collect information for planning or control purposes.

A grocery store sells to individuals and also to groups, such as the local fire department and a college fraternity. Given this set of facts, the grocery store's revenue cycle REA diagram would model the relationship between Sales and Customers as being . A) 1:1 B) 1:N C) N:1 D) M:N

C) N:1 Each sale is made to one, and only one, specific customer. Although the fire department and the fraternity may be comprised of multiple individuals, each is considered to be just one single entity with regard to business transactions.

Customers are sent monthly statements that list and total all sales transactions during the preceding month. Customers must pay the entire balance owed in full with one check. Given this set of facts, the relationship between the Sale and Receive Cash events would be modeled as being . A) 1:1 B) 1:N C) N:1 D) M:N

C) N:1 Each sale is paid with one check, but one check can pay for multiple sales

The XYZ Company sells sports equipment. The actual sales price of a given item varies throughout the year due to sales events. The actual sales price should be stored as an attribute in the___________________table. A) Sales B) Inventory C) Sales-Inventory

C) Sales-Inventory The actual sales price varies over time. Thus, it is a fact about both the item being sold and the particular sales transaction. Thus, it belongs in the Sales-Inventory table that represents the M:N relationship between those two entities

Which of the following would most likely be the foreign key in the cash receipts table? A) cash receipts number B) customer check number C) customer number D) cash receipts date

C) customer number

CH4 Question 10: Which of the following attributes in the Cash Receipts table (representing payments received from customers) would most likely be a foreign key? A) cash receipt number B) customer check number C) customer number D) cash receipt date

C) customer number Cash Receipt number would be a good Primary Key No good reason to have customer check number in another table Date is a bad foreign key

Creating an REA

Create a table for each distinct entity in the diagram and for each many-to-many relationship. Assign attributes to appropriate tables. Use foreign keys to implement one-to-one and one-to-many relationships.

CH4 Question 5: Which of the following would managers most likely use to retrieve information about sales during the month of October? A) DML B) DSL C) DDL D) DQL

D) DQL DML (Data Management Language)- Used to CRUD data DDL (Data Definition Language)- builds the data dictionary, creates the database, describes logical views, and specifies record or field security constraints DQL (Data Query Language)

In an integrated REA diagram for both the revenue and expenditure cycles, the quantity on hand during the middle of the year for an inventory item would be stored as an attribute in the table: A) Inventory B) Sales-Inventory C) Purchases-Inventory D) None of the three tables

D) None of the three tables An integrated REA diagram for the revenue and expenditure cycles shows how inventory is both acquired and sold. The quantity on hand at a particular point in time, therefore, reflects the net effect of all purchases and sales. Thus, it would not be stored as a fixed attribute, but would rather be obtained by a QUERY that summed the quantity purchased attribute in the Purchases-Inventory table and then subtracted from that the sum of the quantity sold attribute in the Sales-Inventory table, plus the quantity on hand at the beginning of the period attribute in the Inventory table.

CH4 Question 3: What is each row in a relational database table called? A) relation B) attribute C) anomaly D) tuple

D) tulple relation [Incorrect. A relation is a table in a relational database.] attribute [Incorrect. Each column in a relational database is an attribute that describes some characteristic of the entity about which data are stored.] anomaly [Incorrect. An anomaly is a problem in a database, such as an insert anomaly or a delete anomaly.] tuple [Correct. A tuple is also called a row in a relational database.]

Data Definition Language (DDL)

DBMS language that builds the data dictionary, creates the database, describes logical views, and specifies record or field security constraints

Data Manipulation Language (DML)

DBMS language that changes database content, including data element creations, updates, insertions, and deletions.

report writer

DBMS language that simplifies report creation

Primary Key

Database attribute, or combination of attributes, that uniquely identifies each row in a table.

data modeling

Defining a database so that it faithfully represents all key components of an organization's environment. The objective is to explicitly capture and store data about every business activity the organization wishes to plan, control, or evaluate.

record layout

Document that shows the items stored in a file, including the order and length of the data fields and the type of data stored.

Record

Employee name (row). can be referred to as a tuple

Merging REAs

First, combine all common resources between graphs then look to combine events

normalization

Following relational database creation rules to design a relational database that is free from delete, insert, and update anomalies

3. Foreign keys, if not null, must have values that correspond to the value of a primary key in another table

Foreign keys link rows in one table to rows in another table. In Table 4-5, Customer # can link each sales transaction with the customer who participated in that event only if the Sales table Customer # value corresponds to an actual customer number in the Customer table. This constraint, called the referential integrity rule, ensures database consistency. Foreign keys can contain null values. For example, when customers pay cash, Customer # in the sales table can be blank.

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.

data query language (DQL)

High-level, English-like, DBMS language that contains powerful, easy-to-use commands that enable users to retrieve, sort, order, and display data.

Logical View

How a user conceptually organizes and understands data.

logical view

How people conceptually organize, view, and understand the relationships among data items.

Cash:Received Cash Cardinality

If best practices are used it should by a 1:N Cash side is 1:1 because each time cash is received, it is placed into 1 and only 1 cash account. Received Cash side is 0:N because cash account still exists if no cash is collected or if many cash payments are received.

insert anomaly

Improper database organization that results in the inability to add records to a database.

update anomaly

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

delete anomaly

Improper organization of a database that results in the loss of all information about an entity when a row is deleted

1. Every column in a row must be single valued

In a relational database, there can only be one value per cell. At S&S, each sale can involve more than one item. On invoice 102, the customer bought a television, a freezer, and a refrigerator. If Item # were an attribute in the Sales table, it would have to take on three values (item numbers 10, 20, and 30). To solve this problem, a Sales-Inventory table was created that lists each item sold on an invoice. The third line in the Sales-Inventory table in Table 4-5 shows invoice 102 and item number 10 (television). The fourth line shows invoice 102 and item 20 (freezer). The fifth line shows invoice 102 and item 30 (refrigerator). This table repeats the invoice number as often as needed to show all the items purchased on a sales invoice.

agents are analogous to

Master File

Effects of merging

Merging resources does not change cardinalities Merging Events does change cardinalities. In order for the minimum to be 1, each instance of that entity must be associated with at least on instance of another entity. When Event Entities are joined, it is possible for that event to take place with out affecting the other entity Example: The Event Entity "Cash Disbursements" can be used to pay a supplier for receiving inventory or can be paid to employees based off time worked. When a supplier is paid, it does not effect employees so there is no association thus deeming no association which breaks the original cardinality of a minimum of 1.

4. All nonkey attributes in a table must describe a characteristic of the object identified by the primary key.

Most tables contain other attributes in addition to the primary and foreign keys. In the Customer table in Table 4-5, Customer # is the primary key, and customer name, street, city, and state are important facts that describe the customer.

referential integrity rule

The constraint that all foreign keys must have either null values or the value of a primary key in another table.

maximum cardinality

The maximum number of instances that an entity can be linked to the other entity in the relationship. Only two options: 1 or many.

minimum cardinality

The minimum number of instances that an entity can be linked to the other entity in the relationship. Only two options: 0 and 1.

conceptual-level schema

The organization-wide view of the entire database that lists all data elements and the relationships between them.

agents

The people and organizations who participate in events and about whom information is desired. Usual agents are customers (external), Suppliers (external) Employees (internal). If its an internal event, the internal agent is who is giving up the custody of a resource and the external is the agent receiving the resource

resources

Those things that have economic value to an organization such as cash, inventory, supplies, factories, and land.

events are analogous to

Transaction file

concatenated keys

Two or more primary keys of other database tables that, together, become the unique identifier or primary key of an M:N relationship table.

Agent:Event Cardinality

Typically 1:N Agent side is typically a 1:1 relationship because an order is placed by 1 external employee and for accountability reasons is handled by 1 sales agent (if event required a team of employees it can be many) Event side is 0:N because employees and customers still exist if they do not place/take an order. Employees are expected to take more then one order when working for a company and customers may make multiple orders

Inventory:Event Cardinality

Typically N:M for mass produced goods Resource (inventory) side is typically a 1:N relationship because in order for there to be a sale, there must be at least a change of 1 unit of inventory and can be a maximum of many if a company only sells physical inventory. Events side is typically 0:N relationship because a unit of a kind of inventory may not be involved in a sale or many units of a kind of inventory may be involved. custom or specialty items like one of a kind art change the events side to a 0:1 relationship because multiple sales cannot occur for that specific inventory

semantic data modeling

Using knowledge of business processes and information needs to create a diagram that shows what to include in a fully normalized database (in 3NF).

3NF (Third Normal Form)

When database is free of update, insert, and anomalies

Event:Event Cardinalities

When moving from top to bottom, the cardinality minimum related to the proceeding event will be 0 because of timing issues. For example with Order:Sales has a min of 0 there is a time delay between when an order is taken until the point of sale (orders that have not been filled yet) and not every sale must come from a customer order. Order:Sales max- each order is considered one sale and each order is sold once (dont sell someones order to multiple people) For Sale:Receive Cash, minimum on received cash side is 0 if credit is extended. If credit is not extended it will be a 1 because customers must pay for each order before leaving store (common with retail)

internal-level schema

a low-level view of the entire database describing how the data are actually stored and accessed

Tuple

a row in a table that contains data about a specific item in a database table

File

a set of related records

subschema

a subset of the schema; the way the user defines the data and the data relationships

Query

a tool used to retrieve specific information from a database

external-level schema

an individual user's view of portions of a database

external-level schema

an individual user's view of portions of a database; also called a subschema

Field

attributes (columns)

data dictionary

information about the structure of the database, including a description of each data element

Look at review powerpoint slide 15

look at slide 15

physical view

the way data are physically arranged and stored in the computer system