Introduction to Databases

Before using PostgreSQL, let’s make sure of our ground by covering general
database theory. You will not need to enter any of the example code; it’s
only there for illustration purposes.

The goal for this lesson: To understand fundamental database concepts.

15.1.1. What is a Database?

A database consists of an organized collection of data for one or more uses, typically in digital
form. - Wikipedia

A database management system (DBMS) consists of software that operates databases, providing
storage, access, security, backup and other facilities. - Wikipedia

15.1.2. Tables

In relational databases and flat file databases, a table is a set of data elements (values) that is
organized using a model of vertical columns (which are identified by their name) and horizontal
rows. A table has a specified number of columns, but can have any number of rows. Each row is
identified by the values appearing in a particular column subset which has been identified as a
candidate key. - Wikipedia

id | name | age
----+-------+-----
1 | Tim | 20
2 | Horst | 88
(2 rows)
In SQL databases a table is also known as a relation.

15.1.3. Columns / Fields

A column is a set of data values of a particular simple type, one for each row of the table. The
columns provide the structure according to which the rows are composed. The term field is often
used interchangeably with column, although many consider it more correct to use field (or field
value) to refer specifically to the single item that exists at the intersection between one row and
one column. - Wikipedia

A column:

| name |
+-------+
| Tim |
| Horst |
A field:

| Horst |

15.1.4. Records

A record is the information stored in a table row. Each record will have a field for each of the
columns in the table.

2 | Horst | 88 <-- one record

15.1.5. Datatypes

Datatypes restrict the kind of information that can be stored in a column. - Tim and Horst

There are many kinds of datatypes. Let’s focus on the most common:

 String - to store free-form text data

 Integer - to store whole numbers

 Real - to store decimal numbers

 Date - to store Horst’s birthday so no one forgets

 Boolean - to store simple true/false values

You can tell the database to allow you to also store nothing in a field. If there is nothing in a
field, then the field content is referred to as a ‘null’ value:

insert into person (age) values (40);

select * from person;

Result:

id | name | age
---+-------+-----
1 | Tim | 20
2 | Horst | 88
 4 | | 40 <-- null for name
(3 rows)
There are many more datatypes you can use - check the PostgreSQL manual!

15.1.6. Modelling an Address Database

Let’s use a simple case study to see how a database is constructed. We want to create an address
database.

The properties that describe an address are the columns. The type of information stored in each
column is its datatype. In the next section we will analyse our conceptual address table to see
how we can make it better!

15.1.7. Database Theory

The process of creating a database involves creating a model of the real world; taking real world
concepts and representing them in the database as entities.

15.1.8. Normalisation

One of the main ideas in a database is to avoid data duplication / redundancy. The process of
removing redundancy from a database is called Normalisation.

Normalization is a systematic way of ensuring that a database structure is suitable for general-
purpose querying and free of certain undesirable characteristics - insertion, update, and deletion
anomalies - that could lead to a loss of data integrity. - Wikipedia

There are different kinds of normalisation ‘forms’.

Let’s take a look at a simple example:

Table "[Link]"

Column | Type | Modifiers

----------+------------------------+------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
| |
name | character varying(50) |
address | character varying(200) | not null
 phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
select * from people;

id | name | address | phone_no

---+---------------+-----------------------------+-------------
1 | Tim Sutton | 3 Buirski Plein, Swellendam | 071 123 123
2 | Horst Duester | 4 Avenue du Roix, Geneva | 072 121 122
(2 rows)
Imagine you have many friends with the same street name or city. Every time this data is
duplicated, it consumes space. Worse still, if a city name changes, you have to do a lot of work
to update your database.

15.1.10. Indexes

A database index is a data structure that improves the speed of data retrieval operations on a
database table. - Wikipedia

Imagine you are reading a textbook and looking for the explanation of a concept - and the
textbook has no index! You will have to start reading at one cover and work your way through
the entire book until you find the information you need. The index at the back of a book helps
you to jump quickly to the page with the relevant information:

create index person_name_idx on people (name);

Now searches on name will be faster:

Table "[Link]"

Column | Type | Modifiers

----------+------------------------+-------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
| |
name | character varying(50) |
address | character varying(200) | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"person_name_idx" btree (name)

15.1.11. Sequences

A sequence is a unique number generator. It is normally used to create a unique identifier for a
column in a table.
In this example, id is a sequence - the number is incremented each time a record is added to the
table:

id | name | address | phone_no

---+--------------+-----------------------------+-------------
1 | Tim Sutton | 3 Buirski Plein, Swellendam | 071 123 123
2 | Horst Duster | 4 Avenue du Roix, Geneva | 072 121 122

15.1.12. Entity Relationship Diagramming

In a normalised database, you typically have many relations (tables). The entity-relationship
diagram (ER Diagram) is used to design the logical dependencies between the relations.
Consider our non-normalised people table from earlier in the lesson:

select * from people;

id | name | address | phone_no

----+--------------+-----------------------------+-------------
1 | Tim Sutton | 3 Buirski Plein, Swellendam | 071 123 123
2 | Horst Duster | 4 Avenue du Roix, Geneva | 072 121 122
(2 rows)
With a little work we can split it into two tables, removing the need to repeat the street name for
individuals who live in the same street:

select * from streets;

id | name
----+--------------
1 | Plein Street
(1 row)
and:

select * from people;

id | name | house_no | street_id | phone_no

----+--------------+----------+-----------+-------------
1 | Horst Duster | 4 | 1 | 072 121 122
(1 row)
We can then link the two tables using the ‘keys’ [Link] and people.streets_id .

If we draw an ER Diagram for these two tables it would look something like this:

The ER Diagram helps us to express ‘one to many’ relationships. In this case the arrow symbol
show that one street can have many people living on it.
Answer

15.1.13. Constraints, Primary Keys and

Foreign Keys

A database constraint is used to ensure that data in a relation matches the modeller’s view of how
that data should be stored. For example a constraint on your postal code could ensure that the
number falls between 1000 and 9999 .
A Primary key is one or more field values that make a record unique. Usually the primary key is
called id and is a sequence.

A Foreign key is used to refer to a unique record on another table (using that other table’s
primary key).

In ER Diagramming, the linkage between tables is normally based on Foreign keys linking to
Primary keys.

If we look at our people example, the table definition shows that the street column is a foreign
key that references the primary key on the streets table:

Table "[Link]"

Column | Type | Modifiers

-----------+-----------------------+--------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
 phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

15.1.14. Transactions

When adding, changing, or deleting data in a database, it is always important that the database is
left in a good state if something goes wrong. Most databases provide a feature called transaction
support. Transactions allow you to create a rollback position that you can return to if your
modifications to the database did not run as planned.

Take a scenario where you have an accounting system. You need to transfer funds from one
account and add them to another. The sequence of steps would go like this:

 remove R20 from Joe

 add R20 to Anne
If something goes wrong during the process (e.g. power failure), the transaction will be rolled
back.

15.1.15. In Conclusion

Databases allow you to manage data in a structured way using simple code structures.
Activity: Exploring Databases Concepts Using QGIS
(PostgreSQL/PostGIS)

A. Learning Outcomes
After this activity, the student should be able to:
1. Identify tables, records, fields, and datatypes in a spatial layer.
2. Relate a QGIS attribute table to a relational database table.
3. Apply normalization by splitting data into related tables (people & streets).
4. Visualize database relationships (1:N) using QGIS.

B. Materials
 QGIS installed
 PostgreSQL + PostGIS installed
 Sample spatial layer of points (e.g., “People” or “Houses”) or students can
create a new point layer.

C. Activity Instructions
1. Connect QGIS to PostgreSQL
1. Open QGIS.
2. Go to Database → DB Manager.
3. Under PostgreSQL, create or use an existing connection to your PostGIS
database.
4. Click Connect.
Teacher note: Explain that this database is the DBMS that stores your tables.

2. Create a “people” table (as a spatial layer)

1. In DB Manager → your database, right–click → Create Table.
2. Name it: people.
3. Add the following fields (columns):
 Field
Datatype Notes
name

id integer primary key, NOT NULL

name varchar(50) person’s name

full address (non-

address varchar(200)
normalized)

phone_no varchar contact number

geometry(Poi
geom location of the person
nt)

4. Set id as Primary Key (sequence/auto-increment if possible).

5. Click OK to create the table.

3. Add and view records (rows)

1. In DB Manager, right-click people → Add to Canvas.
2. On the Layers Panel, right-click the people layer → Open Attribute Table.
3. Toggle Editing Mode and add at least 5 records (with geometry points on
the map):
o Each row = one record.

o Fill in name, address, phone_no.

o Save edits.

Question 1:
a. How many fields does your people table have?
b. How many records did you create?
c. Identify one example of a field value.

4. Observe datatypes and null values

1. From the attribute table, click Field Panel / Layer Properties → Fields.
2. List each field and its datatype (integer, string/varchar, geometry, etc.).
3. Leave the phone_no of one person empty.
Question 2:
a. What datatype is used for id, name, address, and phone_no?
b. What do you call an empty value in a field?
c. Why might allowing NULL be useful?
5. Normalize: split into people and streets tables
1. In DB Manager, create a new table streets with fields:

Field
Datatype
name

integer
id
(PK)

varchar(10
name
0)

2. Insert sample streets, e.g.:

o 1 – Plein Street

o 2 – Avenue du Roix

3. Modify the people table structure:

o Add a new integer field: street_id.

o Fill street_id with the correct value based on the address.

4. In QGIS → Layer Properties → Joins / Relations:

o Create a relation between people.street_id (foreign key) and [Link]
(primary key).
Question 3:
a. What is the Primary Key in the streets table?
b. What is the Foreign Key in the people table?
c. Is the relationship between streets and people 1:1, 1:N, or M:N? Explain.

6. Visualize the relationship on the map

1. Add both people (points) and streets (optional: line layer or just table) to the
canvas.
2. Use Identify Features on a person and check the related street through the
relation/join.
3. Observe that many people can live on one street – a 1:N relationship.
Question 4:
Explain in your own words how QGIS is showing a relational database using layers
and attribute tables.
D. Output / Submission
Students will submit:
1. Screenshot of:
o Attribute table of people (non-normalized with address), and

o Normalized version showing people (with street_id) and streets table.

2. Short written answers to Questions 1–4.