DATABASES AND WEB PROGRAMMING M - ZModule DATA-BASE
Academic Year 2023/2024 - Teacher: CONCETTO SPAMPINATOExpected 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.
- To be able to create and query unstructured databases (NoSQL) by manipulating JSON documents
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 modellation
- Explaining the motivations behind specific design choices
Learning skills
- Learning how to use the tecnologies for the development of databases and their scalability
- Learning how to compare alternative projects schemese from different perspectives
- Learning to recognize design principles underlying alternative modellation development strategies
Course Structure
Lectures, hands-on exercises, laboratory and seminars.
Should teaching be carried out in mixed mode or remotely, it may be necessary to introduce changes with respect to previous statements, in line with the programme planned and outlined in the syllabus.
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.
- Database NoSQL:
- JSON*
- ACID vs BASE*
- "Scheme-less"* models
- NoSQL DB types*: key-value, document-based, column-based and graph-based
- CAP Theorem*
- Overview of NoSQL frameworks: MongoDB, Redis, Cassandra, HBase, Neo4j
- MongoDB*: Selectors, Search, Sort, Pipeline, Grouping
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*.
* 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 | Database NoSQL: Proprietà ACIDE vs BASE, Tipi di database NoSQL, Teorema CAP, | Dispense del docente |
10 | MongoDB: Selettori, Ricerca, Ordinamento, Pipeline, Raggrumenti e Aggregazione | Dispense del docente |