Assume that morgan has created a database with the tables described at the end of chapter 7
Fantastic news! We've Found the answer you've been seeking!
Question:
- Assume that morgan has created a database with the tables described at the end of chapter 7 (note that store uses the surrogate key storeid):store (storeid, storename, city, country, phone, fax, email, contact)purchase_item (purchaseitemid, storeid, purchasedate, itemdescription, category, priceusd)shipment (shipmentid, shipperid, shipperinvoicenumber, origin, destination, departuredate, arrival date)shipment_item (shipmentid, shipmentitemid, purchaseitemid, insuredvalue)shipper (shipperid, shippername, phone, fax, email, contact)assume that all relationships have been defined as implied by the foreign keys in this table list. if you want to run these solutions in a dbms product, first create a version of the of the mi database described in chapter 7 named mi-ch08.a. create a dependency graph that shows dependencies among these tables. explain how you need to extend this graph for views and other database constructs, such as stored procedures.b. using your dependency graph, describe the tasks necessary to change the name of the shipment table to morgan_shipment.c. write all sql statements to make the name change described in part b.d. suppose that morgan decides to allocate some purchases to more than one shipment. make design changes in accordance with this new fact. you will need to make assumptions about how purchases are divided and allocated to shipments. state your assumptions.e. code sql statements to implement your redesign recommendations in your answer to part d.f. suppose that morgan considers changing the primary key of purchase_item to (storeid, purchasedate). write correlated subqueries to display any data that indicate that this change is not justifiable.g. suppose that (storeid, purchasedate) can be made the primary key of purchase_item. make appropriate changes to the table design.h. code all sql statements necessary to implement the changes described in part g.
Related Book For
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George
Posted Date: