por He relational model of databases It is a method of structuring data using relationships, using grid-like structures consisting of columns and rows. It is the conceptual principle of relational databases. It was proposed by Edgar F. Codd in 1969.
Since then it has become the dominant database model for business applications, when compared to other database models such as hierarchical, network, and object.
Codd had no idea how extremely vital and influential his work as a platform for relational databases would be. Most people are very familiar with the physical expression of a relationship in a database: the table.
The relational model is defined as the database that allows you to group your data elements into one or more independent tables, which can be related to each other by using fields common to each related table.
[toc]
Database management
A database table is similar to a spreadsheet. However, the relationships that can be created between the tables allow a relational database to efficiently store a large amount of data, which can be effectively retrieved.
The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly declare what information the database contains and what information they want from it.
On the other hand, they leave it to the database management system software to describe the data structures for storage and the retrieval procedure to answer queries.
Most relational databases use the SQL language for querying and defining data. Currently there are many relational database management systems or RDBMS (Relational Data Base Management System), such as Oracle, IBM DB2 and Microsoft SQL Server.
Features and elements
– All data is conceptually represented as an ordered arrangement of data in rows and columns, called a relation or table.
– Each table must have a header and a body. The header is simply the list of columns. The body is the data set that fills the table, organized in rows.
– All values are scalars. That is, at any given row/column position in the table, there is only one unique value.
-Items
The following figure shows a table with the names of its basic elements, which make up a complete structure.
tuple
Each row of data is a tuple, also known as a record. Each row is an n-tuple, but the «n-» is usually discarded.
Column
Each column of a tuple is called an attribute or a field. The column represents the set of values that a specific attribute can have.
Clue
Each row has one or more columns called the key of the table. This combined value is unique for all rows in a table. This key will uniquely identify each tuple. That is, the key cannot be duplicated. It is called the primary key.
On the other hand, a foreign or secondary key is the field of one table that refers to the primary key of some other table. It is used to reference the parent table.
-Integrity rules
When designing the relational model, you define some conditions that must be met in the database, called integrity rules.
key integrity
The primary key must be unique for all tuples and cannot have the null value (NULL). Otherwise, you won’t be able to uniquely identify the row.
For a key made up of multiple columns, none of those columns can contain NULLs.
Referential integrity
Each value of a foreign key must match a value of the primary key of the referenced or parent table.
A row with a foreign key can only be inserted into the child table if that value exists in a parent table.
If the value of the key changes in the parent table, due to the row being updated or deleted, then all rows in the child tables with this foreign key should be updated or deleted accordingly.
How to make a relational model?
-Collect data
The necessary data must be collected to be stored in the database. These data are divided into different tables.
An appropriate data type must be chosen for each column. For example: integers, floating point numbers, text, date, etc.
-Define primary keys
For each table, a column (or few columns) must be chosen as the primary key, which will uniquely identify each row of the table. The primary key is also used to reference other tables.
-Create relationships between tables
A database consisting of independent and unrelated tables serves little purpose.
The most crucial aspect in designing a relational database is identifying the relationships between the tables. The relationship types are:
one to many
In a “Class List” database, a teacher can teach zero or more classes, while a class is taught by only one teacher. This type of relationship is known as one-to-many.
This relationship cannot be represented in a single table. In the «Class List» database, you can have a table called Teachers, which stores information about the teachers.
To store the classes taught by each teacher, you could create additional columns, but you would face a problem: how many columns to create.
On the other hand, if you have a table called Classes, which stores information about a class, you could create additional columns to store information about the teacher.
However, because a teacher can teach many classes, their data would be duplicated in many rows of the Classes table.
design two tables
Therefore, two tables need to be designed: a Classes table to store information about classes, with Class_Id as the primary key, and a Teachers table to store information about teachers, with Teacher_Id as the primary key.
The one-to-many relationship can then be created by storing the primary key from the Master table (Maestro_Id) in the Classes table, as illustrated below.
The Maestro_Id column in the Classes table is known as a foreign key or secondary key.
For each Maestro_Id value in the Maestro table, there can be zero or more rows in the Classes table. For each Clase_Id value in the Classes table, there is only one row in the Masters table.
many to many
In a «Product Sale» database, a customer’s order may contain multiple products, and a product may appear in multiple orders. This type of relationship is known as many-to-many.
You can start the “Product Sales” database with two tables: Products and Orders. The Products table contains information about products, with productID as the primary key.
On the other hand, the Orders table contains the customer’s orders, with orderID as the primary key.
The ordered products cannot be stored inside the Orders table, since it is not known how many columns to reserve for the products. You also can’t store orders in the Products table for the same reason.
To support a many-to-many relationship, you need to create a third table, known as a join table (OrderDetails), where each row represents an item from a particular order.
For the OrderDetails table, the primary key consists of two columns: orderID and productID, uniquely identifying each row.
The orderID and productID columns in the OrderDetails table are used to reference the Orders and Products tables. Therefore, they are also foreign keys in the OrderDetails table.
One by one
In the “Product Sale” database, a product may have optional information, such as additional description and its image. Keeping it inside the Products table would create a lot of empty spaces.
Therefore, another table (ProductExtras) can be created to store the optional data. A record will only be created for products with optional data.
The two tables, Products and ProductExtras, have a one-to-one relationship. For each row in the Products table, there is a maximum of one row in the ProductExtras table. The same productID must be used as the primary key for both tables.
Advantages
structural independence
In the relational database model, changes to the database structure do not affect access to the data.
When it is possible to make changes to the structure of the database without affecting the ability of the DBMS to access the data, structural independence can be said to have been achieved.
conceptual simplicity
The relational database model is even more conceptually simple than the hierarchical or network database model.
Since the relational database model frees the designer from the details of physical data storage, designers can focus on the logical view of the database.
Ease of design, implementation, maintenance and use
The relational database model achieves both data independence and structure independence, which makes database design, maintenance, administration, and use much easier than the other models.
Ad-hoc query capability
The presence of a very powerful, flexible and easy to use query capability is one of the main reasons for the immense popularity of the relational database model.
The query language of the relational database model, called Structured Query Language or SQL, makes ad-hoc queries a reality. SQL is a fourth generation language (4GL).
A 4GL allows the user to specify what should be done, without specifying how it should be done. Thus, with SQL, users can specify what information they want and leave the details of how to get the information up to the database.
Disadvantages
hardware expenses
The relational database model hides the complexities of its implementation and the details of the physical storage of user data.
To do this, relational database systems require computers with more powerful hardware and data storage devices.
Therefore, the RDBMS needs powerful machines to run smoothly. However, as the processing power of modern computers is increasing at an exponential rate, the need for more processing power in today’s scenario is no longer a big problem.
Ease of design can lead to bad design
The relational database is easy to design and use. Users do not need to know the complex details of physical data storage. They don’t need to know how the data is actually stored to access it.
This ease of design and use can lead to the development and implementation of very poorly designed database management systems. Since the database is efficient, these design inefficiencies will not come to light when the database is designed and when there is only one…