Database and Information Systems
News
- (02.04.) The test together with the solutions is available here.
Credit conditions
Student has to pass one test from procedural languages PL/SQL and T-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 will be in 7. week. Each student will get randomly chosen test from PL/SQL or T-SQL. 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 / T-SQL
Data Model: pdf (last change: 16.3.2018), scripts:
Model for the real test (from the last year)
Data Model: pdf (last change: 21.3.2017), scripts:
The real time tests with the solutions are presented here 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: 12 points, min. 6 points (1. submission 12 points, 2. submission 9 points, 3. submission 6 points)
- ORM: 8 points, min. 4 points
- IS: 5 points, min. 3 points
Lectures and Exercises
| Week | Lecture | Exercise |
| 1 | Introduction to PL/SQL I - PL/SQL block, variables pdf (last update: 12/02/2018) | SQL Language --> Tasks and Examples tasks, solutions (last update: 13/02/2018) PL/SQL Language -> Tasks and Examples tasks, solutions (last update: 13/02/2018) |
| 2 | PL/SQL II - Procedures, functions, Cursors pdf (last update: 05/03/2018) | PL/SQL Language -> Tasks and Examples tasks, solutions (last update: 20/02/2018) |
| 3 | PL/SQL III - Triggers, Dynamic SQL, Bind variables, Exceptions pdf (last update: 05/03/2018) | PL/SQL Language -> Tasks and Examples Scripts: league_create.sql, league_init.sql, league_drop.sql tasks, solutions (last update: 28/02/2018) |
| 4 | T-SQL pdf (last update: 05/03/2018) | T-SQL Language -> Tasks and Examples tasks, solutions (last update: 07/03/2018) |
| 5 | - | T-SQL Language -> Tasks and Examples tasks, solutions (last update: 14/03/2018) |
| 6 | PL/SQL IV - bulkload operations pdf (last update: 14/06/2018) | Example test tasks, solutions (last update: 14/03/2018) Scripts for Oracle: create.sql init.sql drop.sql Scripts for SQL Server: create.sql init.sql drop.sql |
| 7 | Functional Analysis pdf (last update: 26/03/2018) | PL SQL / T-SQL test |
| 8 | - | Consultation of the project Topic, Data Analysis, Draft of Functional Analysis |
| 9 | Recovery of DBMS pdf (last update: 16/04/2018) | PL SQL / T-SQL Repair Test, Consultation: Functional Analysis Deadline for 1. submission of Complete Analysis: 16.4.2018 |
| 10 | Object Relational Mapping (ORM)
| Deadline for 2. submission of Complete Analysis: 23.4.2018 |
| 11 | ASP.NET, Integration of ORM:
| Deadline for 3. submission of Complete Analysis: 30.4.2018 Deadline for 1. submission of ORM: 30.4.2018 |
| 12 | Object-Relational Data Model pdf (last update: 15/05/2018) | Deadline for 2. submission of ORM: 7.5.2018 |
| 13 | Physical Database Design, Query Processing pdf (lecture, last update: 15/05/2018) | Deadline for 1. submission of IS: 14.5.2018 |
| 14 | - | Deadline for 2. submission of IS: 20.5.2018 |