At this step the instructor will assign you domain topic of your assignment.
Step 3
During exercises, you will be primarily working on task according to these materials. You will be using IntelliJ IDEA or DataGrip in order to remotely connect to your school dedicated PostgreSQL database or a local clone run as a docker container.
Important note:
The school server you are currently using is only available within the school network. It means, that you will be able to connect to it from school computers or when connected to eduroam wifi network. If you want to connect to this server from other networks (e.g. from home), you need to setup VPN connection to TUKE according to instructions on https://uvt.tuke.sk/wps/portal/uv/sluzby/vzdialeny-pristup-vpn.
Mac user can use the https://remote.tuke.sk/ web app.
Task 3.1
Set up a connection to your school account using DataGrip utility.
Note:
DataGrip by JetBrains could be obtained for free, under the condition of using a student account. First, create a student account using your university e-mail address at their portal. After the usually prompt approval, you get free access to a variety of JetBrians products for your study (the license renewal is in annual periods).
DataGrip is a special multiplatform IDE, created for accessing different data sources. In the case you prefer using an open-source tool, there is the PgAdmin tool directly from the postge community, but DBeaver is also quite popular.
A new connection could be created by clicking the + in the context menu for the Database folder (see picture below).
Fig. 1:Create a new connection
Configuratio parameters are important to follow, while the title is arbitrary.
User name and password are generated based on your university e-mail address. It consisst of all alphanumeric characters of the user-part of it.
For examle, for e-mail address janko.hrasko@student.tuke.sk, both user and password become "jankohrasko" (see figure below). For janko.hrasko.2@student.tuke.sk, it would be then "jankohrasko2" (the database name will also contain the digits!).
Note:
You are able to change your password after first login. There are two databases for each student account at the school server. One to get used during solving the main task, and one to practice using the Chinook database, which will require your installation before it can be used.
While user and password remain the same, there are two databases available at the school server:
userdb for your main task,
userchinook for practicing using Chinook DB.
IDE defaults/presets should be changed to:
Name: any title, will be generated if not set.
Host: dbs.kpi.fei.tuke.sk
User: user , as explained above.
Password: user , exactly the same string.
Database: userdb and/or userchinook
Fig. 2:Connection params
Task 3.2
Set up a connection to your school account using IntelliJ IDEA software.
Here, we start by creating a new project. Projects can then have datasources defined.
Fig. 3:Create a new (empty) IDEA project
Then, associate a PostgreSQL datasource with the project.
Fig. 4:Add data source
For the first connection setup, it might happen that a driver is missing. Install it to be able to proceed as requested by your tool.
Fig. 5:Missing driver
Task 3.3
By clicking Test Connection one can check if everything is set up properly. After checking, it is worth to save the details.
Fig. 6:Test connection in IntelliJ IDEA
Note:
The school server blocks your account after 3 failed login attempts. Contact your teacher to unlock it.
Note:
After a successful connection, your software might ask you to switch to a specific dialect to improve code completition quality.
Fig. 7:Setting SQL dialect for your IDEA project
Step 4
We can try our first steps with SQL now. Connect to the server using one of your stored connections (I will use "perhacdb@dbs.kpi.fei.tuke.sk") DataGrip opens a console to run SQL (see figure below).
Fig. 8:DataGrip console
You might be interested checking tips and tricks as well.
Task 4.1
Find out how to change password for the database user.
Note:
To change user account preferences, we use ALTER USER: ALTER USER.
Note:
Please note that there is a lot of useful information in the online documentation one can use to solve laboratory tasks. While learning the commands for the exams, you can also learn making use of the documentation.
Commands can be executed in DataGrip in two ways, either by clicking the green play button or hitting Ctrl + Enter (Windows, Linux) or command + enter (Mac OS), respectively. You can try it using the following:
SELECT version();
which returns the PostgreSQL version (see next figure).
Fig. 9:Running commands or scripts
Task 4.2
Change your password.
Note:
In the case of success, you will be disconnected from the server. Don't forget to update the saved connection settings!
Task 4.3
Check your saved connection to userchinook database. Use the chinook.sql script (after download to your computer) to fill the database by data.
Right click on "Databases", then select the "Run script" option from the context menu (see next figure).
Fig. 10:Running scripts directly
The script is 15 639 lines long. It might take a bit longer to finish (about 10 seconds). The resulting database will consist of 11 tables (see figure below). We will use this database for practicing during the labs. In a case of bigger failure, you might need to re-create it.
Fig. 11:IDE after running the chinook.sql script
As a part of your homework, you can start to work on your assignment right after the first lecture. Knowing everything from first lecture you should have enough information to be able to create the entity-relational model of your assignment.
Comment
In a classical way, the analysis is done with a customer who defines what he needs. In this approach the right model of domain is what the customer needs and want. Your approach will be a little bit different as you don't have a customer, therefore you can be your own customer or you can ask your instructor for help defining model. This means that you must specify what you want to store in the specified domain - for example if you have domain for managing student assignments, you need to think if it is required for you to store students entities in database or not. Will you create student entity, will you create assignment entity?
Task A.2
Although you can work from home at our school database server, We recommend installing it locally. This will allow you to work while offline, and you will not have to connect via VPN. You can decide whether installing locally or to a hosted dedicated guest OS only running the database.
When you decide for docker, Docker Desktop could be used for graphical container management. You can use the built-in search to find and pull the latest postgre image. You can see the local copy of an official image below.
Fig. 12:postgre:latest image in Docker Desktop
This image requires to set up one environment variable only, POSTGRES_PASSWORD. All other are optional, but useful for a more advanced setup such as allowing systematic backups. The most basic container is shown below, keeping the default port for a faster connection setup in our tools.
Fig. 13:Port parameter for the postgre container
Comment
Once installed, you can create another connection in your DataGrip or IntelliJ IDEA program in order to connect to your local computer database. Hostname should be set as "localhost", other parameters could be left default. We used the right setup in one of our earlier figures on this page. Can you find it?
