Module Content
● Embedded SQL statements into 3rd generation programming languages.
● Open Data Connectivity: ODBC and JDBC.
● Object-Relational Databases: User Defined Data Types and Functions, Objects, Object Identity and Reference Types, Hierarchy, ODMG 3.0 model.
● Object Query Language (OQL).
● Three types of Object-Oriented nosql (a) query by examples, (b) native queries and (c) SODA queries.
● Object-Relational Database Model and SQL3: Database Design for Object-Relational Database Model, Collections.
● Query language SQL1999.
● Comparison of Relational, Object-Relational and Object-Oriented Database Models.
● Technology of Semi-Structured Database Model: XML, XML Schema, SQL2008, XQuery, Xpath.
● Manipulation of XML type from Object-Relational Database Management Systems.
● Data Web Services and their implementation by using stored procedures.
● The SQL transaction as a logical unit of work
● ISO/ANSI errors and diagnostics in the context of the client-server RDBMS architecture
● ISO/ANSI error- and exception handling
● Transaction concurrency relating problems: lost updates, dirty reads, non-repeatable reads, read- and update phantoms
● The ISO/ANSI ACID properties and best practices in the transactional context
● Transaction isolation levels
● Transaction isolation implementation technologies: Multi-Granular Locking / Locking Scheme Concurrency Control (MGL/LSCC), Multi-Versioning Concurrency Control (MVCC), Optimistic Concurrency Control (OCC)
Description
The course is a continuation of the course ‘Database Management Systems’, and focuses on issues related to query optimization of the Relational Database Model, development of stored procedures and embedding SQL statements into 3rd generation programming languages. Moreover, students practice in modern database environments which implement and manipulate the Object-Oriented, the Object-Relational and the Semi-Structured Data Model. They realize in practice: (a) the concept of the SQL transaction, (b) the need to maintain consistency of the database content, (c) error and exception handling, and (d) issues relating to transaction concurrency control and the implementation of the ISO/ANSI isolation levels in a number of RDBMS platforms. For hands-on practicing, the DBTechNet (www.dbtechnet.org) DebianDB virtual machine is freely available to the students for downloading; the software incorporates pre-installed (free) versions of a number of RDBMS products like IBM DB2 Express-C, Oracle Express, MySQL, and PostgreSQL.
Structure
It consists of two basic parts:
a) Study of all fundamental theory and techniques of advanced Database Technology.
b) Practice in the lab using hands on exercises.
Evaluation
Evaluation is based on final exams. Optional assignments contribute to student evaluation.
projects and oral exams
The course aims to develop a substantial understanding of advanced issues of Database Systems. On completing the course, students should have achieved reasonable competence in these technologies. They should also be able to:
● Embeds SQL statements into programs using JDBC technology, OLEDB, Embedded SQL,
● Implements, by using stored procedures, data manipulation services in the databse server,
● Analyzes related concepts and practices to Object-Oriented Database Model and Object Query Language,
● Analyzes related concepts and practices to Object-Relational Database Model and SQL3,
● Identifies basic concepts and practices of semi-structured databases and related technologies like XML, XPath, XQuery.
● Explains the concepts and practice pertaining to the implementation of transaction concurrency control in the modern DBMS
● Appreciates and differentiates between the underlying technologies implementing transaction concurrency control and isolation levels in popular DBMS environments like MySQL, Oracle, IBM DB2, PostgreSQL, etc.
● Develops programming code in the transactional context, emphasizing on the preservation of the database data content consistency
● Experiments, reproduces, and explains problems relating to the absence or inadequacy of transaction concurrency control. Realizes the degree to which the application of best practices plus the proper use of transaction isolation levels amplify or eliminate completely these problems
● Learns not to rely on what the DBMS technical manuals state, but always prove by experimenting the system behavior in real life critical data integrity situations
1. R. Ramakrishnan και J. Gehrke, Database Management Systems, 3rd Edition, Mc Graw-Hill, 2002.
2. T. Connolly, C. Begg, Database Systems: A Practical Approach to design, Implementation, and Management, 6th Edition, Addison Wesley, 2014.
3. R. Elmasri και S.B. Navathe, Fundamentals of Database Systems, 7th Edition, Addison-Wesley 2015.
4. J.D. Ullman, J. Widom, A First Course in Database Systems, 3rd Edition, Prentice-Hall, 2007.
5. C. J. Date, An Introduction to Database Systems, 8th Edition, Pearson, 2003.
6. The Object Data Management Standard: ODMG 3.0, Edited by R. G.G. Cattell, D. K. Barry, M. Berler, J. Eastman, D. Jordan, C. Russell, Ol. Schadow, T. Stanienda, and F. Velez. Morgan Kaufmann, 2000
7. M. Fisher, J. Ellis and J. Bruce. JDBC: API Tutorial and Reference, 3rd Edition. Addison-Wesley Professional, 2003.
8. M. Laiho, D.A. Dervos, K. Silpiö, SQL Transactions – Theory and Hands-On-Exercises, 1st Edition, DBTechNet 2013: http://150.214.214.5/DBTechVETProd/sites/default/files/sql-transactions_handbook_en_2014-11-10.pdf
