Kzebrows Week 6

Questions to Answer

For this week's assignment I worked in the computer lab in Seaver to use the Postgres tool installed on the computers. I used

kzebrows@my.cs.lmu.edu 

followed by my password to log into the LMU CMSI database.

Next, following the Direct Download/Unzipping Commands section of the Week 6 Assignment, I downloaded the FDA drug file directly to my.cs.lmu.edu and unzipped it. I opened each file using the cat application.txt or cat Product.txt commands to view them first.

Provide the DDL (create table) statements that you used for your application and product tables.

This required me to open the Postgre SQL Tutorial wiki page. I then created the following commands in SQL, following the protocol we did in class with the movie database. The command began with create table [application/Product]. Then, as Application was the primary key, the first thing I entered in parentheses after the create table command was (ApplNo int primary key; however, because Product has a foreign key as indicated by the assignment page, I just used (ApplNo int. Then I entered the names of each column that I viewed by opening the Product and Application files on Notepad, followed by either int for integers, varchar for varying characters, and boolean for columns with True or False values. The following commands to create tables in SQL looked like this:

Application:

  create table Application (ApplNo int primary key, ApplType varchar, SponsorApplicant varchar, 
  MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, 
  Chemical_Type int, Ther_Potential int, Orphan_Code varchar)

Product:

  create table Product (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus 
  varchar, TECode int, ReferenceDrug int, drugname varchar, activeingred varchar)

Note that originally, the TECode column was entered as int; however, upon creating the command (see next question) to convert the files from sed to SQL insert statements, I realized that because not everything in this code was an integer (SQL pointed this out to me with an instance of AA in the TECode column when I tried to insert the data into the SQL table) the command needed to be changed.

  create table Product (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus 
  varchar, 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.

Command to convert Application (written by Anu with edits from Kristin)

  cat application.txt | sed "s/\t/~/g" | sed -r "s/( ){2}//g" | sed "s/ \~/~/g" | sed "s/\~ /~/g" | 
  sed "s/\~\~/~null~/g" | sed "s/\~\~/~null~/g" | sed "s/\~\r$/~null/g" | sed "s/~/,'/1" | sed "s/~/','/1" | 
  sed "s/~/',/1" | sed "s/~/,/1" | sed "s/~/,'/1" | sed "s/~/',/1" | sed "s/~/,'/1" | sed "s/~/','/1" |sed "s/~/,'/1" | 
  sed "s/\r$/'/g" | sed "s/'null'/null/g" | sed "s/'null/null/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/application.sql.txt

Command to convert Product (written by Kristin with edits from Anu)

  cat Product.txt | sed "s/\*\*Federal Register determination that product was not discontinued 
  or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/\*\*Federal Register determination
  that product was not discontinued or withdrawn for safety or efficacy\*\*//g" | sed "s/\*\*Federal
  Register notice determination that product was not discontinued or withdrawn for safety or 
  efficacy reasons\*\*//g" | sed "s/\*\*Federal Registration determination that product was not discontinued
  or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/See current Annual Edition, 1.8 
  Description of Special Situations, Levothyroxine Sodium//g" | sed "s/'//g" | sed "s/\t/*/g" |
  sed -r "s/( ){4}//g" | sed -r "s/( ){2}//g" | sed "s/ \*/*/g" | sed "s/\* /*/g" | sed "s/\*\*/*null*/g" | 
  sed "s/\*\*/*null*/g" | sed "s/\*/,/1" | sed "s/\*/,'/1" | sed "s/\*/','/1" | sed "s/\*/',/1" | sed "s/\*/,'/1"
  | sed "s/\*/',/1" | sed "s/\*/,'/1" | sed "s/\*/','/1" | sed "s/\r$/'/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