Relational Data Modeling for Database Systems Part – 3

Relational Languages

So far in this series of the article the database structure have been considered, along with the relations or tables as well as the associations among the tables or relations. Currently, in this part of the article we will consider the different tactical methods to use for extracting the information as well as to filter them after the retrieval from database tables or relations. A relational language is desired to express the T-Sql queries in a precise method. A relational language is a theoretical language that offers the database end user with a graphical user interface (GUI) by which they can state which information need to be recovered giving some selection conditions. The two (2) key relational languages are relational algebra as well as relational calculus. Relational algebra, which will be focused in this article, lets the end user with a group of operators which might be castoff to form a fresh (provisional) relation or table grounded on the data present in the current tables or relations. Relational calculus, on the other hand, offers a group of keywords to let the end user for making extemporized queries.

Relational Algebra

Relational algebra is a technical language containing a group of different operators. Every single operator takes one (1) or more (N) tables or relations as its input as well as yields only one (1) table or relation as its output. There are seven (7) rudimentary relational algebra operations present, which are

1. Selection,

2. Projection,

3. Joining,

4. Union, (Part – 4)

5. Intersection, (Part – 4)

6. Difference (Part – 4) and

7. Division (Part – 4)

It is significant to remember that these above mentioned operations do not modify the database tables or relations. The table or relation formed by means of an operation is obtainable to the end user but then again it is not kept permanently in the database by means of the operation.

Selection (Known as Restriction)

The SELECT operator chooses every rows or tuples from a specific table or relation, for the reason that a number of columns or attributes in every single row or tuple fulfill certain situation. A novel table or relation having the particular rows or tuples is then formed as output. Assume the table or relation named – EMPLOYEE:

The relational operation will be as following:

R₁ = SELECT * FROM EMPLOYEE WHERE Department_ID = ‘Dep_15’

This above syntax will select every row or tuple form EMPLOYEE table or relation which has the Department_ID as ‘Dep_05’ as well as forms the fresh table or relation, R₁, that seems as follows:

Now, an individual can also force selection conditions on more than one column or attribute. For an instance,

R₂ = SELECT * FROM EMPLOYEE WHERE Department_ID = ‘Dep_05’ AND Working_Hours = 09

This above syntax will select every row or tuple form EMPLOYEE table or relation which has the Department_ID as ‘Dep_05’ and Working_Hours as ‘09’ as well as forms the fresh table or relation, R₂, that seems as follows:

Projection

The PROJECT operator hypotheses a fresh table or relation from a number of present table or relation by means of choosing quantified columns or attributes of the present table or relation as well as removing identical rows or tuples in the freshly shaped table or relation. For an instance,

R₃ = PROJECT EMPLOYEE OVER Department_ID, Name

This above syntax will fetch result as follows:

Joining

The JOINING is an operator for merging two (2) or more (N) tables or relations into a sole table or relation. In the beginning, it necessitates selecting the columns or attributes to match the rows or tuples in every single table or relation. Rows or tuples in dissimilar tables or relations but then again with the identical data of corresponding columns or attributes are joined into a solo row or tuple in the resulting table or relation.

For an instance, with a fresh table or relation DEPARTMENT:

­And the earlier table or relation EMPLOYEE:

Now, if an individual combined DEPARTMENT to EMPLOYEE by means of the operator:

R₄ = JOIN EMPLOYEE, DEPARTMENT OVER Department_ID

The resultant table or relation R₄ would look like as follows:

The above table or relation is resulted from a joining of DEPARTMENT and EMPLOYEE over the shared column or attribute Department_ID, that is any rows or tuples of every single table or relation which has the identical data of Department_ID were linked together to create a sole row or tuple.

Joining tables or relations together grounded on equivalence of data of shared columns or attributes is known as an EQUIJOIN. Conditions of join might be further than equivalence – an individual might have a ‘greater-than’ otherwise ‘less-than’ joins too.

At the time when identical columns or attributes are deleted from the resultant of an EQUIJOIN then that is known as a NATURAL JOIN. The instance above mentioned is like a NATURAL JOIN as Department_ID appeared only once in the resultant table or relation.

It should be noted that there is frequently an association among the keys (Primary as well as Foreign) in addition the columns or attributes on which a join is executed in order to merge the data from several associated tables or relations in a database. In the above mentioned instance, EMPLOYEE. Department_ID is a Foreign Key (FK) imitating the Primary Key (PK) DEPARTMENT. Department_ID. When an individual join on Department_ID the association among the tables or relations is articulated explicitly in the resultant output table or relation. To demonstrate, the association among these relations or tables, it can be communicated as an Entity Relationship (ER) illustration, as presented below.

In the upcoming part we will be discussing the remaining relational algebras which are Union, Intersection as well as Difference, Division and the Relational Database Management Systems (RDBMS).