DataBase and Data Mining Group

Data management and visualization (2020/2021)

Table of content

• General information
• Material
  • Data Warehousing
  • Non-relational Databases
  • Data Visualization
• Exercises
• Laboratory practices

General information

• ECTS: 8
• Professors: Daniele Apiletti, Silvia Chiusano, Diego Monti
• Assistant lecturers: Eliana Pastor,  Alessandro Fiori, Diego Monti

Announcements

• [15-10-2020] The first laboratory will be held on October 21 and October 22, 2020 on the Virtual Classroom.
  • LAB SCHEDULE.
    TEAM A (FROM A TO G) on Wednesday from 1 pm to 2:30 pm
    TEAM B (FROM H TO Z) on Thursday from 16pm to 17.30pm

Exams

• Exam rules

• February 1st, 2021
  • Text + DW and NOSQL solutions (pdf)
  • Data visualization solutions (pdf)
• February 15, 2021
  • Text + DW and NOSQL solutions (pdf)
  • Data visualization solutions (pdf)
• June 17, 2021
  • Text + DW and NOSQL solutions (pdf)
  • Data visualization solution (pdf)
• September 1st, 2021
  • Text + DW and NOSQL solutions (pdf)
  • Data visualization solutions (pdf)

Material

• Course introduction (slides) – slightly updated on Tuesday, September 29, 2020 at 12:20 CEST

Data Warehousing

• Data warehouse: introduction (slides)
• Data warehouse: conceptual and logical design (slides)
• Data warehouse: analysis (slides)
• Data warehouse: materialized view (slides)
• Data warehouse: ETL process (slides)
• Data warehouse: Extended SQL in Oracle (slides)
• Data warehouse: physical design (slides)

Data Warehousing exercises

Non-relational Databases

• NoSQL introduction and document-based DBMS (slides)
• MongoDB intro and query (slides)
• MongoDB operations (slides)
• MongoDB query recap (slides)
• Distributed Data Management – replication, CAP, and conflicts (slides)
• Distributed transactions (slides)
• MongoDB replication (slides)
• MongoDB aggregation pipeline with examples (slides) – updated on Nov 19, 2020
• MongoDB query exercises (slides) – updated on Nov 19, 2020
• Map Reduce (slides)
• A NoSQL design recipe (slides)
• NoSQL design patterns, part 1 (slides)
• NoSQL design patterns, part 2 (slides)

Data Visualization

• What is Visualization (slides)
• Visual perception (slides)
• Graph construction (slides)
• Data Quality (slides)
• Past exams (pdf) [Updated]

Exercises

Collection of exercises from practices and past exams. For doubts, question or reporting, use Piazza or write to eliana.pastor@polito.it

Data Warehousing

• Vehicle rental (Text, draft solution) [Updated 30/12]
• Versioning services(text, draft solution)
• Craft beer (text, draft solution) [Updated]
• Tickets (text, draft solution)
• Food delivery (text, draft solution) [Updated]

NoSQL

• MongoDB design pattern exercises (text, solutions) – updated 2021-01-21
• MongoDB query exercises (text, solutions) – updated 2021-01-21

Laboratory practices

The laboratories will start next week (21 and 22 October). You are divided into two teams by the initial letter of your surname.
– Team A: surname in the range [A-G]
– Team B: surname in the range [H-Z]

We will have two laboratory sessions. Each team is assigned to one session as follows:
– Session 1 on Wednesday at 1-2:30 PM for Team A*
– Session 2 on Thursday at 4-5:30 PM for Team B

During this semester, the laboratories will be entirely virtual on Virtual Classroom. As such, we will record and publish each session.
During the laboratories, Piazza will be used as a support for Live Question&Answer. In case of pre-enroll students, send an email to eliana.pastor@polito.it to ask for joining.

Exception: January 6 there will be no lesson for Team A, it will delayed on Thursday 7, as follows
January 7 Thursday at 4-5:30 PM for Team B
January 7 Thursday at 5:30-7 PM for Team A*

Lab 1: Extended SQL

• Text (pdf)
• Data warehouse tables in csv format (zip)
• Import Database and Tables
  • Tutorial
• Installing Oracle Database 18c Express Edition and SQL Developer
  • To download and install Oracle Express Edition: home page
  • To download and install SQL Developer: home page
  • Tutorial
    • Installation Guide for Windows
    • Installation Guide for Ubuntu
    • Installation Guide for Mac OS
  • If you have problems with the installation, please use Piazza (folder oraclexesql)
• Draft solution of queries 1-5 and materialized view (pdf) and DW design

Lab 2: Extended SQL

• Text – Additional queries   (pdf)

Lab 3: Data-warehouse analytics and reporting with Google Data Studio

• Text (pdf)

Lab 4: NoSQL in MongoDB Compass

• Text (pdf)
• Solution

Lab 5: Create a replica set and query a MongoDB database

• Text (pdf)
• database dump (restaurants_collection)
• docker file (docker-compose.yml)
• Solution

Lab 6: Visualization analysis

• Text (pdf)
• Data (xlsx)
• Solution

Lab 7: Redesign with Tableu

• Text (pdf)
• Solution

Lab 8: Visualization of a dataset

• Text (pdf)
• Solution

Lab 9: Intervals and dashboards

• Text (pdf)
• Dataset (zip)
• Solution

Lab 10: Geographic roles and maps

• Text (pdf)
• Dataset (zip)
• Solution

Lab 11: Exam simulation Dataviz

• Solution (pdf, zip)