INTRODUCTION

You will need to create an operational database for an organization. You will need to apply technical skills and using an appropriate tool to create the database based on the scenario below.

Scenario

Morgan Importing Ltd. has hired you as a database consultant to develop an operational database having the following four tables.

TASK 1 (LO 3)

The database contains several tables. The description of the business is as follows:

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 the organizations personnel are the owner (Marcia), an office administrator, and one full-time salesperson, and two part-time salespeople. Marcia and the office administrator needs to process data in all tables. Additionally, the full-time salesperson can enter purchase and shipment data. The part-time employees can only read shipment data.

Prepare a document for your organization to address the following issues:

Explain the need for database administration in Morgan Ltd. and your recommendation as to who should serve as a database administrator. Assume that Morgan is not sufficiently large that it needs or can afford a full-time database administrator.

Explain the nature of database administration activities at Morgan. As an aggressive consultant, keep in mind that you can recommend yourself for performing some of the DBA functions.

For the employees described above, define users, groups, and permissions on data in these five tables. Use the security scheme as an example and create a table. Dont forget to include yourself. Also, write SQL queries for creating users and assigning permissions.

Suppose that you are writing a stored procedure to record new purchases. Suppose that you know that while your procedure is running, another stored procedure that records shipment data can be running, and a third stored procedure that updates shipper data can also be running.

Give an example of a dirty read, a non- repeatable read, and a phantom read among this group of stored procedures.

What concurrency control measures are appropriate for the stored procedure that you are creating?