This tutorial is for the Lecture 3 of the Data Modelling and Databases class [http://www.ds3lab.com/dmdb-2020/#lecture3].
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
Installing a DBMS
We first install a DBMS in Ubuntu. Here we will install PostgreSQL and use it as an example.
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
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'
We can now install
apt-get install -y postgresql postgresql-contrib
Now we have
PostgreSQL installed, we can start it
by using the following command.
pg_ctlcluster 12 main start
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
su - postgres
psql will connect us with the DBMS engine.
We can now type some commands. For example, let's change
the password for our default user
ALTER USER postgres PASSWORD 'myPassword';
We can quit the
psql program with