Databases
A.Y. 2019/2020
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: First 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
First semester
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.
- 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.
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.
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).
- 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 < 25 or P < 25) the interview is not possible and the grade including the written test and the laboratory project is automatically confirmed.
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 < 25 or P < 25) the interview is not possible and the grade including the written test and the laboratory project is automatically confirmed.
INF/01 - INFORMATICS - University credits: 12
Laboratories: 48 hours
Lessons: 72 hours
Lessons: 72 hours
Shifts:
-
Professor:
Ferrara AlfioTurno A
Professor:
Montanelli StefanoProfessor(s)
Reception:
On appointment
room 7008
Reception:
On appointment. The meeting will be online by first contacting the professor by email.
Online. In case of a meeting in person, Department of Computer Science, via Celoria 18 Milano, Room 7012 (7 floor)
Reception:
By appointment to be arranged by email
Room 7015, Dipartimento di Informatica "Giovanni degli Antoni", Via Celoria 18 - 20133 Milano