Biological Database Systems, BE768, Spring 2008

This page is often updated. Please check this page out before each lecture if you get a chance. Press the Reload button of your browser to view the newest version. For questions email: Gary Benson gbenson@bu.edu .

Instructors Info
Name	Phone #	Office Hours	Email
Dr. Gary Benson	(617) 358-2965	By Appointment Office: Rm 903, 24 Cummington St.	gbenson@bu.edu
Teaching Assistant: Stephanie Tauber	(617) 353-8733	T 3-4pm and by appointment Lab: Rm 620, 24 Cummington St.	stauber@bu.edu

Tutorials

Perl: http://www.perl.com and http://www.cpan.org/ (where the good stuff is)

SQL: http://www.w3schools.com/sql/ and http://dev.mysql.com/doc/

MySQL: http://www.mysql.com/products/mysql/ and http://dev.mysql.com/doc/administrator/en/index.html (graphical adminstrator)

CGI: http://cgi-spec.golux.com/ and http://www.cgi101.com/book/

HTML: http://www.w3schools.com/html/ and http://www.w3schools.com/css/ (style sheets)

Perl CGI: http://www.perl.com/pub/a/2001/06/05/cgi.html

Perl DBI: http://www.perl.com/pub/a/1999/10/DBI.html

Perl SOAP: http://www.perl.com/pub/a/2001/01/soap.html

PHP: https://bioed.bu.edu/phpMyAdmin/Documentation.html

Documentation Resource for HTML, PHP, MySQL, Perl: http://gotapi.com/

Class Responsibilities

Public BioDB Presentation. You will give a presentation about a public biological database. Your presentation should last approximately 15 minutes and should highlight the data and capabilities of the database. Only relatively well known databases can be used for the presentations. Your presentation will be in powerpoint format using screenshots. NO live access to the database will be allowed during your presentation. After your presentation your powerpoint file will be placed on the class website along with a link to the database. Guidelines (pdf)

Class Project. The goal of this project is for you to learn how to build a biological database by actually building one. You will do this in small groups, typically two or three students to a group (depending on class size). You select your own group. These are the steps you should take:

Determine what type of data your database will store. This will be your choice, but consult with me before you make a final decision. To help you find realistic projects, we will have guest lectures by several biology and medical school professors who will speak about their database needs. A project with one of them has a strong potential to result in a journal publication.
Design the database, i.e. produce an annotated diagram of the conceptual schema for the data objects and the relations between the objects.
Implement the schema using SQL, Perl (or another programming language), and one of the database management systems (DBMSs) which will be made available to you (MySQL or PostgreSQL).
Draw up a set of common questions that could be asked by a user of your database and design a set of efficient queries (in SQL) that answer those questions.
Create a Web interface for your database using HTML and CGI. Make this easy to use and intuitive.
Create informative ways to view the results of a database query. For example, use graphics to display the results.
Present your database to the class at the end of the semester in the Database Design Competition.

Schedule

Date

Topics

HW Assignment

HW Solutions

Jan 17

Examples of Biological Data

Lab 1 - Accessing bioed and MySQL
Instructions for Accessing the Lab (LSEB B03)
Instructions for Uploading Files to bioed

Readings:
Ch 1.5.1, 2-2.5

HW 1
Due: Jan 24

Jan 22

The Entity-Relationship Model
ER Diagrams

Example 1 - ER Diagram
Example 1 - SQL Create Table Schema

Readings:
Ch 3-3.5

Jan 24

Creating a SQL Database from the ER Model (1)
(entities, attributes, key constraints, relationship classes)

HW 2
Due: Jan 31

HW2

Jan 29

Creating a SQL Database from the ER Model (2)
(foreign keys, weak entities, aggregations)

Lab 2.1 - Creating Tables with MySQL
Using mySQL via phpMyAdmin

Jan 31

Creating Tables in MySQL - Enforcing Constraints

Lab 2.2 - Creating Tables with MySQL
Tables with Foreign Keys via phpMyAdmin
Table Instances (text file)

HW 3
Due: Feb 7

HW3

Feb 5

Relational Algebra
Sample Problems

Feb 7

Relational Algebra & Select Statements

Lab 3 - More Useful SQL Functions

HW 4
Due: Feb 14

HW4

Feb 12

Nested Subqueries

Lab 4 - Perl DBI
DBI Template (text)
Using Perl DBI - Tutorial
Additional Perl DBI Methods

Glycomics Database Project Presentation (PPT)

Feb 14

Aggregate Queries

HW 5
Due: Feb 21

HW5

Feb 19

(No class - Monday schedule)

Feb 21

Client-Server Interactions

HW 6
Due: Feb 28

HW6 - Queries
HW6 - Output

Feb 26

Functional Dependencies

Feb 28

Functional Dependencies

Lab 5.1 - Creating Forms with CGI
Perl CGI Template
Handout: Using Perl CGI

Draft Project Proposal
Due: Mar 4

Mar 4

Key Concepts for Midterm

Lab 5.2 - Responding to User Input with CGI

Mar 6

MIDTERM

HW 7
Due: Mar 20

Mar 11&13

Spring Break

Mar 18

Public Database Presentations

Lab 6.1 - Perl GD, GD Graph, and Image Maps

Using Perl GD      Using Perl GD Graph
perlgd-example.cgi (text file)      perlgdgraph-example.cgi (text file)

Using HTML Image Maps
Image Map Example 1      Image Map Example 2

HW 8
Due: Mar 27

Mar 20

Public Database Presentations

Lab 6.2 - Perl GD, GD Graph, and Image Maps

Mar 25

Lab 5: Connecting to NCBI
blast_example.pl (zip file)
efetch_example.pl (zip file)
See also: http://ncbi.nlm.nih.gov/blast/blastcgihelp.shtml
and: http://ncbi.nlm.nih.gov/Class/PowerTools/blast/slides/intro_2_wwwblast.doc

Mar 27

web blast example (text file)

Apr 1

Apr 3

JavaScript PowerPoint Presentation

Apr 8

Open Lab

Apr 10

Programming Resources
Name	Web Site	Description
Toolkit for Oracle (TOra)	Globecom	SQL GUI for Oracle and other DBMS's
MySQLStudio	PremiumSoft
MyTool	SurreySoftware
DBQwikedit	TDS
SQL Grinder	Advenio	SQL GUI for Oracle and other DBMS's for Mac OS X

Books
Title	Author/s	Publisher
SQL
Database Management Systems	Ramakrishnan and Gehrke	McGraw Hill
SQL in a Nutshell	Kline & Kline	O'Reilly
Introduction to Database Systems	Date	Addison Wesley
Perl
Programming Perl	Wall, Christiansen & Orwant	O'Reilly
Beginning Perl for Bioinformatics	Tisdall	O'Reilly
Programming the Perl DBI	Descartes & Bunce	O'Reilly
Perl Cookbook	Christiansen & Torkington	O'Reilly
CGI Programming with Perl	Guelich, Gundavaram & Birznieks	O'Reilly
Graphics Programming with Perl	Verbruggen	Manning
Effective Perl Programming	Hall & Schwartz	Addison Wesley
Bioinformatics
Computational Methods in Molecular Biology	Salzberg, Searls & Kasif	Elsevier
Developing Bioinformatics Computer Skills	Gibas & Jambeck	O'Reilly
Bioinformatics: A Practical Guide to the Analysis of Genes and Proteins	Baxevanis & Ouellette	Wiley InterScience
Bioinformatics: Sequence and Genome Analysis	Mount	Cold Spring Harbor Lab. Press
Java
Java 2 Bible	Walsh, Couch & Steinberg	IDG
Java Cookbook	Darwin	O'Reilly
Learning Java	Niemeyer & Knudsen	O'Reilly
Java Servlet Programming	Hunter & Crawford	O'Reilly
Database Programming with JDBC & Java	Reese	O'Reilly
Java in a Nutshell	Flanagan	O'Reilly
Other Programming Languages
Learning XML	Ray	O'Reilly
Jython Essentials	Pedroni & Rappin	O'Reilly
Learning Python	Lutz & Ascher	O'Reilly

Important Dates for Boston University (see also here)
Day	Date	Event
Wednesday	January 16	Instruction Begins
Monday	January 21	Holiday, Classes Suspended
Monday	February 18	Holiday, Classes Suspended
Tuesday	February 19	Substitute Monday Schedule of Classes
Saturday - Sunday	March 8 - March 16	Spring Recess
Monday	March 17	Instruction Resumes
Monday	April 21	Holiday, Classes Suspended
Wednesday	April 23	Substitute Monday Schedule of Classes
Thursday	May 1	Instruction Ends
Friday - Monday	May 2 - May 5	Study Period
Tuesday	May 6	Final Exams Begin
Tuesday	May 13	Final Exams End
Sunday	May 18	COMMENCEMENT