What are databases?
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
We use them every day, they are necessary, most of us don't know what they are and maybe even their existence... We are talking about databases.
But what do we define as a database?
By term we define any set of organized data stored on a physical medium. Practically even an excel in which we write a list of customers is a database.
Where is it used?
When we make a transaction, for example a purchase at the supermarket, a withdrawal at a bank, to see the balance of our card on the prepaid card, to book a ticket at the cinema, etc.. For all these actions-transactions (transactions) each time actually runs back a query to a database called query.
The ACID properties
It should go without saying that database transactions must have the following four properties ACID where is Individuality, Consistency, Isolation and Permanence. I don't want to bore you with explaining what each one means, but I will give you an example. What if in a transaction e.g. to reserve a cinema seat suddenly the power went out? Would the information that someone paid for the position be lost? or when during the reservation of the seat, a second customer was looking for the seat could two customers mistakenly book the same seat? Of course not.
This happens thanks to them relational databases, there are other types such as NoSQL which deserve a topic of their own.
What do we define as a relational database?
It is databases that the entities they contain are interdependent. As an entity we can think of one customer which has some address and buys some products. All these entities at relational database management systems(RDBMS) we define them as tables.
What is normalization?
So the data is stored in the tables, but they should be normalized meaning have one value in each field, have no duplicate entries, and have no fields that depend on other fields that they don't belong in the primary key (we will analyze below what key means).
Let's look at an example of a table before and after full normalization:
You see the information of the Country, AMKA, VAT number, Address, etc. that are repeated every time the transaction is made. This results in needing more space to store them and more processing power to manage the data.
Let's see next:
You see the information is not repeated e.g. the country Greece has been written in the base in only one field. The relational (relational) connection between them you achieve with the use of keys/keys.
What are the keys
As primary key we define a field or combination of fields that remains unique in the array and we usually also define it as clustered index key which we will analyze in another article.
As foreign key we define a field or a combination of fields it is referred to as primary key in another table.
Relationships between tables
We have 3 kinds of relationships between tables:
One by one each customer has an AMKA and an AFT. We see the different foreign key in each entry in the table:
Many to many each customer orders many products and many products are ordered by many customers. To do this, the intermediate table of sales should be created so that the primary key of both tables to exist as foreign in one:
One to many each country has several addresses:
What are constraints?
But what will happen to the addresses if we delete a country? We won't know after which country the address is? There they kick-in the so-called foreign keys constraints. We can and do declare them during the creation of the table or even afterwards. Essentially they don't allow us to delete a country if we haven't first deleted all the addresses listed as foreign key. So it ensures us that this will not happen.
What else is there besides tables in a relational database?
There are the following:
- Views are table views that allow users to view fields from different tables or from the same table. They allow for filtering by criteria.
- Stored procedures are queries that have been saved so that they can be called with a name (eg execute sp_tade) over and over again. They can also contain parameters (eg execute sp_tade @AFM='1024921′) and return the corresponding result.
- Functions they are like procedures with the difference that they can accept only one parameter. They should also always return a result. While in procedures it is not mandatory to return something (e.g. function UPPER() will capitalize whatever parameter you give it).
- Triggers they are a type of stored procedure that are executed automatically when some action occurs in the database. As e.g. if the value of this field changes, keep the value somewhere else.
This is the basic theory of databases.
In closing, I would like to mention that there are many different relational database management systems (DBMS). Such as Microsoft SQL Server, Oracle Database, MySQL/MariaDB, Netezza, Sybase, DB2 and PostgreSQL. There are differences between them, but the basic principles that we analyzed in the article are valid.
