DATABASES AND WEB PROGRAMMING A - LModule DATA-BASE
Academic Year 2024/2025 - Teacher: Daniela GIORDANOExpected Learning Outcomes
The course covers the fundamental concepts of management and design of database systems. Topics include data models (ER and relational); query languages (SQL);
implementation techniques of database management systems (index structures and nd query processing); noSQL databases.
The learning objectives of the course, expressed according to the Dublin descriptors, are:
Knowledge and understanding
- To understand the main concepts of management and design of database systems
- To understand concepts and tools for querying databases of different nature (therefore with structured and unstructured data) and at different scales
- To understandi the role and the impact that data and their correct modeling may have on software applications using them
Applying knowledge and understanding
- To be able to design, starting from the requirements, a relational (and non) relational database, from a conceptual, logical and physical point of view.
- To be able to understand and use the SQL language to manage data and perform efficient queries.
Making judgements
- Analyzing functional and non-functional requirements
- Understanding the implications of design choices related to response times and alternative implementations
- Understanding the implications of design choices related to change management and maintenance of alternative software stacks
- Understanding the performance issues associated to alternative DBMS
Communication skills
- Preparing database specification documents, addressing the specificity of conceptual and logical modeling
- Explaining the motivations behind specific design choices
Learning skills
- Learning how to use the technologies for the development of databases and their scalability
- Learning how to compare alternative projects schemes from different perspectives
- Learning to recognize design principles underlying alternative modeling development strategies
Course Structure
Lectures, hands-on exercises, laboratory and seminars.
Detailed Course Content
Models and Languages for Database Management
- Fundamentals of Database Management Systems (DBMS)
- Relational databases:
- Relational Model: basic concepts, integrity* constraints and keys*.
- SQL language: data definition*, data modification*, queries*, views*, transactions*.
- Advanced SQL: triggers*, procedures* and assertions
- An example of relational DMBS: MySQL.
Design of databases
- Conceptual design: E/R model*, conceptual design strategies*, quality verification.
- Logical design: restructuring of the E / R schemes,* translation into the relational model*.
- Physical design: storage structures for tables and indices*. Hashing techniques. Query execution and optimization
- Database normalization: normal forms
* Topics to know and understand to pass the exam.
Textbook Information
1. Basi di dati - 4/ed, Modelli e linguaggi di interrogazione. Di: Paolo Atzeni, Stefano Ceri, Piero Fraternali, Stefano Paraboschi e Riccardo Torlone, Casa editrice: Mc-Graw-HIll
2. Teaching materials provided by the instructor
Course Planning
| Subjects | Text References | |
|---|---|---|
| 1 | Introduzione alle basi di dati e al modello relazionale: basi di dati e sistemi di gestione delle basi di dati, modelli dei dati, indipendenza dei dati. Il modello relazionale: relazioni, tabelle, attributi. Vincoli di integrità. | Testo 1. Capitoli 1 e 2 |
| 2 | Algebra e calcolo relazionale: operazioni sugli insiemi, ridenominazione, selezione, proiezione, join, interrogazioni. Cenni sul calcolo relazionale | Testo 1. Capitolo 3 |
| 3 | SQL concetti base: definizione dei dati, interrogazioni in SQL (operatori aggregati, raggruppamento, interrogazioni insiemistiche, interrogazioni nidifcate), Istruzioni di modifica dei dati. | Testo 1. Capitolo 4 |
| 4 | L'ambiente MySQL | Dispense del docente |
| 5 | SQL: viste, asserzioni, funzioni, stored procedures | Testo 1. Capitolo 5 |
| 6 | SQL: Trigger, transazioni e controllo degli accessi | Testo 1. Capitolo 5 |
| 7 | Progettazione delle basi di dati: metodologie, Il modello Entità- Relazione, documentazione degli schemi, raccolta dei requisiti, qualità di uno schema concettuale | Testo 1. Cap. 6 e 7, Dispense del docente |
| 8 | La progettazione logica: analisi delle prestazioni su schemi ER, ristrutturazione di schemi, traduzione nel modello relazionale | Testo 1. Capitolo 8 |
| 9 | Cenni alla normalizzazione: e ai Database NoSQL | Testo 1. Capitolo 9, dispense del docente |