Teaching code

4S00037

Credits

12

Coordinator

Alberto Belussi

Language

Italian

Scientific Disciplinary Sector (SSD)

ING-INF/05 - INFORMATION PROCESSING SYSTEMS

Moodle Seminars 0

The teaching is organized as follows:

Learning outcomes

The learning targets are presented for each module of the course.

Module of Theory
--------------------------
The first part of the course, called “Databases Theory”, has the aim to provide the student with the necessary concepts and methods for the design of a database and its applications. In particular, it will be focused on the methodologies for the conceptual and logical design of a database and for the successive database implementation on database systems. Moreover, the fundamental characteristics of some query languages will be illustrated: in particular SQL (in Lab) and of the relational algebra .

Module of Lab
------------------------
The aim of this module is to provide the student with the necessary concepts and methods for operating with a database management system and developing web applications exploiting databases. The main technologies considered during the course are based on Python language. Python will be introduced during the course. To attend the course in a productive way, students have to be confident with object-oriented programming.

Module of Database technology
------------------------
The second part of the course has the aim to provide the student with the necessary concepts and methods for the effective usage of relational database systems (RDBMS). In particular, this module will focus on: transactions, concurrency control techniques, recovery management, indices, techniques for interacting with a RDBMS from a program. Finally some concepts regarding current approaches for modeling semi-structured data will be presented (XML and XML schema).

At the end of the course, the students will be able to understand how a database management system works and they will know how to:
- design and implement relational databases;
- develop web applications that interact with relational databases.

Considering the learning targets of the degree in Computer Science this course helps to provide the student with:
- the necessary expertise for designing, development, implementation, management and maintenance of database management systems
- the basic expertise in the fields: programming, database and information systems

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: conceptual data models. The Entity-Relationship model (ER). Elements of the ER model: entities, attributes, relationships, ISA hierarchies and cardinality constraints.
- Logical database design: logical data models, the relational data model. Elements of the relational data models: relations and integrity constraints. Mapping between conceptual schemas in ER model and logical schema in the relational model.
- Interacting with a database system: languages for the definition, querying and update of a database. The relational algebra. Optimization of algebraic expressions. SQL: select-from-where statement, join in SQL, the GROUP BY and ORDER BY clauses, using subqueries. Views.

Teaching methods: lecturing, practicing with the teacher, didactical material (slides) and further exercises available on the eLearning platform, individual meetings during office hours according to the timetable published on the teacher web page.

Module of Lab
---------------------
- Introduction to the relational database management system (RDBMS) PostgreSQL.
- Introduction to the use of SQL in PostgreSQL.
- Query Optimization.
- Introduction to the transaction.
- Introduction to Python Language.
- Database access from applications written in Java/Python.
- Introduction to Flask (Python) micro framework for developing simple database-based web applications.

Lecturing and practicing in computer laboratory, didactical material (slides) and further exercise texts are available on the eLearning platform, the teacher is available for individual meeting in office hours.

Module of 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.
- Techniques for the interaction between a DBMS and an application.
- XML, XML schema, UML for XML data design (hints).

Teaching methods: lecturing, practicing with the teacher, didactical material (slides) and further exercises available on the eLearning platform, individual meetings during office hours according to the timetable published on the teacher web page.

Bibliography

Examination Methods

The exam is composed of two parts: theory and laboratory.

To pass the exam, the student 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.

Module of Theory and Database technology
----------------------------------------------------------------
For the modules of "Teoria" and "Tecnologie per le basi di dati" the exam consists of a written test with a duration of 2.5 hours containing: (i) an exercise about the conceptual modeling (using the E-R model) and the logical modeling (using the relational model) of a database; (ii) some exercises about the specification of queries in relational algebra on a given database; (iii) some exercises on XML and XML-Schema and some questions on the theory. On the e-learning platform in the section "TEMI D'ESAME E ALTRI ESERCIZI RIEPILOGATIVI" some tests of the previous years are published.
During the year, it is also possible to undergo the mid-term tests: these tests are fixed by the teacher in agreement with the students and are managed on the eLearning platform. These are three tests: the first test regards the design of a relational database: conceptual design (E-R model) and logic design (relational model), this weighs 4/9 of the theory grade; the second test regards the specification of queries on a relational database in relational algebra and SQL, this test weighs 3/9 of the theory grade; the third test on the program module of Databases Technologies, the latter test weighs 2/9 of the theory grade.

Module of Laboratorio
---------------------------------
The examination consists of a written test containing 5 exercises based on the module program. The grade in this module is worth 1/4 of the grade in the course examination. A selection of previous exam tests is published at http://profs.scienze.univr.it/~posenato/courses/labBD/raccoltaTemiEsameLaboratorioBasiDatiDal2016.pdf

The total grade (theory+laboratory) is given by the following weighted average: theory_grade*3/4 + laboratory_grade*1/4.