[Practical Exercise] Data Storage and Analysis: Storing Scraped Data into MySQL and Performing Data Cleaning and Preprocessing

# Practical Exercise: Data Storage and Analysis - Storing Scraped Data in MySQL and Data Cleaning and Preprocessing

## 2.1 Introduction to MySQL Databases

### 2.1.1 Basic Concepts and Terminology of MySQL

MySQL is a relational database management system (RDBMS) that stores data and manages it through defined relationships. Here are some basic concepts and terminology:

- **Database:** A collection of one or more tables that store data about a specific subject.
- **Table:** A collection of rows that represent individual data records.
- **Row:** A data record consisting of multiple columns.
- **Column:** A field containing data of a specific type.
- **Primary Key:** A column or combination of columns that uniquely identifies each row in a table.
- **Foreign Key:** A column that references the primary key in another table to establish relationships between tables.

## 2. Practical Data Storage Practices

### 2.1 Introduction to MySQL Databases

#### 2.1.1 Basic Concepts and Terminology of MySQL

MySQL is a popular relational database management system (RDBMS) that stores and manages data based on SQL (Structured Query Language). The basic concepts of MySQL include:

- **Database:** A database contains one or more tables for storing related data.
- **Table:** A table consists of rows and columns, where a row represents a single data record, and columns represent data fields.
- **Field:** A field represents a specific attribute or characteristic of a data table, such as name, age, or address.
- **Primary Key:** A primary key is a unique identifier for a table, used to uniquely identify each record.
- **Foreign Key:** A foreign key is a column that associates a record in one table with a record in another table.
- **Index:** An index is a data structure used for fast data retrieval within a table, thereby improving query performance.

#### 2.1.2 Installation and Configuration of MySQL

**Installing MySQL**

- Download the MySQL installer and run it.
- Follow the wizard's instructions, choosing the installation path and configuration options.
- Set a password for the root user.

**Configuring MySQL**

- Open the MySQL command-line interface (CLI).
- Use the following command to connect to MySQL:

mysql -u root -p

- Enter the root user password.
- Use the following command to create a database:

CREATE DATABASE my_database;

- Use the following command to switch to the newly created database:

USE my_database;

### 2.2 Data Import and Export

#### 2.2.1 Methods and Tips for Data Import

**Importing Data Using the Command-Line Interface (CLI)**

LOAD DATA INFILE 'data.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

**Importing Data Using MySQL Workbench**

- Right-click on the table where you want to import data.
- Select "Import Data."
- Choose the data source file and configure the import options.

**Optimizing Data Import**

- Use batch insert statements (INSERT INTO...VALUES...).
- Disable foreign key constraints (SET FOREIGN_KEY_CHECKS=0).
- Optimize data file formats (e.g., use CSV instead of Excel).

#### 2.2.2 Formats and Options for Data Export

**Exporting to CSV Format**

SELECT * FROM my_table INTO OUTFILE 'data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

**Exporting to JSON Format**

SELECT * FROM my_table INTO OUTFILE 'data.json'
FORMAT JSON;

**Exporting to XML Format**

SELECT * FROM my_table INTO OUTFILE 'data.xml'
FORMAT XML;

**Optimizing Data Export**

- Use parallel export (e.g., using mysqldump --parallel).
- Compress the export file (e.g., using mysqldump --compress).
- Filter the exported data (e.g., using mysqldump --where).

# 3.1 Overview of Data Cleaning

#### 3.1.1 Necessity and Purpose of Data Cleaning

Data cleaning is a crucial step in the data analysis process aimed at addressing errors, inconsistencies, and missing values in the data. These issues can severely impact subsequent data analysis and modeling, leading to incorrect conclusions and decisions.

The necessity for data cleaning is reflected in several aspects:

- **Low data quality:** Raw data may contain various errors, such as input errors, data type err