Full University Schema

1. Database Structure

This section demonstrates the database diagram for the full University database, referenced in the SQL articles. All tables are included in the default schema. The database diagram below outlines all the tables in the database and their relationships:

The ER diagram above describes all the tables and their relationships in this database. In certain instances, such a complex schema is unnecessary for the articles. In these cases, we can refer to a simplified university schema consisting of just three basic tables.

2. Set Up and Run Queries

We’ll run all the queries using PostgreSQL, MySQL, and SQL Server. 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. Docker Setup

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-tutorial GitHub repository that contains the necessary configurations. Let’s execute the commands for the setup:

cd 1-setup/docker-setup
PROFILE=full 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. Note the use of the PROFILE environment variable with the docker-compose command. If this variable is absent, it sets up the simple University schema with only three tables, as previously discussed.

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
PROFILE=full 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.