Lenaolufson Week 6

The FDA Drug Database

Direct Download/Unzipping Commands

• First, I logged on with my username and password and then entered the class folder and created a directory for this assignment, and then entered the directory:

ssh eolufson@my.cs.lmu
cdbiodb
mdir week6
cd week6

• Next, I downloaded the file from the website using:

curl -O http://www.fda.gov/downloads/Drugs/InformationOnDrugs/UCM054599.zip

• Then I unzipped the file (using my class resources):

unzip UCM054599.zip

• I then moved the product.txt and the application.txt files into my public folder, but first created a new folder for them that would help me stay organized:

cd ~/public_html
mkdir biodb
mv application.txt ~/public_html/biodb
mv product.txt ~/public_html/biodb

• Next I was able to determine the variable for the tables by looking at the top columns:
  • application.txt contained 9 variables: ApplNo, ApplType, SponsorApplicant, MostRecentLabelAvailableFlag, CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, and Orphan_Code; and the data types are as follows (respectively): int primary key, varchar, varchar, boolean, boolean, varchar, int, varchar, and varchar.
  • product.txt contained 9 variables: ApplNo, ProductNo, Form, Dosage, ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred; and the data types are as follows (respectively): int, int, varchar, varchar, varchar, varchar, int, varchar, and varchar.

Creating the Database

• Using pgAdmin as well as the SQL editor, I was able to create the tables needed for the data:

create table application (ApplNo int primary key, ApplType char, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, 
CurrentPatentFlag boolean, ActionType varchar, ChemicalType int, Ther_Potential char, Orphan_Code char)
create table product (ApplNo int references application, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus int, 
TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)

• Now that my tables were set up, I needed to add in the appropriate data and I used sed command to do this, but I first realized that the beginning of the application.txt is not needed so I removed the first line. So, my command was this:

cat application.txt | sed "1D"

• After being stuck in a state of confusion as to what my next move was because the screen I was viewing had a lot of what appeared to be unnecessary spaces in between randomly.