AIS Exam Two
What is the annual cost of Bad Data?
$600 billion
What are the three main uses for REA Diagrams to retrieve information from a database?
- Create Journals and Ledgers - Generate Financial Statements - Create Managerial Reports
What are the types of DBMS Languages?
- Data Definition Language (DDL) - Data Manipulation Language (DML) - Data Query Language (DQL)
What are the Advantages of a Database System?
- Data Integration - Data Sharing - Minimal Data Redundancy and Data Inconsistencies - Data Independence - Cross Functional Analysis
What are the essential features of an REA?
- Each event is linked to at least one resource that it affects - Each event is linked to at least one other event - Each event is linked to at least two participating agents
What are the Basic Requirements of a Relational Database?
- Every column in a row must be single valued - Primary keys cannot be null (entity integrity rule) - Foreign keys, if not null, must have values that correspond to the value of a primary key in another table (Referential integrity rule) - All non-key attributes in a table must describe a characteristic of the object identified by the primary key
Which of the following steps in the expenditure cycle would appear as event entities in an REA diagram? - Ordering - Receiving - Approve Supplier Invoices - Cash Disbursements
- Ordering - Receiving - Cash Disbursements
What entities are merged when individual REA diagrams are combined?
- Redundant resource entities - Redundant event entities
Which of the following steps in the revenue cycle would appear as event entities in an REA diagram? - Sales Order Entry - Shipping - Billing - Cash Collections
- Sales Order Entry - Shipping - Cash Collections
Two types of MS SQL Server Security Methods
- Windows Authentication - SQL Server Authentication
An REA diagram contains four instances of the Employee entity. How many tables does this require in a relational database? - 1 - 2 - 3 - 4
1
Steps for Implementing an REA diagram in a Relational Database
1. Create a table for each distinct entity in the diagram and for each M:N relationship 2. Assign attributes to appropriate tables 3. Use foreign keys to implement one-to-one and one-to-many relationships 4. Completeness Check
Steps in Developing an REA Diagram
1. Identify Relevant Events 2. Identify Resources and Agents 3. Determine Cardinalities of Relationships
Steps in the Normalization Process
1. Remove all repeating groups to create the 1nf and select the primary key. 2. Remove partial dependencies (attributes dependent on part of the primary key) to create the 2NF. 3. Remove all transitive dependencies (nonprimary key attributes dependent on nonprimary key attributes).
A company's suppliers send it separate invoices for each purchase. It also sends the company a monthly statement that summarizes all transactions during thepreceding calendar month. Sometimes a supplier offers a discount if a specific invoice is paid in full within 15 days; in such cases, the company takes advantage ofthe discount. Otherwise, the company pays the full amount listed on the monthly statement within seven days of receiving the statement. Given this set of facts, the relationship between the Disburse Cash and Purchase events in the company's expenditure cycle REA diagram would be modeled as being ____. A. 1:1 B. 1:N C. N:1 D. M:N
1:N
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
1:N
An art museum only purchases and displays original pieces of art. It sometimes purchases several works of art from the same artist at the same time. The REA diagram for the art museum's expenditure cycle would model the relationship between Purchases and Inventory as being ____. a. 1:1 b. 1:N c. N:1 d. M:N
1:N
In most cases, the relationship between agent entities and event entities is? - 1:1 - 0:N - M:N - 1:N
1:N
When merging REA diagrams from two different cycles, it is not necessary to change the minimum cardinality for _______ that appear in both of the separate REA diagrams. a. resources b. agents c. events
A
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,b,d
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,b,d
"A" in REA
Agents
Combining two REA diagrams typically does not involve merging which type of entity? - Resources - Events - Agents - All of these
Agents
Which of the following elements of a traditional AIS can be derived from queries of an REA database? - Journals - Ledgers - Claims - All of These
All of These
Which of the following types of entities must become a separate table in a relational database? - Resources - Events - Agents - All of These
All of These
Every event that involves the disposition of a resource must be linked to:
An event that involves the acquisition of a resource
What is Business Intelligence?
Analyzing large amounts of data for strategic decision making
What is the normalization approach?
Assumes that everything is initially stored in one large table
What is a Foreign Key?
Attribute in a table that is also a primary key in another table and is used to link the two tables
Data elements that define an entity
Attributes
What is Data Definition Language (DDL)?
Builds the data dictionary, creates database, describes logical views for each user, specifies record or field security constraints
What is Data Manipulation Language (DML)?
Changes database content, including data element creations, updates, insertions, and deletions
What is a Database System?
Collectively, the database model, the DBMS, and the application programs
The organization-wide view of the entire database, lists all data elements and the relationships among them
Conceptual Schema
Data warehouse processing does not include
Condensing data
What is a data dictionary?
Contains information about the structure of the database (contains metadata)
Main DDL Statements
Create, Alter, Drop
(Which Language) Database administrator defines the logical structure of the database
DDL
(Which Language) Additional field is added to the fixed-asset records to record the estimated salvage value of each asset
DML
(Which Language) Inventory serial number is extended in inventory records to allow for recognition of items with more than 10 digits
DML
(Which Language) Programmer develops a program to update the fixed assets records stored in the database
DML
(Which Language) Controller requests a cost accounting report containing a list of all employees being paid for more than 10 hours of overtime
DQL
(Which Language) HR Manager requests a report noting all employees retiring within 5 years
DQL
(Which Language) User develops a program to print out all purchases made in the past week
DQL
Could be described as the blueprint of the database
Data Dictionary
Enables app programs to interact with and manipulate the data
Data Manipulation Language (DML)
Abstract representation of database contents
Data Model
____ occurs when a specific file is reproduced for each user who needs access to the file reproduces data
Data Redundancy
The task of searching the database to locate a stored record for processing
Data Retrieval
Insertion anomaly
Data cannot be added to a table because of its dependence on something else
What is meant by the Database Approach?
Data is pooled into a common database that is shared by all programs and users
Acts as an interface between the database and the various application progrmas
Database Management System (DMS)
What is a Primary Key?
Database attribute, or combination of attributes, that uniquely identifies a specific row in a table
What is meant by the flat file approach?
Database is used only by one application and is not shared with others
Main DML Statements
Delete, Insert, Update
What is a schema?
Description of the data elements in a database
Database Administrator duties involve all except: - Database Planning & Implementation - Design of Application Programs - Operation & Maintenance - Change
Design of Application Programs
What is the Semantic Data Modeling Approach?
Designer uses knowledge of business processes and information needs to create a diagram that shows what to include in the database
Tuple
Each row in a table, contains data about a specific item in a database table
What is the goal of normalization?
Eliminate anomalies and get to Third Normal Form 3nf
The constraint that all primary keys must have nonnull data values is referred to as which of the following? - entity integrity rule - relational data model rule - normalization rule - referential integrity rule
Entity Integrity Rule
"E" in REA
Events
The "E" in REA stands for:
Events
Individual user's view of portions of a database, each of which is referred to as a subschema
External-Level Schema
T/F: Accountants have no real reason to care about databases.
False
T/F: An Excel file can be a database
False
T/F: Data dictionaries contain the entire dictionary stored in a table
False
T/F: Data entered into the data warehouse must be normalized.
False
T/F: In two-tier architecture, the database and application functions are separated
False
T/F: Normalizing your database puts it at risk to the three database anomalies.
False
T/F: Under the REA approach, support events may directly affect a change in resources
False
A database is a related group of _____
Files
Links rows in one table to rows in another table
Foreign Keys
A table is in its first normal form when it is:
Free of repeating group data
What is Data Query Language (DQL)?
High-level, English-like language that contains powerful, easy-to-use commands that enable users to retrieve, sort, order and display data
Which of the following is a DML Statement? - WHERE - CREATE - INSERT
INSERT
When is a table in 2nf?
In 1nf and eliminated partial dependency
What is a Database Administrator?
Individual responsible for coordinating, controlling, and managing the database
The general ledger master file is an (output, input) of the financial reporting system
Input
Low-level view of the database, describes how the data are stored and accessed
Internal-Level Schema
Which of the following tables would most likely have a concatenated primary key? - Sales - Inventory - Inventory-Sales - None of These
Inventory-Sales
The view of a database system that shows how a user or programmer conceptually organizes and understands the data
Logical View
The REA diagram for the revenue cycle of a pet store would represent the relationship between Sales and Inventory as being ____. a. 1:1 b. 1:N c. N:1 d. M:N
M:N
What is the logical view?
Manner in which people conceptually organize and understand the relationships among data items
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
N:1
ABC company has a checking account, savings account, and payroll account with the XYZ bank. The REA diagram for ABC's expenditure cycle would model the relationship between Disburse Cash and Cash as being ____. A. 1:1 B. 1:N C. N:1 D. M:N
N:1
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 - N:1 - 1:1 - 1:N - M:N
N:1
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
N:1
What are the approaches to Database Design?
Normalization and Semantic Data Modeling
Every resource must be linked to:
One event that increases the resource and one that decreases the resource
What is a Data Warehouse?
One or more very large databases containing both detailed and summarized data for a number of years that is used for analysis rather than transaction processing
What does OLAP stand for?
Online Analytics Processing
______ Handles large numbers of relatively simple transactions
Online Transaction Processing Programs
What is an Agent?
People and organizations that participate in events and about whom information is desired for planning, control, and evaluation purposes
Identify the attribute below that would not make a good primary key - Phone Number - Computer Generated Number - Postal Code - SSN
Postal Code
Data Modeling
Process of defining a database so that it faithfully represents all aspects of the organization, including interactions with the external environment
What is a Database Management System (DBMS)?
Program that manages and controls the data and the interfaces
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 Layout
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? - referential integrity rule - entity integrity rule - foreign key value rule - null value rule
Referential Integrity Rule
What is the physical view?
Refers to the way data are physically arranged and stored in the computer system
Relational Data Model
Represents conceptual and external-level schemas as if data are stored in two dimensional tables
"R" in REA
Resources
Update Anomaly
Result of data redundancy
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. - Inventory - Sales-Inventory - Sales
Sales-Inventory
Main DQL Statements
Select, Show, Help
What is the Report Writer?
Simplifies report creation
What is the most common tradeoff in a database decision?
Speed vs. something else (accuracy, quality)
Which of the following attributes would most likely be a primary key? - supplier zip code - supplier name - supplier number - supplier account balance
Supplier Number
A characteristic of the management reporting system (MRS) is
The MRS focuses on internal decision-making information
The installation of a database management system is likely to have the least impact on - the logic needed to solve a problem in an application program. - entity-wide sharing of common data. - exclusive ownership of data. - data redundancy. - the internal controls over data access.
The logic needed to solve a problem in an application program
What is a Resource?
Things that have economic value to an organization
What is a fully normalized database called?
Third normal form (3nf)
Which duty is not the responsibility of the database administrator?
To design application programs
T/F: At least two REA agents participate in each economic event
True
T/F: Data sharing (the absence of ownership) is the central concept of the database approach.
True
T/F: Each ECONOMIC event is associated with at least one internal agent and one external agent who participate in the exchange.
True
T/F: Each event is linked to at least one other event
True
T/F: GL access controls are a SOX requirement
True
T/F: Normalization involves identifying and remodeling structural dependencies from tables being modeled
True
T/F: Primary keys are the characteristics that uniquely identify each record in a table.
True
T/F: REA modeling embraces two classes of events: economic events and support events
True
T/F: The primary key of the entity that can be linked to multiple instances of the other entity must become a foreign key in that other entity.
True
T/F: Tuples are database records.
True
T/F: XBRL is based on XML
True
The formal name for a row in the physical database table is
Tuple
In the relational data base model, how is data represented?
Two-dimensional tables
Deletion anomaly
Unintentional Deletion of data
What functions allow data to be selected from multiple tables into the same query?
Union, Join
What are the types of anomalies?
Update, Insertion, Deletion
What is Online Analytical Processing?
Using queries to investigate hypothesized relationships among data
What is Data Mining?
Using sophisticated statistical analysis, including artificial intelligence techniques such as neural networks to discover unhypothesized relationships in data.
What is an Event?
Various business activities about which management wants to collect information for planning or control purposes
What does "allow nulls" mean?
Whether or not a record can be left empty
What is the preferred method of MS SQL Security?
Windows Authentication (better policies and easier cleanup when employees leave)
An REA diagram contains four instances of the Employee entity. How many tables does this require in a relational database? a. 1 b. 2 c. 3 d. 4
a
If customers pay for each sales transaction with a separate check and are not permitted to make installment payments on any sales, then the relationship between the Sale and Receive Cash events would be modeled as being which of the following? a. 1:1 b. 1:N c. M:N d. 0:N
a
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
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
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
With respect to relational databases, which of the following statement(s) is (are) true? a. The referential integrity rule ensures that there are no update anomaly problems with the foreign keys. b. A delete anomaly occurs when deleting a row has unintended consequences. c. Each relational table row is called a tuple and contains data about a specific item in the table; each column contains data about an attribute of that item. d. The normalization process is used to create a set of relational tables in 3NF. e. In a relational database, there can be no more than two values per cell.
a, b, c, e
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. A report writer is a high-level, English-like language with powerful, easy-to-use commands to help sort, order, and update data. c. The DDL and DML should be restricted to authorized administrators and programmers; the DQL is available to users. d. DBMS software links the way data are physically stored with each user's logical view of the data. e. An individual user's view of portions of a database is referred to as an internal-level schema.
a, c, e
With respect to databases and files, which of the following statement(s) is (are) true? a. There is a record in the data dictionary that describes each file in the database. b. The DQL is used to change the database, such as creating, deleting, and updating records. c. A schema describes data elements and the relationships among them in a user's logical view of the data. d. In file-based systems, programmers do not need to know the physical location and layout of records like they do in databases. e. A DBA can change physical storage to improve system performance without affecting users or application programs.
a, c, e
With respect to database systems, which of the following statement(s) is (are) true? a. A record layout shows the items stored in a file, including the type of data stored and both the order and length of the data fields. b. The physical view of data is how people conceptually organize and understand the relationships among data items. c. A DBMS allows users to query or update a database without knowing where data are actually stored. d. The DML builds the data dictionary, creates the database, describes logical views for each user, and specifies security constraints. e. The external-level schema is the organization-wide view of a database and lists all data elements and the relationships among them.
a,c
Every event must be linked to ___ resource(s) and ___ agent(s)
at least one resource, at least two agents
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
Data modeling occurs during which stages of database design? a. systems analysis and physical design b. systems analysis and conceptual design c. conceptual design and implementation and conversion d. physical design and implementation and conversion
b
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
How many tables are needed to implement an REA data model that has seven distinct enities, three M:N relationships, and five 1:N relationships in a relational database? a. 7 b. 10 c. 12 d. 15
b
In most cases, the relationship between agent entities and event entities is . a. 1:1 b. 1:N c. M:N d. 0:N
b
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
The relational data model portrays data as being stored in: a. hierarchies b. tables c. objects d. files
b
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
Which of the following is a software program that runs a database system? a. DQL b. DBMS c. DML d. DDL
b
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
With respect to data warehouses, databases, and files, which of the following statement(s) is (are) true? a. The database controller (DBC) is responsible for coordinating, controlling, and managing the database. b. Since strategic decision making requires access to large amounts of historical data, organizations are building separate databases called data warehouses. c. Data warehouses minimize redundancy, while transaction processing databases are purposely redundant to maximize query efficiency. d. Data validation controls that ensure input accuracy are one of the most time-consuming and expensive steps in creating a data warehouse. e. While it is important to regularly backup transaction processing databases, that is not the case with data warehouses.
b
With respect to data warehouses, databases, and files, which of the following statement(s) is (are) true? a. A problem with databases is data inconsistencies, as the same data is stored in two or more master files. b. A database system consists of the database, the DBMS, and the application programs that access the database through the DBMS. c. Data mining is using queries to investigate hypothesized relationships among data. d. A data warehouse is one or more very large databases containing both detailed and summarized data for a number of years. e. Data sharing refers to data and the programs that use them being independent of each other; each can be changed without changing the other.
b,d,e
A business orders mass-produced merchandise frequently throughout the year. In which table should the attribute "quantity ordered" appear? a. Order Inventory b. Inventory c. Order Inventory-Inventory d. none of the above
c
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
Combining two REA diagrams typically does NOT involve merging which type of entity? a. resources b. events c. agents d. all of the above
c
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
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
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
Which of the following is NOT likely to be depicted as an entity in the REA data model? a. customers b. sales c. invoices d. delivery trucks
c
Which of the following most accurately models the sales of low-cost, mass-produced items by a retail store?
c
Which of the following statements is true only about an integrated REA data model? a. Every event must be linked to at least two agents. b. Every increment (Get) event must be linked to a decrement (Give) event. c. Every resource must be linked to at least one increment event and at least one decrement event. d. Every resource must be linked to at least one agent.
c
Which of the following tables would most likely have a concatenated primary key? a. Inventory b. Sales c. Inventory-Sales d. none of the above
c
Which type of relationship cardinality must be implemented in a relational database as a separate table? a. 1:1 relationship b. 1:N relationship c. M:N relationship d. all of the above
c
With respect to data warehouses, databases, and files, which of the following statement(s) is (are) true? a. Cross-functional analysis refers to combining master files into larger pools of data so more application programs can access the data. b. In the master file approach, data is an organizational resource used by and managed for the entire organization, not just the originating department. c. Data warehouses complement transaction processing databases by providing support for strategic decision making. d. OLAP is using sophisticated statistical analysis to find unhypothesized relationships in data. e. To avoid outdated, incomplete, or erroneous data, management needs policies and procedures that ensure scrubbed data.
c,e
With respect to data warehouses, databases, and files, which of the following statement(s) is (are) 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. A DBMS manages and controls the interface between stored data and the application programs that use the data. d. Since data warehouses are only used for transaction processing, they are updated in real time rather than periodically. e. Few mainframes and servers use database technology, and database use in personal computers is growing slowly.
c. A DBMS manages and controls the interface between stored data and the application programs that use the data.
Accounts Receivable would appear in an REA diagram as an example of which kind of entity? a. resource b. event c. agent d. none of the above
d
In a relational database designed according to the REA data model, information traditionally stored in ledgers can be obtained by querying which of the following? a. resources b. events c. M:N relationship tables between resources and events d. a set of queries involving all of the above
d
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
The REA diagram for the revenue cycle of a pet store would represent the relationship between Sales and Inventory as being: a. 1:1 b. 1:N c. N:1 d. M:N
d
What is each row in a relational database table called? a. relation b. attribute c. anomaly d. tuple
d
Which of the following elements of a traditional AIS can be derived from queries of an REA database? a. journals b. ledgers c. claims (receivables and payables) d. all of the above
d
Which of the following types of entities must become a separate table in a relational database? a. resources b. events c. agents d. all of the above
d
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
A file is a related group of ______
records
What is a database?
set of interrelated, centrally coordinated files
