hero-image

Teaching database courses at the Department of Computer Science

Database and Information Systems

News

20.3.2019 - The solution of the test is presented here. The repair of the test will be on practise 26.3.2019.

Credit conditions

Student has to pass one test from procedural languages PL/SQL. Minimimal number of points is 8 and maximal 15. At most two attempts of the test will be possible. Another part of the credit is a home project which is focused on a analysis and implementation of an information system under platform .NET or Java.

Real time test

Real time test from PL/SQL will be in 5th week. Student can use any type of materials during the test (except the social networks and the communication with other people).

Model for the test from PL/SQL

The test will use the following data model. Run this script to create the tables in your database. Last update of the script: 6.3.2019 14:20.

 

Model for the real test (from the last years)

  • Data Model: pdf (last change: 16.3.2018), scripts:
    • Oracle: script (last change: 21.3.2018)
    • SQL Server: script (last change: 21.3.2018)
  • Data Model: pdf (last change: 21.3.2017), scripts:
    • Oracle: script (last change: 21.3.2017)
    • SQL Server: script (last change: 21.3.2017)

The real time tests with the solutions are presented herehere, and  here.

Project

Project should be defended in the last week. Student can implement the chosen project under platform .NET or Java.
Requirements:

  • IS will have at least 7 tables and 5 non-trivial methods. At least 3 methods will be implemented as stored procedures.
  • At lest 3 of the implemented non-trivial methods will be as transactions. Non-trivial method is not CRUD command.
  • Cascade delete is not allowed as a non-trivial method.
  • The implementation has to follow the analysis.
  • The architecture has to contain layers. Data layer has to contain own Object-Relation Mapping (ORM)
  • Student can implement in ASP.NET or J2EE (in the case of web application) or .NET or Java (in the case of desktop application).
  • ORM will support also lazy evaluation and update only changed attributes.
  • ORM will support transaction (at least level of isolation SERIALIZABLE).
  • A part of ORM will be a test method utilizing all methods of ORM.
  • IS will support basic validation of input values.
  • Only two forms of the IS have to be implemented. The forms will be chosen by a teacher according to the analysis. The forms have support basic validation of input values.
  • The complete project has to contain documentation with analysis, SQL scripts and implementation.
  • Example analysis of the project here
  • Example ASP.NET project here

Evalution of the project:

  • Analysis: 10 points, min. 6 points (1. submission 10 points, 2. submission 8 points, 3. submission 6 points)
  • ORM: 10 points, min. 5 points
  • IS: 10 points, min. 5 points

Lectures and Exercises

WeekLectureExercise
1Introduction to PL/SQL I - PL/SQL block, variables
pdf (last update: 11/02/2019)
SQL Language --> Tasks and Examples
taskssolutions (last update: 13/02/2019)

PL/SQL Language -> Tasks and Examples
taskssolutions (last update: 13/02/2019)
2PL/SQL II - Procedures, functions, Cursors
pdf (last update: 11/02/2019)
PL/SQL Language -> Tasks and Examples
taskssolutions (last update: 19/02/2019)
3PL/SQL III - Triggers, Dynamic SQL, Bind variables, Exceptions
pdf (last update: 25/02/2019)
PL/SQL Language -> Tasks and Examples
taskssolutions (last update: 25/02/2019)

Scripts: league_create.sql, league_init.sql, league_drop.sql
taskssolutions (last update: 25/02/2019)
4PL/SQL IV - bulkload operations
pdf (last update: 4/03/2019)
Example test
taskssolutions (last update: 12/03/2019)

Scripts for Oracle: create.sql init.sql drop.sql
5Transactions, Recovery of DBMS
pdf (last update: 11/3/2019)
PL SQL test
6T-SQL
pdf (last update: 18/03/2019)
T-SQL Language -> Tasks and Examples
taskssolutions (last update: 20/03/2019)
7Functional Analysis
pdf (last update: 25/03/2019)
T-SQL Language -> Tasks and Examples
taskssolutions (last update: 14/03/2018)
8Concurrency Control I
pdf (last update: 01/04/2019)

Consultation of the project

Topic, Data Analysis, Draft of Functional Analysis

Deadline for 1. submission of Complete Analysis: 7.4.2019

9

Object Relational Mapping (ORM)

  • pdf (lecture, last update: 8/4/2019)
  • scripts.zip (create script, a generator of DTO, last update: 16/04/2018)
  • AuctionSystemORM_sqls (ORM, .NET,  SQL Server, last update: 16/04/2018)

Consultation: Functional Analysis

Deadline for 2. submission of Complete Analysis: 14.4.2019

10

ASP.NET, Integration of ORM:

Deadline for 3. submission of Complete Analysis: 21.4.2019

Deadline for 1. submission of ORM: 21.4.2019

11-Deadline for 2. submission of ORM: 28.4.2019
12

Patterns of ORM Integration into User Interface Forms

Deadline for 1. submission of IS: 5.5.2019
13

Physical Database Design, Query Processing

pdf (lecture, last update: 6/5/2019)

Deadline for 2. submission of IS: 12.5.2019
14Object-Relational Data Model
pdf (last update: 15/05/2018)