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

   Now that you created your new BCNF schema in lab 4, you must alter the views you created in Lab 3.  They are listed below.  Submit the views as requested below. 

   Now write and submit SQL views to answer the following questions for your data analysis staff.  Utilize joins (left, right, inner and outer) and nested queries where appropriate.  Also submit each of these as relational algebra, tuple relational calculus and domain relational calculus statements, using the syntax from the book. Who makes Cogentin? Which company has the longest time interval between the exclusivity date and the patent expiration date?  What is the average time interval between these two dates for all products made by that company? Which companies make products that have the same ingredients, strengths, dosage form and route as Percodan? Which company has the most products containing some type of aspirin?  How many products is that? Which company has a product with the most ingredients in addition to aspirin?  How many ingredients is that? Which company has the highest number of exclusive products? for which the exclusivity is still active? Which day has the most applications filed and which companies filed on that day and what are the trade names and ingredients for those products? Are there any companies that make Demerol in 50 mg/5ml but whose product doesn't come in tablets? I need to pass in a company name, trade name, ingredients, strengths, route and dosage form from my code and accept back that company's product information, plus all competitive products that are considered therapeutically equivalent to the standard for that product and that have not been discontinued.  My client considers any product that is a reference listed drug (RLD) to be the standard and any product with a TE Code starting with "A" to be as good as the standard, while all of the rest are inferior.