Databases Laboratory

General Information

Contact information of the instructor

Name: Gergely Bihary
E-mail: bihary.gergely.sandor@db.bme.hu

Time and place of classes

The classes take place in Room IL.105, according to the following:

Oracle: Sep. 25 2017, 14:15-15:45
SQL I: Oct. 30 2017, 14:15-15:45
SQL II: Nov. 13 2017, 14:15-15:45
SQL III: Nov. 20 2017, 14:15-15:45
Optimization: Dec 4 2017, 14:15-15:45

At the beginning of each class, an entry test will be written which is necessary for completing the given class. The course material includes everything needed for the entry test.

You will start solving the exercises during class. The amount of exercises that you get can be solved during class. Once finished, you can upload your solutions in a predefined format (see below) in the lab administration system, which is here. If you can't finish the exercises during class, you can finish them at the Students' Computer Center (HSZK, floor 4, building R), or at home, but 1 day from the beginning of the given class, your solution has to be uploaded.

Please, neither try to log in the administration system, nor ask for a new password until I give you your access codes.

Software you will need

  • Oracle SQL Developer 4.1.3. To run it: Unpack it, and execute sqldeveloper\sqldeveloper.exe. If it does not work, then run sqldeveloper\sqldeveloper\bin\sqldeveloper.bat. This is for Win64. You'll need JRE to run this.
  • For other platforms, you can download the same version of SQL Developer here. You'll need to install the appropriate Java 8 JDK to run this.

The work environment is provided and supported in the laboratory (IL.105), and in HSZK.

Software you might need (for Windows)

Network settings for home

  • SQL Developer connection:
    • Hostname: rapid.eik.bme.hu
    • Port: 1521
    • SID: szglab

Useful material

Classes

Class 1, Oracle

You should upload a single PDF file to the administration system, containing your report. The name of the file should be NEPTUN-1-C12A, where you should substitute NEPTUN with your actual NEPTUN code

The SQL code to be executed during the lab

Below is the SQL code that has to be executed after solving exercise 5.

column grantor format a8;
column grantee format a8;
column table_name format a20;
column privilege format a20;
select grantor
     , grantee
     , table_name
     , privilege
     , initcap(grantable) grant_opt
  from all_tab_privs
 where grantor = user
    or grantee = user
 order by grantor, grantee, table_name, privilege
;

Classes 2, 3, and 4: SQL1, SQL2, SQL3

The material for the SQL labs (SQL1, SQL2, and SQL3) can be found here.

For the SQL1 lab, you should be prepared from table manipulation statements and also, from the ER modeling and ER->relational mapping chapters of the Databases course.

For the SQL2 lab, you should be prepared from SQL queries.

For the SQL3 lab, you should be prepared from the entire SQL guide.

For the requirements of completing this lab, and for some SQL tips, read the Students' guide!

The skeleton of the script to be handed in is here, while here is a page that helps you create the XML file if you don't do it manually.

Class 5: Optimization

The material for the optimization lab is the lecture slideshow, which can be downloaded from here. Please, read the Students' Guide here.

Csatolmány: