Advanced DataBases

Master 1

Personal course page for the Advanced DataBases module — Master 1 NDS (Networks and Distributed Systems) and AI (Artificial Intelligence), University of Skikda. This page provides centralized access to lectures, tutorials, labs, pedagogical resources, and module information.

Course progress

  • This page follows the course as it is taught: new chapters and labs are added once they have been covered in class.
  • Almost all course files have been revised. Download the latest versions, even if you already have earlier ones.

Official Syllabus

Official syllabi — NDS and AI tracks

Track — Networks and Distributed Systems (NDS)

Track — Artificial Intelligence (AI)

The Advanced DataBases module, part of both Master 1 AI (Artificial Intelligence) and Master 1 NDS (Networks and Distributed Systems) programs at the University of Skikda, is available on www.al-moualime.com. It focuses on advanced techniques for designing, managing, and optimizing modern databases. Topics include advanced SQL (views, functions, stored procedures, triggers, error handling), the object-relational model, NoSQL databases (MongoDB, Cassandra, Neo4j), distributed and cloud databases, and security (access control, indexing, encryption, auditing). The 63-hour course combines lectures, Tutorials, and Labs, emphasizing performance, reliability, and the mastery of technologies such as PostgreSQL and MongoDB. Assessment: Final exam (60%) and Continuous assessment (40%).

Resources

Software

PostgreSQL — Relational Database Management System (RDBMS)

PostgreSQL is an open-source Relational Database Management System compliant with the Structured Query Language (SQL) standard. It supports ACID transactions and Multiversion Concurrency Control (MVCC). Used in the lectures and labs through its command-line client psql.

Lectures

Chapter 1 — Introduction to Advanced DataBases

This chapter introduces the fundamentals of Database Management Systems (DBMS), the role of data in Big Data, the relational model, and NoSQL databases.

  • Objective: Understand the role of a DBMS and master core concepts: schema, instance, constraints, and the SQL language.
  • Concepts to know: DDL, DML, DCL, TCL commands; primary/foreign keys; ACID properties; MVCC control.
  • Key ideas: Data → information; ER model → tables; ANSI/SPARC architecture; client-server model.
  • NoSQL in perspective: NoSQL databases (MongoDB, Cassandra, Neo4j) enable flexible schemas and large-scale data handling.

Takeaway: Choosing the right data model (relational or NoSQL) directly affects reliability, scalability, and performance.

Before the next lecture: Review the Chapter 1 PDF and prepare questions for the next tutorials and labs.

Chapter 2 — Advanced SQL

This chapter deepens SQL through two complementary parts. The declarative part focuses on mastering analytical queries using standard SQL constructs — joins, subqueries, aggregations, and window functions. The procedural part introduces SQL/PSM programming with stored procedures, functions, control structures, and error handling. The NoSQL data manipulation languages (e.g., JSONiq, Cypher), though included in the official syllabus, will be addressed later in the NoSQL module. Covering them now would unnecessarily complicate the learning process for students still consolidating relational foundations.

Part 1 — Advanced Declarative SQL 

Focus on advanced declarative SQL: sorting, pagination (FETCH FIRST, OFFSET), joins (INNER, OUTER, SELF), subqueries (IN, ANY, ALL, correlated), CTEs (WITH), aggregations, GROUP BY/HAVING, analytic (window) functions, and views.

Part 2 — Advanced Procedural SQL 

Focus on SQL procedural programming: the SQL/PSM standard, BEGIN / END blocks, variables, control structures, exception handling, stored procedures, functions, cursors, triggers, and transactions.

Tutorials

Organisation of the tutorials

An assistant lecturer will help you in the tutorials: first with Appendix 1 — Data integrity, then with the rest.

Appendix 1 — Data integrity

Integrity constraints (structural and non-structural), their expression in SQL (CHECK, domains, FOREIGN KEY, MATCH, assertions) and triggers — why constraints are a sine qua non condition for a truly relational DBMS.

Labs