The course provides the fundamental concepts related to databases and systems for their management, with particular focus on relational database systems. The course addresses i) theory issues dedicated to models, languages, and design methodologies as well as security and transaction aspects; and ii) laboratory issues dedicated to the use of design and management tools for relational databases, as well as the main technologies for the development of web applications based on databases.
Expected learning outcomes
The student will acquire the following knowledge and the corresponding practical skills: design the conceptual scheme and the relational scheme of a database both starting from an informal description of the application domain and starting from a set of unstructured data; verify the level of normalization of the scheme and understand the concepts of functional dependency and normalization; understand and execute complex SQL queries correctly and define the execution plan in terms of relational algebra operations; understand, design and define the different types of indexes to optimize data access; implement active database functionalities by means of SQL procedures; understand and create web interfaces for data access.
The topics theory part are: - Concepts and architecture of a database system - Relational model, constraints, normalization - Data modeling, ER model and design concepts - Logical database design - Relational algebra - SQL - Physical organization of data and indexes - Security and access control - Transactions (general concepts)
The topics covered in the laboratory part: - The PostgreSQL DBMS - Creation and manipulation of schemes - User and role management - Access Firewall (hba.conf) - Dump of databases - Procedural language (PLpgSQL) - Web programming with PHP - Client / server architecture - HTTP protocol - GET / POST parameters - Cookies / sessions - Interaction with the DBMS
A detailed list of the topics is published and updated on the course website.
Prerequisites for admission
To know and to be able to use at least one imperative programming language. Basic notions of propositional logic. Passing the Programming exam is a prerequisite for the course of Database.
The theory part is carried out through lectures including three lectures devoted to carry out exercises on database design, relational algebra, and SQL, respectively. Exercises are then corrected and commented in the classroom. Slides and handouts are employed throughout the lectures and they are progressively published on the reference course website on the Ariel platform (https://aferrarabd.ariel.ctu.unimi.it). The laboratory part consists of lectures, exercises, and practical activities carried out in computer rooms. Lecture attendance is not mandatory, but it is strongly recommended.
The course is based on two reference books:
- R. Elmasri, S.B. Navathe, Database systems - Fundamentals and Complements (7th ed.), Pearson, 2018. - P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone, Data bases (V ed.), McGraw-Hill, 2018.
The exam consists of a written test (mandatory), a laboratory test (mandatory) and an interview (only if needed according to the following rules).
Written test It is composed by a part A with closed-answer theory questions and a part B with exercises. The written test is evaluated in range 0-30 (grade S).
Laboratory test It is composed by a part A with theoretical questions and a part B with exercises to be executed on a computer. The laboratory test is evaluated in range 0-30 (grade P).
Interview After the completion of the written test and the laboratory test, the student receives a grade in range 0-30 corresponding to the weighted average of the two tests, as follows: grade = (2S + P) / 3. If a grade of at least 25/30 is obtained in both tests (S >= 25 and P >= 25), the interview is possible. If the interview is not attended, the grade including the written test and the laboratory project is automatically confirmed. If the student has NOT obtained a grade of at least 25/30 in each of the two tests (S