Database Systems II
Credit conditions
The first part of the semester is finished with a test on PL/SQL (probably in the 6th week). The second part includes a semestral project - an analysis and implementation of the data layer (ORM) of a part of an information system.
Test on PL/SQL
- The rvvi database (description), see Database Systems I, Oracle version: rvvi2023_create.sql (last update: 14.2.2025). Records import:
- Run rvvi2023_copy.sql in an SQL client (it copies records from tables on dbsys.cs.vsb.cz, processing time approx. 15s).
- Download rvvi2023_insert.sql.zip, repack into a directory and run @<path to the file> in an SQL client, e.g. @g:\users\rvvi2023_insert.sql (processing time is approx. 1h). Checking the number of records: rvvi2023_rowcount.sql (2 253 244).
- Test with the solutions
- Test2 with the solutions
- Test3 and solution
Tests on PL/SQL from previous years
- See menu Archiv/Database Systems II
Other resources
- Sakila sample database for Oracle (last update 25.1.2024)
Lectures and Exercises
| Week | Lecture | Exercise |
| 1 | Introduction, SQL: Oracle vs SQL Server pdf (last update: 17.2.2025) | tasks, create/init sql script, solutions (last update: 17.2.2025) |
| 2 | PL/SQL I: block, variables, exceptions pdf (last update: 25.2.2025) | tasks, solutions (last update: 25.2.2025) |
| 3 | PL/SQL II - Procedures, functions, triggers pdf (last update: 4.3.2025) | tasks, update sql script, solutions (last update: 4.3.2025) |
| 4 | PL/SQL III - Programming constructs, cursors pdf (last update: 14.3.2025) | tasks, solutions (last update: 7.3.2025) |
| 5 | PL/SQL IV - Dynamic SQL pdf (last update: 24.3.2025) | tasks, solutions (last update: 14.3.2025) |
| 6. | Object-relational data model pdf (last update: 26.3.2025) | Sample task, solution (see Lecture 5) (last update: 24.3.2025) PL/SQL test |
| 7. | Transactions and recovery
| PL/SQL test
|
| 8. | Functional analysis and form design pdf (last update: 11.4.2025) | Project: form design and functional analysis |
| 9 | Functional analysis: minispecification of a function | Project: form design and functional analysis |
| 10. | Project: minispecification of a function | |
| 11 | T-SQL pdf (last update: 24.4.2025) | tasks, create-script, solution (last update: 24.4.2025) Implementation of transaction as stored procedure |
| 12 | Object-relational Mapping StoreIS_orm.zip (C# + ADO.NET + SQL Server, 24.4.2025) | Implementation of transaction in ORM
|
| 13 | Query processing in DBMS | Implementation of transaction in ORM |
Concurrency Control I Concurrency Control II |