The Entity/Relationship (ER) and relational models. Methodologies, techniques, and models for database design. The basic constructs of the ER model: entity types and instances, relation types and instances, attributes (simple/compound, single-valued/multiple-valued, optional, derived), keys (internal and external identifiers, weak entities); attribute domains and the use of NULL; participation constraints and cardinality ratio; recursive relations and roles; binary relations and relations with arity >= 2; ER diagrams; documentation of ER schemas; business rules (integrity constraints and derivation rules); advanced ER constructs: specialization and category. UML data modelling. The relational data model: basic notions, relations, constraints, relational database schemas and instances, update operations (insertion, deletion, modification) and possible constraint violations, constraint violation policies.

Database design. Conceptual design: requirement collection and analysis; criteria for data representation; design strategies; qualities of a conceptual schema; tools. Logical design: performance analysis on ER schemas; restructuring of ER schemas (redundancy analysis, elimination of specialization hierarchies, partitioning/ merging of entities/relations, choice of the primary identifier); mapping of ER schemas into relational ones. The logical design of relational databases: functional dependencies, relation decomposition (lossless join and preservation of functional dependencies), normal forms.

Relational algebra, relational calculi, and SQL. Relational algebra: basic operations, queries, derived and additional operations, algebraic optimization, the limitations of relational algebra; the relational calculus: basic notions, domain relational calculus and tuple relational calculus with range specification, the relationships between them, relational calculi and relational algebra. SQL: data definition and data types; queries; updates; additional data definition features (generic integrity constraints, views, specifying constraints as assertions); the DBMS PostgreSQL, notes on SQL and programming languages (triggers, procedures, and functions).

Physical organization of data. The storage of file records and the organization of primary files: introduction, the management of secondary storage, placing file records on disks, operations on files, files of unordered records (heap files) and of ordered records (sorted files), hashing techniques, other primary file organizations, efficient disk access by means of RAID technology. Indexing structures for files: single-level ordered indexes, static multilevel indexes, dynamic multilevel indexes (B-trees and B+-trees), other types of indexes.

Database server technology. Transactions: introduction to transaction processing, properties of transactions, transaction scheduling and recovery, serialization techniques, transaction support in SQL. Buffer manager: architecture, primitives, buffer management policies, relationships between buffer manager and file system. Concurrency control techniques: problems, architecture, anomalies of concurrent transactions, view-based and conflict-based techniques, two-phase locking techniques (2PL and strict 2PL), timestamp method, multiversioning-based techniques, granularity of data items.

Database recovery techniques: basic concepts, architecture, stable memory, log file, transaction processing, fault management. Query processing and optimization: system catalogs, query optimization (query internal representation, relation profiles, cost-based optimization), physical design of databases.