ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred) values (/g"

| sed "s/$/);/g" | sed "1D" > ~/public_html/Product.sql.txt

*Aside- In the section below, I detail ways I could have simplified these command sequences.

===Creating a Database with the Drugs@FDA Data Using SQL===

*Creating the Database

**''product'' table

***I copied over the SQL command sequence from [http://my.cs.lmu.edu/~bklein7/Product.sql.txt] and pasted it into the SQL editor. From there, I ran the command sequence.

−

*The data was now ~~viable~~ in ''pgAdminIII''.

*The data was now available in ''pgAdminIII''.

==The FDA Drug Database==

===Questions to Answer===

# Provide the DDL (<code>create table</code>) statements that you used for your ''application'' and ''product'' tables.

#*''application'' table: <code>create table application (ApplNo int, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag varchar, CurrentPatentFlag varchar, ActionType varchar, Chemical_Type int, Ther_Potential varchar, Orphan_Code varchar)</code>

#*''product'' table: <code>create table Product (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus int, TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)</code>

# Provide the <code>sed</code> command sequences that you used to convert the raw text files into sequences of SQL <code>insert</code> statements.

#*The following sed command sequence was used to convert the ''application.txt'' file into sequences of SQL <code>insert</code> statements:

cat application.txt | sed "s/,/~/g" | sed "s/ /,/g" | sed -r "s/( ){2}//g"

| sed "s/,,/,NULL,/g;s/,,/,NULL,/g" | sed "s/ ,/,/g" | sed "s/,/,'/1;s/,/','/2;

s/,/',/3;s/False/'False'/g;s/,/,'/5;s/,/',/6;s/,/,'/7;s/,/',/8" | sed "s/,\r$/,NULL/g;

s/V\r$/'V'/g" | sed "s/'NULL'/NULL/g" | sed "s/~/,/g" | sed "s/^/insert into

application(ApplNo, ApplType, SponsorApplicant, MostRecentLabelAvailableFlag,

CurrentPatentFlag, ActionType, Chemical_Type, Ther_Potential, Orphan_Code)

values (/g" | sed "s/$/);/g" | sed "1D" > ~/public_html/application2.sql.txt

#*The following sed command sequence was used to convert the ''Product.txt'' file into sequences of SQL <code>insert</code> statements:

cat Product.txt | sed "s/'/<nowiki>''</nowiki>/g" | sed "s/,/<comma>/g" | sed "s/ /,/g"

| sed "s/,,/,NULL,/g;s/,,/,NULL,/g" | sed "s/,\r$/,NULL/g" | sed "s/,/,'/2;s/,/','/3;

s/,/',/4;s/,/,'/5;s/,/',/6;s/,/,'/7;s/,/','/8;s/\r$/'/g" | sed "s/'NULL'/NULL/g"

| sed "s/<comma>/,/g" | sed "s/^/insert into Product(ApplNo, ProductNo, Form, Dosage,

ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred) values (/g"

| sed "s/$/);/g" | sed "1D" > ~/public_html/Product.sql.txt

#*Aside: After reviewing the assignment's supplementary information (which I believe has been updated since I began the assignment?), I realized some ways I could have simplified the code above. For example, the extensive command sequence used to specifically place single quotes only around varchars could have been replaced with a global command to simple put single quotes around every variable (in the spirit of <code>sed "s/,/','/g"</code>). Additionally, the control-V trick I used for tab could have been replaced by <code>\t</code>. However, the above sequences are the ones I used to get the necessary SQL command statements.

# Using the command line, how can you determine the number of records in each file? Provide the command.

#*For ''application.txt'', I used the command <code>wc application.txt</code>. This yielded the output <code> 19747 147336 1615694 application.txt</code>.

#*The above output shows that the ''application.txt'' file has a total of 19,747 lines. Because the first line is the variable labels, we subtract one. The total number of records is thus <code>19746</code>

#*For ''Product.txt'', I used the command <code>wc Product.txt</code>. This yielded the output <code> 32771 369379 2856017 Product.txt

</code>.

#*The above output shows that the ''Product.txt'' file has a total of 32,771 lines. Because the first line is the variable labels, we subtract one. The total number of records is thus <code>32770</code>

# Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL <code>select</code> statement.

#*To count the number of records using SQL, I used the command <code>select count(*) from table</code>.

#**For the ''application'' table, I used the command <code>select count(*) from application</code> and received the output <code>19746</code>. This matches the number of records calculated using the command line.

#**For the ''product'' table, I used the command <code>select count(*) from product</code> and received the output <code>32770</code>. This matches the number of records calculated using the command line.

# In your database, are these numbers the same or different? Explain why you think so.

Exception encountered, of type "Error"
[8f152688] /biodb/fall2015/index.php?diff=prev&oldid=2234&title=Bklein7_Week_6 Error from line 434 of /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php: Call to undefined function each()
Backtrace:
#0 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(544): DiffEngine->diag()
#1 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(344): DiffEngine->compareSeq()
#2 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(227): DiffEngine->diffLocal()
#3 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(721): DiffEngine->diff()
#4 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(859): Diff->__construct()
#5 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(980): MappedDiff->__construct()
#6 /apps/xmlpipedb/biodb/fall2015/includes/diff/TableDiffFormatter.php(194): WordLevelDiff->__construct()
#7 /apps/xmlpipedb/biodb/fall2015/includes/diff/DiffFormatter.php(140): TableDiffFormatter->changed()
#8 /apps/xmlpipedb/biodb/fall2015/includes/diff/DiffFormatter.php(111): DiffFormatter->block()
#9 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(888): DiffFormatter->format()
#10 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(802): DifferenceEngine->generateTextDiffBody()
#11 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(733): DifferenceEngine->generateContentDiffBody()
#12 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(662): DifferenceEngine->getDiffBody()
#13 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(632): DifferenceEngine->getDiff()
#14 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(453): DifferenceEngine->showDiff()
#15 /apps/xmlpipedb/biodb/fall2015/includes/page/Article.php(795): DifferenceEngine->showDiffPage()
#16 /apps/xmlpipedb/biodb/fall2015/includes/page/Article.php(506): Article->showDiffPage()
#17 /apps/xmlpipedb/biodb/fall2015/includes/actions/ViewAction.php(44): Article->view()
#18 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(395): ViewAction->show()
#19 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(273): MediaWiki->performAction()
#20 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(566): MediaWiki->performRequest()
#21 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(414): MediaWiki->main()
#22 /apps/xmlpipedb/biodb/fall2015/index.php(44): MediaWiki->run()
#23 {main}