As mentioned before, a relational database is based on the separation and independence of the the 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 end user. The separation
of physical and logical layers means that you can change either layer without 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:
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.
Part 1 of this Oracle tutorial is available here
Looking for more Oracle tips? Suggestions for further reading, book reviews and links to other resources for information about Oracle are available from the resources section. For links to other articles and tutorials on SQL, PL/SQL and Oracle go to Oracle Tips & Tricks.
---------------------------------------
Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective, proven method of boosting productivity. Click here for details of our scheduled Oracle training courses or let us know your requirements.
Oracle secrets revealed! Subscribe to our ezine, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and incrase your return on investment. Subscribe today and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd
22 Logan Road, Bristol BS7 8DT
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.