Structure of a relational database

Table concept or relation :

We propose to create the grade management database to manipulate the grades of a student in a high school.
In this database we can have the following entities: student, subject, grade

A student can be identified by his Id, surname, first name, class, section and address.
A subject can be identified by subject code, label and coefficient.
The grade entity can have as attribute the student Id, the subject code, and the exam grade.

Definition :

A table is a set of data relating to the same entity. It is structured in the form of a table (list). A table can also be called a relation (it contains the data which are in relation to a given object)

Examples

Student table
+----+-----------+------------+-------+---------+
| Id | surnom    | first name | class | section |
+----+-----------+------------+-------+---------+
|    |           |            |       |         |
+----+-----------+------------+-------+---------+

Subject table
+----------------+-----------+-------------+
| Subject code   | label     | coefficient | 
+----------------+-----------+-------------+
|                |           |             |
+----------------+-----------+-------------+

Grade table
+----------------+------------+-------------+
| Subject code   | Student Id | exam grade  | 
+----------------+------------+-------------+
|                |            |             |
+----------------+------------+-------------+

Notes : The data of a table can be stored on one or more files. The same file can also contain data from several tables.
The table can be considered as a mathematical set so we can make the intersection and union of two tables

Concept of columns or attributes :

Definition

A column (or field or attribute) is an elementary property of the entity described by a table.

The characteristics of an attribute:

Name (the choice of name is subject to the same rules for choosing variable names in programming
Data type (numeric, text, date, boolean, etc.)
Possible size (maximum length taken by the values of this attribute)
A mandatory presence indicator (YES / NO) (YES ie the field must always be filled in Not Null; NO ie the field can be empty.
It is said to be empty has the value Null ≠ zero (numeric) ≠ empty string (text))
Default value
Validity if: there is a rule on the values to use for this field (greater than a certain value, between two values, etc.)

Concept of rows or records :

Definition

A row (or record or tuple) is an instance of the entity represented by the table.

Example

Student table
+----+-----------+------------+-------+---------+
| Id | surnom    | first name | class | section |
+----+-----------+------------+-------+---------+
| 1  | Mostafa   | SEDOKI     | CS04  |  CS     |
| 2  | Sara      | Morabet    | EL04  |  EL     |
+----+-----------+------------+-------+---------+

For the example above the students, Mostafa and Sara each take a row or represent a record.

Notes : When we create a table, it is initially empty. So to use it, we can insert new lines, modify certain lines, delete certain lines that we no longer need or consult certain lines according to a well-defined criterion.

Primary key concept

Definition

The primary key (or identifier) of a table is a field or a set of fields used to uniquely identify each record in the table (or row). So she is unique and not Null

For the table student, we can't have two or more students with the same Id

Links between tables or relationships

Definition

A link between two tables A and B results in the addition in table B of a field corresponding to the primary key of table A. This field is called a foreign key.
In this case, A is the parent table (Domain) and B is the child table (Co-domain)

Example :

For our first example above, there exists a relation between the table student and table grade (because a student can have one or many grades), so we add the attribute Student Id (primary key in student table) into the grade table (it becomes a foreign key).
The same between the table subject and grade.

Student and Subject are parent tables (Domain), Grade is a child table (Co-domain).

Types of Relations

There are four types of links between tables :

One-to-one : In mathematical terms, we say that each element in the domain has only one image in the co-domain and each element in the co-domain is associated with only one element in the domain. In particular, each element of the domain has a unique image.
One-to-many : In mathematical terms, we say that one element in the domain has many images in the co-domain. That is to say, it is not all elements of the domain that has unique images.
Many-to-one : In mathematical terms, a many-to-one relation is a relation in which several or many elements in the domain have one image in the co-domain.
Many-to-many : In mathematical terms, we say several elements in the domain have many elements in the co-domain. And several elements in the co-domain are associated with many elements in the domain.

Integrity constraints concept

Definition

An integrity constraint is a rule applied to a column (field) or a table and which must always be checked.

Types of constraints:

The main types of integrity constraints are:

Domain constraints: they apply to the fields to define their validity (valid if)
Table integrity constraints: they ensure that each table must have a primary key
Referential integrity constraints: they make it possible to ensure that the values entered in the foreign key field of a child table already appear in the parent table. So deleting a record from a parent table A used by the child table B is prohibited

Database normalization

Database normalization is the process of efficiently organizing data in a database. This normalization process has two reasons

Eliminate redundant data, for example, store the same data in multiple tables.
Make sure data dependencies make sense.

These two reasons are worthwhile goals, as they reduce the amount of space used by a database and ensure that data is stored logically.

Standardization consists of a series of guidelines that will help you create a good database structure.

Standardization directives are divided into normal forms. The purpose of normal forms is to organize the structure of the database, so that it follows the rules of the first normal form, then the second normal form, and finally the third normal form.

It is your choice to go further and go to the fourth normal form, fifth normal form, etc., but in general, the third normal form is more than enough.

We will talk later in detail about these normal forms, this is just an introduction

Structure of a relational database