LV EN RU DE FR

Large Databases

Course developer

Prior knowledge

DatZ2004, Database Technologies I

DatZ2005, Database Technologies II

Course abstract

The aim of the study course is to master the specifics of large database management, data management, and the use of the Oracle Database system.
The course covers various types of large databases and the memory and process architecture of their database management systems. The topics discussed include the concepts of logical and physical data models of databases, defining database structures, and utilizing different methods for data retrieval and loading into databases.
Additionally, the course explores the extended capabilities of large database management systems, including specific query language constructs, PL/SQL extension capabilities, the creation of procedures and functions, as well as query optimization and the storage of specific data.
Students will acquire practical skills in working with the Oracle Database large database management system and will complete independent coursework.

Learning outcomes and their assessment

Knowledge of large database architecture and the application possibilities of Structured Query Language extensions (practical tests, theory test).
Skills to practically develop databases for storing and retrieving large volumes and various types of data; to evaluate large database architectures; to use Structured Query Language extensions for data retrieval (practical assignments, home works, tests).
Competencies to analyze data independently or in collaboration with a team, select and justify technological solutions for storing and retrieving large volumes of data (practical tests, theory test).
The course outcomes are assessed through one assignment, in which students must develop a database using the Oracle Database Management System and the knowledge acquired in the course, as well as through three tests.
A theoretical test covers database architecture, data storage and retrieval, and PL/SQL (all topics learned in the course).

Course Content(Calendar)

1. Logical and Physical Models of Large Databases and Their Management – Schemas, tablespaces, segments, extents, data blocks, system log files, control files (Lecture – 1h).
2. Database Management System Instances and Their Architecture – Memory structures, server, and background processes (Lecture – 1h, Practical work - 2h).
3. Creating and Administering Different Database Users – User role management and access to various database resources (Lecture – 2h, Practical work - 2h).
4. Defining Database Structures in Oracle Database – Oracle database specifics, sequence creation, transaction management, metadata, and synonym management (Lecture – 2h, Practical work - 2h).
5. Conditional Functions in Structured Query Language (SQL) – Usage of conditional functions in Oracle Database queries and data management functions (Lecture – 2h, Practical work - 2h).
6. Data Selection in Hierarchical Data – SQL language extensions for working with hierarchical data (Lecture – 1h, Practical work - 1h).
7. Working with Database Views – Managing simple and complex views, using DML operators on views, significance and management of materialized views (Lecture – 1h, Practical work - 3h).
8. Fundamentals of Database Report Creation – Importance of reports, report creation using standard database tools, and report scripting (Lecture – 2h, Practical work - 2h).
9. Data Loading, Import, and Export Capabilities – Loading external data of various types into a database using standard tools, exporting and importing specific data sets (Lecture – 1h, Practical work - 2h).
10. Concepts of Spatial Data Management – Basic principles of spatial data and SQL language extensions for spatial data management (Lecture – 1h).
11. SQL Query Language Extension – Procedural Language PL/SQL – General capabilities, principles, and limitations of PL/SQL (Lecture – 2h).
12. PL/SQL Procedures and Functions – Programming procedures and functions, as well as invocation mechanisms (Lecture – 3h, Practical work - 3h).
13. PL/SQL Composite Data Types – PL/SQL records and tables, indexed tables, and nested tables (Lecture – 3h, Practical work - 3h).
14. Cursor Variables – Mechanisms for modifying data retrieved by cursors, data locking, and memory allocation techniques when opening a cursor (Lecture – 1h, Practical work - 2h).
15. PL/SQL Packages – Concepts of package structures: specification and body, rules for defining package specifications and bodies (Lecture – 1h, Practical work - 2h).
16. Triggers – Concept and Usage – Types of triggers, trigger execution mechanism, and trigger programming (Lecture – 2h, Practical work - 2h).
17. Creating Objects in a Relational Database – Defining object types, concepts of object tables, object columns, and nested tables, and their creation (Lecture – 2h, Practical work - 2h).
18. Dynamic SQL – Concept and advantages of Dynamic SQL, standard database management system packages for using Dynamic SQL (Lecture – 1h).
19. Query Optimization – Execution plans in structured query language and query optimization possibilities (Lecture – 1h, Practical work - 2h).
20. Using NoSQL for Data Storage – NoSQL database tools, usage, and operating principles (Lecture – 2h).
.

Requirements for awarding credit points

To receive the course credits, it is necessary to successfully pass the exam. In order to take the exam, the average grade of all three tests and the grade of the assignment must be passing grade.

Description of the organization and tasks of students’ independent work

Students are required to independently complete an assignment in which they must develop a database using the Oracle Database Management System, following the requirements defined in the lecture.

Criteria for Evaluating Learning Outcomes

The course includes one assignment and three tests:
1. Practical test: Oracle SQL extension management
2. Practical test: PL/SQL procedures and functions
3. Theoretical test: Oracle database concepts – architecture, PL/SQL
To be eligible to take the exam, the average grade of all three tests and the grade of the assignment must be passing.
In the exam, students receive a task variant and must answer theoretical questions on the course topics, as well as complete practical tasks and submit them on the platform specified by the instructor.
The exam consists of two parts:
Theoretical part – the student answers questions orally (oral exam) and can earn a maximum of 3 points.
Practical part – the student receives a task to implement in the specified database management system and can earn up to 7 points.

Compulsory reading

1. Rosenzweig B., Oracle PL/SQL by Example Oracle Press, 2023. 480 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. 700p.
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. Nuijten A. Modern Oracle Database Programming: Level Up Your Skill Set to Oracle's Latest and Most Powerful Features in SQL, PL/SQL, and JSON. Apress, 2023. 604 p.

Further reading

Coronel C., Morris S. Database Systems: Design, Implementation, and Management. Australia: Cengage Learning 13ed, 2018. 816 p.

Periodicals and other sources

1. "The VLDB Journal", Springer Berlin Heidelberg, https://link.springer.com/journal/778
2. "Oracle Connect", https://blogs.oracle.com/connect/
3. "Data Base Journal", QuinStreet, https://www.databasejournal.com/