Relational database concept

A relational database is a collection of data elements with predefined relationships between them. These elements are organized as a set of tables with columns and rows that comply with the relational model, which is currently the most used model to implement already planned databases and allow to establish interconnections between the data using the primary keys, and through these connections, to relate the data of both tables, hence the name: «Relational Model«.

A relational database is made up of several tables or relationships. There can not be two tables with the same name. Each table is a set of records (rows and columns). The relationship between a parent table and a child is carried out by means of the primary and foreign (or foreign) keys. Primary keys are the primary key to a record within a table and they must comply with data integrity. The foreign keys are placed in the daughter table, they contain the same value as the primary key of the parent record; through these relationships are made.

In a relational database, all data is stored and accessed through relationships. Relationships that store data are called «base relations» and are implemented in a table.

Each table can have one or more fields whose values ​​uniquely identify each record of said table, that is, there can not be two or more different records whose values ​​in said fields are identical. This table is the relationship between tables.

Table Users:

Name Id
Dani 2
Jhon 5

Table Users in SQL is:

CREATE TABLE Users(
    Id   INT(6) PRIMARY KEY,
    Name VARCHAR(30),
);

Table Cars:

Model Id
Ford  4
Fiat  2

Table Cars in SQL is:

CREATE TABLE Cars(
    Id   INT(6) PRIMARY KEY,
    Model VARCHAR(30),
);

Relational Table UsersCars:

id_p_key Id_user id_car
   1        1      4
   2        2      2

Relational Table UsersCars in SQL is:

CREATE TABLE UsersCars(
    id_p_key INT(6) PRIMARY KEY,
    Id_user INT(6),
    id_car INT(6),
    FOREIGN KEY (Id_user) REFERENCES Users(Id),
    FOREIGN KEY (id_car) REFERENCES Cars(Id)
);

As we can see in the relational table UserCars relates the 2 user tables and cars by the id of each row.

Given this relational tableUsersCars we can say that the user with id 1 has the car with id 4, so Dani (2) has a Ford (4).

Advantages relational database

1. Reduced duplication – for example the book’s details and the customer’s details need only be entered into the database once.  Consequently because of this, mistakes are less likely to happen.  Even if there were a mistake in a customer’s record, correcting it will correct the mistake database-wide.
2. Duplication is avoided – this keeps the database’s file size down.
3. Accessibility – Details about books and customers are easily accessible using their unique IDs. 3. Better security. By splitting data into tables, certain tables can be made confidential. When a person logs on with their username and password, the system can then limit access only to those tables whose records they are authorised to view. For example, a receptionist would be able to view employee location and contact details but not their salary. A salesman may see his team’s sales performance but not competing teams.
4. Future Developments – By having data held in separate tables, it is simple to add records that are not yet required. However, it may well be that this is required at some point in the future.
5. Improved security – By splitting data into tables, certain tables can be made confidential. For example, when a person logs on with their username and password, the system can then limit access only to those tables whose records they are authorised to view. For example, an administrator working in a college would be able to view a teachers staffroom and their contact details, but not their salary.