When using the relational model, we can use 2 sets of operations. Relational algebra is one of them.
When usingRelational model, We can use 2 sets of operations.
The first one is calledRelational algebra,This is anprogramming language.
This is the foundation of SQL, so learning is very important-because SQL is the de facto standard for using relational databases.
The second one is calledRelational calculusNot proceduralDeclarative language. This is the fundamental difference in the way we interact with the database, because you will not tell the database softwarewhat to do, You just have to say itwhat do you want, And then sort out the specific operation method.
Language like thisdata record,Qualified Education BureauwithQuillBased on relational calculus. I am not going to discuss this issue because it is a more niche approach than the more practical approach adopted by SQL, but you can check it out as needed.
With this introduction, let's continueRelational algebra.
We have 2 types of operations:
- Main operation
- Franchise action
Main operations in relational algebra
The main operations are:
- allianceAs long as the two tables have the same column and attribute type (domain), you can get data from the two tables and generate the sum of tuples.
- the differenceAs long as the two tables have the same column and attribute type (domain), you can get the data contained in the first table but not in the second table, resulting in a difference in tuples.
- Cartesian ProductObtain the data in the two tables according to the attribute value and generate a single table that combines their data.
- selectOnly certain tuples (rows) contained in the table are extracted based on certain conditions.
- projectGenerate a new table containing only one or more attributes (columns) of an existing table
- RenamedUsed to rename attributes to prevent conflicts when multiple tables have the same name on different data
Join operations in relational algebra
Connection is probably the most powerful operation that relational algebra can perform. They are built on the main operation and allow you to correlate data contained in different relationships (tables).
Note: I will soon discuss the connection of DBMS in practice, which is mainly theoretical.
We have 2 main addition versions:Natural connectionwithθ connection. All the other versions are extracted from those 2.
Naturally joinAssociate two relationships (tables) and create a new table based on the same value of the attribute.
First, we need two relationships with the same attribute name (column). Then, if the attribute value in Relation A does not match the attribute in Relation B, the row is not part of the result and the row will be ignored.
|Manager's name||Employee ID|
We can perform natural connection to get the name of each employee's boss:
|Employee ID||Name||Manager's name|
Since these relationships have a common Employee ID attribute name, they only appear once in the results, not twice.
Since there is no corresponding entry in relationship B, employee #3 Rick who exists in relationship A is not included in this table.
Theta-join allows to perform joins based on any conditions to compare two columns in two different relationships, not just for equality like natural joins.
It performs the Cartesian product of two tables and filters the results based on the choices we want to make.
Equivalent joins are theta joins, where selection is based on the equality between attribute values in two different tables.
The difference with natural join is that we can choose the attribute name (column) to compare.
We will talk more about joins when we introduce SQL later, so we can use them in practice.
More database tutorials:
- MongoDB basic tutorial
- How is MongoDB different from SQL database
- Information system, data and information
- Data model
- Introduction to ER data model
- Relational model
- Relational algebra
- relational database
- What is a database? There is also a DBMS?
- Introduction to SQL
- How to install PostgreSQL on macOS
- Do you always need to provide a database for the application?
- How to install SQLite on macOS
- Introduction to PostgreSQL
- PostgreSQL user permissions
- How to list all users in PostgreSQL
- How to switch database using PostgreSQL
- How to list all databases using PostgreSQL
- How to use PostgreSQL to list the tables in the current database
- How to install MySQL on macOS
- Create user on MySQL
- PostgreSQL vs MySQL, comparison
- SQLite user permissions
- MySQL user permissions
- SQL, create table
- SQL, add data to the table
- SQL, how to use SELECT
- SQL, handle empty cells
- SQL, unique key and primary key
- SQL, how to update data
- SQL, how to update the table structure
- SQL, how to delete data and tables
- SQL connection
- SQL view
- How to insert multiple items in a MongoDB collection at once
- How to delete all items from MongoDB collection