Architecture of databases management systems. The Entity Relationship model. The relational model. Relational algebra: operators and queries. Analysis and transformation of an ER schema into a relational schema. Functional dependencies, normal forms, decomposition schemes. Introduction to concurrency control, reliability and log. The SQL language: basic concepts and advanced features. MySQL, a real databases management system.
- P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone, Basi di dati, McGraw-Hill, 2018, quinta edizione.
- P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone, Basi di dati - Modelli e linguaggi di interrogazione, McGraw-Hill, 2013, quarta edizione.
- Reference Manual for MySQL, http://www.mysql.com
- Other material available on the web site.
Learning Objectives
Knowledge: the student acquires knowledge about the relational model, relational algebra and ER design methodology, in particular, learning to transform an ER diagram into a relational schema. Also captures the basic concepts and most advanced features of SQL learning how to translate the relational schema, and related query, in MySQL.
Acquired skills: the student will acquire the fundamental skills to design and analyse a relational database and to use it by means of a real databases management system, such as MySQL. Relational databases are the most used and are also of primary importance for the definition of concepts and methodologies developed further in subsequent proposals.
Capacity acquired at the end of the course: the student acquires the ability to design a database that meets the requirements of a specific application, accompanied by a critical assessment of the characteristics and performance. Additionally, the student will have the ability to critically evaluate databases already designed and / or implemented, and, if necessary, propose modifications in order to improve its structure. Finally, thanks to the skills acquired on the languages and databases systems, the student will be able to implement various aspects of applications that make intensive use of large amounts of data.
Prerequisites
Courses required: Programming
Teaching Methods
CFU: 9
Total number of hours of the course: 225
Number of hours for personal study and other individual learning activities: 153
Number of hours on the classroom activity: 72
Further information
Frequency of lessons and practice: recommended
Tools to support teaching UniFi E-Learning: http://el.unifi.it
Office hours: by appointment via e-mail.
Department of Statistics, Computer Science, Applications
Viale Morgagni, 65
50134 - Florence (FI)
Tel: 055 2751509
E-Mail: donatella.merlini@unifi.it
Type of Assessment
The exam consists of the following activities:
- a group project (3 people maximum, even one-person groups are fine in case of difficulty in forming a group) to be delivered via the Moodle platform in a .zip or .rar archive, according to the deadlines shown on the site of the course. The project consists in the creation of a database starting from the conceptual design phase, which must be followed by the logical design phase and the implementation in MySQL. The context of the databases is free choice. The groups will have to draw up a pdf report (maximum 15 pages) with the description of the database design phases and a sql script with its implementation in MySQL, organized according to a template available on the course website. The script must include a part of DDL instructions for creating and populating the database, with integrity constraints, and a part of DML instructions for querying the database through select instructions and the use of procedures and functions. Views and triggers should also be used to show the management of further constraints on the database. The project will be evaluated taking into account the form and contents of the report as well as the DDL and DML aspects of the script and will weigh 3 cfu on the final evaluation. The project must be sent before the oral exam, even in a session prior to the one in which the oral exam is taken and when passed it is valid for the entire academic year. During the year, intermediate tests may be be carried out in the laboratory, the passing of which may replace the project.
- an oral exam with questions on the whole program, exercises to carry out and brief discussion of the project.
The exercises will serve to evaluate 1) the understanding of the concepts seen in class related to conceptual and logical design, relational model and algebra, normalization and transactions 2) the ability to design and analyze a relational database, 3) the ability to apply the knowledge acquired for the design of a database that meets the required requirements, accompanying it with a critical evaluation of the characteristics and performance. The oral test will assess the knowledge of the topics presented, the acquisition of the technical language appropriate to the context, the ability to relate different topics of the program to each other. The oral exam will weigh 6 cfu on the final evaluation.
Course program
Architecture of databases management systems. The Entity Relationship model. The relational model. Relational algebra: operators and queries. Analysis and transformation of an ER schema into a relational schema. Functional dependencies, normal forms, decomposition schemes. 3NF and BCNF normalization. Introduction to concurrency control, reliability and log. The SQL language: basic concepts and advanced features. MySQL, a real databases management system.