Databases

A.Y. 2020/2021
12
Max ECTS
120
Overall hours
SSD
INF/01
Language
Italian
Learning objectives
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.
Course syllabus and organization

Single session

Responsible
Lesson period
First semester
In the emergency phase, the teaching activities will be provided in form of synchronous lectures by the Zoom platform according to the course time schedule. The lectures will also be recorded and uploaded on the ARIEL website. Students will be informed as soon as possible, through the ARIEL website, also in case they will be able to attend classes at University. This possibility will depend upon the evolution of the emergency and the respect of the security directives.
Course syllabus
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.
Teaching methods
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.
Teaching Resources
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.

Additional supplementary material, prepared by the lecturers, includes several examples and datasets and is available on the course website (https://aferrarabd.ariel.ctu.unimi.it).
Assessment methods and Criteria
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
INF/01 - INFORMATICS - University credits: 12
Laboratories: 48 hours
Lessons: 72 hours
Shifts:
Professor: Ferrara Alfio
Turno A
Professor: Montanelli Stefano
Turno B
Professors: Bellandi Valerio, Livraga Giovanni
Educational website(s)
Professor(s)
Reception:
Friday, 15.00- 16.00
Department of Computer Science, via Celoria 18 Milano, Room 7012 (7 floor)
Reception:
Thursday from 11AM to 12PM (check news at the specified webpage)
Room 7015, Dipartimento di Informatica "Giovanni degli Antoni", Via Celoria 18 - 20133 Milano