Question 1 -

Company XYZ provides cell phone service along with cellphone products to its customers.  The telesales division of XYZ takes orders for new cell phones from existing customers, but does not handle changes to cellphone service plans or the establishment of new accounts (new accounts must be established in person).

The order entry process for telesales begins when a customer calls the telesales headquarters. The customer provides his/her phone number and pin, which is entered into the computer by the salesperson. The computer pulls up the customer's account information and displays it for the salesperson.

The salesperson then obtains and enters the last four digits of the phone number associated with the equipment that the customer would like to upgrade. Based on what the sales person entered, the computer accesses the enterprise database to provide pricing and financing options for phones offered by XYZ. The salesperson shares this information with the customer. The customer then tells the salesperson what model they would like to purchase as well as their preferred financing terms, and the salesperson makes the appropriate selection which results in the allocation of inventory when inventory is in-stock (phones not in-stock trigger an error routine).

The computer then displays on the screen of the salesperson a request for the billing information and shipping address from the customer (to obtain the required down payment or full purchase price of the phone). The salesperson obtains and enters credit card information, a billing address, and a shipping address from the customer.  The computer contacts the credit card company, provides the billing information and purchase amount, and receives a confirmation number if the charges are approved (the associated error routine is not described here).

The salesperson provides the customer with an order verification number that the computer displays on the salesperson's computer screen. The computer then changes the inventory from allocated to sold and sends an electronic picking ticket to the warehouse.

1. Provide a table of entities and activities for XYZ's telesales order entry process.

2. Provide a context DFD for XYZ's telesales order entry process. (Note, you do not need to depict data stores on your context DFD.

3. Provide a physical DFD for XYZ's telesales order entry process (note: for your physical DFD, you must use a separate data store for each type of information that is being accessed; do not use a single data store for the "enterprise database".

4. Partition your table of entities and activities and annotate it to designate the sub-processes for your level 0 logical DFD.  Below, draw a level 0 logical DFD. (Note, for your level 0 logical DFD, you must use a separate data store for each type of information that is being accessed; do not use a single data store for the "enterprise database".

5. Provide a systems flow chart for XYZ's telesales order entry process (Note: you can use a single data disk to designate the enterprise database.

Question 2 -

Put the above database into UNF, 1NF, 2NF, and 3NF using the notation taught in Week 4's lecture. Place repeating groups inside of the following brackets: "{"and "}". Place tables inside the following brackets: "["and "]". Do not use character values as part of the primary key. Name tables if there is more than one. Underline the attributes comprising the primary key for each table. Finally, designate foreign keys for 3NF.

Question 3 -

Give the results of the following joinsfor the below tables:

a) inner equijoin on ApprovalCode that selects all columns (10 points)

b) full outer equijoin on ApprovalCode that selects all columns (10 points)

c) left outer equijoin on ApprovalCode that selects all columns (10 points)

d) right outer equijoin on ApprovalCode that selects all columns (10 points)

Question 4 -

Complete the following SQL queries. Provide the code that you used as an attachment to your exam.

1. What supplier appears to be the most important (in terms of revenue generation)? Write a query to answer this question.

2. How much revenue by product category does each shipper handle? Write a query to answer this question.

3. What customer purchases the most seafood (in terms of revenue)? Write a query to answer the question.  

Question 5 -

Apply the ERM framework to a lemonade stand that would like to expand throughout a small neighborhood.  Assume the lemonade stand has several investors. You can pick a risk appetite for the company.  Discuss all eight components of the ERM framework, and specifically identify in gone opportunity and two risks.  Explain how the opportunity might be used to establish a strategy. Complete a qualitative risk assessment (i.e. no need for dollar amounts or precise probabilities) of the two risks that you identified, and discuss the associated risk response, control activities, etc. Assess (qualitatively) the risk on an inherent and residual basis.