Kmill104 Week 8

Purpose

The purpose of this week's assignment was to learn how data sets are organized and how relationships are defined between them, as well as how to download data and set up these relationships on our own database. It also gave us practice using queries in Microsoft Access and following the SQL format.

Methods

1. My first step was to click on the FDA Drug Database link on the Week 8 page.
   • On the database website, there is a file containing the FDA Drug Database's data sets.
     • Screenshot of website: FDA Drug Database
   • Clicking this file link then downloaded a zip folder, which I had to decompress in FileExplorer.
   • Within the folder, there were 11 text documents for each table in this image: FDA Drug Database
2. I then created my own Microsoft Access database, and navigated to the External Data tab in the toolbar.
   • From there, I was able to click on New Data Source, which gave me the option to choose a text file.
   • I then imported each of the text files as a new table into the current database.
3. For each file, I kept the data in the Delimited format and chose tab as the delimiter to separate the fields.
   • I also made sure to check off that the first row contained the field names.
   • For each field type, I kept the names the same but, if needed, I changed the data type to match what is seen on the FDA website.
     • On the website, each table column is assigned some data type, with the five options for data types being char, varchar, int, text, and datetime.
     • Submissions table for website example: FDA Drug Database
     • In Access, each column that has data type char on the website I then assigned the Short Text data type. For the others, varchar was assigned as Long Text, int as Integer or Long Integer, text as Long Text, and datetime as Date with Time.
   • I also attempted to match the table's primary keys to what is seen on the website, but Access often stated that the column could not be a primary key as it contained duplicates or was missing data in some rows. In these cases, I chose there to be no primary key.
4. After downloading each file and configuring the fields in its table, I navigated to the Database Tools tab and then clicked on Relationships.
   • In Relationships, I was able to click on "Edit Relationships" which opened a tool where I could enter my desired two tables under Table/Query: and Related Table/Query:, and then underneath these I could choose the related columns. After clicking OK, a line indicating the relationship is displayed between the two tables.
   • I repeated this step for every relationship that is shown on the FDA website, which is similarly indicated by lines between tables.
     • For those tables that had multiple columns in common, I made sure to define the relationship for each of them.
5. After setting up my database, I was able to navigate to the Create tab, where I clicked QueryDesign to create a new query. The queries I used to answer the questions are indicated below, and they are all in the SQL format.

Results

1. What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?
   • LOVENOX, VELCADE, APIDRA, ENOXOPARIN SODIUM, ACTEMRA, BORTEZOMIB, WEZLANA
     • SELECT DrugName FROM Products WHERE form ='INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';
2. What are the names of the drug products whose active ingredient (activeingred) is ATROPINE?
   • ATROPEN, ATROPINE, ATROPINE (AUTOINJECTOR)
     • SELECT DrugName FROM Products WHERE ActiveIngredient ='ATROPINE';
3. In what forms and dosages can the drug product named BENADRYL be administered?
   • Form: Capsule;Oral and Strength: 50MG
   • Form: Elixir;Oral and Strength: 12.5MG/5ML
   • Form: Capsule;Oral and Strength: 25 MG
   • Form: Injectable;Injection and Strength: 10MG/ML
   • Form: Injectable;Injection and Strength: 50 MG/ML
     • SELECT Form, Strength FROM Products WHERE DrugName = 'BENADRYL';
4. Which drug products have a name ending in ESTROL?
   • DIETHYLSTILBESTROL, STILBESTROL
     • SELECT DrugName FROM Products WHERE DrugName LIKE '*ESTROL';
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.)
   • Not possible, no therapeutic_potential 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.)
   • Not possible, no chemical_type column
7. What are the names of the drug products that are sponsored (sponsor applicant column) by MERCK?
   • Drug Names Sponsored by Merck
     • SELECT Products.DrugName FROM Products LEFT JOIN Applications ON Products.ApplNo = Applications.ApplNo WHERE (((Applications.[SponsorName])='MERCK'));
8. Which sponsor applicant companies have the text LABS in their names and have products whose active ingredients (activeingred) include both ASPIRIN and CAFFEINE?
   • Watson Labs, Nostrum Labs Inc, Dr Reddys Labs SA
     • SELECT SponsorName, ActiveIngredient FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' AND SponsorName LIKE '*LABS*';
9. Also answer, would you have been able to answer these questions using the web interface that the FDA provides to this database (Drugs@FDA?)

Data and Files

Katie Miller's Database Media:DBScreenshot.png File Link Website Screenshot Media:DataBTables.png Tables Image Website Screenshot Media:DBSubmissions.png Submissions Table Website Screenshot