Hivanson Week 8

Purpose

Import public-access FDA drug database into Microsoft Access, then compare query capabilities using SQL on Access to those of the search function on the FDA drug database.

Methods

1. I downloaded and extracted the files from the FDA Drug Database.
2. I created a Microsoft Access database and imported all data files from step 1 using "Import"-->"Text File", and selecting "First Row Contains Field Names."
3. When importing, I ensured that the fields matched the datatype outlined by the FDA website schema. I did not match the keys, as many of the fields that were stated to be keys contained duplicate information and therefore could not be listed as primary keys on Access.
4. I attempted to set the relationships between my tables to match the schema provided by the FDA, but he two entity relationship diagrams provided on the FDA website contained some conflicting information. I matched the relationships as best I could according to the diagrams and according to common field names. See screenshot of my relationships below:
5. I used SQL functions to query the database. See Results section.

Results

• Query 1: SELECT DrugName FROM Products WHERE form ='INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';
  • Table output here

• Query 2: SELECT DrugName FROM Products WHERE ActiveIngredient ='ATROPINE';
  • Table output here

• Query 3: SELECT Form, Strength FROM Products WHERE DrugName = 'Benadryl';
  • Table output here

• Query 4: SELECT DrugName FROM Products WHERE DrugName LIKE '*ESTROL';
  • Table output here

• Query 5: Instructions "Produce a table listing all of the known values for the therapeutic_potential column in the application table and how many application records there are of each. (Side note: The therapeutic_potential codes are explained in the ReviewClass_Lookup.txt file, in case you’re interested.)"
  • No "therapeutic_potential" column present in database, so cannot search column for query.

• Query 6: Instructions "Produce a table listing all of the known values for the chemical_type column in the application table and how many application records there are of each. (Side note: The chemical_type codes are explained in the ChemTypeLookup.txt file, in case you’re interested.)"
  • No "chemical_type" column present in database, so cannot search column for query.

• Query 7: SELECT DrugName FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE SponsorName = 'Merck';
  • Table output here

• Query 8: SELECT SponsorName, ActiveIngredient FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' And SponsorName LIKE '*Labs*';
  • Table output here

• Question 9: The search function on the FDA Drugs index states that a user can "Search by Drug Name, Active Ingredient, or Application Number." Going through the list of queries that we used SQL to run, none can be run using the search function on the website. The FDA Drugs index does not allow search by form of drug (Query 1). The index does not allow a search to find a singular active ingredient (perhaps the search they are using is closer to LIKE '*<text>*' than = '<text>') and therefore cannot complete Query 2. Only one result is shown when searching the index for "Benadryl" while Query 3 using SQL retrieved 5 results. Endings and beginnings cannot be searched (Queries 4 and 8). Sponsor name cannot be searched (Query 7). These queries could not have been completed using the search function on the FDA Drugs index website alone, unless combing through and checking each listing for the desired characteristics (which is not a search).

Conclusion

what was your main finding for today's project? Did you fulfill the purpose? Why or why not?