Block 4: Databases and SQL

Why do we need databases? A small-scale data is easy to manage in a spreadsheet (such as MS Excell), but when you have large and diverse data, it is the moment to consider moving to a database system.

This class includes an overview of data and supporting data structures, and popular Database Management Systems (DMBS) such as basic ones with a graphical interface (Microsoft Access, Open Office Base), and more robust ones, generally controlled by SQL language (MySQL or PostgreSQL).

 


Class: March 26th 1PM Please prepare the following for the class

  1. Install postgres 8.4 (this was done in Basic Linux part 2, exercise 1)
  2. Type in your terminal

    psql --help

    to make sure your program has been installed

  3. You will need to have a linux user to connect to your database. By default we will use the current linux user (i.e. ‘bioinfo’). The database superuser is called postgres. Switch in your terminal to that user using su command by typing

    sudo su - postgres

  4. Now that you are logged in as ‘postgres’ user connect to the Postgres database server by typing the command

    psql

    You are now in a psql terminal.

  5. Add your default linux user to the database server by typing in your psql terminal

    CREATE USER bioinfo WITH PASSWORD 'bioinfo' ;

    Now create a password for your psql postgres user (you will need this for root access to the database later on)

    \password postgres

    The Upper-case commands here are in the database language SQL, which is case insensitive, but written in upper-case for convenience. The username and password are case-sensitive. Don’t forget the semi-colon at the end of each SQL statement!

  6. User ‘bioinfo’ and password ‘bioinfo’ are the defaults provided with your original virtual-machine. Use your linux user and password if you have changed this.
  7. Create a new empty database. This will be used later on in class. Type in your psql terminal

    CREATE DATABASE my_database;

    (my_database is now your database name, but you can name it whatever you want).

  8. Grant privileges on your new database to your linux user, the same one from step #5 above. Type

    GRANT ALL PRIVILEGES ON DATABASE my_database TO bioinfo ;

  9. Quit from your database terminal. Type

    \q

    Now you are back to your linux terminal, but logged in as ‘postgres’ user. Type

    exit

    to logout. You should be logged now as ‘bioinfo’.

  10. Test user ‘bioinfo’ database terminal login. Type in your linux terminal

    psql -U bioinfo -d my_database

    psql is the command to open a postgres terminal -U is the option for typing the username, -d is the option for the database name.

  11. Are you in a psql database terminal ? Type

    help

    Do you get some message starting with ‘you are using psql? If yes, type \q to quit the terminal, and you are all set. If not, try to figure out why or come see Naama or Aure.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: