Teaching code

4S012328

Credits

12

Coordinator

Alberto Belussi

Language

Italian

Scientific Disciplinary Sector (SSD)

INF/01 - INFORMATICS

Courses Single

Authorized

Moodle Seminars 0

The teaching is organized as follows:

Learning objectives

The course aims to provide the necessary skills for (a) data design based on requirements with reference to different application contexts and within the production process of software systems; (b) the effective and efficient management and use of data always according to the assigned requirements; (c) the use of a relational database management system to create, manage and query information contained in a database; (d) the development of applications that interact with relational databases. At the end of the course, the student will have to: demonstrate knowledge and understanding of the data models and query languages that characterize data management systems, the fundamental mechanisms for developing applications that interact with a database; knowing how to apply the knowledge acquired for the design of a data collection with respect to a set of application requirements, ability to efficiently query and use the data managed by a system, ability to design and implement an application that interacts with a base of data; Be able to choose the most appropriate representation for a set of data that characterizes an application area and/or evaluate the quality of a given data representation in a relational system; knowing how to evaluate the correctness and quality of a query on relational systems; knowing how to argue in a technical and precise way the quality of the representation of a set of data and/or the correctness of a query on relational systems; Be able to independently expand the knowledge in order to analyze and highlight the model and language differences of new data management systems.

Prerequisites and basic notions

Basic knowledge of linear algebra, logic, programming and operating systems (secondary memory and file system)

Program

The program is specified for each module as follows.
Module of Theory
--------------------------
- Introduction to database management systems (DBMS): architectures and functionalities of a DBMS. Physical and logical data independence. Data models. Concepts of model, schema and instance of a database. Languages for database systems. DBMS vs. file system.
- Conceptual database design: definition of information system, conceptual data models. The Entity-Relationship model (ER). Elements of the ER model: entities, attributes, relationships, ISA hierarchies and cardinality constraints.
- Logical database design: the relational data model adn the data model of the document-based systems. Elements of the relational data models: relations and integrity constraints. Mapping between conceptual schemas in ER model and logical schema in the relational model. Mapping between conceptual schemas in ER model and logical schema in the document-based systems: labelling of ER schema and translation into JSON document schema.
- Interacting with a database system: languages for the definition, querying and update of a database. The relational algebra. Optimization of algebraic expressions. Tuple relational calculus and MongoDB query language.
LAB
---------------------
- Introduction to the Relational DataBase Management System (RDBMS) PostgreSQL.
- Introduction to using SQL in PostgreSQL.
- Query optimization. - Introduction to transactions.
- Access to databases from Python programs.
- Introduction to the use of the NoSQL DBMS of MongoDB
Database Technology
----------------------------------------------
- The internal architecture of a DBMS. Transactions. Transactions properties. The concurrency control: schedules, the two-phase locking. Access methods (indexes): primary and secondary indexes, B-+tree, hashing based access methods. Query execution and optimization.
- The architecture of a document-based NoSQL system: the case of MongoDB.

Didactic methods

Theory Module
-------------------------
Teaching delivery methods: face-to-face lectures (also available in recorded streaming; recordings will be made available on request for students unable to attend in person or in streaming), classroom exercises with the teacher, teaching material (slides) and further exercises available on the eLearning platform, reception at the times indicated on the teacher's web page.
Laboratory Module
--------------------------------
Teaching delivery methods: classroom lectures and exercises in the computer lab , didactic material (slides) and further exercises on the eLearning platform, individual reception at the times agreed with the teacher.
Database Technologies Module
---------------------------------------------------------
Teaching methods: lectures, classroom exercises with the teacher, teaching material (slides) and further exercises available on the eLearning platform, reception at the times indicated on the page web site of the teacher.

Learning assessment procedures

The exam consists of two parts: a theory test, an integrated technology test, and a laboratory test.
Theory Modules
--------------------------------------------------------------------
For the "Theory" module, there is a written exam lasting approximately two hours. This exam consists of an exercise on the conceptual (ER model) and logical (relational and/or JSON model) design of a database, exercises on queries in relational algebra and relational calculus on an assigned database, and questions on the theory portion. Exam topics from previous years are available in the EXAM TOPICS AND OTHER SUMMARY EXERCISES section of the eLearning platform.
Students can also take ongoing tests during the year: these tests are set by the instructor in consultation with the students. There are two tests: (i) the first test on database design, including conceptual (ER model) and logical (relational and/or JSON model) design; (ii) the second part on querying a relational database using relational algebra and relational calculus. The overall exam, or the combination of the two midterm exams, accounts for 6 out of 12 credits.
Technologies and Laboratory Module
--------------------------------
For the "Technologies" and "Laboratory" modules, there is a two-hour written exam that includes:
(i) LAB - theory questions and exercises on optimizing SQL queries in PostgreSQL and writing code in Java or Python to access a relational database.
(ii) TECH - theory questions and exercises on SQL and NoSQL queries, transactional systems, the concept and properties of a transaction, concurrency control (VSR, CSR, and two-phase locking), data access methods (B-tree indexes and hashing), and MongoDB architecture.
The technology exam accounts for 6 out of 12 credits of the overall grade.
Both tests are preceded by a computerized test aimed at verifying the minimum skills required for the written test.

Evaluation criteria

To pass the exam, the students must show that:
- they have understood the concepts related to the theory of relational databases and their design and implementation on database management systems
- they are able to describe the concepts in a clear and exhaustive way without digressions
- they are able to apply the acquired knowledge to solve application scenarios described by means of exercises, questions and projects.

Criteria for the composition of the final grade

The final grade out of thirty is obtained by taking the weighted average of the grade obtained in the theory test (6/12) and the grade obtained in the integrated Technology and Laboratory test (6/12).

Exam language

italiano