Relational database is rooted in theory of normalization, where in both the physical and the logical data are separate. The advantage of RDBMS is that it gives a lot of flexibility in terms of storage of the data. The data can be stored physically and the output given to the user will not be affected.
Relational database is considered a group of two dimensional tables known as relations. Every table consists of rows known as tuples and columns known as domains. The relationship among both tables are made with one column of one table, having the same meaning in the other table, it does not refer to having same value.
Let’s take an instance, there are two tables one which holds the employee details like Emp ID, Emp name and Emp Department ID and the other table holding details of the department like Emp department ID and Department name. There is a direct relationship amongst both tables, the employee table contains Emp department ID and the department table contain the Emp Department ID. This is how the concept of foreign key is explained in Oracle, this way the values are prevented from inclusion if they were absent in the referenced Key. For instance if in the Department table there does not exist a department name “123”, then this can’t be used in the Emp details too.
On the basis of Set Theory, is where the relational databases get their flexibility? This allows relations to be made or combined in different ways which included usage of Join/intersection, usage of Union (which is nothing but sum of two sets), usage of OR (pertains to two different sets), usage of Outer Join.
SQL join and union
In join or intersection, the result that is produced is available only if it is available in both the sets. For instance if you need inputs on the number of employees working in “123” dept, you will get the data only and only if there are employees are working in 123 department and there exists a department named 123.
The next one, union, provides the sum of tables. For instance in this case it will provide details of all records in the employee table and details of all departments. The output can contain duplicates too. Please note that the union operator can only be used if the number and the data types of each variable in the sets are the same.
With the advent of object-oriented features, Oracle also changed. The Oracle eight version started supporting both relational database as well as object-oriented features. At this point of time, the features that are relational are of more importance than the Object-oriented features, however; we are at a point of time, where thing will change very fast. The use of Object-oriented features will soon gain momentum. An object has attributes as well as methods. Methods are nothing but programs which are stored along with an object meant to do a certain task or action. The use of Object-Oriented programs will let us allow usage of multilevel inheritance.