Relational Database Concepts (Relational Algebra http://spaceprogrammer…
Relational Database Concepts
If you're looking for the most essential part or the very center of something, you're looking for its core.
A table is a collection of related data held in a table format within a database.
a row, also called a tuple, represents a single, implicitly structured data item in a table.
A column is a set of data values of a particular simple type, one value for each row of the database.
Result set is a set of rows from a database, as well as metadata about the query such as the column names, and the types and sizes of each column.
A database is an organized collection of data, generally stored and accessed electronically from a computer system.
This model organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row.
A one-to-one relationship exists when one row in a table may be linked with only one row in another table and vice versa.
A one-to-many relationship exists when one row in table A may be linked with many rows in table B, but one row in table B is linked to only one row in table A.
A many-to-many relationship refers to a relationship between tables in a database when a parent row in one table contains several child rows in the second table, and vice versa.
Each row in a table has its own unique key (Primary key). Rows in a table can be linked to rows in other tables by adding a column for the unique key of the linked row (such columns are known as foreign keys).
Set theory is a branch of mathematical logic that studies sets, which informally are collections of objects.
the power set is the set of all subsets with the set of all functions results of two or more elements
Structured Query Language (SQL)
Standard data management language that interacts with major relational model-based databases.
Data Definition Language (DDL)
Commands that can be used to define the database schema.
Example: CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME
DML(Data Manipulation Language (DML)
Commands that deals with the manipulation of data present in database
Example: SELECT, INSERT, UPDATE, DELETE
Data Control Language (DCL)
Commands which mainly deals with the rights, permissions and other controls of the database system.
Example: GRANT, REVOKE
Transaction Control Language (TCL)
Commands deals with the transaction within the database.
Example: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
Top Relational Database Management Systems (RDBMS)
Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM Db2, Microsoft Access, SQLite, MariaDB, Hive, Teradata
Selection, as its name implies, selects rows, tuples by a given condition.
the and of a set of operands is true if and only if all of its operands are true.
Or: ∨ or +
the or of a set of operands is true if and only if one or more of its operands is true.
Not: ¬ or -
that produces a value of true when its operand is false and a value of false when its operand is true.
Projection is used when there is a need to take only columns of interest in a relationship, and not work with all columns of that relationship.
The union between two relations AUB, brings in a new relation C with all existing tuples in A and B, without repetition of tuple.
The intersection of two relations A∩B brings a new relation C containing the tuples, lines in common, that is, that exist in both relations.
Set Difference: -
The difference between two relations A - B, brings a new relation C with tuples that exist in A but do not exist in B, that is, that only exist in A. Of the same, equivalent to B - A, brings a new relationship with tuples that exist only in B, so order is important.
Cartesian Product: ×
The Cartesian product between two relations A X B brings a new relation C that contains all fields, columns that A and B contains, and the combination of each tuple of A with each tuple in B.
Right after making a Cartesian product in a relationship, there is usually a need to make a selection in which the fields that are in A and also in B are the same, because the Cartesian product brings tuples that are not of interest. The join is the operation that simplifies it all. She makes a Cartesian product, then a selection of tuples of interest and finally a projection to remove duplicate columns.