70155 - Data Bases

Learning outcomes

At the end of the course the student: - knows the relational data model and the SQL language; - is able to design and develop a relational database; - is capable of processing a project to implement an information system.

Course contents

The course program is divided into four parts, each addressing complementary topics related to databases and information systems.

In the first part, the fundamental concepts of the course are introduced (data, information, database, etc.), along with the characteristics of the relational model and relational DBMSs, and the main functionalities of languages used for performing CRUD operations on data. In this context, the features of the SQL language for creating, modifying, and querying relational databases are explained in detail. Additionally, there are brief discussions on alternative approaches to the relational model (NoSQL databases) and technologies for big data management.

The second part presents methodologies and tools for designing databases based on the analysis of system requirements. This includes a detailed discussion of the traditional phases of software engineering applied to data design: requirements analysis, conceptual design, logical design, physical design, performance analysis of data models, and normalization techniques.

The third part provides a (brief) overview of data mining techniques and algorithms based on supervised and unsupervised learning methods, aimed at creating classification and prediction models from the data stored in a database. Examples of applications of these techniques in industrial and business contexts are also provided.

Finally, the fourth part introduces the main features of Web Information Systems (WIS), and presents some technologies used for the development of web applications that can interface with SQL databases.

Summary of course topics:

Course Introduction: Information, Data, and DBMS

Models and Languages for Databases

• The relational model: basic concepts, integrity constraints, and keys

• Features of relational Database Management Systems (DBMSs)

• The SQL language: data definition, data modification, queries, view definitions, transactions

• Alternative approaches to the relational model, brief overview of NoSQL and big data technologies

Database Design

• Conceptual design: the E/R model, requirements gathering and analysis, conceptual design strategies, quality assessment

• Logical design: restructuring E/R schemas, translation into the relational model, quality analysis of E/R schemas

• Normalization techniques: normal forms (Boyce-Codd, third normal form), decomposition into normal form

• Physical design: indexes and data storage techniques

Introduction to Data Mining

• General concepts and application scenarios of data mining techniques

• Classification and clustering algorithms

DBMS for Applications

• Examples of DBMSs: MySQL, MongoDB, Cassandra

• SQL and server-side scripting languages (PHP + MySQL)

Readings/Bibliography

The reference textbook of the course is the following:

- Paolo Atzeni, Stefano Ceri, Stefano Paraboschi, Riccardo Torlone, "Basi di dati" 5/ed, 2018, ISBN: 9788838694455

Moreover, these books are recommended for the project development:

- Kevin Yank, "Sviluppare applicazioni con PHP e MySQL" 1/ed, 2012, ISBN: 9788850331826

Teaching methods

The course includes in-person lectures held in the classroom, with the use of a computer and projector by the instructor.
SQL language exercises are conducted in class using the CodeRunner tool integrated within the Virtuale platform.

Assessment methods

The course includes a written exam (mandatory), a project (mandatory), and an oral exam (optional).

The written exam lasts 2.5 hours; no books or notes are allowed. Students must register in advance via the ALMA-ESAMI website. The exam consists of exercises covering the various parts of the course and is aimed at assessing the achievement of the learning objectives related to: knowledge of relational database concepts, query languages, and database design. The grade of the written exam, expressed on a scale of 30, accounts for 2/3 of the final grade.

The project involves the implementation of an information system with a web interface, and is intended to assess the student’s ability to design and implement databases using the software tools, technologies, and methodologies presented in class. The project accounts for 1/3 of the final grade. The instructor provides the project topics (which can also be proposed by students, subject to prior discussion with the instructor), as well as the delivery guidelines and deadlines, through the course web page.

The oral exam is optional and includes questions and exercises on the topics covered in the course.

Optional bonus exercises may be assigned during the course as take-home work. Submission of these exercises may provide a bonus of up to one point, which is added to the average of the previous components (written exam/project/oral exam).

Teaching tools

The teaching materials (slides, code) presented during lectures are made available to students through the VIRTUALE platform.
The course dashboard on VIRTUALE also includes:
– links to software tools that can be used for the project, and
– references to information sources (e.g., forums, online journals) related to the course topics.

Office hours

See the website of Marco Di Felice