Online tools.
Dia for creating E-R diagrams.
Connecting to the Chalmers Postgres server.
Installing Postgres and using Postgres on your own computer.

Online tools

Query Converter is an online tools that can generate E-R diagrams, analyse functional dependencies, translate SQL to Relational Algrebra, run simple SQL query examples, validate XML documents and run XPath queries, etc.
Here is a link to another online tool for Functional Dependencies and Normal Forms.
JSON Path evaluator. A tool to test JSON Path queries.
JSON Schema Lint: can be used to check both that a JSON schema is valid and that a JSON document is valid with respect to a JSON schema.

Dia

Dia (dia-installer.de) is the recommended editor for ER‐diagrams. It is freely available for Linux, macOS and Windows and should also be installed on the Chalmers computers in the labs.

Dia supports lots of different kinds of diagrams. To find symbols for E-R diagrams, choose ER from the menu in the tools panel.

Pro-tip: Double-click an entity/relation to set its properties (e.g. weak etc) ER-mode.
Pro-tip: Double-click an attribute to set primary key etc.
Pro-tip: Double-click a line to change arrows, the curved arrow is available under "more arrows".
Pro-tip: There are no ISA-relationships in the ER, but you can use merge/extract from flowchart (and just write ISA on them).
Pro-tip: Always connect lines to the points of diamonds, not their center.

Connecting to the Chalmers Postgres server

NOTE: Connecting to the database server only works from the Chalmers network. For this and other reasons, installing Postgres on your own machine is preferable.

To connect to the database run this in a terminal window (replace nnn by your actual username number):

psql -v ON_ERROR_STOP=1 -h ate.ita.chalmers.se -U tda357_nnn

To avoid having to type the password you can run this command instead:

psql -v ON_ERROR_STOP=1 "postgresql://tda357_nnn:password@ate.ita.chalmers.se"

Replace password by your actual password and nnn by your actual account number.

Deleting everything

Placing this script at the top of your .sql file should (quietly) delete everything. Once again, replace nnn by your number.

\set QUIET true
SET client_min_messages TO ERROR;
DROP OWNED BY tda357_000 CASCADE;
SET client_min_messages TO WARNING;
\set QUIET false

Note that your database is not named "portal", but is the same as your username, so the command "\c portal" will not work and you do not ever need to switch or create databases on the Chalmers server.

Installing and using Postgres on your own computer

Having an efficient workflow for postgres is essential to completing the lab in a timely manner.

Usually, that means using a text editor to edit a number of .sql files (the ones requested in the assignment) and running the psql command line application with a script that:

1. Deletes everything in the database

2. Runs all your files to recreate the database and print any errors that occur.

To get this running you will need to install postgres, and run the psql tool in a terminal window. There are various GUI tools for editing Postgres databases, but more often than not these are not very helpful.

TLDR:

Run this in a terminal window to start psql and connect to the database "portal" after creating it:

psql -v ON_ERROR_STOP=1 -U postgres portal

First time running psql

This assumes you have successfully installed the latest version of PostgreSQL (see instructions further down), opened a terminal window, and navigated the terminal to a directory where you have created .sql files (tables.sql, views.sql and inserts.sql) and downloaded the runtests.sql file from the Assignment text.

Run this command:

psql -U postgres

If the terminal says psql is not a valid command, you have not added the bin directory of postgres to your path environment variable. Either do that or locate the psql binary (e.g. psql.exe on Windows) and run it with its complete path. In windows command prompt (cmd.exe) that might look something like this (note the quotes):

"C:\Program Files\PostgreSQL\12\bin\psql.exe" -U postgres

In MS Powershell, something like this should work:

& 'C:\Program Files\PostgreSQL\12\bin\psql.exe' -U postgres

If it works you are prompted to enter the password you used when installing. When at the psql prompt (postgres=#) input this command:

CREATE DATABASE portal;

It should echo "CREATE DATABASE". If it does, type \quit to exit the application and restart it with this command in your operating system terminal:

psql -v ON_ERROR_STOP=1 -U postgres portal

This will connect you to the database portal, using the user postgres.

Workflow

Now run the command \i runtests.sql at the psql prompt to execute the (so far empty?) files. This will give errors since the views do not yet exist, so edit runtests.sql, commenting out the SELECT clauses (-- for single line comments) and all the inserts in inserts.sql, then gradually return them as you progress in the lab.

Add a CREATE TABLE statement to tables.txt and check that it works by re-running \i runtests.sql (just press up arrow and enter in the terminal).

The workflow from here on out will be making edits in whatever text editor you are using (one with SQL syntax highlighting is VERY useful), re-running the \i command, and fixing any error you get etc.

Installing PostgreSQL on Windows

Follow the link to the EnterpriseDB installer from this page: https://www.postgresql.org/download/windows/ (Links to an external site.)

When prompted for a password, use "postgres" or choose a simple one that you do not mind storing in plain text, also using the same password for both members of the group is beneficial. The password does not need to be strong.

After installing, you will need to add the \bin subdirectory of the installation folder to your windows path.

Installing PostgreSQL on MacOS

Follow the instructions on https://postgresapp.com/ (Links to an external site.)

When prompted for a password, use "postgres" or choose a simple one that you do not mind storing in plain text, also using the same password for both members of the group is beneficial. The password does not need to be strong.

Alternatively, if you use Homebrew on your Mac, you can install PostgreSQL by running brew install postgresql in a Terminal window, and follow the instructions to start the database server. You will not need a password to connect to the database.

Installing on other operating systems

Follow the instructions on https://www.postgresql.org/download/ (Links to an external site.).

Alternatively, PostgreSQL is included in most popular Linux distributions (like Debian, Ubuntu, Raspbian), so installing it can be as simple as running apt install postgresql in a Terminal window. But you might not get the latest version, so it is probably better to follow the instructions on the PostgreSQL download page.