Nowość
Za darmo

ebook Fundamentals of databases PostgreSQL: examples and practice exercises Lidia Stępień, Marcin R. Stępień, Artur Gola

Wydawca: Uniwersytet Jana Długosza w Częstochowie

Rok wydania: 2024

Preface
Database is an entity that always applies to a certain fragment of reality (the area of
analysis) and constitutes a collection of data, with specified internal structure, which
represents said fragment of reality and makes data retaining easier. The description the
database’s inner structure is handled by data models, which allow for precise (usually
formalised) depiction of the data’s properties, with the use of mathematical language, eg.
relational algebra in the relational database model. Aside from describing data structures,
models are also used for determining permissible data operations and as a way of imposing
constraints ensuring the correctness of the database.
To manage a database (understood as a collection of data), a Database Management
System (DBMS) is used. It is an organised set of tools that enables the execution of operations
on data that are essential for the user. So far, many database management systems
(DBMS) have been developed based on various models, but those based on the relational
model remain the most popular. Regardless of the choice, such software requires an understanding
of the theoretical foundations of the model and the high-level language SQL
(Structured Query Language). The difficulty in learning lies in the diversity and complexity
of DBMS software offered by different vendors. The authors of this textbook propose
learning based on PostgreSQL, one of the most popular open relational database management
systems (as of 2023, according to https://db-engines.com/en/blog_post/106).
For many years, this software has not fallen from the global popularity ranking of DBMS,
consistently remaining in the top ten among nearly 400 DBMS of all types (the DBEngines
website: https://db-engines.com/en/).
The proposed textbook includes an introduction to basic SQL statements performed
in relational databases, richly illustrated with examples, and tasks to be solved independently
to consolidate the acquired skills. The reader will be introduced to how to create
tables (basic data structures in the relational model), modify, delete and manipulate data.
We will show how to improve query execution time by using indexes, while discussing selected
basic issues related to the functions of the plpgSQL procedural language. In the
last chapter we will present how to create a database application in Java we assume that
the reader has already mastered programming skills in this language.

Spis treści ebooka Fundamentals of databases PostgreSQL: examples and practice exercises

Contents
Preface 6
1 Introduction to PostgreSQL System 7
1.1 System Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.2 Environment Preparation . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.3 psql Client Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.4 Session Information Functions . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.5 Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.6 SQL Data Types and Operators . . . . . . . . . . . . . . . . . . . . . . . . 12
1.6.1 Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.6.2 Mathematical Operators . . . . . . . . . . . . . . . . . . . . . . . . 14
1.6.3 Logic and Comparison Operators . . . . . . . . . . . . . . . . . . . 15
1.6.4 Symbols and Operators for Building Regular Expressions . . . . . 16
2 Basic Table Operations 18
2.1 Table Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
2.2 Modifying the Table Structure . . . . . . . . . . . . . . . . . . . . . . . . 23
2.3 Adding a New Data to the Table . . . . . . . . . . . . . . . . . . . . . . . 25
2.4 Modifying and Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.5 Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.6 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
3 Simple Database Queries 33
3.1 SELECT Query Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
3.2 Fetching Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.2.1 SELECT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.2.2 WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
3.2.3 ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.3 Copying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
3
Contents 4
3.4 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
4 Data Joining 50
4.1 Joining Tables Horizontally . . . . . . . . . . . . . . . . . . . . . . . . . . 50
4.1.1 Inner Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
4.1.2 Outer Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
4.1.3 Cartesian Product - CROSS JOIN . . . . . . . . . . . . . . . . . . 57
4.2 Connecting Vertical Connection . . . . . . . . . . . . . . . . . . . . . . . . 58
4.3 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
5 Aggregate Functions 61
5.1 Functions Operating on Groups of Rows . . . . . . . . . . . . . . . . . . . 61
5.1.1 GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
5.1.2 HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
5.2 Data Cleansing and Quality Control . . . . . . . . . . . . . . . . . . . . . 67
5.3 Window Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
5.4 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
6 Query Nesting 75
6.1 Subquery Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
6.1.1 Independent Subqueries . . . . . . . . . . . . . . . . . . . . . . . . 76
6.1.2 Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . 79
6.2 Recursive nesting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
6.3 WITH Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
6.4 Subqueries and Aggregate Functions in Practice . . . . . . . . . . . . . . . 85
6.5 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
7 Creating and Using Views 89
7.1 Basic Operations Related to Views . . . . . . . . . . . . . . . . . . . . . . 89
7.2 Views in Action . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
7.3 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
8 Creating and Using Indexes 96
8.1 B-tree Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
8.2 Stored Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
8.3 Sample Session . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
8.4 Effective Use of Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
8.5 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Contents 5
9 Transactions 111
9.1 Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
9.1.1 Read/Write Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
9.1.2 The Range of Locks . . . . . . . . . . . . . . . . . . . . . . . . . . 112
9.2 Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
9.2.1 ACID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
9.2.2 Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
9.2.3 Deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
9.2.4 Transactions in PostgreSQL - AUTOCOMMIT . . . . . . . . . . . . . . 119
9.3 Practice Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
10 Creating Database Applications in Java 121
10.1 Setting Up the Work Environment . . . . . . . . . . . . . . . . . . . . . . 121
10.2 Java JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
10.3 Types of JDBC Drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
10.4 Structure of a Database Application in Java . . . . . . . . . . . . . . . . 125
10.4.1 Example – Establishing a Connection to the Database . . . . . . . 125
10.5 Working with SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . 131
10.6 SQLException Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . 134
10.7 Example – creating a table in the database . . . . . . . . . . . . . . . . . 136
10.8 Example – inserting data into the table . . . . . . . . . . . . . . . . . . . 136
10.9 Example – executing queries . . . . . . . . . . . . . . . . . . . . . . . . . 138
10.10 Example – creating an application with a GUI (Graphical User Interface) 139
10.11 Practice exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Bibliografia 148
Appendix A 149
Appendix B 156