Difference between revisions of "Hivanson Week 8"
(purpose) |
(fixed methods formatting) |
||
| Line 1: | Line 1: | ||
* '''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. | * '''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''': |
| − | + | *#I downloaded and extracted the files from the [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database]. | |
| − | + | *#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 downloaded and extracted the files from the [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database]. | + | *#I attempted to set the relationships between my tables to match the schema provided by the FDA, but he two photos provided on the FDA website contained some conflicting information. I matched the relationships as best I could according to the photos and according to common field names. See screenshot of my relationships below: |
| − | #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 photos provided on the FDA website contained some conflicting information. I matched the relationships as best I could according to the photos and according to common field names. | ||
Answer the questions below. | Answer the questions below. | ||
Revision as of 16:29, 13 March 2024
- 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:
- I downloaded and extracted the files from the FDA Drug Database.
- 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 photos provided on the FDA website contained some conflicting information. I matched the relationships as best I could according to the photos and according to common field names. See screenshot of my relationships below:
Answer the questions below.
- Your results: the answers to the questions below, plus any other results you gathered.
- Usually it makes sense to embed your answers to the questions/results in the "methods" in the order in which you obtained them.
- A scientific conclusion: what was your main finding for today's project? Did you fulfill the purpose? Why or why not?
1.
SELECT DrugName
FROM Products
WHERE form ='INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';
2. SELECT DrugName FROM Products WHERE ActiveIngredient ='ATROPINE';
3. SELECT Form, Strength FROM Products 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.)" No "therapeutic_potential" column present in database, so cannot search column.
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.) No "chemical_type" column present in database, so cannot search column.
7. SELECT DrugName FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE SponsorName = 'Merck';
8. SELECT SponsorName, ActiveIngredient FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' And SponsorName LIKE '*Labs*';
