Difficulty: Beginner

This tutorial is for the Lecture 3 of the Data Modelling and Databases class [http://www.ds3lab.com/dmdb-2020/#lecture3].

Goal

In Lecture 3, we looked at a range of query languages, including Relational Calculus, Relational Algebra, Conjunctive Query, and SQL. However, but just looking at the slides, it is hard to grasp how a real-world database system actually works. This tutorial is to facility our understanding of how DB interacts with the user and other programs.

After this tutorial, you should be able to understand:

  • How to install a DBMS in Ubuntu.
  • How to create a DB using the DBMS we installed.
  • How to create relations inside the DB we created.
  • How to interact with the DB from other programs.
  • How to interact with the DB from the built-in DBMS client.
  • How to bulk load and dump data, and how to issue simple SQL queries.

Lecture 3: DB in Action & Simple SQL Queries

Step 1 of 6

Step 1

Installing a DBMS

We first install a DBMS in Ubuntu. Here we will install PostgreSQL and use it as an example.

Setup

Like all software, to install a DBMS, we need to set-up our environments and dependencies. This process is particularly easy for installing PostgreSQL in Ubuntu. We first step-up the apt-get environment as follows:

apt-get install wget ca-certificates

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

apt-get update

We can now install PostgreSQL:

apt-get install -y postgresql postgresql-contrib

Start DBMS

Now we have PostgreSQL installed, we can start it by using the following command.

pg_ctlcluster 12 main start

Connect

Now PostgreSQL is running as a service in the background and we can connect to it using its default client psql. By default, PostgreSQL will create a user postgres and it is easier to connect to it as this user.

We first swtich the user to postgres

su - postgres

Runinng psql will connect us with the DBMS engine.

psql

We can now type some commands. For example, let's change the password for our default user postgres.

ALTER USER postgres PASSWORD 'myPassword';

We can quit the psql program with