Decision Support Databases
Graduate Program in Data Science and Business Informatics, Academic Year 2017-2018, first semester
- Lessons will be held at: Polo Didattico "L. Fibonacci", Via F. Buonarroti 4, Pisa.
- Monday, Room C1, hours: 14:00-16:00
- Friday, Room C1, hours: 16:00-18:00
- Office hours: Salvatore Ruggieri. Tuesday 14-17, Room 321/DO, Dept. of Computer Science. Telephone +39-050-2212782.
- Preliminary program and
calendar of lessons.
- Audio-video records of lessons can be found here.
- Regular exam sessions: the exam consists of a written test and an oral test (there are no mid-terms). Dates of written tests (registration is mandatory):
Compulsory course material
The teaching material is freely accessible from the unipi.it domain. Outside it is password-protected (ask the teacher for credentials).
- [DW] A. Albano, S. Ruggieri. Decision Support Databases Essentials, University of Pisa, 2017.
- [DWSol] Decision Support Databases Essentials: Solutions to Case Studies, University of Pisa, 2017.
- [DB] A. Albano. Databases Essentials, University of Pisa, 2016.
- Examples of written exams with solutions.
Optional (but recommended) readings
- [MPDB] S. Babu, H. Herodotou. Massively Parallel Databases and MapReduce Systems, Foundations and Trends in Databases: Vol. 5: No. 1, pp 1-104, 2013.
- Monday 18 September 2017, 14-16 [DW: 1.1-1.2]
Course overview. Need for Strategic Information. Information Systems in Organizations: Operational and Decision support. Data driven Decision support systems and Business Intelligence applications. From data to information for decision making. Types of data synthesis: Reports, Multidimensional data analysis, Exploratory data analysis.
- Friday 22 September 2017, 16-18 [DW: 1.3-1.7]
The data warehouse (DW) and DW architectures. What to model in a DW: Facts, measures, dimensions and dimensional hierarchies. Examples of data analysis. Exercises on data analysis in SQL.
- Monday 25 September 2017, 14-16 [DB: 1.1,2.1-2.5]
Recalls: the Object Data Model.
- Friday 29 September 2017, 16-18 [DW: 2.1]
DW modeling. A conceptual multidimensional data model. Representation of Fact, measures, dimensions, attributes and dimensional hierarchies. Key steps in conceptual design from business questions. How to identify Fact types and fact granularity and measure types. How to identify dimensions, dimensional attributes and hierarchies. Examples.
Slides: university requirements.
- Monday 2 October 2017, 14-16 [DW: 2.1, A.1]
The example of a data model for Master program exams. Presentation and discussion of the Hospital case study.
Friday 6 October 2017, 16-18
LESSON CANCELED TO ALLOW PARTICIPATION TO THE INTERNET FESTIVAL. IT WILL BE RECOVERED ON 17TH NOVEMBER.
- Monday 9 October 2017, 14-16 [DB: 3.1-3.2]
Recalls: the relational model and relational algebra. Exercises.
- Friday 13 October 2017, 16-18 [DW: 2.1,2.2,A.1]
More about data mart conceptual design, changing dimensions and advanced data model features. From Conceptual design to relational logical design. Star model, snowflake, and constellation. Logical schema of the Hospital case study.
- Monday 16 October 2017, 14-16 [DB: 3.2-3.3]
Recalls: the relational model and relational algebra. Logical trees. Exercises.
- Friday 20 October 2017, 16-18 [DW: 2.3,2.4]
Multidimensional Cube model: OLAP Operations. The extended cube and the lattice of cuboids. Pivot tables in Excel. PowerPivot.
Additional learning material:
- Monday 23 October 2017, 14-16 [DW: A.2,3.1-3.5]
Discussion of students' solutions of conceptual and logical design case studies: The airline companies. A Data Warehouse Design Methodology. Approaches. Design phases. Requirements specifications.
- Friday 27 October 2017, 16-18 [DW: 3.1-3.5]
Data mart logical design. Slowly changing dimensions, fast changing dimensions, shared dimensions. Recursive hierarchies. Multivalued dimensions. Multivalued Dimensional Attributes.
- Monday 6 November 2017, 14-16 [DB: 3.4,4.1-4.2,5.1-5.11]
ODM-to-Relational Mapping. Recalls on: DBMS, from SQL to extended relational algebra. Exercises.
Software: JRS (Java Relational System) DBMS.
- Friday 10 November 2017, 16-18 [DW: 4.1-4.8]
A DW to support Analytical CRM Analysis.
- Monday 13 November 2017, 14-16 [DW: 5.1-5.4]
OLAP systems. Data Analysis Using SQL. Simple reports. Examples. Moderately Difficult Reports. Examples of variance reports. Solutions in SQL.
- Recover lesson Friday 17 November 2017, 14-16, Room C1 [DW: 5.5-5.6]
Very Difficult Reports without Analytic SQL. Example of reports with ranks. Analytic Functions with the use of partitions and running totals. Examples. Analytic Functions with the use of moving windows. Examples.
Software: SQL Server 2016 Management Studio (Win), or SQL Ectron GUI (Win, Linx, Mac).
- Friday 17 November 2017, 16-18 [DB: 6.1-6.6, 6.8, 7.1-7.2]
Recalls of relational DBMS internals: Storage, Indexing and Query Evaluation. Physical operators and physical plans for projection, selection, joins and grouping. Examples.
- Monday 20 November 2017, 14-16 [DW: 6.1-6.8], [MPDB]
Data Warehouse Systems: Special-Purpose Indexes and Star Query Plan. Bitmap indexes. Join indexes. Star queries optimization and query plans. Examples of query plans for star queries. Column-Oriented Data Warehouse Systems.
- Friday 24 November 2017, 16-18 [DW: 7.1-7.7]
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.
- Monday 27 November 2017, 14-16 [DW: 8.1-8.2, DB: 3.5.1-3.5.4]
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.
- Friday 1 December 2017, 16-18 [DW: 8.3-8.6]
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 queries.
- Monday 4 December 2017, 14-16 [DW: 9.1-9.4]
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.
Monday 11 December 2017, 14-16
LESSON CANCELED DUE TO ADVERSE WEATHER CONDITIONS.
Recover lesson Monday 11 December 2017, 16-18, Room C1
LESSON CANCELED DUE TO ADVERSE WEATHER CONDITIONS.
- Friday 15 December 2017, 16-18
DW trends. Examples of written exams with solutions. Q. & A.