Summary INFOMDWR Data Wrangling and Data Analysis 2022/2023

Lieve Göbbels
Data Wrangling & Data Analysis
Semester 1, 2022-2023




Data Wrangling and Data Analysis
Week 1: Data Collection and Extraction 2
Introduction to Databases 2
Introduction to the Relational Model 5
Introduction to SQL 7
Database Design Using the E-R Model 11
Week 2: Advanced SQL and the Relational Model 13
The Relational Model 13
Advanced SQL 14
Schema Re nement 14
Finding Similar Items 17
Week 3: Preprocessing 23
Data Preprocessing 23
Outlier Detection 29
Week 4: Visualization and EDA 36
Data Transformation with dplyr 36
EDA 38
Tibbles with tibble 39
Week 5: Supervised Learning 40
Statistical Learning 40
Linear Regression 42
Classi cation 47
Resampling methods 49
Week 6: Missing Data & Clustering 51
Missing Data - Introduction 51
Unsupervised learning 55
Week 7: Clustering and Text mining 59
Mixture Models 59
Speech and Language Processing 60
Text Mining with R 65
Week 8: Advanced Topics 68
Time Series 68
Mining Data Streams 71
Handling Imbalanced Data 78

, Week 1: Data Collection and Extraction
In short:
• Silberschatz et al. (2020) - Introduction
• Silberschatz et al. (2020) - Introduction to the Relational Model
• Silberschatz et al. (2020) - Introduction to SQL & Join Expressions
• Silberschatz et al. (2020) - Database Design Using the E-R Model


Introduction to Databases
Database-System Applications
This chapter introduces the principles of database systems. A DBMS is a collection of interrelated
data and a set of programs to access those data. Database systems are used to manage collections
of data that 1) are highly valuable, 2) are relatively large, and 3) are (simultaneously) accessed by
multiple users and applications.
Modern database systems exploit commonalities in the structure of data to gain e ciency
but also allow for weakly structured data and for data whose formats are highly variable. The key to
complexity management is the concept of abstraction. As database systems become more
sophisticated, better languages have been developed so that programmers can interact with the
data, along with user interfaces that allow end users within the enterprise to query and update data.
In general, there are two usage modes of databases:
1. support online transaction processing (a large number of users retrieving small parts of data and
performing small updates);
2. support data analytics (processing data to draw conclusions, etc.).

The eld of data mining combines knowledge-discovery techniques invented by AI and statistics
researchers with e cient implementation techniques, which allows the use of extremely large
datasets.

Purpose of Database Systems and View of Data
The classical le-processing system, supported by a conventional operating system, has several
major disadvantages:
• data redundancy and inconsistency; • atomicity problems;
• data accessing di culties; • concurrent-access anomalies;
• data isolation; • security problems.
• integrity problems;

A major purpose of a database system is to provide users with an abstract view of the data.
Underlying the database structure is the data model, which can be de ned as a collection of
conceptual tools to describe data, its relationships, its semantics, and consistency constraints. Data
models are generally divided into four categories:
1. relational model (collection of tables);
2. entity-relationship model;
3. semi-structured data model (individual data items of the same type have di erent sets of
attributes, e.g. JSON/XML);
4. object-based data model (currently integrated into relational databases).