Gene Database Testing Report- cw20151210

Files Asked for in the Gene Database Testing Report

For convenience, all of the files explicitly asked for in the sections below were compressed together in this file: [[]]

Pre-requisites

The following set of software was used in the creation and testing of the Bordetella pertussis gene database:

1. 7-ziptool that for unpacking .gz and .zip files
2. PostgreSQL on Windows (version 9.4.x)
3. GenMAPP Builder
4. Java JDK 1.8 64-bit
5. GenMAPP 2
6. XMLPipeDB match utility for counting IDs in XML files
7. Microsoft Access for reading .mdb files

Gene Database Creation

Downloading Data Source Files and GenMAPP Builder

• I download the UniProt XML, GOA, and GO OBO-XML files for Bordetella Pertussis along with the GenMAPP Builder program.
  • All files were saved to the folder Bklein7_CW\bpertussis_cw20151210 on my computer's ThawSpace.
  • Files that required extraction were unzipped using 7-zip.
  • Data files that remained in a folder after unzipping were removed from their folders to facilitate organization and command line processing.

UniProt XML

• I went to the UniProt Complete Proteomes page.
  • From there, I navigated to the complete proteome download page for Bordetella pertussis (strain Tohama I / ATCC BAA-589 / NCTC 13251).
  • I clicked on the "Download" button at the top of the page above and selected the following options:
    • "Download all"
    • "XML" from the "Format" drop-down menu
    • "Compressed" format
  • I extracted the file using 7-zip.

GOA

• UniProt-GOA files can be downloaded from the UniProt-GOA ftp site.
• Within the above site, I navigated to the for Bordetella pertussis strain Tohama I.
  • This text file was automatically opened by my browser. Therefore, I had to manually download the file.

GO OBO-XML

• I downloaded the GO OBO-XML formatted file from the Gene Ontology legacy download page.
• I extracted the file using 7-zip.

Downloaded GenMAPP Builder

1. I downloaded the custom version of GenMAPP Builder including the most recent version of the Bordetella pertussis custom class (Version 3.0.0 Build 5 - cw20151210): File:Dist cw20151210.zip.
2. I extracted the GenMAPP Builder folder using 7-zip.

Creating the New Database in PostgreSQL

• I launched pgAdmin III and connected to the PostgreSQL 9.4 server (localhost:5432).
  • On this server, I created a new database: bpertussis_cw20151210_gmb3build5.
  • I opened the SQL Editor tab to use an XMLPipeDB query to create the tables in the database.
    • I clicked on the Open File icon and selected the file gmbuilder.sql. This imported a series of SQL commands into the editor tab.
    • I clicked on the Execute Query icon to run this command.
    • In viewing the schema for this database, I confirmed that there were 167 tables after running the above command.

Configuring GenMAPP Builder to Connect to the PostgreSQL Database

• To begin, I launched gmbuilder.bat.
• I selected the "Configure Database" option and entered the following information into the fields below:
  • Host or address: localhost
  • Port number: 5432
  • Database name: bpertussis_cw20151210_gmb3build5
  • Username: postgres
  • Password: Welcome1

Importing Data into the PostgreSQL Database

• The downloaded data files for Bordetella pertussis were specified and imported into the database by clicking on the following buttons:
  • Selected File > Import UniProt XML...
  • Selected File > Import GO OBO-XML...
  • Clicked OK to the message asking to process the GO data.
  • Selected File > Import GOA...

Exporting a GenMAPP Gene Database (.gdb)

• I selected File > Export to GenMAPP Gene Database... to begin the export process.
• I typed my name in the owner field (Brandon Klein).
• I selected the custom profile "Bordetella pertussis, Taxon ID 257313" as the gene database species and then clicked Next.
• The database was saved as bpertussis-std_cw20151210.
• I checked the boxes for exporting all Molecular Function, Cellular Component, and Biological Process Gene Ontology Terms.
• Finally, I clicked the "Next" button to begin the export process.

Gene Database Testing Report

Export Information

Version of GenMAPP Builder: Version 3.0.0 Build 5 - cw20151210

Computer on which export was run: Seaver 120- Last computer on the right in the row farthest from the front of the room

Postgres Database name: bpertussis_cw20151210_gmb3build5

UniProt XML filename: [[]]

• UniProt XML version (The version information was found at the UniProt News Page): 2015_12
• UniProt XML download link: Bordetella pertussis (strain Tohama I / ATCC BAA-589 / NCTC 13251)
• Time taken to import: 2.88 minutes
  • Note: The import time was similar to that when creating the previous "Bordetella pertussis" gene database: bpertussis-std_cw20151203.gdb (2.59 minute). No interruptions occurred during this process.

GO OBO-XML filename: [[]]

• GO OBO-XML version (The version information was found in the file properties): Last Modified- ‎‎ ‎December ‎10, ‎2015 (TIME?)
• GO OBO-XML download link: Gene Ontology legacy download page
• Time taken to import: 6.97 minutes
• Time taken to process: 4.52 minutes
  • Note: The import and processing times were similar to those for the previous "Bordetella pertussis" gene database: bpertussis-std_cw20151203.gdb (7.08 minutes and 4.42 minutes respectively). No interruptions occurred during these processes.

GOA filename: [[]]

• GOA version (found in the Last modified field on the FTP site): Last Modified- 08-Dec-2015 02:45
• GOA download link: for Bordetella pertussis strain Tohama I
• Time taken to import: 0.03 minutes
  • Note: The import time was very similar to that of the previous "Bordetella pertussis" gene database: bpertussis-std_cw20151203.gdb (0.04 minutes). No interruptions occurred during this process.

Name of .gdb file: [[]]

• Time taken to export:
  • Start time: 1:19 AM
  • End time: 2:11 AM
  • Elapsed time: 52 minutes

Note: No interruptions occurred during the export process.

TallyEngine

• I ran the TallyEngine in GenMAPP Builder and specified the following files:
  • XML- [[]]
  • GO- [[]]
• Results:
  • 
    • All TallyEngine results were consistent across both files.
    • The TallyEngine was not customized to reflect the coding changes made to GenMAPP Builder Version 3.0.0 Build 5 - cw20151210.
      • Therefore, the total count for "Ordered Locus Names" and "ORF" gene IDs remained 3446. The extra ID that was imported in this build, "BP3167A", was not listed in either of these categories.
      • Further TallyEngine customization is necessary to raise the count to 3447 gene IDs.

Using XMLPipeDB Match to Validate the XML Results from the TallyEngine

The following functions were performed using the Windows command line (cmd).

• I entered my project folder using the following command:

cd /d T:\Bklein7_CW\bpertussis_cw20151210

• I used XMLPipeDB match to identify matches of gene IDs in the UniProt XML file that conformed to the following the patterns: "BP####", "BP####.1", "BP####A", and "BP####B". The command used was as follows:

java -jar xmlpipedb-match-1.1.1.jar "BP[0-9][0-9][0-9][0-9](A|B|\.1|)" < "uniprot-proteome%3AUP000002676_cw20151201.xml"

Match Results:

• 
  • The number of unique matches generated by XMLPipeDB Match, 3447, matched with our expectation. The count includes the total number of ordered locus (3435) and ORF (11) gene IDs along with the unique EnsemblBacteria reference ID "BP3167A".

Using SQL Queries to Validate the PostgreSQL Database Results from the TallyEngine

I ran a SQL query designed to count...:

[query goes here]

Results:

• The number of unique matches yielded by this SQL query, 3446, reflects the total number of ordered locus (3435) and ORF (11) gene IDs present in the database. This number is consistent with that produced by the TallyEngine.

OriginalRowCounts Comparison

I opened the gene database file File:Bpertussis-std cw20151210.zip in Microsoft Access and assessed the "OriginalRowCounts" table to see if the expected tables were listed with the expected number of records. The contents of this table were compared to the OriginalRowCounts table of an existing .gdb file created during Week 9.

Benchmark .gdb file: File:Vc-Std 20151027 TR.gdb

"OriginalRowCounts" table from the benchmark and new gdb:

• 
  • All 52 tables present in the 2015 Vibrio cholerae database were also present in the B. pertussis gene database, bpertussis-std_cw20151210. This confirmed that all expected tables were successfully created.
  • The "OrderedLocusNames" table count is listed as 3447. This count demonstrates that the missing ID, "BP3167A", was successfully added to the export (confirmed below).
    • 

Note: The "OriginalRowCounts" tables were too large to screenshot. To circumvent this problem and facilitate the comparison, I copied the "OriginalRowCounts" tables from both gene databases into an Excel file and zoomed out. The above screenshot was taken from this Excel file. The "OrderedLocusNames" row count for bpertussis-std_cw20151210 is highlighted in yellow.

Visual Inspection

I visually inspected individual tables within File:Bpertussis-std cw20151210.zip using Microsoft Access.

• Systems Table
  • 35 gene ID systems were listed, 11 of which were used in the creation of this .gdb file and listed the appropriate import date (12/10/2015).
    • All gene ID systems relevant to B. pertussis were listed. This includes: EMBL, EnsemblBacteria, GeneID, GeneOntology, InterPro, OrderedLocusNames, Pfam, RefSeq, and UniProt.
    • This result corresponded with that of the benchmark .gdb file listed in the "OriginalRowCounts Comparison" section.
  • The "OrderedLocusNames" listing properly displayed customizations to the Bordetella pertussis species profile.
    • In this row, the species was listed correctly as "Bordetella pertussis".
    • In this row, the link corresponded to the Bordetella pertussis database at GeneDB. The link was as follows: http://www.genedb.org/gene/~;jsessionid=A06A0EFE93C64E476380393D4CBEFA69?actionName=%2FQuery%2FquickSearch&resultsSize=1&taxonNodeName=Bpertussis.
• UniProt Table
  • This table contained 3258 entries with 6 character IDs.
  • All ID's in the UniProt table conform to the following pattern:
    • 
• RefSeq Table
  • This table contained 6627 entries. All IDs began with one of three prefixes: "NP_", "YP_", or "WP_". The meanings of these prefixes can be found in the RefSeq documentation found here.
    • "NP_" and "YP_" Prefixes
      • Refer to proteins. There are 3410 "NP_" IDs and 7 "YP_" IDs.
    • "WP_" Prefixes
      • Refer to "autonomous non-redundant proteins that are not yet directly annotated on a genome". There were 3210 IDs with the "WP_" prefixes.
    • Overall, every entry in the ID column was an expected value.
• OrderedLocusNames Table
  • This table contained 3447 entries (consistent with the XMLPipeDB Match result).
  • The IDs were copied into an Excel document for analysis:
    • 3434 IDs conformed to the pattern "BP####".
    • 11 IDs conformed to the pattern "BP####A".
      • This included 10 ORF gene IDs & "BP3167A" (reference to an EnsemblBacteria ID).
    • 1 ID exhibited the pattern "BP####B".
      • This corresponded to an ORF gene ID.
    • 1 ID exhibited the pattern "BP####.1".
      • This ID was the manner in which UniProt classified "BP3167A".