11. Building a database using SQL

We’re using PostgreSQL and we’ve already touched on some of this in the previous post.

Let’s say we want to build a database with two tables.

The first is a people table (people relation) and will contain name, height, gender and date of birth.
The second is an address table (address relation) and will contain house number, street, city and country.
Firstly let’s create the new database and call it example.

CREATE DATABASE example;

This returns:

CREATE DATABASE

In the last example we closed SQL Shell and then logged in again. But we can also use the \c command to connect to the database. So we can type:

\c example;

And we should see:

You are now connected to database "example" as user "postgres".
example=#

I can clear the screen by typing \! cls. This runs Windows commands (I’m using Windows 10, not Linux etc).

We can now add our two tables by typing this in:

CREATE TABLE people (pk SERIAL PRIMARY KEY,
name VARCHAR(256) NOT NULL,
height_cm INT,
gender VARCHAR(256),
date_of_birth DATE);

CREATE TABLE address (pk SERIAL PRIMARY KEY,
house_number INT,
street_name VARCHAR(256),
city VARCHAR(256),
country VARCHAR(256));

If we then look for the tables by typing \dt we get this:

          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | address | table | postgres
 public | people  | table | postgres
(2 rows)

We can describe a specific table by, for example, typing \d people:

                                       Table "public.people"
    Column     |          Type          | Collation | Nullable |              Default
---------------+------------------------+-----------+----------+------------------------------------
 pk            | integer                |           | not null | nextval('people_pk_seq'::regclass)
 name          | character varying(256) |           | not null |
 height_cm     | integer                |           |          |
 gender        | character varying(256) |           |          |
 date_of_birth | date                   |           |          |
Indexes:
    "people_pkey" PRIMARY KEY, btree (pk)

We can now link these tables – a name may have more than one address, so there is a one-to-many relationship here. We can add FOREIGN KEYS to link these.

We can alter an existing table using the ALTER command. So we need to add a new column (field) and then make it a foreign key.

ALTER TABLE address ADD people_pk INT;
ALTER TABLE address ADD FOREIGN KEY (people_pk) REFERENCES people(pk);

In the second command, people_pk represents the new field in the address table that we just created, and people(pk) references the primary key in the people table we created earlier. Typing both in returns ALTER TABLE from the system.

Many people could live at a single address, so there is a one-to-many relationship in the other direction as well. We can repeat this in the opposite direction.

ALTER TABLE people ADD address_pk INT;
ALTER TABLE people ADD FOREIGN KEY (address_pk) REFERENCES address(pk);

If we do a \d people again we can see more information about the foreign key:

                                       Table "public.people"
    Column     |          Type          | Collation | Nullable |              Default
---------------+------------------------+-----------+----------+------------------------------------
 pk            | integer                |           | not null | nextval('people_pk_seq'::regclass)
 name          | character varying(256) |           | not null |
 height_cm     | integer                |           |          |
 gender        | character varying(256) |           |          |
 date_of_birth | date                   |           |          |
 address_pk    | integer                |           |          |
Indexes:
    "people_pkey" PRIMARY KEY, btree (pk)
Foreign-key constraints:
    "people_address_pk_fkey" FOREIGN KEY (address_pk) REFERENCES address(pk)
Referenced by:
    TABLE "address" CONSTRAINT "address_people_pk_fkey" FOREIGN KEY (people_pk) REFERENCES people(pk)

We can add further tables. Note that for efficiency, foreign keys can be added at the time we create our table. Let’s create a cars table and a pets table. They both reference the people table – each car or pet can be attributed to a person in that table.

CREATE TABLE cars (pk SERIAL PRIMARY KEY,
engine_size_cc INT,
colour VARCHAR(256),
manufacturer VARCHAR(256),
model VARCHAR(256),
year INT,
people_pk INT,
FOREIGN KEY (people_pk) REFERENCES people(pk));

CREATE TABLE pets (pk SERIAL PRIMARY KEY,
species VARCHAR(256),
coat VARCHAR(256),
age INT,
people_pk INT,
FOREIGN KEY (people_pk) REFERENCES people(pk));

We can now insert some data into people:

INSERT INTO people (name, height_cm, gender, date_of_birth) VALUES ('Ben', 167, 'Male', '1978-03-14');

Note: we need to use single quotation marks for Ben, Male etc. This returns INSERT 0 1 and if we now do SELECT * FROM people; we’ll see this:

 pk | name | height_cm | gender | date_of_birth | address_pk
----+------+-----------+--------+---------------+------------
  1 | Ben  |       167 | Male   | 1978-03-14    |
(1 row)

We can add Ben’s cat – note that we are giving people_pk in pets the value of 1 (Ben’s value in people)

INSERT INTO pets (species, coat, age, people_pk) VALUES ('cat', 'brown', 4, 1);

Wednesday 27 October 2021, 615 views

Next post: 12. Limitations to database modelling
Previous post: 10. Introduction to PostgreSQL