Upgrade to remove ads
Only $2.99/month

AC 389 Final Exam

Terms in this set (118)

Database
efficiently and centrally coordinates information for a related group of files
File
Related group of records
Record
Related group of fields
Field
A specific attribute of interest for the entity (record)
Database Management Systems
What does DBMS stand for?
Database Management Systems (DBMS)
Set of computer programs that enable users to create, modify, and utilize database information................is the program that manages and controls the data and the interfaces between the data and the application programs that use the data stored in the database.
Microsoft Access
Oracle
Give two examples of Database Management Systems (DBMS)
1. Data is integrated
2. Data Sharing
3. Minimize data redundancy and inconsistencies
4. Data is independent of the programs that use the data
5. Data is easily accessed for reporting and cross-functional analysis
5 Advantages of Databases
logical views
Different users of the database information are at an external level of the database. These users have ________________ _______________ of the data.
Physical view
At an internal level of the database is the ______________ ___________ of the data which is how the data is actually physically stored in the system
user's needs
conceptual level
Designers of a database need to understand _______________ _____________ and the _________________ _________________ of the entire database as well as the physical view.
Data Definition Language
What was DDL stand for?
Data Definition Language (DDL)
the language used to create and format the fields in a database
1. Determine the field size (number of characters)
2. Assign field name
3. Set data type (text, numeric, date, etc)
4. Identify primary key
5. Establish default value
What are examples of Data Definition Language (DDL)
Data Manipulation Language
What doe DML stand for?
Data Manipulation Language (DML)
Language used for data maintenance, refers only to data names
1. Updating (change data)
2. Inserting (add data)
3. Deleting (remove data)
3 forms of Data Manipulating Language
Schema
the entire database
subschema
a subset of the database (called a view)
Queries
_____________ allow users to extract some portion of the data in the database (i.e.- a subschema)
Change
Delete
In addition to extracting data, queries can also be used to _______________ or ___________ a record.

Query answer can be used to update database (using DML)
Online Analytical Processing
What does OLAP stand for?
Online Analytical Processing (OLAP)
In addition to extracting data, _____________________ allows users to summarize data by different dimensions
--(e.g- time, customer, region)
-- uses pivot tables like excel
"Drill down"
Online Analytical Processing (OLAP) has ___________ __________ capability, which allows users to click on data and see one less level of aggregation

For example:
Total Company Sales--> Sales by District--> Sales by Store
Structured Query Language (SQL)
A standardized language used to query relational data bases

Many software packages support it, including Access
SELECT
used to identify which columns will be displayed
SELECT
If you want to see a column in your output, the column must be included in the _________________ statement
FROM
Used to identify the tables that will be queried
FROM
The tables needed to access the columns used in the SELECT and WHERE statements must be included in the _______________ statement
WHERE
used to select which two rows to retrieve

Conditions are defined using statements such as, =, <=, >=, or BETWEEN.
AND
Several conditions can be linked using what statement?
WHERE
When more than one table is included in the query, the columns on which the tables are to be linked must be identified in the ___________ statement as follows:

Table name.column name = table name. column name
SUM
the total for a column
MINIMUM
the lowest amount in a column
MAXIMUM
the highest amount in a column
COUNT
the number of observations in a column
AVERAGE
the average for a column
GROUP BY
used to identify which field is subject to a mathematical operation (subtotals)
ORDER BY
used to sort the answer in ascending or descending order (sort output)
Foreign Keys
Primary keys from another table
Insert Anomaly
Improper database design that results in an inability to add database records
Record
all the fields about one entity
Entity
Anything in an organization about which data is stored
Attribute
an _____________ and field are the same thing
Record
All of the information about a particular student (such as name, GPA, major, etc) is stored in what?
Unique
A well structured database imposes several requirements on the structure of tables.

A primary key must be _______________
Foreign Key
A well structured database imposes several requirements on the structure of tables.

A _____________ ___________ must be a primary key from another table
null (blank)
A well structured database imposes several requirements on the structure of tables.

A primary key cannot be _____________.
Create tables for each entity and each many-to-many relationship
When developing a relational database using an E-R diagram, which is done first?
Accounts Receivable
Which of the following is not considered a resource in an REA data model?

Cash
Accounts Receivable
Equipment
Inventory
SELECT
Which command is used to determine the columns in query output?
WHERE
Which command is used to limit output to only certain records (e.g- only dates in 2018)?
FROM
Which command is used to identify the table(s) where data for a query can be found?
WHERE
Which command is used to join tables in a relational database query?
GROUP BY
Which command is used for obtaining subtotals in query output?
Identify attributes including primary keys for each table
What is the second step when designing a database using an ERD?
10
If an ERD has 7 entities, 3 many-to-many relationships and 8 one-to-many relationships, how many tables are needed in the database?
flat files
data is stored in one giant table
flat files
Typically different functions have separate ________ _________ that do not link to each other
Databases
data is shared between funtions
1. hierarchial
2. network
3. relational
What are the three types of databases?
Data Modeling
Process of defining a data base so that it faithfully represents all aspects of the organization
entity
anything about which the organization wishes to store data
fields
what you want to know about an entity
records
all the fields containing data about one entity (e.g. one employee) form this
primary key
each table has a ___________ ______________
(customer #, invoice #, inventory #)

each field in the table should tie back to the ____________ ___________

the __________ __________ has to be unique (e.g- you can't have two customers with the same number)
Foreign keys
What are used to link tables?
foreign keys
these are primary keys from other tables?
Primary Key
-uniquely identifies a record
-each integrity cannot be null
REA Model
separates a business process into three types of entities
1. Resources
2. Events
3. Agents
What are the three entities of the REA Model?
Resources
An entity of the REA Model

-things of value an organization acquires and uses (e.g- cash, inventory, equipment)
Events
An entity of the REA Model

-business activities (e.g- sales order aka business event, and sales event aka economic event)
Agents
An entity of the REA Model

-people and organizations (e.g- employee, customer)
Entity-Relationship Diagram (ERD)
The 3 entities of the REA model (Resources, events, and agents) are represented in what way?
1. Identify Events (economic and business)
2. Identify resources and agents (what and who)
3. Determine the linkages between entities
4. Determine the cardinalities.
How to create an ERD?
The symbol of a circle is "Zero"
When using the crow's feet notation:

What does this symbol stand for: O
The symbol of a single stroke is "One"
When using the crow's feet notation:

What does this symbol stand for:
|
A one-to-one relationship (1:1)
What exists when the maximum cardinality on each entity in the relationship is 1?
a one-to-many relationship (1:N)
What exists when the maximum cardinality on one side is 1 and the maximum on the other side is many?
A many-to-many relationships (M:N)
What exists when the maximum on body sides is many?
1. resource
2. agents
3. disposition
acquisition`
REA Diagram Rules

1. Every event must be linked to at lease one ___________________

2. Every event must be linked to the ___________________ who participate in that event

3. Every event that involves the ________________ of a resource must be linked to an event that involves the ______________ of a resource (give-get exchange)
Database administrator
Person responsible for maintaining the database
Data dictionary
describes each field in the database
Data integrity controls
ensure accuracy of data
transaction controls
ensure transactions are performed accurately
Concurrency controls
prevent simultaneous access to a table by multiple users and ensure that transactions are performed sequentially
Journals
Information contained in event tables
Ledgers
information contained in resource tables (increment and decrements of events)
Financial statements
information contained in resources and events using a set of queries
Management Reports
Financial and non financial information
Customers may make multiple payments for a particular sale, and a cash payment can cover more than one sale
Give an example of what would represent a many to many relationship between sale and cash receipt?
databases
Database Management Systems (DBMS) provides management of ___________________
query
Database Management Systems (DBMS) contain a ________________ language to retrieve information easily
Database Management Systems (DBMS) controls ________________ to data
access
subschema
a view of the database
Data Manipulation Language (DML)
Language that includes operations such as updating, inserting, and deleting portions of the database?
SELECT
math operations create new fields for use in queries, thus are listed in what statement?
WHERE
What statement do you use to display items >200 or Quantity purchased between 1/1/18 and 6/30/18
Distinct Count
What function in a pivot table would you use to count the total number of shipping transactions (primary key ship #) in a database?
entities
relationships
It is called an E-R diagram because it shows the various _______________ being modeled and the important _______________ among them.
minimum cardinality
The minimum number of instances of one entity that must participate in a relationship
maximum cardinality
The maximum number of instances of one entity that can participate in a relationship
a, b, and 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, and d
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
C


Each sale is paid with one check, but one check can pay for multiple sales
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
Answer: b.


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
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
Answer: d.


Each sale can consist of many different inventory items (e.g., a 10 lb. bag
of Brand X dog food), and each inventory item can be part of many different sales
transactions
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
Answer: b.


Each purchase may involve multiple pieces of art, but each piece of art is
only purchased once.
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
Answer: b. Each disburse cash (payment) event to a supplier can be linked to multiple
purchases, but each purchase is paid for in full in one disbursement (the company
does not make installment payments).
A company's suppliers send it separate invoices for each purchase. It also sends the
company a monthly statement that summarizes all transactions during the
preceding 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 of
the 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
Answer: c.


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. However,
each customer may participate in many sales events
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
Answer: c.


Each Cash account can be linked to multiple Disburse Cash events, but
each Disburse Cash event is made from one, and only one, Cash account.
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
Answer: d. Only M:N relationships must be implemented as separate tables. The
other three types of relationships can be implemented using foreign keys.
Which of the following types of relationships in an REA diagram must be
implemented as a separate table in a relational database?
a. 1:1
b. 1:N
c. N:1
d. M:N
Answer: a.


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.
2. 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
Answer: a.


Merging two REA diagrams that share common resources does not
required 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. For
example, a disburse cash event could be linked to either an employee or a supplier as
the recipient of the payment, but not to both simultaneously. In addition, it may be
necessary to change the minimum cardinality of events in each cycle that are linked to
the common event to zero because the common event can only be linked to an event
in one cycle, not to two different events in two cycles.
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
Answer: c.


Tables are needed for three types of agents (customers, suppliers, and
employees), two resources (inventory and cash), and four events (purchases, disburse
cash, sales, and receive cash). There is no need for multiple tables for any of the
agents: the employee's role can be represented as an attribute in the employee table.
None of the relationships are many-to-many (M:N), so all can be implemented via
foreign keys.
ABC Company sells original manuscripts of Broadway plays. ABC pays for all its
purchases in full with one check. It allows customers to make installment payments
on sales that exceed $33,000. However, a customer cannot make another purchase
until all prior purchases have been paid for. The REA diagram for ABC company
depicts seven Employee entities, each labeled for the role played by that type of
employee (e.g., cashier, salesperson, shipping clerk, accountant, etc.). The REA
diagram also contains two resource entities (Inventory and Cash) and the following
events: Purchases, Disburse Cash, Sales, and Receive Cash. The REA diagram also
contains two instances of the Customer entity and three instances of the Supplier
entity. Implementing the integrated REA diagram for the revenue and expenditure
cycles of the ABC company in a relational database requires how many tables?
a. 5
b. 7
c. 9
d. 15
e. 18
Answer: c.


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 SalesInventory table that represents the M:N relationship between those two entities.
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
Answer: d.


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.
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
THIS SET IS OFTEN IN FOLDERS WITH...