In your job at WebMD, you have been given the task of obtaining data from the U.S. Food and Drug Administration (FDA) for use on a new website: Generic & Brand Name Drug Selector.  You should now have all three tables created and imported into your database.  Recall that you named each of you columns to match the FDA documentation.

You have responsibility for creating a database that will efficiently allow appropriately authorized users access to the data to meet the following business needs:

1. Data analysis by a group of clinical personnel within the company

2. Data editing by a group of medical writers within the company

3. Data queries to be incorporated in webpages by other programmers to mimic the functionality on the FDA website

4. Schema creation and alteration by DBAs

The current schema you created is used to implement the FDA website.  Using the schema you have, plus the functionality on the website, create an Entity Relationship Diagram using UML as described in the text.

There is a need to further normalize your data.  One field has multiple ingredients, another has packaging and route of administration combined, and another has multiple strengths (probably in the same order and number as the ingredients).  These need to be altered to atomic values for ingredients, packaging, route of administration and ingredient strength. 

First create an Entity Relationship Diagram to describe this new model and functionality. 

Then alter your schema and move the data into the new schema.   (If you find data that won't convert easily, don't lose the data.)  You must convert the data using both of the following two methods:

1. in a stored procedure using DECLARE CURSOR and manipulating the data on a record-by-record basis and applying COMMIT and ROLLBACK to manage the transaction.

Submit both stored procedures and any views used by the stored procedures.

Run both stored procedures and record and submit the time required for each to complete their task.

Now run both procedures and create an experiment to interrupt the transaction before completion.  Did both methods ROLLBACK properly?  Describe what happened with each and explain why.

Be sure that all appropriate constraints and access paths are applied to the new schema.