Database and Information Systems

PL/SQL test

Test 9.4.2021

Test 18.3.2021 with solution 

News

• The repair test is 30.3. 13:15

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 6th week. Student can use any type of materials during the test (except the social networks and the communication with other people).

Model for PL/SQL test

• Description: pdf (last change: 1.3.2021), script (last change: 1.3.2021).

Models for the real-time test from last years:

• Data model:
  • pdf (last change: 3.4.2020)
  • script (last change: 3.4.2020)
  • Solution: 1 , 2
• Data model:
  • data model.
  • script
  • Example and solution: here
• 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 here, here, and  here.

Project deadlines

• Analysis: 
  • 1.attempt: 1.4. (8. week)
  • 2.attempt: 8.4. (9. week)
  • 3.attempt: 15.4. (10. week)
• ORM: 
  • 1.attempt: 22.4. (11. week)
  • 2.attempt: 29.4. (11. week)
• GUI (2-3 forms): 
  • 1.attempt: 29.4. (12. week)
  • 2.attempt: 6.5. (13. week)

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