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 confirmation12.01.2016

Responsible UnitDepartment of Computer Systems

Course developer

author Datoru sistēmu katedra

Gatis Vītols

Dr. sc. ing.

Prior knowledge

DatZ2004, Database Technologies I

DatZ2005, Database Technologies II

DatZ3003, Large Databases I

Course abstract

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

Knowledge about large database architecture and application possibilities of Structured Query Language extensions. Practical 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. Tests topics: 1.PL/SQL procedure and function programming 2.Trigger programming and dynamic SQL. 3.Theory test main topics relate to description of SQL extension description with focus on PL/SQL and SQL tuning.

Course Content(Calendar)

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

Requirements for awarding credit points

Students get 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.

Description of the organization and tasks of students’ independent work

There are no individual assignments scheduled during the course.

Criteria for Evaluating Learning Outcomes

Semester final test mark is calculated as an average mark based on student scores gathered from homework and semester tests.
To access exam, students need to receive positive mark in tests which is calculated as an average mark from three tests. During exam students take written test (part 1) which includes questions from semester topics. After first part, students take 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. 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.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.

Periodicals and other sources

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

Notes

Compulsory course in Computer Science and Computer Science and Information Technology for Sustainable Development.