Introduction to SQL

Before we begin:

- Did you install Red Hat Linux 7.1 on your computer?
- Did you download and install MySQL locally?
- Is Perl DBI modules installed on your computer?

Why use a database?

- versus a file system


Basic Database Concept:

• What is Object-based database?
• What is Object-Oriented database?
• What is a relational database?
• DDL (Data Definition Language) - use to declare the structure of the database to DBMS
• DML (Data Manipulation Language) -
• relation = tables ~ files
• attribute = columns ~ fields
• tuples = rows ~ records
• relationships

• Characteristics of Biological Data <-- list

• Some coments by Paul Litwin

Scenario: Class design problem

As you walk down the hall, you meet XX who tells you:

"We need a new database as soon as possible to do the following:

manage sequence and other information for mammalian membrane proteins
with 7 transmembrane domains:

I want to be able to find information and generate reports based on the
following:

the species of origin
the chromosome of origin
the official gene symbol
gene names
protein names
sequence accession numbers
protein molecular weight
genetic variants
EC numbers
expression patterns

We will first discuss how to convert this 'vague' request into a draft
schema to refine after talking more with XX.

NOTE: we need to keep this simple, and do not want to spend multiple days
discussing multiple options of the design. So in preparing for the first
class, don't start worrying for example, about whether the molecular
weight is Mr or mass calculated from the amino acids sequence or ...

Read the Chapter 9: MySQL Tutorial of the full MySQL manual.

Once we have our schema we can go ahead and start writing the SQL for retrieving, modifying data, stored procedures. >> NEXT

Database Concept Links:

• http://www.citilink.com/~jgarrick/vbasic/database/
• http://www.citilink.com/~jgarrick/vbasic/database/fundamentals.html
• http://www.microsoft.com/TechNet/Access/technote/ac101.asp
  

MySQL and SQL Tutorial/Documentation:

• http://www.mysql.com/doc/ --> documentation for MySQL
• MySQL [Local] [Online - http://www.mysql.com/documentation/mysql/full/]
• MySQL [Adobe Acrobat]
• MySQL Text Version [Download - tar.gz]
• SQL Tutorial:
  • Baycongroup SQL Tutorial: http://www.baycongroup.com/tocsql.htm
  • jhoffman's sql tutorial: http://www.dbbm.fiocruz.br/class/Lecture/d17/sql/jhoffman/sqltut.html
  • Interactive/On-line SQL Tutorial with SQL Interpretator & live practice database -- http://www.sqlcourse.com/ HIGHLY RECOMMENDED. Use MySQL when they ask for a database.

Resources:

• MySQL --> http://www.mysql.com/
• http://www.nusphere.com/ --> commercial version of MySQL includes transaction processing