Deck 10: View Integration and Implementation Compromises

Question 1

One reason for creating separate business process level REA models for each transaction cycle is that each cycle represents a manageable set of entities.

Answer 1

True

Question 2

The conceptual models for each business process view should be converted to logical level models before they are integrated into a common view.

Answer 2

False

Question 3

Some information needs require data from multiple business processes.

Answer 3

True

Question 4

View integration may be used in the normal course of database design for a single enterprise; alternatively it may be used to consolidate separate databases as a result of a corporate merger, acquisition, or other form of business consolidation.

Question 5

The first step in view integration is to examine each relationship in the model and resolve any relationship conflicts.

Question 6

Entity name conflict occurs when the same entity in different conceptual models is not labeled identically.

Question 7

Homonyms are two or more different words used to describe the same thing.

Question 8

When an entity participates in multiple relationships and it isn't convenient to place the relationships close to each other, a copy of the entity may be portrayed with a diagonal slash in the bottom right corner of the entity and no attributes attached to the entity copy.

Question 9

Relationship structure conflicts exist when the same relationship is assigned different cardinalities in different views.

Question 10

Implementation compromises are made only at the physical implementation level of database design.

Question 11

Exclusion of an entity or relationship because of inadequate measurement mechanisms is one example of a conceptual level modeling implementation compromise.

Question 12

Conceptually congruent events are pairs or groups of events that always occur simultaneously.

Question 13

A compromised implementation in which conceptually congruent events have been consolidated contains more tables than an uncompromised implementation for the same enterprise.

Question 14

A compromised implementation in which tasks are materialized as events contains more tables than an uncompromised implementation for the same enterprise.

Question 15

A theoretically pure relational database should never allow a null value in a table.

Question 16

Reduction of query complexity is one reason for compromising logical level models for relationships for which posting foreign keys result in a relatively low percentage of null values.

Question 17

Inability to enforce referential integrity is a disadvantage of the combination of entity sets without generalization.

Question 18

A database table to which the event history rollup compromise has been applied has more rows than the same table had before the compromise.

Question 19

A database query to calculate the quantity of inventory on hand should include both beginning and ending date constraints.

Question 20

Because most resources flow between business processes, most queries to calculate balances on hand of resources require data from tables in multiple business processes.

Question 21

Which of the following statements regarding view integration of transaction cycle (business process) level REA models is true?

A) They are always integrated based on a common inventory entity
B) They are only integrated based on an agent entity
C) All transaction cycles include cash, thus the cash entity serves as the only focal point for view integration
D) They may be integrated based on any common entity, regardless of the type of the entity
E) Whenever they share a common resource entity they also always share a common relationship involving that resource entity; both the entity and the relationship serve as integration points

Question 22

Using the following tables extracted from Carlisle Company's database, calculate the ending cash balance for the checking account Carlisle has located at First Union. (Assume all beginning balances are zero)

A) $2,368,000
B) $1,847,100
C) $1,292,900
D) $ 520,900
E) None of the above

Question 23

Using the following tables extracted from Carlisle Company's database, calculate the cost of goods sold for the time period covered in these tables, assuming Carlisle uses weighted average unit costing and assuming that all information in the database is up to date.

A) $1,865,000
B) $ 640,000
C) $2,510,000
D) $4,672,000
E) None of the above

Question 24

Data about the Cash Receipts activity is needed to calculate which of the following GAAP financial reporting numbers?

A) Accounts Receivable
B) Allowance for Uncollectible Accounts
C) Accounts Payable
D) Fixed Assets
E) Sales

Question 25

Which of the following attributes would NOT need to be included in a query (or set of queries) to determine Cost of Goods Sold (COGS) for an income statement according to the weighted average inventory costing method?

A) Beginning date for income statement period for which COGS is needed
B) Actual unit costs of each inventory item on each purchase
C) Quantities sold of each inventory item on each sale
D) Actual unit selling prices of each inventory item on each sale
E) Quantities purchased of each inventory item on each purchase

Question 26

The resource that is most likely to be found in multiple business processes in all types of organizations is

A) Cash
B) Employee
C) Inventory
D) Sale
E) Cash Disbursement

Question 27

Which of the following is NOT one of the steps in integrating business process level REA "view models"?

A) Re-name relationships as necessary to ensure uniqueness of relationship names
B) Perform set intersections of entity attributes (i.e., in the integrated view, include only those attributes that the entity has in each of the separate views)
C) Either merge the models based on a common entity, or remove the attributes from one copy of the entity and put a slash through the corner to show it is a copy of an existing entity
D) Determining which entity or entities are shared by the view models
E) All of the above are part of view integration

Question 28

Which of the following implementation compromises is made only after the database design is complete and data has been entered?

A) Event activity roll-up
B) Combined entity key posting
C) Collapse of conceptually congruent entities
D) Omission of entity due to inadequate measurement tools
E) Materialization of tasks as entities

Question 29

Which of the following financial statement line items require queries that encompass duality relationships?

A) Net Sales on an Income Statement
B) Accounts Receivable on a Balance Sheet
C) Cost of Goods Sold on an Income Statement
D) Inventory on a Balance Sheet
E) Wages Expense on an Income Statement

Question 30

Which of the following relationships represent duality relationships?

A) CR/CS, SA/CD, CR/Ca, P/FA, and RS/CR
B) CR/CS and RS/CR only
C) CR/CS, SA/CD, and RS/CR only
D) CR/Ca only
E) P/FA only

Question 31

Two members of a database design team at Large U.S. University assigned different primary keys to the Student entities in their models. One member used Student ID, an internally generated unique number the university assigns to each student upon enrollment. The other chose social security number or other equivalent identifier assigned to each student by the U.S. government. This is an example of

A) Entity name conflict
B) Attribute conflict
C) Cardinality conflict
D) Relationship name conflict
E) Relationship structure conflict

Question 32

Resolution of entity name conflicts is usually part of

A) View integration
B) Conceptual level implementation compromises
C) Event history roll up
D) Combined entity key posting
E) Materialization of tasks as event entities

Question 33

Two entities exist in separate views as follows Which attributes are assigned to the merged Customer entity after performing a set union of the attributes on the two separate Customer entities?

A) CustID
B) CustID and Name
C) CustID, Name, and PhoneNum
D) CustID, Name, Eye Color, and Gender
E) CustID, Name, Eye Color, Gender, and PhoneNum

Question 34

Two entities exist in separate views as follows: If these entities represent the same underlying phenomenon, which of the following must be resolved to integrate the views?

A) Entity name conflict
B) Attribute conflict
C) Relationship name conflict
D) Relationship structure conflict
E) Homonym conflict

Question 35

Which of the following is a likely benefit of using ER grammar format instead of ER diagram format?

A) No need for entity name conflict resolution
B) Easier identification of overlapping entity/relationship clusters
C) Copies of entities become unnecessary
D) No need for resolution of attribute conflicts
E) No need for relationship conflict resolution

Question 36

The conceptual level implementation compromise that eventually results in an increased number of tables in the resulting database is

A) Exclusion of an entity or relationship because of inadequate measurement mechanisms or because no decision need exists for those data
B) Consolidation of conceptually congruent entities
C) Combined entity key posting
D) Materialization of tasks as event entities
E) Combination of entity sets without generalization

Question 37

The ability to produce financial statements without actually closing the books is called

A) An impossible dream
B) A virtual close
C) Event history rollup
D) Combination of entity sets without generalization
E) Materialization of tasks as event entities

Question 38

The objects on which business process model views are typically most often integrated are

A) Resources
B) Events
C) Agents
D) Duality relationships
E) Date constraints

Question 39

Which of the following should be excluded from a set of queries to calculate the cash balance on a specific date?

A) Cash receipt amount
B) Beginning date constraint
C) Cash disbursement amount
D) Ending date constraint
E) None of the choices listed here should be excluded

Question 40

If purchase return and sale events do not occur every day for each inventory item, then what function must be included in a set of queries intended to calculate quantity on hand of inventory? (Note: Since the question specifies "if purchase return and sale events do not occur every day" your answer choice should reflect something that could be omitted from the query set if those events do occur every day for each item).

A) Parameters that allow users to specify the date constraints of interest
B) Inner join operations such that all sales and purchase returns are mapped directly to purchases
C) Date constraints applied only to the purchase date
D) Null to zero functions on the summed purchase return quantity and on the summed sale quantity fields in the final quantity on hand calculation
E) A beginning date constraint set to the date of the oldest purchase return or sale, whichever occurred most recently

Question 41

Using the following tables, which inventory stock number has generated the smallest total dollar amount of gross profit, using weighted average unit costing?

Question 42

Why is it necessary to resolve entity name conflicts when integrating multiple views?

Question 43

Number the following procedures for designing and using database consecutively in the order in which they should normally begin.
_____ Logical modeling
_____ Conceptual modeling implementation compromises
_____ View integration
_____ Physical implementation
_____ Physical level implementation compromises
_____ Conceptual modeling
_____ Logical level implementation compromises
_____ Daily processing of transaction data entry into database

Question 44

When does attribute conflict exist?

Question 45

What cardinality pattern does a relationship between conceptually congruent events have?

Question 46

To develop a theoretically pure relational database (that is completely consistent with relational model theory), how should relationships that have the following cardinality patterns be implemented into tables? Why?
(0,1) - (0,1)
(0,1) - (0,N)
(0,1) - (1,N)
(0,N) - (0,1)
(1,N) - (0,1)

Question 47

List two physical level implementation compromises.

Question 48

In what two database design situations is view integration needed?

Question 49

Why might an enterprise choose to roll up event activity?

Question 50

Name two information needs that require data from multiple business processes.

Question 51

Seminole Sports is a company that sells many different kinds of sporting goods to customers. It is a great store to visit if you are a Seminole fan who needs equipment for any sport, including baseball, basketball, football, golf, gymnastics, lacrosse, swimming/diving, tennis, volleyball, and many others. Of course whatever equipment you purchase will have garnet and gold colors on it somewhere, and will have an insignia of some "famous" Seminole symbol such as the flaming spear, the three torches or a male or female Indian warrior silhouette.
Each type of product sold by Seminole Sports is mass-produced, and therefore each instance of a product type is not specifically identified (e.g. with a unique serial number) but instead is tracked with a product stock number that is the same for all instances of that product type. Sales to some customers (especially current residents of Tallahassee) are often made in person, at the store's location. For those sales, Seminole Sports has a policy of accepting cash only. Its "on-location" customers tend to be fairly homogeneous in their tastes and the marketing department has decided it has no need to track which specific on-location customers are purchasing which types of products. Thus no information is tracked for customers for on-site sales. However, Seminole fans nationwide (especially FSU alumni who have moved to other states) want to buy products from Seminole Sports. To meet this demand, Seminole Sports has established a website and a mail order catalog. Seminole fans may order products from either the mail order catalog or from the website by sending an order form (paper or electronic) or by calling a toll-free number. Customer information for all sale orders and sales to off-site customers must be tracked, especially the customer's shipping address. Seminole Sports accepts approximately 95% of customer orders (the remaining 5% are rejected because customers requested discontinued items). Customers have the option of having Seminole Sports ship the products as they become available or waiting until the order is complete (the former requires them to pay extra shipping costs). Multiple customer orders are never combined into the same shipment. Payment terms offered by Seminole Sports are n/15 and the only form of payment it accepts is cashier's checks (to guarantee no NSF checks). Sometimes customers will pay for multiple sales with a single check, and needs the information system to be able to accept partial payments even though those are rare.
On-site sales and cash receipts are processed by store clerks, who enter product information into an electronic cash register, put the merchandise into bags, and accept the customer's payment (providing change to the customer if necessary). Orders from off-site customers are entered and approved by order entry clerks, who verify that the requested products are (or will soon be) available. They forward the order information to the sales clerks, who fill the orders in-between helping on-location customers, by packing the merchandise into boxes, labeling the boxes with the customer shipping address, mailing the boxes, and entering the shipment into the information system.
When cashiers checks are received from customers, sales clerks process them. They put the cashier's checks into the cash register drawer, which serves as an "on-hand cash account". Seminole also has cash accounts at a local bank, to which it periodically deposits the cash register drawer contents as well as checks received from non-sale sources (such as loans). At the end of each day, Seminole determines which cash account has the lowest balance and make's that day's deposit into that account.
Seminole Sports has a group of purchasing agents who order products from various suppliers. All purchases must be preceded by an order; purchase agents are never allowed to just go out and buy something without going through the formal ordering process. Because the manager approves ALL purchase orders, her participation is not tracked in the database. Only one purchase agent is responsible for any one order. Its suppliers accept nearly all of Seminole Sports' orders. Multiple orders accepted by the same supplier are sometimes combined into a single shipment to Seminole Sports. One of Seminole Sports' inventory clerks receives the products, unpacks and counts them, enters the appropriate information into the information system, and places them on the store display shelves. Seminole Sports pays for all purchases made from a supplier during a given month on the last day of that month with one check, and intends never to make partial payments for purchases of inventory. Each and every check (including checks written for other things such as loan payments, wages, etc.) is written by one of Seminole Sports' accounts payable clerks and is drawn on one of its cash accounts.
Seminole Sports enters information about its employees, cash accounts, products, customers, and suppliers before entering information about any events for which those things need to be tracked. Purchases and purchase orders of different types of things (for example, fixed assets or services) will NOT be included in the same entities with purchases and purchase orders of product inventory; rather they would be considered as separate entities (and you are not modeling those). Seminole Sports does not sell anything other than product inventory.
Below, Seminole Sports has identified attributes that need to be included in its information system (don't add or subtract any attributes). You may create abbreviations for the attributes; be sure that your abbreviations are descriptive enough that someone reading your model will know what you mean.
Required: Create a REA business process level model in either ER diagram or ER grammar format. Be sure to include entities, relationships, cardinalities, and attributes! Write any perceived ambiguities (if any) and assumptions you made to resolve those ambiguities for the Seminole Sports problem.

Question 52

Why does entity name conflict sometimes occur?

Question 53

Consider the following conceptual model.
This model reflects the enterprise policy that once a need for a good is identified and a requisition is prepared, an agent physically goes to a local supplier and verbally requests, accepts, and pays for the goods in a single transaction before returning to the enterprise. If the enterprise expects never to change this policy, what conceptual level implementation compromise do you recommend the enterprise make to the pictured conceptual model? Explain your recommendation and either draw the compromised model or write it in grammar format.

Question 54

Mega Manufacturer pays cash to many different external partners including suppliers, employees, independent contractors, shipping partners, banks, and stockholders. Mega Manufacturer wants to use a single database table in which to store information about all its external business partners. Describe the implementation compromise(s) Mega Manufacturer must make to accomplish this.

Question 55

Snooglestrum Sleep Shop is an enterprise that purchases raw materials such as feathers, foam, fabric, and thread, and manufactures various types of mattresses and bedding. Database design teams who were hired to create an integrated enterprise wide database for the Snooglestrum Sleep Shop developed the following conceptual models for the revenue and acquisition cycles. The database designers who developed these models are competent and made no mistakes. The four designers were also friends who each contributed 25 cents toward a joint purchase of a $1 lottery ticket each week. Last week they won the lottery, closed their database design enterprise and left to enjoy life on a tropical island. You have been retained to complete the view integration of the revenue and acquisition cycle REA-based conceptual models. You may either draw the integrated model in diagram format or write it in grammar format.