Course program
The course introduces students to the fundamental concepts of Database Management Systems (DBMS), covering both the theoretical aspects of data models and the practical skills related to querying and designing relational databases. It includes modules on conceptual modeling, relational algebra, SQL, and logical design, alternating between theoretical lessons and hands-on exercises.
Main Topics
Course introduction and basic concepts: definition of databases and DBMS; basics of concurrency control and DBMS architecture.
Data models: focus on the relational model, intra-relational and inter-relational constraints.
Relational Algebra:
Basic operators: union, intersection, difference, Cartesian product.
Selection, projection, and join operators (theta, equi, natural).
Advanced operators: division and handling of null values.
Dedicated exercises on relational algebra.
SQL (Structured Query Language):
Introduction and basic query structure.
Operations with explicit, natural, and outer joins.
Aggregation: group functions, GROUP BY, HAVING, set operators.
DDL (Data Definition Language) and DML (Data Manipulation Language).
Nested queries.
Practical SQL exercises: installing PostgreSQL, creating databases, views, constraints, and assertions.
Conceptual design:
Entities, relationships, and attributes.
Generalizations (ISA), aggregations, and cardinality and identification constraints.
Design methodologies and conceptual modeling exercises.
Logical design:
Translating a conceptual (ER) schema into a relational schema.
Managing ISA hierarchies, constraints, and redundancies.
Exercises on schema restructuring and translation.
Course conclusion: review exercises and final exam simulations.
Prerequisites
- Basic knowledge of computer science
- Knowledge of at least one high-level programming language
- Introductory notions of discrete mathematics and logic
- Ability to use software tools on desktop operating systems
Books
Slides available on the course website
R. Ramakrishnan, J. Gehrke. Database Management Systems. McGraw-Hill, 2004.
(optional) P. Atzeni, S. Ceri, S. Paraboschi, R. Torlone. Databases: Models and Query Languages. McGraw-Hill, 2014.
Frequency
The attendance of the course is not mandatory, but it is strongly recommended for a better understanding of the content and more effective learning. Students are encouraged to actively participate in lectures and practical exercises, which are always held in the classroom.
Exam mode
The course assessment is based on written tests that combine theoretical-practical exercises and formal queries, aimed at verifying the understanding and application of the fundamental concepts covered. An oral exam is optional, upon request by the student or the instructor.
Assessment details through an example exam:
- Question 1 (10 points): Design of the Entity-Relationship (ER) schema for a concrete application, requiring the translation of textual specifications into a graphical conceptual model with entities, relationships, and attributes.
- Question 2 (8 points): Logical design of the ER schema created, emphasizing the reduction of null values and preparing the database for efficient implementation.
- Question 3 (3 + 4 + 4 points): Writing complex SQL queries on a given relational schema.
- Question 4 (3 points): Translation of an SQL query into relational algebra, verifying mastery of the formal language.
This assessment method therefore requires the student to be able to move from theory to practice, using both conceptual modeling and queries on relational data.The course assessment is based on written tests that combine theoretical-practical exercises and formal questioning, aimed at verifying the understanding and application of the fundamental concepts covered. An oral exam is optional, if requested by the student or the instructor.
Bibliography
R. Ramakrishnan, J. Gehrke. Database Management Systems. McGraw-Hill, 2004.
(optional) P. Atzeni, S. Ceri, S. Paraboschi, R. Torlone. Databases: Models and Query Languages. McGraw-Hill, 2014.
Lesson mode
The course is delivered through a blended approach that combines theoretical frontal lectures with practical exercises in the laboratory. The teaching is structured in several phases:
- Frontal lectures: Fundamental theoretical concepts related to database management systems, relational models, relational algebra, SQL language, and design methodologies at the conceptual, logical, and physical levels are covered. These lectures are integrated with practical examples presented in the classroom.
- Practical exercises: During lab activities, students apply theoretical knowledge by creating ER models, writing SQL queries, and working directly with DBMS systems such as PostgreSQL or MySQL. These sessions are essential for gaining hands-on experience in creating, managing, and querying databases.
- Autonomous activities: Exercises are proposed to be completed individually or in groups to develop autonomy in studying and solving practical problems.
This teaching approach fosters gradual learning that moves from acquiring theoretical knowledge to the ability to implement and query real databases in contexts similar to professional environments.
