DataBase and Data Mining Group

Table of content

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

General information

• ECTS: 8
• Professors: Silvia Chiusano, Daniele Apiletti, Marco Torchiano
• Assistant lecturers: Eliana Pastor, Moreno La Quatra, Diego Monti, Martino Trevisan
• Exam rules valid for the traditional exam only, with students physically present.
• New exam rules valid for the remote online exam due to Covid emergency (June-July-September 2020)

Exams

• Due to the Covid emergency, the exam of the summer session will be executed remotely on the campus-wide Respondus-Exam on-line platform (see official documentation).
  The exercises will be as similar as possible to the traditional exam.
  More details and specific exam rules are available here.
• Jan 31st, 2020
  • results
  • DW and NoSQL text + solution
  • DVis text + solution (updated on Feb 11, 2020)
  • alternative Tableau solutions
• Feb 14th, 2020
  • results (updated March 18th, 2020)
  • DW and NoSQL text + solution
  • DVis text + solution
• June 18th, 2020
  • Exam text + solution
  • Dvis Tableau solution

Material

Data Warehousing

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

Non-relational Databases

• NoSQL introduction and document-based DBMS (slides)
• MongoDB intro and query (slides)
• MongoDB operations (slides)
• Map Reduce (slides)
• Distributed transactions (slides)
• A NoSQL transaction design recipe (slides)
• Distributed Data Management – replication, CAP, and conflicts (slides)
• NoSQL design (slides)
• NoSQL design – Part 2 (slides)
• NoSQL design – Part 3 (slides)

Data Visualization

• Data Visualization (slides)
• Visual perception (slides)
• Graph Construction (slides)
• Data Quality (slides)
• Examples:
  • Basic use of Tableau (zip)
  • Use of Tableau (Lab 6) (zip)
  • Geographical data in Tableau (zip)

Laboratory practice

Practice in Lab starts on Wednesday, October 23rd, 2019. There will be NO practice before.

Since Oct 23rd, all Wednesdays from 13:00 to 14:30 at Labinf there will be a new practice with assistant lecturers.

Students must own an active account to access the Labinf (see registration instructions on Labinf website).

Lab 1: Extended SQL

• • Text (pdf)

• • Data warehouse tables in csv format (zip)
  •        FOR STUDENTS WHO WANT TO PRACTICE AT HOME WITH EXTENDED SQL:
    • to import tables using Oracle SQL Developer: Import data (right click on “Tables” of “Connection” Tab)
    • (alternatively) to import tables from the Oracle XE Web interface: select Home>Utilities>Data Load/Unload>Load>Load Text Data->Upload file (comma separated or tab delimited))

• Installing Oracle 11g Express Edition at home
  • To download and install Oracle Express Edition
    • Oracle XE home page
    • Oracle XE download
    • Installation Guide for Windows
    • Installation Guide for Linux
    • Tutorial
• LABINF – Oracle SQL Developer English language: RUN (cmd), “%APPDATA%\sqldeveloper“, folder of the Oracle version (19.1.0), Add “addVMOption -Duser.language=en” in the product.conf file

• 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 (pdf)

Lab 5: NoSQL in MongoDB

• • Text (pdf)
  • Collection (txt)
  • Docker (file) note: yml/yaml extension
  • Solution (pdf)

Lab 6: Visualization analysis

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

Lab 7: Redesign with Tableau

• • Text (pdf)
  • Solution (zip)

Lab 8: Visualization of a dataset

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

Lab 9: Intervals and dashboards

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

Lab 10: Geographic roles and maps

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

Lab 11: Exam simulation

• • Text (pdf)
  • Graph (jpg)
  • Solution (zip)