Databases are an essential part of the modern living. Every technology we use in our everyday life relies on data, and relational databases are the most common way of keeping that data structured.

In the early years of databases, every application stored data in its own structure. When that data had to be used by a different application, the developers were required to analyze the structure in order to find the data they needed. This was a pretty inefficient way, and hard for maintenance and optimization. The relational database model was then designed to solve this problem

The relational data model provided a standard way of representing and querying data that could be used by any application. The advantage in the relational database model is in its use of tables, which are an intuitive, efficient, and flexible way to store and access structured information. One aditional advantage and strength of the relational model which emerged over time is the structured query langage (SQL), which based on relational algebra, provides a consistent mathematical language that makes it easier to improve the performance of the queries.

Entities and relations

The relational database model relies on entities and the relations between them.

Entities are specific objects or things in the real world that are being represented in the database.

Example: Employee John Smith, project ProductX, Development department, etc...

Attributes are some properties that are being used to describe the entity.

Example: The entity EMPLOYEE can have attributes: Name, SSN, Address, Sex, BirthDate

Every entity has values for every attribute defined.

Example: An Employee can be defined as: Name='John Smith', SSN='123456789', Address ='731, Fondren, Houston, TX', Sex='M', BirthDate='09- JAN-55‘

Every attribute has a value set or a data type that is associated with it. They can be simple, composite or multi-valued.

Example: Data type: Integer, string, subrange, enumerated type, etc... Multi-valued: An entity can have multiple values for a given attribute, like COLOR of a CAR.

Some attributes, that have a different value for each entity, can be defined as a key attribute of that entity. They can be simple or composite, and one entity can have multiple keys.

In the following diagram, we can see an example of a relational diagram for the entity CAR. The key attributes are underlined, and the multi-valued one is with double circles.

The relations between certain entities can be of different types, and they can be:

• 1:1 one to one
• 1:N one to many
• N:1 many to one
• M:N many to many

Example: Employee - Department relationship has a N:1 relation. Meaning many employees can work for a single department.

Relational data model

The Relational Data Model uses the mathematical concept of a relation as the formalism for describing and representing data. The relations are represented as a table of values and have sets of rows, which represent certain facts about an entity or a relation in the real world. Each column has a header that represents the significance of the elements in that column.

Relational schemas are formally described as a k-ary relation schema R(A1, A2, , AK), which is a set A1, A2, , Ak of k attributes.

Example:
COURSE(course-no, course-name, term, instructor, room, time)

CITY-INFO(name, state, population)

Thus, a k-ary relation schema is a blueprint, a template for some k-ary relation.

A relational database schema is a set of relation schemas Ri (A1, A2, , Aki ), for 1 ≤ i ≤ m.

Example:

UNIVERSITY relational database schema :

STUDENT(student-id, student-name, major, status)

FACULTY(faculty-id, faculty-name, dpt, title, salary)

COURSE(course-no, course-name, term, instructor)

ENROLLS(student-id, course-no, term)

There can be certain constraints in the relational schema, and they can be:

• Key constraints - eg. one key can be a primary key, a candidate key, or a superkey
• Entity integrity constraints - eg. the primary key cannot have null values
• Referential constraints - eg. the outer key is a key that connects two entities, so they can be joined by that key

Relational algebra

In database theory, relational algebra is a theory that uses algebraic structures with well-founded semantics for modeling data, and defining queries on it. The main premise of relational algebra is to define operators that transform one or more input relations to an output relation.

A relational algebra expression:

• takes as input one or more relations
• applies a sequence of operations
• returns a relation as output

There are a few defined operations in relational algebra, that are associated with their own symbols from the Greek alphabet.

1. Projection (π) - π set of attributes(relation) Chooses some of the columns, for example : π A1,...,An ( R ) takes only the values of attributes A1, . . . , An for each tuple in R

2. Selection (σ) - σ condition(relation) Chooses rows satisfying a given condition, for example: σθ ( R ) takes only the tuples in R for which θ is satisfied

1term := attribute | constant 
2θ := term op term with op ∈ {=, 6=, >, <, >, 6} | θ ∧ θ | θ ∨ θ | ¬θ

If we do not have attribute names (hence, we can only reference columns via their component number), then we need to have a special symbol, say $, in front of a component number. Thus, $4 > 100 is a meaningful basic clause $1 =“Apto” is a meaningful basic clause, and so on. Consecutive selections can be combined into a single one:

1σθ1 σθ2 ( R )  =  σθ1 ∧ θ2 ( R )

3. Product (×) - RxS Concatenates each tuple of R with all the tuples of S

4. Renaming (ρ) - ρ replacements(relation), Gives a new name to some of the attributes of a relation, where a replacement has the form A → B Example:

1σCustID = CustID'(Customer × ρCustID→CustID'(Account))

5. Union (∪) - R ∪ S A binary operation whose result is a relation including the elements belonging to R, or to S, or both to R and S. The duplicates are removed. Example: Find the SSN of all the employees in department 5, or are supervising an employee in department 5.

1DEP5_EMPS ← σDNO=5 (EMPLOYEE) 
2RESULT1 ← π SSN(DEP5_EMPS) 
3RESULT2 ← πSUPERSSN(DEP5_EMPS) 
4RESULT ← RESULT1 ∪ RESULT2

6. Intersection (∩) - R ∩ S The result of intersection is a relation that has all the common elements of R and S.

7. Difference (−) - R-S SET DIFFERENCE (can be named also MINUS or EXCEPT) is a difference between the two sets. The result is a relation that has all the elements that exist in R, but do not exist in S.

Union and Intersection are commutative operations:

1R ∪ S = S ∪ R, and R ∩ S = S ∩ R

Union and intersection are also associative operations:

1R ∪ (S ∪ T) = (R ∪ S) ∪ T 
2(R ∩ S) ∩ T = R ∩ (S ∩ T)

Difference is not commutative, thus:

1R – S ≠ S – R

8. Join Join is a derived operation and can be expressed in terms of π , σ , × , ρ. It can be represented as a product, followed by a select, to identify and select related entities from two relations. Join combines this in a single operation. This is one of the most important operations in relational databases because it allows the combination of connected entities.

One Pager Cheat Sheet

• Databases are essential for modern living, making data accessible and efficient to use with the traditional relational database model and its use of `structured query language (SQL).**
• The relational database model relies on Entities, their Attributes, Value Sets, Key Attributes and the different types of Relations between them.
• Entities are real world objects described as sets of attributes, that have associated values and key attributes used for identification and facilitate data representation, organization and retrieval.
• The Relational Data Model uses tables to represent data, consists of relation schemas and constraints, and allows entities to be joined by outer keys.
• The key constraints, entity integrity constraints, and referential constraints help to maintain the accuracy and integrity of the data in the relational schema.
• Relational algebra is a theory that uses algebraic structures with well-founded semantics to model data, define queries and apply operations such as projection, selection, product, renaming, union, intersection and difference to manipulate relations.
• The union of two relations R and S will include elements from both R and S, making this statement false.
• Joining creates a new relation containing elements of both R and S.