Databases

An Introduction

8 February 2024

克明峻德，格物致知
 For 10-20 years,
spreadsheets is a Q: It’s working, but why
back-bone IT system databases? Where is the
for the business. bottleneck?

In fact, they are still A: Scalibilities. Business with

being used for high volume won’t be possible
internal housekeeping without a database.
and external data Think (also): ‘Last-Mile Deliveries’.
exchange.

Excel is Microsoft’s best consumer product.

Satya Nadella, Microsoft CEO

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 2

Course Overview

Focus Areas:
Relational Databases
§ In-Depth SQL Queries
§An Introduction
Principles of Database Design
§ ETL Techniques

Tools:
§ MySQL, Google Colaboratory, DBDiagram, ERDPlus

Learning Outcomes:
§ Mastery in SQL Query Processing and Effective Data Management
§ Skills in Scalable Database Design and Optimisation

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 3

What is Database?

§Relational
Definition:Databases
A Database is a structured collection of data, stored electronically for efficient access and
management.
§An Introduction
Role: Essential in a wide range of applications, from web services to complex data analytics.
§ Data Types: Diverse, including text, numbers, multimedia.

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 4

Database Management Systems (DBMS)

Key Functions
Relational and Role:
Databases
§ Definition: DBMS is software that facilitates efficient data management in databases.
§An Introduction
Primary Functions:
§ Data Organization and Security: Organizing data for efficient access and safeguarding against unauthorized access.
§ Maintenance and Accessibility: Ensuring data integrity through backup and recovery, and supporting multi-user access.
§ Query Processing: Executing data retrieval and manipulation.

DBMS Examples:
§ Commercial: Oracle, IBM DB2, Microsoft SQL Server.
§ Open-Source: MySQL, PostgreSQL.

Importance in Data Management:

§ DBMSs are essential for secure, efficient, and reliable data handling in diverse applications.

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 5

The Data Model Concept

Overview:
Relational Databases
§ Data models are blueprints for structuring and managing data in a database.
An Introduction

Key Elements:
§ Schema: Outlines the structure of the entire database, including table designs and relationships.
§ Example: Schema for a library database with tables for 'Books', 'Members', and 'Borrow'.
§ Linking Tables: 'Borrow' connects 'Books' and 'Members', mapping book borrowings to members via BookID and MemberID.
§ Instance: Actual data stored in the database at any given time.
§ Query Language (SQL): Used for data retrieval and manipulation.
§ SQL Example:

SELECT [Link], [Link]

FROM Books B
INNER JOIN Borrow Br ON [Link] = [Link]
INNER JOIN Members M ON [Link] = [Link]
WHERE [Link] = 'Technology';

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 6

Relational Data Model

Understanding the Relational Data Model:

Relational Databases
§ The relational data model is a framework for organizing data in tables (rows and columns).
An Introduction
Key Characteristics:
§ Set Semantics: Ensures uniqueness and no duplication in rows.
§ Typed and Static Attributes: Each column has a defined data type for consistency and integrity.
§ Flat Table Structure: Non-hierarchical, straightforward data organization.

Schema Example with Data:

§ Books Table Schema:
§ Columns: BookID (INT), Title (VARCHAR), Author (VARCHAR), Genre (VARCHAR), PublishedYear (INT).
§ Example Data:
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 7

Detailed Exploration of Relational Model Structure

Table Structure:
Relational Databases
§ Tables (Relations): Represent collections of related data entries.
§ Columns (Attributes): Define the data's properties, like name, type, and category.
An Introduction
§ Rows (Tuples): Each row indicates a single record or data entry.

Example: Books Table:

§ Schema: BookID (INT), Title (VARCHAR), Author (VARCHAR), Genre (VARCHAR), PublishedYear (INT).
§ Data Representation:
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 8

Detailed Exploration of Relational Model Structure

Table Structure:
Relational Databases
§ Tables (Relations): Represent collections of related data entries.
§ Columns (Attributes): Define the data's properties, like name, type, and category.
An Introduction
§ Rows (Tuples): Each row indicates a single record or data entry.

Example: Books Table:

§ Schema: BookID (INT), Title (VARCHAR), Author (VARCHAR), Genre (VARCHAR), PublishedYear (INT).
§ Data Representation:
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 9

Detailed Exploration of Relational Model Structure

Table Structure:
Relational Databases
§ Tables (Relations): Represent collections of related data entries.
§ Columns (Attributes): Define the data's properties, like name, type, and category.
An Introduction
§ Rows (Tuples): Each row indicates a single record or data entry.

Example: Books Table:

§ Schema: BookID (INT), Title (VARCHAR), Author (VARCHAR), Genre (VARCHAR), PublishedYear (INT).
§ Data Representation:
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 10

Detailed Exploration of Relational Model Structure

Table Structure:
Relational Databases
§ Tables (Relations): Represent collections of related data entries.
§ Columns (Attributes): Define the data's properties, like name, type, and category.
An Introduction
§ Rows (Tuples): Each row indicates a single record or data entry.

Example: Books Table:

§ Schema: BookID (INT), Title (VARCHAR), Author (VARCHAR), Genre (VARCHAR), PublishedYear (INT).
§ Data Representation:
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 11

Order Does Not Matter in Sets
Understanding Set Semantics in Relational Databases
§Relational Databases
Key Concept: In the relational model, the order of rows in a table (set) is not significant. What matters is the
data each row contains.
An Introduction
§ Original Books Table:
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

§ Reordered Books Table:

| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| !!... | !!... | !!... | !!... | !!... |

§ Note: Even though the order of rows is different, the dataset remains unchanged in terms of content and meaning.
Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 12
Set Semantics in the Relational Model

Key Points
Relational Databases
§ Set Semantics Principle: Ensures each row in a relational table is unique, crucial for data integrity.
§An Introduction
Violation Example: Demonstrating duplication in the 'Books' table.
§ Books Table Data (with Violation):

| BookID | Title | Author | Genre | PublishedYear |

|--------|---------------------------|--------------|-------------|---------------|
| 101 | Adventures in Coding | P. Programmer| Fiction | 2020 |
| 102 | Mysteries of the Universe | S. Hawking | Science | 2019 |
| 103 | The History of Databases | D. Base | Education | 2018 |
| 103 | The History of Databases | D. Base | Education | 2018 |

Note: Duplicate entry for "The History of Databases" illustrates violation.

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 13

Types and Static Attributes in the Relational Model

Understanding Data Types and Constraints:

Relational Databases
§ Significance: Ensures data integrity and query accuracy in relational databases.
§An Introduction
Issue: Incorrect data types lead to inconsistencies and operational challenges.

Example of Data Type Violation:

§ The 'PublishedYear' for "Future of Data" is incorrectly entered as a string, demonstrating the
violation of the attribute type constraint.
| BookID | Title | Author | Genre | PublishedYear |
|--------|--------------------------|---------------|-------------|---------------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| 104 | Future of Data | I. Analyst | Non-Fiction | 'Twenty Twenty-One' |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 14

Flat Table Structure in the Relational Model

Explanation of the Principle:

Relational Databases
§ In relational databases, each table should represent a single entity, avoiding nested or hierarchical
structures.
§An Introduction
Flat tables enhance data management simplicity and query performance.

Violation Example:
§ Incorrect 'Books' Table Structure:
§ Demonstrates violation with 'Editions' column containing nested data.
| Title | PublishedYear | Editions |
|---------------------------|---------------|-------------------------------|
| Adventures in Coding | 2020 | Edition 1: {2020, ISBN 12345} |
| | | Edition 2: {2021, ISBN 67890} |
| Mysteries of the Universe | 2019 | Edition 1: {2019, ISBN 23456} |
| The History of Databases | 2018 | Edition 1: {2018, ISBN 34567} |

’Editions' column inappropriately combines multiple pieces of data.

Best Practices:
§ Employ normalization to maintain data integrity and ease of access.
Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 15
Structured Query Language (SQL)

SQL Introduction:
Relational Databases
§ SQL is the primary language for relational database management and manipulation.
§An Introduction
Focuses on specifying 'what' data is needed, rather than 'how' to retrieve it.

SEQUEL SQL
(Structure English Query Language)

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 16

SQL Queries

SELECT
Relational Queries:
Databases
§ Overview: Fundamental tool for data retrieval in SQL.
§An Introduction
Function: Enables precise extraction of data from database tables.

Example and Explanation

§ SQL Query:
SELECT *
FROM Books;

§ Purpose: Fetching all data from the 'Books' table to illustrate the simplicity and effectiveness of SQL queries.
§ Output Table:
| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 17

More on SQL SELECT Queries

Data Projection
Relational with SQL's SELECT Clause:
Databases
§ The SELECT clause in SQL specifies the columns to be retrieved, enabling focused and efficient data
extraction from tables.
An Introduction

Source Table: Books:

| BookID | Title | Author | Genre | PublishedYear |
|--------|---------------------------|----------------|-------------|---------------|
| 101 | Journey Through SQL | A. Coder | Technology | 2015 |
| 102 | The History of Databases | D. Base | Education | 2018 |
| 103 | Adventures in Coding | P. Programmer | Fiction | 2020 |
| !!... | !!... | !!... | !!... | !!... |

SQL Query: Output Table:

SELECT Title, Author | Title | Author |

FROM Books; |---------------------------|----------------|
| Journey Through SQL | A. Coder |
| The History of Databases | D. Base |
| Adventures in Coding | P. Programmer |
| !!... | !!... |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 18

Data Representation
Different data models can represent the same data set, each suited specific types of analysis and queries.
Relational Databases
Case Study: Public Transportation Network
§An Introduction
Graph Representation:
+-------------+ Blue Line +-----------+ Blue Line +-------------+
| | ----------------> | | ----------------> | |
| Main St | | Downtown | | Central |
| | <---------------- | | <---------------- | Park |
+-------------+ Red Line +-----------+ Red Line +-------------+

§ Relational Model:
§ Tables: Stations, Routes and StationRoutes. StationRoutes
Stations Routes
| StationID | RouteID |
|-----------|-------------|
| StationID | StationName | | RouteID | RouteName | | 1 | A |
|-----------|--------------| |---------|--------------| | 2 | A |
| 1 | Main St | | A | Blue Line | | 2 | B |
| 2 | Downtown | | B | Red Line | | 3 | B |
| 3 | Central Park |

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 19

Choosing the Right Data Model

Key Models:
Relational Databases
§ Relational Model: Ideal for structured data, requiring strong integrity.
§An Introduction
Document-Oriented (NoSQL): Flexible schemas, suited for unstructured data.
§ Graph-Based: Effective for complex relationships and network analysis.
§ Key-Value: Fast access, useful for caching and real-time operations.

Selection Factors:
§ Data Type and Structure: Nature and complexity of the data.
§ Performance and Scalability: Efficiency in processing and growth handling.
§ Application Use Case: Specific requirements based on the industry or task.

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 20

DBMS Technology Evolution

Relational Databases

Generation
1st
An Introduction
(1960’s)
Field Oriented

§ Supported sequential and random searching of files.

§ Required users to write detailed computer programs for data access.
§ Lacked standardization and interoperability.

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 21

DBMS Technology Evolution

Relational Databases

Generation
1st
An Introduction
(1960’s)
Field Oriented § Enabled management of multiple entity types and complex relationships.
§ Still required computer programming for data access.
§ Saw initial progress in the development of database standards.

2n Generation
d (1970’s)
Navigational

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 22

DBMS Technology Evolution

Relational Databases

Generation Generation
1st
An Introduction 3rd
(1960’s) (1980’s)
Field Oriented Relational with Non-Procedural Access

§ Based on mathematical set theory and relational algebra.

§ Development of query optimization technologies.
§ IBM's research played a pivotal role in commercializing relational database technology.
§ Introduction of SQL (Structured Query Language) for database interaction.
2n Generation
d (1970’s)
Navigational

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 23

DBMS Technology Evolution

Relational Databases § Extension of database technology beyond traditional boundaries.

§ Emergence of distributed databases and data warehousing.
§ Ability to store and process non-traditional data types, including
Generation
An Introduction Generation unstructured data.
1st 3rd § Development of technologies for efficient handling of Big Data.
(1960’s) (1980’s) § Incorporation of Object-Oriented features for more complex
data modeling.
Field Oriented Relational with Non- § Introduction of NoSQL databases for high scalability and
Procedural Access performance with semi-structured and unstructured data.
§ Enhanced capabilities for web data management and publishing.

2n Generation Generation
4th
d (1970’s) (1990’s)
Navigational Object Oriented, NoSQL

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 24

DBMS Technology Evolution

Relational Databases

Generation Generation Generation

1st
An Introduction 3rd 5th
(1960’s) (1980’s) (2000’s+)
Field Oriented Relational with Non- Cloud Based and
Procedural Access Big Data Technologies

§ Emergence of cloud-based database services,

offering scalability, flexibility, and cost-efficiency.
§ Growth in Big Data technologies and analytics.
§ Advancements in real-time processing and
2n Generation Generation streaming data.
4th § Integration of machine learning and AI
d (1970’s) (1990’s) capabilities for advanced data analytics and
prediction.
Navigational Object Oriented,
NoSQL

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 25

DBMS Technology Evolution

Relational Databases

Generation Generation Generation

1st
An Introduction 3rd 5th
(1960’s) (1980’s) (2000’s+)
Field Oriented Relational with Non- Cloud Based and
Procedural Access Big Data Technologies

2n Generation Generation
4th
d (1970’s) (1990’s)
Navigational Object Oriented, NoSQL

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 26

Recap and Key Takeaways

§Relational Databases
The relational model offers a structured, efficient way to store, retrieve, and manipulate data. Its tabular format,
governed by set theory and predicate logic, not only simplifies data handling but also enhances data integrity and
consistency.
An Introduction
§ DBMS technologies provides a foundation for management of long-term memory of organisations. DBMS
enables daily operational tasks and supports short- and long-term decision makings in business.

Example SQL Recap:

SELECT Title, Author
FROM Books;

§ Output emphasizes SQL's role in fetching relevant data.

Preparing for SQL Basics:

§ Next Lecture Preview:
§ Dive deeper into basic SQL queries and functions.
§ Understand Primary Key and Foreign Key.

Tarapong Sreenuch PhD Databases: An Introduction 8 February 2024 27

How might relational databases and SQL evolve with emerging
technologies like AI?