Databases
A.Y. 2024/2025
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.
Lesson period: Second semester
Assessment methods: Esame
Assessment result: voto verbalizzato in trentesimi
Single course
This course cannot be attended as a single course. Please check our list of single courses to find the ones available for enrolment.
Course syllabus and organization
Single session
Responsible
Lesson period
Second semester
Course syllabus
The course is organized in two parts, one about the theory and one about the laboratory.
The topics covered in the 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 (Structured Query Language)
- Physical organization of data and indexes
- Security and access control
- Transactions (general concepts)
The topics covered in the laboratory part:
- The PostgreSQL DBMS
- Creation, manipulation, and dumping of schemes
- Access control (firewall), user and role management
- Procedural language (PLpgSQL)
- Client/server architecture of web applications and the HTTP protocol
- Web programming with HTML and PHP
- GET/POST parameters
- Interaction with the DBMS
A detailed description of the syllabus is available on the course website (Ariel platform).
The topics covered in the 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 (Structured Query Language)
- Physical organization of data and indexes
- Security and access control
- Transactions (general concepts)
The topics covered in the laboratory part:
- The PostgreSQL DBMS
- Creation, manipulation, and dumping of schemes
- Access control (firewall), user and role management
- Procedural language (PLpgSQL)
- Client/server architecture of web applications and the HTTP protocol
- Web programming with HTML and PHP
- GET/POST parameters
- Interaction with the DBMS
A detailed description of the syllabus is available on the course website (Ariel platform).
Prerequisites for admission
The student must know and must be able to use at least one imperative programming language as well as basic notions of propositional logic.
Passing the Computer Programming exam is a prerequisite for the course of Databases.
Passing the Computer Programming exam is a prerequisite for the course of Databases.
Teaching methods
The theory part is carried out through lectures. There are practice sessions in which students carry out exercises that are then corrected and commented on in the classroom. The laboratory part consists of lectures, guided exercises, and practical activities carried out in a computer room.
Attendance, although not mandatory, is strongly recommended.
Attendance, although not mandatory, is strongly recommended.
Teaching Resources
The bibliography coincides for attending and non-attending students.
About the theory part, choose a book between:
- R. Elmasri, S.B. Navathe, Sistemi di basi di dati - Fondamenti e Complementi (7 ed.), Pearson, 2018.
- P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone, Basi di dati (VI ed.), McGraw-Hill, 2023.
About the laboratory part, consider the online handbook of the tools:
- PostgreSQL (https://www.postgresql.org/docs/manuals/)
- PHP (https://www.php.net/download-docs.php)
Additional supplementary material is made available on the course website (Ariel platform) throughout the lecture period.
About the theory part, choose a book between:
- R. Elmasri, S.B. Navathe, Sistemi di basi di dati - Fondamenti e Complementi (7 ed.), Pearson, 2018.
- P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone, Basi di dati (VI ed.), McGraw-Hill, 2023.
About the laboratory part, consider the online handbook of the tools:
- PostgreSQL (https://www.postgresql.org/docs/manuals/)
- PHP (https://www.php.net/download-docs.php)
Additional supplementary material is made available on the course website (Ariel platform) throughout the lecture period.
Assessment methods and Criteria
Attending and non-attending students: The exam consists of two mandatory tests covering the theory part and the laboratory part, respectively. The student may take the theory test and the laboratory test in the order she/he prefers. No more than one year shall elapse between the two tests. If this deadline is not met, the grade of the test already taken is cancelled.
Theory test
It is based on a written test of approximately two hours about the syllabus of the whole theory part. The test consists of part A with closed-ended questions and part B with exercises. To take the exam, students must register to one of the exam sessions planned on the official calendar. The written test is evaluated in the range 0-30 (grade S) and it is based on the following parameters: degree of knowledge of topics, ability to apply such knowledge to solving exercises, completeness of answers, correctness of reasoning in carrying out exercises. If a grade of at least 25/30 is achieved, the student can ask for an oral discussion to improve the grade. If the oral discussion is not taken, the grade for the theory test is automatically confirmed. If a grade of less than 25/30 is achieved, the oral discussion is NOT possible.
Laboratory test
It consists of a project and an oral discussion of approximately thirty minutes, both mandatory. The project aims to evaluate practical aspects of one or more topics covered in the syllabus of the laboratory part. The oral discussion focuses on the topics developed in the project and it aims to evaluate its quality. The requirements and specifications of the project are communicated at the end of the laboratory lectures. The project is developed, by each student individually, within 12 months from the project publication. To take the oral discussion, students must register to one of the exam sessions planned on the official calendar and must upload the project material according to the specifications. The overall laboratory test is evaluated in the range 0-30 (grade P) and it considers both the project and the discussion according to the following parameters: degree of knowledge of topics, ability to apply such knowledge to solving a real project, quality of the developed project, critical thinking, clarity of expression, property of language.
Final grade
After taking both the theory test and the laboratory test, the student receives a grade proposal in thirtieths corresponding to the weighted average of the two tests calculated as follows: grade=(2S+P)/3. The student has the option of rejecting the grade proposal. In this case, the teachers can allow the student to keep the grade received in one of the two tests (theory or lab).
Theory test
It is based on a written test of approximately two hours about the syllabus of the whole theory part. The test consists of part A with closed-ended questions and part B with exercises. To take the exam, students must register to one of the exam sessions planned on the official calendar. The written test is evaluated in the range 0-30 (grade S) and it is based on the following parameters: degree of knowledge of topics, ability to apply such knowledge to solving exercises, completeness of answers, correctness of reasoning in carrying out exercises. If a grade of at least 25/30 is achieved, the student can ask for an oral discussion to improve the grade. If the oral discussion is not taken, the grade for the theory test is automatically confirmed. If a grade of less than 25/30 is achieved, the oral discussion is NOT possible.
Laboratory test
It consists of a project and an oral discussion of approximately thirty minutes, both mandatory. The project aims to evaluate practical aspects of one or more topics covered in the syllabus of the laboratory part. The oral discussion focuses on the topics developed in the project and it aims to evaluate its quality. The requirements and specifications of the project are communicated at the end of the laboratory lectures. The project is developed, by each student individually, within 12 months from the project publication. To take the oral discussion, students must register to one of the exam sessions planned on the official calendar and must upload the project material according to the specifications. The overall laboratory test is evaluated in the range 0-30 (grade P) and it considers both the project and the discussion according to the following parameters: degree of knowledge of topics, ability to apply such knowledge to solving a real project, quality of the developed project, critical thinking, clarity of expression, property of language.
Final grade
After taking both the theory test and the laboratory test, the student receives a grade proposal in thirtieths corresponding to the weighted average of the two tests calculated as follows: grade=(2S+P)/3. The student has the option of rejecting the grade proposal. In this case, the teachers can allow the student to keep the grade received in one of the two tests (theory or lab).
INF/01 - INFORMATICS - University credits: 12
Laboratories: 48 hours
Lessons: 72 hours
Lessons: 72 hours
Shifts:
Turno
Professor:
Montanelli StefanoTurno A
Professor:
Bellandi ValerioTurno B
Professor:
Livraga GiovanniProfessor(s)
Reception:
On appointment
room 7008
Reception:
On appointment by email
Room 7015, Dipartimento di Informatica "Giovanni degli Antoni", Via Celoria 18 - 20133 Milano