Difference between revisions of "Hivanson Week 8"

From LMU BioDB 2024
Jump to navigation Jump to search
(added instructions to edit)
(→‎Methods: reorder notes/obs)
 
(32 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
* The '''purpose''':  what was the purpose of your investigations?
+
==FDA Drugs Database on Microsoft Access==
−
* Your '''methods''': what did you actually do?  Give a step by step account for Tasks 1-4 listed above.
+
==='''Purpose'''===
−
** There should be enough detail provided so that you or another person could re-do it based solely on your notebook.
+
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 index.
−
** You may copy protocol instructions on your page and modify them as to what '''''you''''' actually did, as long as you provide appropriate attribution in your acknowledgments section.
 
−
** Take advantage of the electronic nature of the notebook by providing screenshots, links to web pages, links to data, etc.
 
−
Download and decompress the files.
 
−
Import the data files into a Microsoft Access database you will create.
 
−
Configure the fields in your tables to match the schema provided by the FDA (keys, datatypes).
 
−
Set the relationships between your tables to match the schema provided by the FDA.
 
−
Answer the questions below.
 
  
−
* Your '''results''': the answers to the questions below, plus any other results you gathered.
+
==='''Methods'''===
−
** Usually it makes sense to embed your answers to the questions/results in the "methods" in the order in which you obtained them.
+
#I downloaded and extracted the files from the [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database].
−
* A '''scientific conclusion''': what was your main finding for today's project?  Did you fulfill the purpose?  Why or why not?
+
#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."
 +
#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.
 +
#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:[[Image:hivanson_week8_relationships.png|800px]]
 +
#I used SQL functions to query the database. See '''Results''' section.
 +
'''Notes and Observations''':  
 +
Date columns were giving me issues; to resolve the issues upon importation: I changed the datatype from date/time to short text, and then after importing changed it back to date. This resolved the issue.
 +
SQL functions were tough to figure out at first, but eventually they all seemed pretty similar to one another. I would love to have more practice making more complex SQL queries.
  
 +
==='''Results'''===
  
 +
[[Media:Hivanson_FDA_3.12.24.database.zip|Download my competed database]]
  
 +
*Query 1: SELECT DrugName FROM Products WHERE form ='INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';
 +
**[[Media:Hivanson_Week8_Query1.xlsx|Table output here]]
  
 +
*Query 2: SELECT DrugName FROM Products WHERE ActiveIngredient ='ATROPINE';
 +
**[[Media:Hivanson_Week8_Query2.xlsx|Table output here]]
  
 +
*Query 3: SELECT Form, Strength FROM Products WHERE DrugName = 'Benadryl';
 +
**[[Media:Hivanson_Week8_Query3.xlsx|Table output here]]
  
−
1.
+
*Query 4: SELECT DrugName FROM Products WHERE DrugName LIKE '*ESTROL';
−
SELECT DrugName
+
**[[Media:Hivanson_Week8_Query4.xlsx|Table output here]]
−
FROM Products
 
−
WHERE form ='INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';
 
  
−
2. SELECT DrugName
+
*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.)"
−
FROM Products
+
**No "therapeutic_potential" column present in database, so cannot search column for query.
−
WHERE ActiveIngredient ='ATROPINE';
 
  
−
3. SELECT Form, Strength
+
*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.)"
−
FROM Products
+
**No "chemical_type" column present in database, so cannot search column for query.
−
WHERE DrugName = 'Benadryl';
 
  
−
5. "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.)"
+
*Query 7: SELECT DrugName FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE SponsorName = 'Merck';
−
No "therapeutic_potential" column present in database, so cannot search column.
+
**[[Media:Hivanson_Week8_Query7.xlsx|Table output here]]
  
−
6. 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.)
+
*Query 8: SELECT SponsorName, ActiveIngredient FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' And SponsorName LIKE '*Labs*';
−
No "chemical_type" column present in database, so cannot search column.
+
**[[Media:Hivanson_Week8_Query8.xlsx|Table output here]]
  
−
7. SELECT DrugName
+
*Question 9: The [https://www.accessdata.fda.gov/scripts/cder/daf/index.cfm 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 <code>LIKE '*<text>*'</code> than <code>= '<text>'</code>) 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).
−
FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo
 
−
WHERE SponsorName = 'Merck';
 
  
−
8. SELECT SponsorName, ActiveIngredient
+
==='''Conclusion'''===
−
FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo
+
By importing the FDA Drugs database to Microsoft Access, setting relationships between tables, and comparing searching using SQL functions to the search function on the FDA Drugs index website, we have learned the limitations of search functions provided by usual user interfaces, and have an understanding of how much can be done with SQL search functions. SQL allows for more flexibility and specificity in searching the FDA Drugs database than the FDA Drug Index search function provides.
−
WHERE ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' And SponsorName LIKE '*Labs*';
+
 
 +
==Acknowledgments==
 +
I worked on this assignment during class time on 3/7/2024, 3/9/2024, and 3/12/2024 receiving help and guidance from Drs. Dionisio and [[User:Kdahlquist | Dahlquist]], as well as collaborating in-person with all five of my classmates.
 +
 
 +
Except for what is noted above, this individual journal entry was completed by me and not copied from another source.
 +
 
 +
[[User:Hivanson|Hivanson]] ([[User talk:Hivanson|talk]]) 22:19, 13 March 2024 (PDT)
 +
 
 +
==References==
 +
*Access SQL: basic concepts, vocabulary, and syntax—Microsoft Support. (n.d.). Retrieved March 13, 2024, from https://support.microsoft.com/en-us/office/access-sql-basic-concepts-vocabulary-and-syntax-444d0303-cde1-424e-9a74-e8dc3e460671?ui=en-us&rs=en-us&ad=us
 +
*Drugs@FDA Data Files. (n.d.). FDA; FDA. Retrieved March 13, 2024, from https://www.fda.gov/drugs/drug-approvals-and-databases/drugsfda-data-files
 +
*Drugs@FDA: FDA-Approved Drugs. (n.d.). Retrieved March 13, 2024, from https://www.accessdata.fda.gov/scripts/cder/daf/index.cfm
 +
*Ford, P. (2015, June 11). What Is Code? Bloomberg. https://www.bloomberg.com/graphics/2015-paul-ford-what-is-code/
 +
*LMU BioDB 2024. (2024). Week 8. Retrieved March 13, 2024, from https://xmlpipedb.cs.lmu.edu/biodb/spring2024/index.php/Week_8
 +
 
 +
{{Template:Hivanson}}

Latest revision as of 22:24, 13 March 2024

FDA Drugs Database on Microsoft Access

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 index.

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:Hivanson week8 relationships.png
  5. I used SQL functions to query the database. See Results section.

Notes and Observations: Date columns were giving me issues; to resolve the issues upon importation: I changed the datatype from date/time to short text, and then after importing changed it back to date. This resolved the issue. SQL functions were tough to figure out at first, but eventually they all seemed pretty similar to one another. I would love to have more practice making more complex SQL queries.

Results

Download my competed database

  • Query 1: SELECT DrugName FROM Products WHERE form ='INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';
  • Query 2: SELECT DrugName FROM Products WHERE ActiveIngredient ='ATROPINE';
  • Query 3: SELECT Form, Strength FROM Products WHERE DrugName = 'Benadryl';
  • Query 4: SELECT DrugName FROM Products WHERE DrugName LIKE '*ESTROL';
  • 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';
  • Query 8: SELECT SponsorName, ActiveIngredient FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' And SponsorName LIKE '*Labs*';
  • 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

By importing the FDA Drugs database to Microsoft Access, setting relationships between tables, and comparing searching using SQL functions to the search function on the FDA Drugs index website, we have learned the limitations of search functions provided by usual user interfaces, and have an understanding of how much can be done with SQL search functions. SQL allows for more flexibility and specificity in searching the FDA Drugs database than the FDA Drug Index search function provides.

Acknowledgments

I worked on this assignment during class time on 3/7/2024, 3/9/2024, and 3/12/2024 receiving help and guidance from Drs. Dionisio and Dahlquist, as well as collaborating in-person with all five of my classmates.

Except for what is noted above, this individual journal entry was completed by me and not copied from another source.

Hivanson (talk) 22:19, 13 March 2024 (PDT)

References

Template:Hivanson

Retrieved from "https://xmlpipedb.lmucs.org/biodb/spring2024/index.php?title=Hivanson_Week_8&oldid=2216"