Databases and Visualization

Introduction to Databases

Assoc. Prof. Ing. Ladislav Körösi, PhD.

Institute of Robotics and Cybernetics

FEI STU Bratislava

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 1 / 31
 Databases
Definition

A database (data base, English: database) is any collection of interrelated information (without
unnecessary redundancy).

Examples of data:
shopping list,
to-do list,
phone directory,
products and their parameters in an online store.

Examples of database storage:

on paper (shopping list, to-do list),
in one’s mind (shopping list, to-do list),
on a computer (shopping list, to-do list, phone directory, products and their parameters in
an online store, ...).
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 2 / 31
 Databases
Comparison of Two Databases

E-shop (eBay, Amazon, Alza, ...)

Users (contact details, payment cards), products (parameters, availability, ratings), orders,
invoices, complaints, ...
A vast amount of stored and processed information.
Sensitive data; its loss can disrupt the operation of the business.
Security is the top priority.
Data is stored on a computer.

Shopping List
Names and quantities of products, the store where they are planned to be purchased.
Typically consists of a few dozen products.
Losing the list has no serious consequences.
Security is not important.
Data is stored on paper, in a mobile app, or in one’s memory.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 3 / 31
 Databases
Data Storage

Data in a database:
is stored independently of the programs that use it,
data addition, modification, and retrieval are centrally managed (we do not deal with
whether data is stored locally or distributed, in RAM or on an HDD, etc. – i.e., we are not
concerned with how we want to perform something, but what we want to perform),
data can be used and updated to various extents, from different perspectives, by one or
multiple users.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 4 / 31
 Databases
Database Management System

A Database Management System (DBMS, database server) is a special software (a set of prog-
rams) running under a computer’s operating system that directly interacts with the database.

It enables:
access to data from various applications (clients),
data storage (various storage options such as RAM, HDD),
updating, deletion, etc.,
backups,
protection against improper manipulation.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 5 / 31
 Databases
Database Classification

Two types of databases:

Relational databases (SQL) - data is organized into tables that are interconnected. A
table consists of rows representing records and columns representing attributes of a record
of a specific data type.
Non-relational databases (NoSQL, Not Just SQL) - all databases that are not relational.
These include, for example, JSON, XML documents, graphs, vectors, etc.

We will focus on databases from the perspective of a designer, developer, and user of a database
application that handles persistence using a relational database.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 6 / 31
 Relational Databases (SQL)
Examples of DBMS or relational database management systems (RDBMS):
MySQL - used during the semester,
Oracle,
MariaDB,
PostgreSQL,
Microsoft SQL Server, etc.

Structured Query Language (SQL)

a standardized language for interacting with RDBMS,
used to perform so-called CRUD (Create, Read, Update, and Delete) operations
(managing and defining databases, tables, etc.) and administrative tasks (users, backups,
etc.),
SQL is a standard, but implementations in different RDBMS may vary.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 7 / 31
 Relational Databases (SQL)
Basic Concepts

Consider a table named Users:

id_user (PK) name date_of_birth age salary
1 Jano 1.1.2000 21 700
2 Marek 2.2.1980 31 780
3 Jano NULL NULL 1700

Table (entity) - contains related (functionally dependent) data.

Column (attribute) - represents a property of a record of a specific data type (INT,
CHAR, VARCHAR, ...).
Row - represents a single record (instance) in the table.
Value (data) - an entry in a specific row and column (it can also be derived – see the age
column). NULL means an unknown (undefined) value (not zero or an empty string).

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 8 / 31
 Relational Databases (SQL)
Basic Concepts

Consider a table named Users:

id_user (PK) name date_of_birth age salary
1 Jano 1.1.2000 21 700
2 Marek 2.2.1980 31 780
3 Jano NULL NULL 1700

Derived data - typically not stored and calculated at the time of query.
Domain - a list/range of permissible values.
Functional dependency is a relationship between two sets of attributes in a table, where the
values of one set determine the values of the other set. Consider attributes A and B. Attribute
B is functionally dependent on A if for every value of A, there is exactly one value of B. This
functional dependency is written as: A → B. This means that the value in attribute A determines
the value in attribute B.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 9 / 31
 Relational Databases (SQL)
Basic Concepts

Consider a table named Users:

id_user (PK) name date_of_birth age salary
1 Jano 1.1.2000 21 700
2 Marek 2.2.1980 31 780
3 Jano NULL NULL 1700

For example:
id_user → name - For each unique user ID, there is always one unique name (a unique
user). This means we can uniquely determine the user’s name based on their ID. This
dependency is functional.
id_user → salary - Although the salary is assigned to each user based on their ID, in the
real world, salary may depend on factors like position, seniority, etc. This means that
id_user → salary is not a functional dependency because multiple users with the same
ID can have different salaries.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 10 / 31
 Relational Databases (SQL)
Basic Concepts - Primary Key, Composite Key, and Simple Key

Consider the tables Users and Grades:

id_user (PK) name date_of_birth age
1 Jano 1.1.2000 21
2 Marek 2.2.1980 31
3 Jano 3.3.2010 11

id_student (PK) id_course (PK) grade

1 1 A
1 2 B
2 4 C
Primary Key (PK) uniquely identifies records. It contains unique (non-repeating) values (a com-
bination of values). A PK is defined as PRIMARY KEY (automatically unique and must not
contain NULL, see later SQL syntax):
By a single column (see Users table) - simple key.
By combining multiple columns (see Grades table) - composite key.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 11 / 31
 Relational Databases (SQL)
Basic Concepts - Superkey

Consider the table Student:

id_student ISIC_number personal_ID passport_number name
1 12345 991212/1234 AB111222 Jano
2 54321 991010/1000 CD333444 Marek
3 11111 980909/1111 EF444555 Jano

Columns or combinations of columns (i.e., subsets of the table’s attributes) that uniquely iden-
tify rows are called superkeys. For example: {id_student}, {ISIC_number}, {personal_ID},
{passport_number}, {id_student, ISIC_number}, {id_student, personal_ID}, . . ., {id_student,
ISIC_number, personal_ID, passport_number, id_student, ISIC_number}, {id_student, ISIC_num
personal_ID, passport_number, id_student, ISIC_number, name}, . . .

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 12 / 31
 Relational Databases (SQL)
Basic Concepts - Primary Key, Candidate Key, Natural Key, and Surrogate Key

Consider the table Student:

id_student ISIC_number personal_ID passport_number name
1 12345 991212/1234 AB111222 Jano
2 54321 991010/1000 CD333444 Marek
3 11111 980909/1111 EF444555 Jano

A Candidate Key is the minimal subset of table attributes whose elements together uniquely
identify the rows of the table. The Primary Key (PK) is chosen from the candidate keys by the
database designer. Some entities do not have enough attributes to form a PK.
An entity without a PK is called a weak entity (a new column is artificially added - e.g.,
id_user in the Users table) - surrogate key.
An entity with a PK is called a strong entity (in the Student table, for example, the
personal ID number or passport number) - natural key.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 13 / 31
 Relational Databases (SQL)
Basic Concepts - Alternate Key

Consider the table Student:

id_student ISIC_number personal_ID passport_number name
1 12345 991212/1234 AB111222 Jano
2 54321 991010/1000 CD333444 Marek
3 11111 980909/1111 EF444555 Jano

An Alternate Key, in addition to the PK, ensures the uniqueness of data in a given column (it
is an alternative to the PK). Let the PK be the column id_student. For each alternate key, we
use the “UNIQUE constraint“, which ensures the uniqueness of values in the given column.

A constraint is a rule that determines what values can be inserted into a table. In MySQL (and
SQL in general), constraints are used to ensure data integrity in tables and to define rules that
the data must comply with.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 14 / 31
 Relational Databases (SQL)
Basic Concepts - Foreign Key

Consider the tables Student and Dormitory:

personal_ID (PK) passport_number name dormitory_id (FK)
991212/1234 AB111222 Jano 1
991010/1000 CD333444 Marek 1
980909/1111 EF444555 Jano 2

dormitory_id (PK) name

1 Mladosť
2 Štúrák
3 Manželáky

A Foreign Key (FK) is a column containing a subset of the primary key data from another table.
The column names may differ. A Foreign Key establishes relationships between tables, removes
duplicate records (space saving), and prevents so-called anomalies.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 15 / 31
 Relational Databases (SQL)
Basic Concepts - Foreign Key

Consider the tables Customer, Account, and Customer_Account:

customer_id (PK) Name
1 Name 1
2 Name 2

account_id (PK) Something

1 Data 1
2 Data 2

customer_id (PK) account_id (PK)

1 1
2 1
2 2
A Compound Key is a composite primary key consisting of a foreign key. Essentially, it is a
composite key.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 16 / 31
Relational Databases (SQL)
Basic Concepts - Foreign Key

Consider a company that manufactures two categories of toys:

Traditional
Modern
Among traditional toys, they produce:
Board games
Puzzles
...
Among modern toys, they produce:
Quadcopters
Robots
...
Examples of product codes: **kl-st-02, kl-st-10, kl-pu-02, kl-pu-11, kl-pu-99, mo-kv-07, mo-
ro-13, mo-ro-14**. The column containing product codes serves as a **natural key** and is
also referred to as an **intelligent key**—it consists of multiple parts, each carrying specific
meaning.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 17 / 31
 Database Keys
Basic Concepts - Keys - Summary

**Primary key** – uniquely identifies records in a table,

**Candidate key** – a minimal key that can serve as a primary key,
**Superkey** – a column or set of columns that uniquely identifies records,
**Alternate key** – an alternative to the primary key,
**Foreign key** – used to establish relationships between tables,
**Surrogate key** – an artificial key, often added for simplicity or when a natural key is
absent,
**Natural key** – derived from real-world data (e.g., a national identification number),
**Simple key** – a key consisting of a single column,
**Composite key** – a key made up of multiple columns,
**Compound key** – a key combining multiple columns (sometimes used interchangeably
with composite key, but may have broader definitions),
**Intelligent key** – a key where values carry meaningful information.
Video on Database Keys
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 18 / 31
 Relational Databases (SQL)
Basic Concepts - Anomalies

Consider the following **Student** table:

national_id (PK) name dorm_name dorm_phone
991212/1234 Jano Mladosť +4212123123
991010/1000 Marek Mladost +4212123123
980909/1111 Jano Štúrák +4212111111
Examples of anomalies:
Insertion Anomaly - A typo in the dormitory name. When searching by name, some records may
not appear, leading to incomplete or incorrect results. Additionally, it’s impossible to register a
new dormitory without assigning a student to it.
Update Anomaly - If the dormitory **"Mladosť"** is renamed, its name must be changed in
multiple records. Typos may lead to inconsistencies, causing unreliable data.
Deletion Anomaly - If the records for **Jano and Marek** are deleted, all information about
dormitory **"Mladosť"** (e.g., its phone number) is lost. This forces redundant data entry in the
future.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 19 / 31
 Relational Databases (SQL)
Selected Data Types - Integers

TINYINT(d) - A numerical data type with a size of **1 byte**. Values can range
**from -128 to 127 (signed)** or **from 0 to 255 (unsigned)**. The **d parameter**
defines the number of displayed digits. If a value exceeds d digits, it is still stored and
displayed. The d value is metadata that a client application may or may not use.
SMALLINT(d) - A numerical data type with a size of **2 bytes**. Values can range
**from -32,768 to 32,767 (signed)** or **from 0 to 65,535 (unsigned)**. The d
parameter functions the same way as in TINYINT.
INT - A numerical data type with a size of **4 bytes**. Values can range **from
-2,147,483,648 to 2,147,483,647 (signed)** or **from 0 to 4,294,967,295 (unsigned)**.
The d parameter functions the same way as in TINYINT.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 20 / 31
 Relational Databases (SQL)
Selected Data Types - Floating-Point Numbers

DECIMAL(c,d) - The **DECIMAL** data type is used to store **exact numerical

values** in a database. It defines a total of **c digits**, with **d digits** being decimal
places. For example, **DECIMAL(5,2)** allows values from **-999.99 to 999.99**. - If
**DECIMAL(c)** is used, it is equivalent to **DECIMAL(c,0)**. - **DECIMAL**
without parameters is equivalent to **DECIMAL(10,0)**. - The **maximum total
number of digits (c) is 65**.
FLOAT(m,d) - A **floating-point data type** that represents an **approximate numeric
value**. - Similar to DECIMAL, it allows defining **m digits** with **d decimal
places**, but this is **not recommended for compatibility reasons**. - Example: Inserting
**999.00009** into **FLOAT(7,4)** will round the value to **999.0001**. - **FLOAT
occupies 4 bytes** of storage.
DOUBLE(m,d) - A **floating-point data type** with a **larger range and greater
precision** than FLOAT. - It uses **8 bytes** of storage. - Like FLOAT, it represents
**approximate numeric values**.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 21 / 31
 Relational Databases (SQL)
Selected Data Types - Text Strings

CHAR(Length) - Fixed-length text strings **ranging from 0 to a maximum of 255

characters**. - If the length is **10** and a shorter string is inserted, it is **padded with
spaces** on the right. - When queried, these spaces are automatically **removed**. -
**Advantage**: Fixed length allows **faster record retrieval**. - **Disadvantage**:
Fixed size can be **inefficient** for storing variable-length strings.
VARCHAR(Length) - Similar to **CHAR**, but with a **maximum length from 0 to
65,535 characters**. - Unlike **CHAR**, **VARCHAR uses variable-length storage**. -
The actual length of stored text is tracked using **1 or 2 bytes** (depending on string
length). - **Advantage**: Saves **storage space** by using only the necessary length. -
**Disadvantage**: Slower **record retrieval** compared to CHAR.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 22 / 31
 Relational Databases (SQL)
Selected Data Types - Date and Time

YEAR - A data type for storing **year values** ranging from **1901 to 2155** or
**0000**. - The value **0000** is a special placeholder used by MySQL to represent a
**"nullör unknown** year.
DATE - Stores **date values** in the format **’YYYY-MM-DD’**. - Supported range:
**’1000-01-01’ to ’9999-12-31’**. - If an invalid date is entered, it is replaced by
**’0000-00-00’**.
TIME - Stores and displays **time values** in the format **’HH:MM:SS’** or
**’HHH:MM:SS’** for extended hour values. - Range: **’-838:59:59’ to ’838:59:59’**
(supports negative and large values).
DATETIME - Stores **both date and time** in the format **’YYYY-MM-DD
HH:MM:SS’**. - Supported range: **’1000-01-01 00:00:00’ to ’9999-12-31 23:59:59’**. -
If an invalid date is entered, it is replaced by **’0000-00-00 00:00:00’**.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 23 / 31
 Relational Databases (SQL)
Example Tables - 1st Example

Major the **Employees table** (a weak entity set)

ID (PK) First Name Last Name Date of Birth Salary
100 Name1 Surname1 1978-03-09 1000.00
101 Name2 Surname2 1980-08-24 1200.00
102 Name3 Surname3 1969-01-31 1150.00

ID - Data type **INT** (Surrogate Key),

First Name and Last Name - **VARCHAR(20)** (important to consider the maximum
allocated size; using **VARCHAR(5000)** or **CHAR(5000)** is a bad practice),
Date of Birth - Data type **DATE**,
Salary - Data type **DECIMAL(6,2)**.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 24 / 31
 Relational Databases (SQL)
Example Tables - 2nd Example

Consider the **Employees table** (a strong entity set)

SSN (PK) First Name Last Name Date of Birth Salary
9912121234 Name1 Surname1 1978-03-09 1000.00
9910101000 Name2 Surname2 1980-08-24 1200.00
9809091111 Name3 Surname3 1969-01-31 1150.00

SSN - Data type **BIGINT** (Natural Key - Social Security Number).

The remaining attributes remain unchanged.

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 25 / 31
 Relational Databases (SQL)
Examples of Tables - Example 3

Let’s have the tables Employees and Departments

RC (PK) first_name last_name salary department_id (CK)
9912121234 Name1 Surname1 1000.00 1
9910101000 Name2 Surname2 1200.00 1
9809091111 Name3 Surname3 1150.00 2

department_id (PK) name

1 IT
2 Service
3 HR

The department_id column from the Employees table refers to the department_id column in
the Departments table, which contains information about the department. How do we add the
department head?
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 26 / 31
 Relational Databases (SQL)
Examples of Tables - Example 4

Let’s have the tables Employees and Departments

RC (PK) first_name last_name salary department_id (CK)
9912121234 Name1 Surname1 1000.00 1
9910101000 Name2 Surname2 1200.00 1
9809091111 Name3 Surname3 1150.00 2

department_id (PK) name RC (CK)

1 IT 9912121234
2 Service 8811112222
3 HR 7711112222

The RC column from the Departments table refers to the RC column in the Employees table.
This ensures the assignment of a department head (using the ID number) to the department.
How do we include branches? Let’s consider one department head independently of the branches.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 27 / 31
 Relational Databases (SQL)
Examples of Tables - Example 5

Let’s have the tables Employees, Departments, and Branches

RC (PK) first_name last_name salary department_id (CK) branch_id (CK)
9912121234 Name1 Surname1 1000.00 1 1
9910101000 Name2 Surname2 1200.00 1 2
9809091111 Name3 Surname3 1150.00 2 3

department_id (PK) name RC (CK)

1 IT 9912121234
2 Service 8811112222
3 HR 7711112222

branch_id (PK) name

1 Bratislava
2 Košice
3 Poprad
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 28 / 31
 Relational Databases (SQL)
Examples of Tables - Example 6

Let’s have the tables Movies and Actors

ID (PK) name IMDB
1 Movie1 4.4
2 Movie2 5.8
3 Movie3 8.0

ID (PK) name
1 Actor1
2 Actor2
3 Actor3

If we added a new column to the Movies table with actor IDs, one movie could only be assigned
one actor. Similarly, the Actors table could be extended. In the case of an M:N relationship,
where multiple actors (M) can play in one movie, and one actor can play in multiple movies
(N), a join table is introduced.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 29 / 31
 Relational Databases (SQL)
Examples of Tables - Example 6

Let’s have the tables Movies, Actors, and MovieActors

ID (PK) name IMDB
1 Movie1 4.4
2 Movie2 5.8
3 Movie3 8.0

ID (PK) name
1 Actor1
2 Actor2
3 Actor3

ID (PK) ID_movie (CK) ID_actor (CK)

1 1 1
2 2 2
3 2 3
In the movie Movie1, Actor1 performs, and in Movie2, Actor2 and Actor3 perform.
Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 30 / 31
 Relational Databases (SQL)
Cardinality of a Relationship

Cardinality refers to the number of occurrences (rows) of the entities involved in a relationship.
No relationship: No connection between tables.
1:1: Each entity has at most one related row in the other table. Rare in practice (e.g., one
office per employee).
1:N: One entity relates to one row, while the other can have multiple rows. (e.g., one
reader can borrow many books).
M:N: Both entities can relate to multiple rows. (e.g., teachers and students).

Assoc. Prof. Ing. Ladislav Körösi, PhD. (IRC) Databases and Visualization - Introduction to Databases 31 / 31