Simple University Schema

1. Database Structure

This section demonstrates the database diagram for the basic version of the University database. This includes 3 tables within the default schema – Department, Student, and Course:

As shown in the diagram, the Course table has a foreign key reference to the Department table, whereas the Student table does not have any foreign key references.

2. Set Up and Run Queries

We’ll use PostgreSQL, MySQL, and SQL Server to run all the queries. In this section, let’s cover how to set up the database and populate it with basic data.

2.1. Manual Setup

For this setup, we first need to install PostgreSQL, MySQL, and SQL Server on the machine. You can find the exact versions tested and other installation notes here: manual-setup.

Once installed, we can execute the database and table creation scripts. The scripts for PostgreSQL, MySQL, and SQL Server are nearly identical, with only minor syntactical differences. Once the tables are created, we can run the INSERT queries from here to populate the data for these tables.

Additionally, it is advantageous to have a database GUI client, such as DBVisualizer, installed. This tool can connect to all three database engines and run the queries efficiently.

2.2. Setup Using Docker

Instead of manually setting up the databases, we can use Docker to simplify the process. First, install Docker on the machine. Once installed, we can use Docker Compose to automate the database setup, including table creation and data population.

Let’s look at the steps to set up the database using Docker. Firstly, we need to clone the sql-tutorials GitHub repository that contains the necessary configurations. Let’s execute the commands for the setup:

cd 1-setup/docker-setup
docker compose up

This starts the Docker instances for four components: one for each database and another for a simple GUI database web client called Adminer.

All databases are automatically configured with the University schema and populated with sample data. The Adminer client is accessible at http://localhost:8080. We can connect to the required database by providing the credentials from the docker-compose.yml file. We should note that when using Adminer, the hostname should be localhost on Linux, but host.docker.internal on Mac and Windows. Additionally, we can connect using any database client such as DBVisualizer using the host as localhost.

Instead of running all the databases, we can also run just a single one. For that, we can navigate to the required database directory and run the command:

cd 1-setup/docker-setup/postgresql
docker compose up

This starts only the PostgreSQL instance, using the same schema.

2.3. Clean Up

We can completely clean up the Docker setup using the following command:

cd 1-setup/docker-setup
docker compose down

This command deletes all data and schemas from the entire database. Rerunning the Docker Compose will set up the database from scratch.