Read Basics

Fast track: In this field, you will learn the most used terms in relation with databases such as RDBMS, SQL, Columns and Rows.
If you are familiar with them, just scan through the section.

Goal of a database

Goal of a database: to store data on a way it survives a restart or power off period. It provides the possibility to easily access it from applications. Stored data is often structured based on some logic.

You might heard about these database systems (often called just databases; so when you hear database, it either means the database system, or a specific database storing a collection of data): MS SQL (= Microsoft SQL = SQL Server), DuckDB, Oracle, PostgreSQL (say: [postgres] or [postgres kju: el]), MongoDB, Neo4j, just to mention a few. They are capable of storing the structures and data you want. TOPdesk (the company) uses mainly MS SQL database.

Terminology (magic words explained)

  • Relational database (= RDBMS = Relational Database Management System)
    This is the most prevalent and common type of database (the other category is called non-relational database). It is a type of database that consists of tables, with relations between those tables. Relations are declared on the database level, for example, if there is a table of cats and of owners, then you define the relations: 1 cat belongs to 0 or 1 owner. 1 owner can have 0 to many cats. A database system must fulfill some criteria to deserve the category relational (atomicity, consistency, isolation, durability etc.). DuckDB is a relational database. drawing

  • SQL vs. noSQL makes the same distinction as relational vs. non-relational. SQL is a language of the relational databases, which can be used to get or put data from/to the database, either by a human, or by an application. noSQL stands for „not only SQL”, and is used to describe non-relational databases. noSQL is a category of very different databases, without a common query language. noSQL databases are a very interesting topic, but outside the scope of this workshop.

  • Table is the main unit of the structure. The table is defined by columns. A table has a name.

  • Rows, records: If there is data stored in the table, then they are the rows or records of the table. Otherwise it’s empty.

  • A field is a value in a specific row and column, but the term is often used as a synonym of column. („What kind of fields does this table have?” „FirstName, LastName etc.”)

  • unique identifier, unid, id: There is often a unique identifier field in a table. It is capable to identify a record.

A table structure is created by defining columns, ID, relations to other tables if any, and possibly other settings.

  • CRUD: Mosaic word for the 4 basic functions of a table:

    • Once a table is created, data can be inserted into it. (Create data.)
    • Data can be read of it. (Read data.)
    • Rows can be updated. (Update data.)
    • Rows can be deleted. (Delete data.)

  • Querying: Reading can be more sophisticated:

    • When reading data, rows can be filtered by conditions, and can be sorted.
    • When reading data, columns to show can be selected.

SQL (Structured Query Language)

Creating, querying a table are done by executing some magic command in a magic tool, and then they are stored at a magic place. In the simplest case:

  • The magic commands are written in SQL language.

  • The magic tool is DuckDB's web shell, which has a nice area for the commands (queries), and runs them right there in your browser.

  • The magic place can be on your own file system, or even just in the browser's memory, which is set in the magic tool.

The term SQL is often used in a context of SQL script: the commands can be saved into a file with .sql extension, which can be executed by the tool on the server.

Database client is a broader term for the applications, which are able to manipulate data or data structure. It can be a tool like DuckDB's web shell, or also an application like TOPdesk. If it's an application, it compiles into SQL statements in the background, connects to the server, and when the code gets to a specific point, it runs without human interaction. In this case, the database client is different from DuckDB's web shell.