[DATABASE] Relational Database feat. Oracle

What is a relational database?

As mentioned before, a relational database is based on the separation and independence of the he logical and physical representations of the data. This provides enormous flexibility and means you can store the data physically in any way without affecting how the data is presented to the nd user. The separation of physical and logical layers means that you can change either layer ithout affecting the other.

A relational database can be regarded as a set of 2-dimensional tables which are known as relations" in relational database theory. Each table has rows ("tuples") and columns "domains"). The relationships between the tables is defined by one table having a column with the same meaning (but not necessarily value) as a column in another table.

For example consider a database with just 2 tables :

emp(id number
,name varchar2(30)
,job_title varchar2(20)
,dept_id number)

holding employee information and

dept(id number
,name varchar2(30))

holding department information.

There is an implied relationship between these tables because emp has a column called dept_id which is the same as the id column in dept. In Oracle this is usually implemented by what's called a foreign-key relationship which prevents values being stored that are not present in the referenced table.

Relational databases obtain their flexibility from being based on set theory (also known as relational calculus) which enables sets or relations to be combined in various ways, including:

• join/intersection
• union (i.e. the sum of 2 sets);
• exclusive "OR" (i.e. the difference between 2 sets)
• and outer-join which is a combination of intersecting and exclusive or ing

The intersection or join between 2 sets (in this case, tables) produces only those elements that exist in both sets.

Therefore, if we join Emp and Dept on department id, we will be left with only those employees who work for a department that is in the dept table and only those departments which have employees who are in the emp table.

The union produces the sum of the tables - meaning all records in Emp and all records in Dept. and this may be with or without duplicates.

Let's use the following data to provide specific examples:

Emp

Id	Name	Dept Id
1	Bill Smith	3
2	Mike Lewis	2
3	Ray Charles	3
4	Andy Mallory	4
5	Mandy Randall	6
6	Allison White	1

Dept

Id	Name
1	HR
2	IT
3	Marketing
4	Sales
5	Finance

The join of Emp and Dept. on the department id would produce the following result:

Emp.Id	Emp.Name	Dept.Id	Dept.Name
1	Bill Smith	3	Marketing
2	Mike Lewis	2	IT
3	Ray Charles	3	Marketing
4	Andy Mallory	4	Sales
6	Allison White	1	HR

The union of Emp and Dept. would produce the following results

Id	Name
1	Bill Smith
2	Mike Lewis
3	Ray Charles
4	Andy Mallory
5	Mandy Randall
1	HR
2	IT
3	Marketing
4	Sales
5	Finance

The union operator is only allowed when the number and data types of the columns in the 2 sets are the same. It is not normally be used to combine sub sections from one or more tables rather than entire tables.

There are other operators and variations but there isn't the space or the time to provide full details in this short Oracle tutorial.

The later versions of Oracle (Oracle 8 onwards) support both relational and object-oriented features. The relational features are more prominent at the moment, but this is beginning to change. In this context an object has both attributes and methods (programs stored with the object that performs a certain action or task) and in a true object-oriented database would belong to a class and would allow multilevel inheritance.

Source : A Short Oracle Tutorial for Beginners