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

A Practical Approach to Database Design

In Class Design Problem:

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

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.

Next lecture:

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

Useful Links

Database Concept Links:

MySQL and SQL Tutorial/Documentation: