LV EN RU DE FR

Database Technologies

Course developer

Course abstract

The aim of the study course is to acquire the fundamentals of database model creation and technological implementation.
The course covers database technologies and their basic functionalities. It introduces the fundamentals of relational database design, the concepts of data models and schemas. Students learn how to develop, link, and normalize data tables in relational databases, as well as different table relationship types. Both theoretical and practical aspects of data retrieval using Structured Query Language (SQL) are studied.
Students gain practical skills in working with the PostgreSQL database management system and complete an independent assignment.

Learning outcomes and their assessment

Knowledge of the fundamental concepts of database technologies, usage methods, and Structured Query Language (SQL) (database project).
Skills to practically develop databases for storing various types of data, evaluate database table designs and address design deficiencies, and retrieve data using SQL (1st test, 2nd test).
Competencies to analyze data independently or in collaboration with a team, select and justify technological solutions for data storage and retrieval (database project, 2nd test).

Course Content(Calendar)

1. Data, information, knowledge, the concept of a data model, relational model (Lecture – 2h).
2. Table normalization (Lecture – 1h).
3. Database management systems (Lecture – 1h, Practical work – 1h).
4. Development of a relational database model for a specific task (Practical work – 3h).
5. PostgreSQL database management system architecture (Lecture – 2h).
6. Development of relational database tables (Lecture – 2h, Practical work – 4h).
7. Creating table relationships and recursive relationships (Lecture – 2h, Practical work – 2h).
8. Structured Query Language (SQL) statements: DML, DCL, and TCL (Lecture – 2h, Practical work – 2h).
9. Data retrieval from tables and table views (Lecture – 4h, Practical work – 4h).
10. Data grouping options in queries (Lecture – 2h, Practical work – 2h).
11. Complex SQL constructs (Lecture – 2h, Practical work – 2h).
12. The importance of indexes in database data retrieval (Lecture – 2h, Practical work – 1h).
13. Data import and export(Lecture – 2h, Practical work – 1h).
14. Development of SQL and PL/pgSQL functions (Lecture – 4h, Practical work – 4h).
15. Data modification functions (Lecture – 2h, Practical work – 2h).
16. Theoretical test on database architecture and SQL (Practical work – 2h).
17. The concept of data warehouses(Lecture – 2h).
18. Database project defense (Practical work – 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 tests and the project work grade must be passing.

Description of the organization and tasks of students’ independent work

As part of the course, a database project must be developed – a database for an information system, demonstrating the knowledge acquired throughout the course. A functional database must be developed for the information system in accordance with the defined requirements. The student must justify the technical implementation of the project by creating a project description document. Six weeks are allocated for the project development.

Criteria for Evaluating Learning Outcomes

The course includes 1 project work and 2 tests:
1. Practical test: ER model development
2. Practical test: ER model implementation in PostgreSQL database, queries, and functions
3. Theoretical test: Database management concepts
To be eligible to take the exam, the average grade of all three tests and the project work grade must be passing.
In the exam, students receive a task variant and must answer theoretical questions on the topics covered in the course, 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. Date C.J. Introduction to Database Systems, An 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. 319p. (Pieejama Datoru Sistēmu katedras bibliotēkā) (Available at library of Department of Computer Systems)

Further reading

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