PostgreSQL MySQL Oracle

cuttlefishblueData Management

Dec 16, 2012 (4 years and 7 months ago)

239 views

Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
EGTDC Database Course 2004
Database Servers
Database Interfaces
Tim Booth : tbooth@ceh.ac.uk
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
What is an RDBMS?

A PostgreSQL database is not just kept in a file, like a spreadsheet.

A program called the database server, or RDBMS, manages all the data 
for us.

We access the data via a client application which connects to the RDBMS.
Databases
User
Client software
RDBMS
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Why use an RDBMS?
With RDBMS:

Multiple concurrent users

Advanced security

Remote access

Reliability

You can create a web interface in Perl or PHP.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Which RDBMS to choose?
PostgreSQL
MySQL
Oracle
Free (BSD)
Free (GPL)/Commercial
Commercial
Just about any platform
Many platforms
Full transaction support
Full transaction support
No subqueries or views (yet)
No stored procedures
Stored procedure support
Unix, including Linux or
MacOS X
Windows only via Cygwin or commercial
port
Limited support for
transactions
Full subqueries and updatable
views
Full subqueries and updatable
views
Stored procedures via plugin
modules
Supports groups, table-level
user privileges
Fine-grained user privileges,
no group support
Full user/group privilege
support
PgAdminIII or pgpPgAdmin are
the best development tools
around. TOra support is in the
pipeline.
phpMyAdmin (web based)
TOra
(a free tool originally for Oracle)
Also MySQL administrator
(still in beta release)
Comprehensive range of
commercial software support.
Administration/development
via TOra
Less well established, until
recently regarded as
essentially experimental code.
Rapidly gaining acceptance
and support.
Well established, especially in
web development.
Known to be good at what it
does, though currently missing
many advanced features.
Very well established. Oracle
market their software as
'unbreakable', and it is highly
regarded.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Some online resources
In case you are called upon to justify choice of software:

The source for my comparison table
http://www.602.cz/produkty/602sql/comparison.htm

A VERY exhaustive comparison of different database facilities
http://dev.mysql.com/tech­resources/crash­me.php

If you must run PostgreSQL on windows:
http://techdocs.postgresql.org/guides/Windows
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Databases on Bio­Linux

PostgreSQL:

Installed and ready to run:
sudo service postgresql start
sudo redhat­config­services  
­ You can set it to run every boot

MySQL:

Runs by default! ­ Just type:
sudo mysql

 
Initialise any users you need, and off you go.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Database Clients

Any program which lets a user communicate with a database.

Custom – designed for end­users, masks the database internals.

Specific to database, could be web­based or standalone

eg Google Search, Ensembl web interface

Generic – you can execute SQL directly

Used by database developers and administrators

May be command­line based, graphical, web based

Connect to any database: eg. Microsoft Access, PGAdmin, psql
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
PgAdminIII

Used on this course

Runs on a variety of platforms

Only for PostgreSQL

Graphical interface to create tables, manage privileges etc.

SQL editor with syntax highlighting and online help
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Command­line clients

Every RDBMS has a basic command­line client – eg psql:
psql ­h ivgfs
Type SQL commands – don't forget to end with a semicolon
Backslash commands to show table names and descriptions:
\d
­ List all tables
\d 
tablename
­ Describe a table
\q
 
­ To quit

This interface may look basic, but like the Linux shell it has some useful 
features, for example tab completion of table/column names.

You might also want to look at 'dbish', a command­line interface to any 
database.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
phpPgAdmin

Has most of the features of pgAdmin3

Access is via a web browser.

You need to set up the scripts and run Apache on the machine.
http://phppgadmin.sourceforge.net/
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Hooking up with Microsoft Access

You can view and manipulate the data in your PostgreSQL or MySQL 
databases using Access on a Windows PC.

Access has a very flexible GUI, but the actual database component is 
weak.

You first need to setup remote connections to PostgreSQL to the 
machine.

(more on that tomorrow ­  see http://envgen.nox.ac.uk/envgen/software/archives/000447.html)

You will also need some extra software on the Windows box ­ this is 
outlined on the above site.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Some EG software which uses 
PostgreSQL

The maxd suite of software for processing microarray results 
incorporates maxdLoad2, which enables storage of experiments in 
various databases, including PostgreSQL.
  The software comes pre­
installed on Bio­Linux 3.
http://envgen.nox.ac.uk/maxd.html

PartiGene is an EST clustering application.  It can save results into a 
PostgreSQL database.  Also pre­installed on Bio­Linux.
http://envgen.nox.ac.uk/est.html

Msatminer is a suite of tools to extract and catalogue microsatellite 
repeats in small genomes.  It can save data to PostgreSQL or MySQL.
http://www.genomics.ceh.ac.uk/msatminer/