Course code DatZ2005

Credit points 3

Database Technologies II

Total Hours in Course81

Number of hours for lectures16

Number of hours for seminars and practical classes16

Independent study hours49

Date of course confirmation06.09.2022

Responsible UnitInstitute of Computer Systems and Data Science

Course developer

author prof.

Gatis Vītols

Dr. sc. ing.

Prior knowledge

DatZ2004, Database Technologies I

Course abstract

The aim of the study course is to learn data base model creation basics technological implementations.
Studies of database technology and application for development of information systems are performed. Skills of structured query language complex constructions, data definition language statement application and specific query constructions are acquired.
Students gain practical knowledge about formulation of complex queries and identification of query costs. Students learn advanced skills about database management system PostgreSQL and individually develop semester assignment.

Learning outcomes and their assessment

Student knows about database technologies, application of data management methods as well as Structured Query Language (data base project).
Acquire skills to develop databases for various data storage; evaluate and improve data model design; retrieve data with Structured Query Language (1st and 2nd test).
Have competence within a group or independently analyse the data, choose and substantiate technological solution for data storage and retrieval (data base project, 2nd test).

Course Content(Calendar)

1. Complex SQL: subqueries (1h lecture 1h practicals).
2. Complex SQL: correlated queries (1h lecture 1h practicals).
3. Multiple query unions (1h lecture 1h practicals).
4. Indexes and indexing for improved data management (1h lecture 1h practicals).
5. Data import and export (1h lecture 1h practicals).
6. Development of SQL functions (2h lecture 2h practicals).
7. Development of PL/pgSQL functions (2h lecture 2h practicals).
8. Data modification functions (1h lecture 2h practicals).
9. Data base reporting (1h lecture 1h practicals).
10. Theoretical test (data base architecture and SQL) (1h lecture).
11. Concepts of data warehouses (1h lecture 1h practicals).
12. Data base technology further development (1h lecture 1h practicals).
13. Data base project presentations (1h lecture 1h practicals).
14. Data base project presentations (1h lecture 1h practicals).

Requirements for awarding credit points

To receive credit points students must submit and receive positive grade for data base project as well as all test average mark must be positive. Practicals must be submitted and graded.
Tests:
1. Data retrieval from multiple tables and complex queries.
2. Data base function development
3. Theoretical test about data base architecture and SQL.

Description of the organization and tasks of students’ independent work

Students need to individually develop data base project assignment which includes development of a data base in database management system, including documentation of the data base following specific requirements. Students must present and defend technical solution for project development and submit documentation for the project.
For development of the project 6 weeks are given since definition of the assignment.

Criteria for Evaluating Learning Outcomes

Semester final mark is awarded from exam mark. To access exam, students must receive positive grade in tests. Practicals must also be submitted and graded positive.
Exam consists from two parts. In the first part students give written answers to theoretical questions. In the second part students show practical skills solving tasks in PostgreSQL data base management system.

Compulsory reading

1. Date C.J. Introduction to Database Systems. 8th Ed. Boston: Addison Wesley, 2004. 1024 p.
2. Coronel C. Database principles: fundamentals of design, implementation, and management. Andover: Cengage Learning, 2013. 866 p.
3. Oppel A. SQL: a beginner’s guide. New York: McGraw-Hill, 2016. 533 p.
4. Kumar V.N.A. PostgreSQL 13 Cookbook. Packt Publishing, 2021. 319 p. (Pieejama Datoru Sistēmu katedras bibliotēkā) (Available at library of Department of Computer Systems)

Further reading

1.Dombrovskaya H., Novikov B., Bailliekova A. PostgreSQL Query Optimization. Apress. 2021. 315 p.

Periodicals and other sources

1.Žurnāls "Data Base Journal". QuinStreet. Pieejams: http://www.databasejournal.com
2.Emuārs "DB-Enignes Blog", solid IT. Pieejams: https://db-engines.com/en/blog

Notes

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