| Entrambe le parti precedenti la revisioneRevisione precedenteProssima revisione | Revisione precedente |
| mds:dsd:start [25/09/2026 alle 17:00 (6 settimane fa)] – Salvatore Ruggieri | mds:dsd:start [02/11/2026 alle 18:51 (35 ore fa)] (versione attuale) – Salvatore Ruggieri |
|---|
| * **[DB]** A. Albano. [[http://fondamentidibasididati.it/wp-content/uploads/2020/11/DBEssential-2021-C30-11-21.pdf|DB Essentials]] and [[http://fondamentidibasididati.it/wp-content/uploads/2020/11/DBEssential-2020-Soluzioni-C30-11-21.pdf|solutions to exercises]], University of Pisa, 1 December 2020. This is a self-contained excerpt (in English) from the book [[http://fondamentidibasididati.it|Fondamenti di basi di dati]] (in Italian, free download). | * **[DB]** A. Albano. [[http://fondamentidibasididati.it/wp-content/uploads/2020/11/DBEssential-2021-C30-11-21.pdf|DB Essentials]] and [[http://fondamentidibasididati.it/wp-content/uploads/2020/11/DBEssential-2020-Soluzioni-C30-11-21.pdf|solutions to exercises]], University of Pisa, 1 December 2020. This is a self-contained excerpt (in English) from the book [[http://fondamentidibasididati.it|Fondamenti di basi di dati]] (in Italian, free download). |
| * Examples of {{ :mds:dsd:dsdsampleswithsolutions.pdf | written exams with solutions}} and {{ :mds:dsd:dsdsampleswithoutsolutions.pdf|written exams without solutions}}. | * Examples of {{ :mds:dsd:dsdsampleswithsolutions.pdf | written exams with solutions}} and {{ :mds:dsd:dsdsampleswithoutsolutions.pdf|written exams without solutions}}. |
| | * Example of {{ :mds:dsd:dsdmid202501.pdf | first mid-term}} and {{ :mds:dsd:dsdmid202502.pdf | second mid-term}}. |
| |
| =====Software===== | =====Software===== |
| |
| ^ Date ^ Hour ^ Room ^ Notes ^ | ^ Date ^ Hour ^ Room ^ Notes ^ |
| | 6/11/2026 | 16:00 - 18:00 | Fib-E | [[https://didattica.di.unipi.it/en/extraordinary-exam-sessions/|Reserved extra-ordinary exam]] | | | 6/11/2026 | 16:00 - 18:00 | Fib-D3 | [[https://didattica.di.unipi.it/en/extraordinary-exam-sessions/|Reserved extra-ordinary exam]] | |
| =====Class calendar ===== | =====Class calendar ===== |
| |
| |13| 22/10 14-16 | Fib-C | Multidimensional Cube model: OLAP Operations. The extended cube and the lattice of cuboids. Pivot tables in Excel. [[http://131.114.72.230:443/dsd/video/dsd13_20241017.mp4|rec13 (.mp4)]] **Additional learning material:** G. Harvey. Excel 2013 All-in-One For Dummies, 2013. [[http://131.114.72.230:443/dsd/PivotTable2013BookVIIchpt2.pdf|Chp. VII-2]] and [[http://131.114.72.230:443/dsd/HerbalTeas.xlsx|example data for pivot table]]. | **[DW: 2.3, 2.4]** {{:mds:dsd:dsd13.pdf|slides13 (.pdf)}} | | |13| 22/10 14-16 | Fib-C | Multidimensional Cube model: OLAP Operations. The extended cube and the lattice of cuboids. Pivot tables in Excel. [[http://131.114.72.230:443/dsd/video/dsd13_20241017.mp4|rec13 (.mp4)]] **Additional learning material:** G. Harvey. Excel 2013 All-in-One For Dummies, 2013. [[http://131.114.72.230:443/dsd/PivotTable2013BookVIIchpt2.pdf|Chp. VII-2]] and [[http://131.114.72.230:443/dsd/HerbalTeas.xlsx|example data for pivot table]]. | **[DW: 2.3, 2.4]** {{:mds:dsd:dsd13.pdf|slides13 (.pdf)}} | |
| |14| 23/10 9-11 | Fib-C | Recalls on: DBMS, from SQL to extended relational algebra. Exercises. {{:mds:dsd:dsd14.assignments.pdf|Exercises at home for the lesson 15}}. [[http://131.114.72.230:443/dsd/video/dsd14_20221102.mp4|rec14 (.mp4)]] | **[DB: 4.1-4.2,5.1-5.11]** {{:mds:dsd:dsd14.pdf|slides14 (.pdf)}} | | |14| 23/10 9-11 | Fib-C | Recalls on: DBMS, from SQL to extended relational algebra. Exercises. {{:mds:dsd:dsd14.assignments.pdf|Exercises at home for the lesson 15}}. [[http://131.114.72.230:443/dsd/video/dsd14_20221102.mp4|rec14 (.mp4)]] | **[DB: 4.1-4.2,5.1-5.11]** {{:mds:dsd:dsd14.pdf|slides14 (.pdf)}} | |
| | |15| 04/11 11-13 | Fib-C1 | OLAP systems. Data Analysis Using SQL. Simple reports. Examples. Moderately Difficult Reports. Solutions in SQL. {{:mds:dsd:dsd15.foodmart.pdf|Foodmart datawarehouse schema}}. [[http://131.114.72.230:443/dsd/video/dsd15_20211102.mp4|rec15 (.mp4)]] | **[DW: 5.1-5.3]** {{:mds:dsd:dsd15.pdf|slides15 (.pdf)}} | |
| | |16| 06/11 9-11 | Fib-C | Examples of variance reports. Very Difficult Reports without Analytic SQL. Example of reports with ranks. Analytic Functions with the use of partitions and running totals. Examples. {{:mds:dsd:dsd16.assignments.pdf|Exercises at home for the lesson 17}}. [[http://131.114.72.230:443/dsd/video/dsd16_20211105.mp4|rec16 (.mp4)]] | **[DW: 5.4-5.5]** {{:mds:dsd:dsd16.pdf|slides16 (.pdf)}} | |
| | |MT1| 06/11 16-18| Fib-E | First mid-term (topics up to lesson 14 included). **No need to register on esami.unipi.it** | {{ :mds:dsd:dsdmid202501.pdf | Text and solution of previous year}} | |
| | |17| 10/11 11-13 | Fib-L1 | Analytic Functions with the use of moving windows. Examples. Exercises on Analytic SQL. {{:mds:dsd:dsd17.assignments.pdf|Exercises during the lesson and at home}} and [[http://131.114.72.230:443/dsd/dsd.17.solutions.txt|solutions]]. [[http://131.114.72.230:443/dsd/video/dsd17_20211109.mp4|rec17 (.mp4)]] | **[DW: 5.5-5.6]** {{:mds:dsd:dsd17.pdf|slides17 (.pdf)}} | |
| | |18| 12/11 14-16 | Fib-C | Recalls of relational DBMS internals: Storage, Indexing and Query Evaluation. Physical operators and physical plans for projection, selection, joins and grouping. Examples. [[http://131.114.72.230:443/dsd/video/dsd18_20211112.mp4|rec18 (.mp4)]] | **[DB: 6.1-6.6, 6.8, 7.1-7.2]** {{:mds:dsd:dsd18.pdf|slides18 (.pdf)}} | |
| | |19| 13/11 9-11 | Fib-C | Data Warehouse Systems: Special-Purpose Indexes and Star Query Plan. Bitmap indexes. Join indexes. Star queries optimization and query plans. Examples. Table partitioning. [[http://131.114.72.230:443/dsd/video/dsd19_20211116.mp4|rec19 (.mp4)]] | **[DW: 6.1-6.4]** {{:mds:dsd:dsd19.pdf|slides19 (.pdf)}} | |
| | |20| 20/11 9-11 | Fib-C | The problem of materialized views selection. The lattice of views and the greedy algorithm HRU for the selection of materialized views. Examples. Other algorithms for the choice of the views to materialize with a workload and dimensional hierarchies. {{:mds:dsd:dsd20.assignments.pdf|Exercises at home for the lesson 21}}. [[http://131.114.72.230:443/dsd/video/dsd20_20211119.mp4|rec20 (.mp4)]] | **[DW: 7.1-7.7]** {{:mds:dsd:dsd20.pdf|slides20 (.pdf)}} | |
| | |24| 26/11 14-16| Fib-C | **For Italian-speaking students:** Seminar (in Italian): SadasDB: Sistema per l’analisi di dati statici di supporto alle decisioni. Speakers: V. Minei, G. Cianci, [[https://www.sadasdb.com/en/|Sadas s.r.l.]]. **For non-Italian-speaking students:** Data Warehousing trends: column-oriented DW, main-memory DW, Big Data framework: watch recorded lesson at [[http://131.114.72.230:443/dsd/video/dsd24_20211203.mp4|rec24 (.mp4)]] | **[DW: 6.5-6.8]** {{:mds:dsd:dsd24.pdf|slides24 (.pdf)}} | |
| | |21| 02/12 11-13 | Fib-C1 | Recalls of functional dependency properties and how they are used to reason about the properties of the result of a query. Properties of the group-by operator. [[http://131.114.72.230:443/dsd/video/dsd21_20221130.mp4|rec21 (.mp4)]] | **[DW: 8.1-8.2, DB: 3.5.1-3.5.4]** {{:mds:dsd:dsd21.pdf|slides21 (.pdf)}} | |
| | |22| TBD | TBD | The problem of evaluating the group-by before the join operator. First case: Invariant grouping. Examples. Other cases: double grouping, grouping and counting. Examples with star join queries. [[http://131.114.72.230:443/dsd/video/dsd22_20221201.mp4|rec22 (.mp4)]] | **[DW: 8.3-8.6]** {{:mds:dsd:dsd22.pdf|slides22 (.pdf)}} | |
| | |23| TBD | TBD | The problem of query rewrite to use a materialized view. Hypothesis and two approaches: With a compensation on the logical view plan, and with a transformation of logical query plan. Examples. [[http://131.114.72.230:443/dsd/video/dsd23_20241210.mp4|rec23 (.mp4)]] | **[DW: 9.1-9.4]** {{:mds:dsd:dsd23.pdf|slides23 (.pdf)}} | |
| | |MT2| 11/12 9-11 | Fib-D3 | Second mid-term (topics from lesson 15 on). **No need to register on esami.unipi.it** | {{ :mds:dsd:dsdmid202502.pdf | Text and solution}} | |
| |
| | Optional oral discussions will be offered during the week of 14–18 December, ahead of the January examination session, at students’ request and subject to slot availability. |
| =====Class calendar: future lessons ===== | |
| | |
| The actual dates are to be defined. The dates below are the ones of the last year. | |
| | |
| | |
| ^ # ^ Date ^ Room ^ Topic ^ Mandatory teaching material ^ | |
| |15| 17/10 9-11 | Fib-C | OLAP systems. Data Analysis Using SQL. Simple reports. Examples. Moderately Difficult Reports. Solutions in SQL. {{:mds:dsd:dsd15.foodmart.pdf|Foodmart datawarehouse schema}}. [[http://131.114.72.230:443/dsd/video/dsd15_20211102.mp4|rec15 (.mp4)]] | **[DW: 5.1-5.3]** {{:mds:dsd:dsd15.pdf|slides15 (.pdf)}} | | |
| |16| 21/10 9-11 | Fib-L1 | Examples of variance reports. Very Difficult Reports without Analytic SQL. Example of reports with ranks. Analytic Functions with the use of partitions and running totals. Examples. {{:mds:dsd:dsd16.assignments.pdf|Exercises at home for the lesson 17}}. [[http://131.114.72.230:443/dsd/video/dsd16_20211105.mp4|rec16 (.mp4)]] | **[DW: 5.4-5.5]** {{:mds:dsd:dsd16.pdf|slides16 (.pdf)}} | | |
| |17| 23/10 11-13 | Fib-C | Analytic Functions with the use of moving windows. Examples. Exercises on Analytic SQL. {{:mds:dsd:dsd17.assignments.pdf|Exercises during the lesson and at home}} and [[http://131.114.72.230:443/dsd/dsd.17.solutions.txt|solutions]]. [[http://131.114.72.230:443/dsd/video/dsd17_20211109.mp4|rec17 (.mp4)]] | **[DW: 5.5-5.6]** {{:mds:dsd:dsd17.pdf|slides17 (.pdf)}} | | |
| |18| 24/10 9-11 | Fib-C | Recalls of relational DBMS internals: Storage, Indexing and Query Evaluation. Physical operators and physical plans for projection, selection, joins and grouping. Examples. [[http://131.114.72.230:443/dsd/video/dsd18_20211112.mp4|rec18 (.mp4)]] | **[DB: 6.1-6.6, 6.8, 7.1-7.2]** {{:mds:dsd:dsd18.pdf|slides18 (.pdf)}} | | |
| |19| 28/10 9-11 | Fib-L1 | Data Warehouse Systems: Special-Purpose Indexes and Star Query Plan. Bitmap indexes. Join indexes. Star queries optimization and query plans. Examples. Table partitioning. [[http://131.114.72.230:443/dsd/video/dsd19_20211116.mp4|rec19 (.mp4)]] | **[DW: 6.1-6.4]** {{:mds:dsd:dsd19.pdf|slides19 (.pdf)}} | | |
| |20| 31/10 9-11 | Fib-C | The problem of materialized views selection. The lattice of views and the greedy algorithm HRU for the selection of materialized views. Examples. Other algorithms for the choice of the views to materialize with a workload and dimensional hierarchies. {{:mds:dsd:dsd20.assignments.pdf|Exercises at home for the lesson 21}}. [[http://131.114.72.230:443/dsd/video/dsd20_20211119.mp4|rec20 (.mp4)]] | **[DW: 7.1-7.7]** {{:mds:dsd:dsd20.pdf|slides20 (.pdf)}} | | |
| |21| 06/11 11-13 | Fib-C | Recalls of functional dependency properties and how they are used to reason about the properties of the result of a query. Properties of the group-by operator. [[http://131.114.72.230:443/dsd/video/dsd21_20221130.mp4|rec21 (.mp4)]] | **[DW: 8.1-8.2, DB: 3.5.1-3.5.4]** {{:mds:dsd:dsd21.pdf|slides21 (.pdf)}} | | |
| |22| 07/11 9-11 | Fib-C | The problem of evaluating the group-by before the join operator. First case: Invariant grouping. Examples. Other cases: double grouping, grouping and counting. Examples with star join queries. [[http://131.114.72.230:443/dsd/video/dsd22_20221201.mp4|rec22 (.mp4)]] | **[DW: 8.3-8.6]** {{:mds:dsd:dsd22.pdf|slides22 (.pdf)}} | | |
| |MT1| 07/11 16-18| Fib-D1 | First mid-term (topics up to lesson 17 included). **No need to register on esami.unipi.it** | {{ :mds:dsd:dsdmid202501.pdf | Text and solution}} | | |
| |23| 12/11 11-13 | Fib-L1 | The problem of query rewrite to use a materialized view. Hypothesis and two approaches: With a compensation on the logical view plan, and with a transformation of logical query plan. Examples. [[http://131.114.72.230:443/dsd/video/dsd23_20241210.mp4|rec23 (.mp4)]] | **[DW: 9.1-9.4]** {{:mds:dsd:dsd23.pdf|slides23 (.pdf)}} | | |
| |24| 4/12 11-13| Fib-C | **For Italian-speaking students:** Seminar (in Italian): SadasDB: Sistema per l’analisi di dati statici di supporto alle decisioni. Speaker: V. Minei, [[https://www.sadasdb.com/en/|Sadas s.r.l.]]. **For non-Italian-speaking students:** Data Warehousing trends: column-oriented DW, main-memory DW, Big Data framework: watch recorded lesson at [[http://131.114.72.230:443/dsd/video/dsd24_20211203.mp4|rec24 (.mp4)]] | **[DW: 6.5-6.8]** {{:mds:dsd:dsd24.pdf|slides24 (.pdf)}} | | |
| |MT2| 16/12 9-11 | Fib-E | Second mid-term (all topics). **No need to register on esami.unipi.it** | {{ :mds:dsd:dsdmid202502.pdf | Text and solution}} | | |
| |
| =====Previous years===== | =====Previous years===== |
