suppose that you have designed a database for morgan importing

Project Description:

suppose that you have designed a database for morgan importing that has the following tables:store (storename, city, country, phone, fax, email, contact)purchase_item (purchaseitemid, storename, 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)if you are going to use a dbms product with this task, see the morgan importing project questions in chapter 10 for sql server 2008 r2, chapter 10a for oracle database 11g, or chapter 10b for mysql 5.5 for additional instructions.a. do you think store should have a surrogate key? if so, create it and make required adjustments in the design. if not, explain why not or make other adjustments to store and other tables that you think are appropriate.b. specify null/not null constraints for each table column.c. specify alternate keys, if any.d. state relationships as implied by foreign keys, and specify the maximum and minimum cardinality of each relationship. justify your choices.e. explain how you will enforce the minimum cardinalities in your answer to part d. use referential integrity actions for required parents, if any. use figure 6-28(b) as a boilerplate for required children, if any.f. write create table statements for each of the tables using your answers to the parts a–e, as necessary. set the first value of purchaseid to 500 and increment it by 5. set the first value of shipmentid to 100 and increment it by 1. use foreign key constraints to create appropriate referential integrity constraints. set update and delete behavior in accordance with your referential integrity action design. set the default value of insuredvalue to 100. write a constraint that store.country be limited to seven countries (you can pick the seven countries you want to purchase from, but be sure to include the countries for the item.city locations shown in figure 2-41).g. explain how you would enforce the rule that shipment_item.insuredvalue be at least as great as purchase_item.priceusd.h. write insert statements to insert the data shown in figures 7-43, 7-44, 7-45, 7-46, and 7-47.i. write an update statement to change values of store.city from new york city to nyc.j. write a delete statement(s) to delete a shipment and all of the items on that shipment.k. create a view called purchasesummaryview that shows onlypurchase_item.purchaseitemid, purchase_item.purchasedate,purchase_item.itemdescription, and purchase_item.priceusd.l. create a view called storepurchasehistoryview that shows store.storename, store.phone,store.contact, purchase_item.purchaseitemid, purchase_item.purchasedate,purchase_item.itemdescription, and purchase_item.priceusd.m. create a view called storehistoryview that sums the priceusd column of store-purchasehistoryview for each store into a column named totalpurchases.n. create a view called majorsources that uses storehistoryview and selects only those stores that have totalpurchases greater than 100000.o. explain, in general terms, how you will use triggers to enforce minimum cardinality actions as required by your design. you need not write the triggers, just specify which triggers you need and describe, in general terms, their logic.
Skills Required:
Project Stats:

Price Type: Negotiable

Expired
Total Proposals: 9
1 Current viewersl
53 Total views
Project posted by:

Proposals

Proposals Reputation Price offered
  • 3.8
    30 Jobs 19 Reviews
    $95 in 0 Day
  • 3.3
    16 Jobs 7 Reviews
    $0 in 0 Day
  • 4.1
    70 Jobs 30 Reviews
    $35 in 0 Day
  • 0.0
    1 Jobs 0 Reviews
    $0 in 0 Day
  • 4.3
    7 Jobs 3 Reviews
    $30 in 0 Day
  • 2.5
    8 Jobs 6 Reviews
    $0 in 0 Day