Database and Information Systems
Credit conditions
Student has to pass one test from procedural languages PL/SQL and T-SQL. Minimimal number of points is 12 and maximal 20. 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 8. 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 communication with other people).
Scripts for Oracle:
Skripts for SQL Server:
Project
Project should be defended in the last week. Student can implement the chosen project under platform .NET or Java. The maximal number of points is 25, minimal is 13.
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.
- Implementation has to follow the analysis.
- 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).
- Tables in the database will contain basic indices.
- IS will 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
Lectures and Exercises
| Week | Lecture | Exercise |
| 1. | Introduction to PL/SQL - PL/SQL block, Procedures and functions, Static and dynamic PL/SQL, Exceptions pdf (last update: 11/2/2014) | SQL Language --> Tasks and Examples tasks, solutions (last update: 9/2/2014) |
| 2. | PL/SQL - Triggers, Cursors, Bind variables, Access rights of the stored procedures, Packages, Bulk SQL operations in PL/SQL, COMMIT, SQL Injection pdf (last update: 19/2/2014) | PL/SQL Language -> Tasks and Examples tasks, solutions (last update: 24/2/2014) |
| 3. | - | PL/SQL Language -> Tasks and Examples tasks, solutions (last update: 5/3/2014) |
| 4. | Transactions, Recovery pdf (last update: 05/03/2014) - | PL/SQL Language -> Tasks and Examples Scripts: league_create.sql, league_init.sql, league_drop.sql tasks, solutions (last update: 5/3/2014) |
| 5. | T-SQL pdf (last update: 12/03/2014) | T-SQL Language -> Tasks and Examples tasks, solutions |
| 6. | Functional Analysis, Sample project pdf (last update: 08/04/2014) | T-SQL Language -> Tasks and Examples tasks, solutions (last update: 23/3/2013) |
| 7. | API to Data Accessing pdf (last update: 08/04/2014) | Example test tasks, (last update: 23/3/2013) |
| 8. | - | PL/SQL / T-SQL Test |
| 9. | ASP.NET: pdf | JDBC, ADO.NET, Consultation: Project description and conceptual model |
| 10. | Object-Relational Mapping (ORM) ORM,ASP.NET | PL/SQL / T-SQL Repair Test, Submission: IS description, conceptual and data model (4 points) Consultation: Functional Analysis |
| 11. | ORM - Patterns ORM,ASP.NET | Submission: Functional Analysis (4 points) Consultation: ORM and ASP.NET |
| 12. | Physical Database Design, Query Processing | Consultation: ORM and ASP.NET |
| 13. | - | Submission: ORM (4 points) |
| 14. | - | Submission of complete project (13 points) |