Course code DatZ3004

Credit points 2

Large Databases II

Total Hours in Course80

Number of hours for lectures16

Number of hours for seminars and practical classes16

Independent study hours48

Date of course confirmation06.09.2022

Responsible UnitDepartment of Computer Systems

Course developer

author prof.

Gatis Vītols

Dr. sc. ing.

Prior knowledge

DatZ2004, Database Technologies I

DatZ2005, Database Technologies II

DatZ3003, Large Databases I

Course abstract

The aim of the study course is to aquire knowledge about tools and technologies that allow to manage large and specific data sets.
Studies of database management system extended functionality are performed. Application of data retrieval language specific constructions, PL/SQL extension, procedure and function creation are discussed and practically applied. Such topics as query optimization, storage of various types of data are included. Students gain practical knowledge about work with large database management system Oracle Database.

Learning outcomes and their assessment

Knows about large database architecture and application possibilities of Structured Query Language extensions.
Have skills about the development of large databases, data storage and retrieval, evaluation of database architecture and application of Structured Query Language extensions for data retrieval are acquired.
Students develop competences of data analysis, choosing and participating in discussions about the technological solution for storage and retrieval of large amounts of data, working in groups or individually.
Learning outcomes are assessed with three tests.

Course Content(Calendar)

1.Query language SQL extensions, procedure language PL/SQL. PL/SQL overview, scope of application (2h lectures, 2h practicals).
2.PL/SQL procedures and functions. Procedure and function programming and execution (2h lectures, 2h practicals).
3.PL/SQL collections and records. Understanding collections and nested tables (1h lectures, 1h practicals).
4.Cursor variables. Cursor data modification. Data records locks and memory management (1h lectures, 1h practicals).
5.PL/SQL packages. Package specification and body management (1h lectures, 1h practicals).
6.Trigger concepts and application. Trigger types. Trigger management mechanisms. Trigger programming (2h lectures, 2h practicals).
7.Object creation in relation database. Object type definition. Object rows, object tables and nested table creation and application (1h lectures, 1h practicals).
8.Dynamic SQL. Dynamic SQL concepts and application cases. Database management system standard packages for programming dynamic SQL (1h lectures, 1h practicals).
9.Query optimization. Structured query language execution plans and optimisation scenarios (3h lectures, 3h practicals).
10.NoSQL application for data storage. NoSQL database tools, application principles (1h lectures, 1h practicals).
11.NoSQL application for data retrieval. Writing queries in a NoSQL database (1h lectures, 1h practicals).

Requirements for awarding credit points

Students receive awarding credit points after they successfully pass exam. To access exam, students need to receive positive mark in tests which is calculated as an average mark from three tests and complete assigned practicals.
Tests topics:
2.PL/SQL procedure and function programming
3.Trigger programming and dynamic SQL.
Theory test main topics relate to description of SQL extension description with focus on PL/SQL and SQL tuning.

Description of the organization and tasks of students’ independent work

There are no individual assignments scheduled during the course.

Criteria for Evaluating Learning Outcomes

To access exam, students need to receive positive mark in tests which is calculated as an average mark from three tests. During exam students complete written test (part 1) which includes questions from semester topics. After first part, students receive practical assignment in classroom which they complete and submit within given time.

Compulsory reading

1. Casteel J. Oracle 12c: SQL. Boston: Cengage Learning, 2016. 604 p.
2. Nanda A., Tierney B., Helskyaho H., Widlake M., Nuijten A. Real-world SQL and PL/SQL: Advice from the Experts. New York: McGraw-Hill Education, 2017. 700 p.
3. McLaughlin M., Oracle Database 12c PL/SQL Advanced Programming Techniques. New York: McGraw-Hill Education, 2015. 381 p.
4. Pivert O. NoSQL Data Models. Hoboken, NJ: ISTE Ltd / John Wiley and Sons Inc. 2018. 249 p.
5. Niemiec R.J. Oracle Database 12c Release 2 Performance Tuning Tips and Techniques. New York: McGraw-Hill Education, 2017. 1085 p.

Further reading

1. Coronel C., Morris S. Database Systems: Design, Implementation, and Management. Australia: Cengage Learning, 2016. 791 p.
2. Kuhn D., Kyte T. Oracle Database Transactions and Locking Revealed. 2nd Ed. Apress, 2021. 265 p. (pieejama Datoru sistēmu katedras bibliotēkā) (Available in the library of Department of Computer Systems)

Periodicals and other sources

1. "The VLDB Journal". Springer Berlin Heidelberg. Pieejams: http://www.springerlink.com/content/1066-8888
2. "Oracle Magazine". Oracle Press. Pieejams: https://blogs.oracle.com/oraclemagazine/
3. "Data Base Journal". QuinStreet. Pieejams: http://www.databasejournal.com

Notes

Compulsory course for study programmes “Computer Control and Computer Science” and “Information Technologies for Sustainable Development”.