Lenaolufson Week 6 - Revision history

Lenaolufson: added template

2015-10-15T05:13:23Z

added template

Lenaolufson: completed the answers to the questions

2015-10-15T05:11:45Z

completed the answers to the questions

Lenaolufson: answered first 3 questions

2015-10-15T04:25:53Z

answered first 3 questions

Lenaolufson: finished creating the commands for the application and product

2015-10-15T04:09:21Z

finished creating the commands for the application and product

Lenaolufson: first part of the assignment

2015-10-15T02:41:03Z

first part of the assignment

New page

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

← Older revision		Revision as of 05:13, 15 October 2015
Line 130:		Line 130:
	select sponsorapplicant from product,application where application.applno = product.applno and sponsorapplicant ~ 'LABS' and activeingred ~ 'ASPIRIN' and activeingred ~ 'CAFFEINE' group		select sponsorapplicant from product,application where application.applno = product.applno and sponsorapplicant ~ 'LABS' and activeingred ~ 'ASPIRIN' and activeingred ~ 'CAFFEINE' group
	by sponsorapplicant		by sponsorapplicant
		+
		+	{{Template:Lenaolufson}}

@@ Line 47: / Line 47: @@
 ===Questions to Answer===
-#Provide the DDL (create table) statements that you used for your application and product tables.
+*Provide the DDL (create table) statements that you used for your application and product tables.
 application.txt:
   create table application (ApplNo int primary key, ApplType varchar,
@@ Line 56: / Line 56: @@
   Dosage varchar, ProductMktStatus int, TECode varchar, ReferenceDrug int,
   drugname varchar, activeingred varchar)
-#Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.
+*Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.
 product.txt:
   cat Product.txt |
@@ Line 77: / Line 77: @@
   sed "s/^/insert into application (ApplNo,ApplType,SponsorApplicant,MostRecentLabelFlag,CurrentPatentFlag,ActionType,
                                   ChemicalType,Ther_Potential,OrphanCode) values (/g" > ~/public_html/application.sql.txt
-#Using the command line, how can you determine the number of records in each file? Provide the command.
+*Using the command line, how can you determine the number of records in each file? Provide the command.
 The command <code>wc</code> tells us the number of lines, the word count, and byte count for every file. For this assignment, one line is used for the column headers so the number of records is one less than the number of lines listed. Thus, application.txt has 19,746 records and product.txt has 32,770 records. Commands are:
   cat application.txt | wc
   cat product.txt | wc
+*Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL select statement.

← Older revision		Revision as of 04:25, 15 October 2015
Line 45:		Line 45:
	sed "s/\t/,'/1"\| sed "s/\t/',/1" \| sed "s/\t/,'/1" \| sed "s/\t/','/1" \| sed "s/\r/'\r/g" \| sed "s/'null'/null/g" \| sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, ProductMktStatus,		sed "s/\t/,'/1"\| sed "s/\t/',/1" \| sed "s/\t/,'/1" \| sed "s/\t/','/1" \| sed "s/\r/'\r/g" \| sed "s/'null'/null/g" \| sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, ProductMktStatus,
	TECode, ReferenceDrug, drugname, activeingred) values(/g" \| sed "s/\r/);\r/g" > ~/public_html/product.sql.txt		TECode, ReferenceDrug, drugname, activeingred) values(/g" \| sed "s/\r/);\r/g" > ~/public_html/product.sql.txt
		+
		+	===Questions to Answer===
		+	#Provide the DDL (create table) statements that you used for your application and product tables.
		+	application.txt:
		+	create table application (ApplNo int primary key, ApplType varchar,
		+	SponsorApplicant varchar, MostRecentLabelFlag boolean, CurrentPatentFlag boolean,
		+	ActionType varchar, ChemicalType int, Ther_Potential varchar, OrphanCode varchar);
		+	product.txt:
		+	create table product (ApplNo int references application, ProductNo int, Form varchar,
		+	Dosage varchar, ProductMktStatus int, TECode varchar, ReferenceDrug int,
		+	drugname varchar, activeingred varchar)
		+	#Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.
		+	product.txt:
		+	cat Product.txt \|
		+	sed "s/'/\"/g" \|
		+	sed "s/\t/','/g"\|
		+	sed "s/\r$/');/g" \|
		+	sed "s/''/null/g" \|
		+	sed "s/'//1" \|
		+	sed "1d" \|
		+	sed "s/^/insert into product (ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,
		+	ReferenceDrug,drugname,activeingred) values (/g" > ~/public_html/Product.sql.txt
		+	application.txt:
		+	cat application.txt \|
		+	sed "s/\t/','/g" \|
		+	sed "s/ *'/'/g" \|
		+	sed "s/\r$/');/g" \|
		+	sed "s/''/null/g" \|
		+	sed "s/'//1" \|
		+	sed "1d" \|
		+	sed "s/^/insert into application (ApplNo,ApplType,SponsorApplicant,MostRecentLabelFlag,CurrentPatentFlag,ActionType,
		+	ChemicalType,Ther_Potential,OrphanCode) values (/g" > ~/public_html/application.sql.txt
		+	#Using the command line, how can you determine the number of records in each file? Provide the command.
		+	The command <code>wc</code> tells us the number of lines, the word count, and byte count for every file. For this assignment, one line is used for the column headers so the number of records is one less than the number of lines listed. Thus, application.txt has 19,746 records and product.txt has 32,770 records. Commands are:
		+	cat application.txt \| wc
		+	cat product.txt \| wc
		+	#

@@ Line 29: / Line 29: @@
 *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.
+*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. After talking it over with a classmate, I was able to see that some of these spaces were used with the data. So, I took out the spaces that were next to another space as those were not needed using: