Acct 340 Exam 2

Fundamental economic events of the Acquisition/payment cycle

-Get inventory -Give cash

Expenditure cycle other names

-Purchasing -Acquisition/payment -Purchase to pay -Procurement -Get product/Give cash

Common heuristics (cardinalities):

-Resource(0,1) -- (1,N)Economic Event Ex: Car (only one sold) -Economic Event(1,1) -- (0,N)Agent -Resource type(0,N) -- (1,N)Economic Event Ex: Stuff animals (multiple can be sold)

Revenue cycle other names

-Sales/Collection -Quote to cash -Fulfillment -Give product/Get cash

SQL

-Structured Query Language -lingua franca of database querying -specifies the tables, attributes, and operations performed on them

Relational databases

-based on set theory and predicate logic -the resultant relations (tables) can be manipulated for information retrieval purposes if properly constructed (i.e. queries)

Left outer join (CarStar)

-connecting the primary keys in 2 tables (bolded line with arrow) -includes all the records from the left table and only the records from the right table where the joined fields are equal -Venn diagram: the whole left circle (which includes the joint middle) -Count total and criteria of <1 goes under the table the arrow is pointing to

Inner join (CarStar)

-connecting the primary keys in 2 tables (regular line) -joins the rows in the tables that have matching/equal values, excluding the unmatched rows -Venn diagram: the inner (joined portion) is shaded

3 main relationship types in the REA ontology

-duality (between the 2 events) -stockflow (between resources and events) -participation (between events and agents)

Database components

-entities -relationships -attributes -cardinalities

Principles of relational databases

-entity integrity -referential integrity -one fact, one place

Enterprise ontology

An attempt to define what kinds of things in enterprises need to be represented

Suppose a manager says it would be easier to interpret the differences between dates if the difference attributes followed the respective date attributes. The most efficient (uses the fewest keystrokes and mouse clicks) way to obtain the desired display while keeping all the attributes in the query result is to:

Reorder the attribute columns in Design View.

Basic REA Diagram for Revenue (Sales/Collection) Cycle

Top row: Cash, (stock inflow), Cash receipts, (participation), Cashier and Customer (Economic duality) connects the 2 events Bottom Row: Inventory, (stock outflow), Sales, (participation), Salesperson and Customer

Business process level: The basic REA model (McCarthy)

Top row: Resource A, (stock inflow), GET Event resource A, (participation), Internal and External Agent Bottom Row: Resource B, (stock outflow), GIVE Event resource B, (participation), Internal and External Agent (Economic duality) connects the 2 events

External agents

external business partners

Redundancy

one fact is multiple places or multiple facts in one place

Load

percentage of non-null values in a foreign key's column

Enterprise

a business, an industrious effort, especially directed toward making money

Business process level

a pattern to which the reality of most (perhaps all) enterprises conform -REA model for rev, exp

Internal agent

act of behalf of the enterprise

Ontology

attempt to define what things exist in the world in general; a branch of metaphysics dealing with the nature of being

Value chain level

business processes of an enterprise connected via the resource flows between the processes -shows interconnection -think of diagram

concatenated primary key

combination of primary keys from each entity in the relationship

REA diagram (general definition)

conceptual representation of business semantics underlying an organizations value chain and value system -provides guidance for database design -captures the relationships among resources, events, and agents -combination of object and script patterns

delete anomaly

delete one item, could lose all the attributes/fields

Type image (extension to REA model)

for basic objects allows specification of policies and controls plus abstract specification of negotiation components

insert anomaly

if key attributes/fields are not yet identified, can't add it later

Update anomaly

if one fact changes, many records must be updated

Queries - "Horizontal" Caluculations

mathematically combine values from different fields for each row

Max cardinality

maximum number of times one instance of an entity set may participate in a relationship "1" = one-time only participation "N" = participation as many times as needed

Min cardinality

minimum number of times one instance of an entity set must participate in a relationship "0" = optional participation "1" = mandatory participation

One fact, one place

only one instance of a pairing of a candidate key attribute value with another attribute value per cell

Decrement (give) economic events result in:

outflow

Foreign key for the table

primary key from a different table that has been posted into the table to create a link between the two tables

Increment (get/take) economic events result in:

resource inflow

The following query is intended to find: (left outer join from CStarSale to DlrSale- DlrSale has a Count total and criteria of <1)

saleIDs in CStarSale that are not in DlrSale.

Object patterns

stereotypical groupings of things and relationships between them

Primary key for the table

the field that uniquely identifies the rows (records) in the table no duplicates are permitted (indexed: yes (no duplicates))

1NF

table contains no repeating field groups

Normalization

-Bottom-up approach -incomplete approach to building databases

3 main entity types in the REA ontology

-resources (R) -events (E) -agents (A)

REA is a:

-top-down -logical database design -E-R (entity relationship) diagram (ERD)

Approach to identifying data appearing in one table but not in another table (CarStar)

1. copy the table 2. enter a test sale in the first available row 3. create a query using this table and the other table 4. create a left outer join -populate query with the joint field -change the other tables info to Count total and criteria of <1 -run the query -the sale you created should be the only one that appears

Value system level

examines the enterprise in context of its external business partners -forms supply chain

Key factors in determining whether or not a separate table should be created

redundancy and load

Script patterns

stereotypical sequences of events (think scenes, actors, props, and roles).

Queries - Aggregate Functions

summarizes the data values within a column -count -average -sum -min -max

3NF

table is in 1NF and 2NF and all the non-Pkey attributes are independent from each other (no transient dependencies) Any 3NF table will have: -no update anomaly -no insert anomaly -no delete anomaly

2NF

table is in 1NF and every non-Pkey attribute is fully dependent on the primary key

Static derivable attribute

will not change when new data is entered -should be stored

Entity integrity

-A primary key in a table must not contain a null value -guarantees uniqueness of entities and enables proper referencing of primary key values by foreign key values

Fundamental economic events of the Sales/collection cycle

-Get cash -Give inventory

Attributes

-fields -data that is useful for business decisions -simple and composite

Typification relationships

-link resources, events, agents, and commitments to "entity-types" -needed when attributes of category level entities need to be stored -categorizing something into a group

Task level

-many different possible "scripts" or patterns exist -documented using flowcharts, BDP, etc.

Summary of creating normalized relational databases in normal form (3NF)

1. create a table for every entity 2. for many-to-many relationships: create a new table whose Pkey is a composite of the Pkeys (concatenated key) from the two tables involved in the N-N 3. for one-to-many max relationships: post the Pkey of the "N" table to the "1" table - the "N" becomes a foreign key in the "1" table (think of Access - just dragging the primary key to the foreign key in the other table to create a relationship between the 2 tables)

Steps to create an REA Model:

1. identify economic exchange event 2. attach resources to the economic events 3. attach external and internal agents to economic events 4. assign attributes to entities and relationships 5. assign the cardinalities 6. validate the model

To find sales in DlrSale with no matching sales in CStarSale requires:

A left outer join on saleID from DlrSale to CStarSale.

Composite attributes

Attributes that can be broken down into simple attributes -Ex: Address into street, city, etc.

Simple attributes

Attributes that cannot be broken down further -Ex: social security numbers

The purpose of the following query is to identify saleIDs in (left outer join from DlrSale to CStarSale - CStarSale has a Count total and criteria of <1):

DlrSale that are not in CStarSale. saleIDs in DlrSale that are not in CStarSale.

Your assistant said that when I put DateDiff("n", [DlrSale]![saleDate], [CStarSale]![saleDate]) in an expression, Design View shows: (expression builder that has "Expr1:" as the title for the expression) What should I do now? You reply: Group of answer choices

Give the expression a meaningful name.

Although customerID would be the primary key in a customer table, in the DlrSale table, it is defined as the figure below shows. This definition: (indexed: No)

Permits multiple sales to a customer. When it says "No" next to indexed that means that duplicates are allowed - customerID is not a primary key in this table, therefore it can permit multiple sales

Your assistant is unable to make expressions that find minimums and maximums of attribute values. To help your assistant, you say:

Put instances of the attributes in the query, open the Totals row, and set the parameters.

Basic REA Diagram for Expenditure (Purchase/Payment) Cycle

Top row: Cash, (stock inflow), Cash disbursements, (participation), Cashier and Supplier (Economic duality) connects the 2 events Bottom Row: Inventory, (stock outflow), Purchase, (participation), Buyer and Supplier

An economic exchange event is when

a resource is either given up or taken

Volatile derivable attribute

change if new data is entered -should be calculated when needed

Information system

A set of interconnected channels for communicating knowledge of specific events or situations

Referential Integrity

A value for a foreign key in a table must either: --be null (blank) --match exactly a value for the primary key in the table from which it was posted -secondary key values must also be in a foreign table

The best statement about the following expression is that:saleTimeDiff: DateDiff("n",[DlrSale]![saleDate],[CStarSale]![saleDate])

Both "[DlrSale]!" and "[CStarSale]!" are required. Bc you have to identify where the SaleDate is coming from - if you just had SaleDate you wouldn't be able to differentiate between the two tables

The following expression gives positive values when:DateDiff("d",[DlrSale]![saleDate],[CStarSale]![saleDate])

CStarSale.saleDate occurs after DlrSale.saleDate. Date2 (CStarSale.saleDate) must come after date1 (DlrSale.saleDate)

Suppose you created a query with a left outer join on the CStarSale table to the DlrSale table on the attribute saleID. After both saleID attributes have been made available to the query, the number of rows in the result will be the number of rows in the:

CarStar table