Exam 10: View Integration and Implementation Compromises

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)

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.

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.

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

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

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

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

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).

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

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.

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.

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

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

In what two database design situations is view integration needed?

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