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
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. | T-SQL pdf (last update: 10.4.2024) | tasks, create-script, solution (last update: 5.4.2024) |
| 9 | Concurrency Control I pdf (last update: 17.4.2024) | PL/SQL test, revision |
| 10. | Functional analysis pdf (last update: 24.4.2024) | Semestral Project: Form design and functional analysis |
| 11 | Concurrency Control II Study at home | |
| 12 | Query processing in DBMS Study at home | Mini-specification of transaction
|
| 13 | Object-relational Mapping StoreIS_orm.zip (C# + ADO.NET + SQL Server, 15.5.2024) | Implementation of transaction |