Databázové systémy
SK EN

Introduction and setup

Objectives

  1. Get acquainted with the organization of the exercises and the conditions for granting the credit.
  2. Receive the domain topic of the assignment.
  3. Connect to a school ORACLE database with your account and try out the SQL Developer program.

Motivation

During this exercise you will be instructed about conditions for obtaining the credit and you will receive the domain topic of your assignment.

Instructions

Step 1

Get acquainted with the terms for receiving credit and organization of exercises.

Task 1.1

Carefully read the terms of the credit from the about this subject page.

Step 2

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 program SQL Developer from Oracle in order to remotely connect to your school dedicated Oracle database.

Task 3.1

Start SQL Developer and create a connection to your Oracle account.

Comment

If you are working on your own computer, you will need to download Oracle SQL Developer from SQL Developer link. Download requires you to have an Oracle account, registration is free and allows you to get to many other tools from Oracle, therefore it is recommended to register. There is no need for installation, you just unzip downloaded zip file and run it with exe file. However, Java 8 is required to run, so if you do not have it, install it (or just download SQL Developer with built-in JDK 8).

You can create new connection in the tool context menu over Connections folder (see figure below).

Create a new connection
Fig. 1: Create a new connection

You need to specify configuration in a new connection window. At first you can name your connection (if you are working on school computer, remember you are not the only one using the computer and others can see your named connections later).

Your username and password are created based on your school email. You will get it by removing @student.tuke.sk domain together with all non-alphanumeric characters.

Thus, for example, if you have email milan.nosal@student.tuke.sk, your database username and password will be "milannosal" (see figure below).

Comment

You can change the password after successful connection.

I do not recommend using Save password option on the school computer.

Finally, you need to change Hostname to "oracle.kpi.fei.tuke.sk", which is school server name with database and SID (Oracle System ID to identify service) change to "db11g".

Set up a connection
Fig. 2: Set up a connection

Task 3.2

Click the Test button to check the correctness of the entered data. In case of success, you can save the connection and even use it to connect to database.

Comment

Even on school computer you can save the connection so you do not have to create it again every week. However, do not mark Save Password in order to not save your password for everybody to use.

Comment

If you try to use incorrect password for 3 times in a row, database system will lock your account and you need to ask your instructor to unlock your account.

Comment

The school oracle 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://nastavenia.tuke.sk/vpn/.

Step 4

At this point, you can try your first task with SQL. By connecting to a created connection (in my case I called it "Milan") you will see new worksheet in SQL Developer and you can use this worksheet to write and execute SQL queries (see the following figure).

Connecting
Fig. 3: Connecting

You can open more of these worksheet editors. You can also access them via the context menu over a specific connection, where you will find the Open SQL Worksheet entry.

Task 4.1

Find out how you can change the user's password in SQL.

Comment

To change user data you can use SQL query ALTER USER. Details of how you can change the password in database system is mostly different in each implementation (Oracle, MySQL, Postgres,...). Although user management does not have much of a priority for us now, you should try to study documentation for changing user data statement in the following link and get information about how to change password in Oracle database system: ALTER USER.

Comment

Note that for the vast majority of tasks you need to do during the exercises there is help in the form of online documentation. Although it is important for the exam to get the necessary commands learned and remembered, it will be very useful for you to learn to work with the available documentation. Maybe later when you get to work after school you will be assigned with some challenging task and there will be no colleague to help you with it, then you need to know how to use search engines (google) and the available documentation to solve this task.

Commands written in SQL Developer can be executed in two ways. In the following figure, the blue and red arrows highlight two buttons, which serve to execute query to the database.

Executing queries and scripts
Fig. 4: Executing queries and scripts

The red arrow highlight button for executing only one query which is actually selected by the text insertion cursor. In the case you have in your editor more than one query, each separated by semicolon, you can execute only one query with this button, but first you need to move text insertion cursor to this query. The alternative is the keyboard shurtcut Left Ctrl + Enter.

The button highlighed by a blue arrow serves to execute the entire content of the text editor.

The purple arrow in the top right corner highlights listbox, which allows to change database connection used to execute queries.

Task 4.2

Change your password to a new one that you can easily remember, but no one else will know it.

Comment

Verify a successful password change by disconnecting and reconnecting with a new password.

Resources

  1. Lecture 01: DBS introduction
  2. Lecture 02: ER modeling
  3. MiniFB ERD model
  4. http://www.oracle.com/

Additional Tasks

Task A.1

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 Oracle database server, I recommend installing Oracle Express Edition from the link Oracle XE. This will allow you to work while offline, and you will not have to connect via VPN. During instalation you will need to set password for user "system", which is the database administrator account. You can use this account to connect yo your database right after installation.

Comment

Once installed, you can create another connection in your SQL Developer program in order to connect to your local computer database. The hostname should be set as "localhost" and the SID should be set to "XE" (short for Express Edition).

© Copyright 2024 KPI FEI TUKE