Q1. Draw an E-R diagram for following narrative.
Small Company Purchasing Database System
A small company allocates a certain amount of dollars to each department to be used to purchase things they need. The approved budget information is kept in the APPROVED-BUDGET file by department number in the purchasing system. When a department determines that it needs to buy something, the REQUESTOR fills out an order to request a purchase (REQ-ORDER). The REQ-ORDER contains the REQUESTOR's name, requestor's department number (DEPT#), quantity (QTY), name or description of the ITEM, COST and the DATE-NEEDED. Optional information that is provided by the REQUESTOR is the name and address of the supplier (SUPPLIER-NAME + SUPPLIER-ADDR). The REQ-ORDER is sent to a clerk in the purchasing system. The clerk compares the total amount of the order to the current amount remaining as specified in the APPROVED-BUDGET file for the requesting department.
If the amount of the order exceeds the remaining amount authorized, the order is returned to the requesting department with a note saying "EXCEEDS BUDGET." If the amount of the order does not exceed the remaining budgeted amount, the order is accepted. The clerk computes the expected amount of the order and updates the APPROVED-BUDGET file to reflect the fact that additional funds are being expended. The ORDER is then passed on to the buyer and placed on the ACCEPTED-ORDERS stack.
To process an order the buyer must verify (if provided) or obtain the name, address, and phone number (SUPPLIER-PHONE#) of the supplier from the SUPPLIERS-DATABASE file. This information is added to the order by the buyer if the information has not been provided by the REQUESTOR. The SUPPLIER-DATABASE also contains the amount of the DISCOUNT provided by the supplier (if any).
The buyer calls the supplier, checks to see if the ITEMS are "in-stock", verifies the COST and DISCOUNT, and obtains the expected ARRIVAL DATE (VERIFIED-ORDER). If the ARRIVAL-DATE is on or before the DATE-NEEDED, a VERBAL-ORDER is placed with the supplier. The REQ-ORDER is marked up indication that the order has been placed and the expected ARRIVAL-DATE agreed upon by the supplier. The order is then passed on to the assistant buyer as ORDER-PLACED.
If the expected ARRIVAL-DATE is beyond the DATE-NEEDED, the buyer calls the REQUESTOR to obtain approval to accept the supplier's ARRIVAL-DATE. If the REQUESTOR approves the new date, the order is placed. If the REQUESTOR does not approve the new date, the buyer then looks up the ITEM being purchased in the SUPPLIERS-DATABASE file for alternative suppliers. Each supplier is checked in the data-base and ranked by the negotiated discount agreed upon between the company and the supplier. If no discount has been previously agreed to, the discount is assumed to be zero.
The buyer then calls the alternative suppliers until the expected ARRIVAL-DATE matches (or is earlier than) the DATE-NEED and a VERBAL-ORDER is placed. If no supplier can meet the requirements of the DATE-NEEDED, the REQ-ORDER is returned to the REQUESTOR with a note saying ‘cannot satisfy DATE-NEEDED'. Once the verbal order is placed, the order is sent to the assistant buyer as ORDER-PLACED.
The assistant buyer makes a copy of the ORDER-PLACED and marks it as a "CONFIRMING-ORDER", and forwards it to the supplier as a written verification of the verbal order placed by the buyer. The original copy of the ORDER-PLACED is then put in a stack of OUTSTANDING-ORDERS and awaits the shipment to come into the Shipping and Receiving department.
When the merchandise arrives in the Shipping and Receiving department, the INVOICE is sent to the assistant buyer in the purchasing system. The assistant buyer matches the original copy of the ORDER-PLACED with the INVOICE and marks the ORDER-PLACED as "RECEIVED". The original copy of the ORDER-PLACED is then forwarded to the accounts payable department as REQ-ORDER + "RECEIVED". The accounts payable department is responsible for sending the payment to the supplier.
Q2. Draw an E-R diagram for following narrative.
ABC Catalog Database Application
The company takes orders for merchandise and ships the merchandise to the customer.
A customer enters the store, chooses some merchandise from the store catalog, takes a blank ORDER-FORM from the table, fills out the form, and presents the ORDER to the order clerk. The order clerk verifies: (1) that the ORDER was properly filled out (2) that the customer included the zip code in the ship-to address, (3) that the customer name and address, quantity, product number are listed, and (4) that the customer listed the correct price for the product. The order clerk computes the total amount due, adds all takes, and determines the grand total. The order clerk accepts PAYMENT from the customer, places the PAYMENT in the CASH-DRAWER, dates the ORDER, approves the order initialing the order, and places the order in the stack of APPROVED-ORDERS in the open window for the data clerk.
The stack of orders in the open window is organized by date and time written. The clerk takes the next APPROVED-ORDER off the top of the stack and enters that order into the company's computer system. The computer system performs four functions with the new order.
The system sends a SHIPPING-ORDER containing the customer's name, ship-to address, product number and quantity to the warehouse, and a copy of the order prints out upon receipt of the shipping clerk's REQUEST-ORDER.
The system saves information about the order in the ORDERS data store by date of sale and customer's name.
The system provides information in the form of a report (ACCT-REPORT) to the accounts receivable clerk (AACT-RECV Clerk). The ACCT-REPORT lists the following information for all sales: date of sale, order clerk's initials, total payment for merchandise, appropriate taxes, and the grand total amount collected from the customer.
The system produces a SALES-VOLUME-REPORT for management that indicates the current dollar volume of sales by week compared to the dollar volume of sales for the previous 12 weeks.
The shipping clerk obtains the SHIPPING-ORDER printed on a multipart pink form (PINK-SHEET). The warehouse worker picks up the PINK-SHEET and goes into the WAREHOUSE to locate and select the ordered product (PRODUCT). The warehouse worker brings the ordered product to the dispatch clerk together with the PINK-SHEET.
The dispatch clerk has four responsibilities:
1. Package the MERCHANDISE for shipment, which includes the PRODUCTS and one copy of the PINK-SHEET.
2. Telephone the delivery service that delivers to the ship-to address. A list on the wall (SHIPPERS) contains information on all of the freight delivery services (names, phone numbers, and the cities and states that they service) sorted by cities and states.
3. Give the teamster the MERCHANDISE and one copy of the PINK-SHEET.
4. Place one part of the PINK-SHEET in the COMPLETED-SHIPMENTS data store.
Q3. Draw an E-R diagram for exercise 3.5 on page 89 of text.
Q4. Map the following E-R model to a relational model.
Q5. Map the following E-R model to a relational model.
Q6. Map the following E-R model to a relational model.
