logo for asktheoracle.net
Home
What's new?
Oracle Tutorials
PL/SQL
SQL
Oracle Tips & Tricks
Advanced Tutorials
Certification
Oracle 10g
Oracle 11g
Oracle and .Net
Oracle Tools
Disclaimer
Privacy Policy
Search This Site
Oracle Questions?
leftimage for asktheoracle.net

Expand Your Oracle Knowledge With Our Special Oracle Tutorials For Beginners

This is the first in a series of Oracle tutorials the aim of which is to provide a quick introduction to Oracle for beginners.
This tutorial explains the theory behind relational databases with a few examples so you can see how relational databases work, plus a brief discussion of object-oriented design as it applies to databases.

Other articles in this series of Oracle tutorials will expand on these concepts, consider the performance of sql queries and examine the Oracle database from the point of view of the dba.

What is an Oracle database?

Oracle is an object-relational hybrid database which means that is a relational database with added object-oriented features. T

This may seem to be a contradiction in terms but Oracle has successfully managed to combine the two different technologies and new features are added and enhancements made with each new release to improve reliability, security and scalability.

We'll be examining some of these new features in later Oracle tutorials.

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.

What is a Relational Database ?

A relational database is based on the concept of normalisation - the separation of the logical data model from the physical. In other words, the way you "see" the database may be completely different to the underlying structure.

This separation of the logical and physical models makes relational databases extremely flexible - you can change either layer without affecting the other. This means if the business changes and new data structures are required you don't have to re-design the whole database.

A relational database can be regarded as a set of 2-dimensional tables (known as "relations" in relational database theory). Each table has rows (known as a "tuples") and columns ("domains") and 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 very simple database with just 2 tables:

  • emp (employees)
  • dept (departments)

emp has the following columns :

  • id
  • name
  • dept_id

dept has these columns :

  • id
  • name
The relationship between these tables exists because emp has a column called dept_id which is the same as the department id stored in dept.

In other words dept_id in emp relates to id in dept, which makes sense because this enables you to see for which department an employee works - especially useful if you have 2 or more employees with the same name.

Relational databases are based on relational calculus (set theory) which enables relations, also known as sets, (i.e. database tables) to be combined in various ways:

  • the result of the union of 2 sets is those elements that exist in either set;
  • the join/intersection of 2 sets comprises only those elements that exist in both sets;
  • the exclusive "OR" of 2 sets produces those items that are in either of the sets but not both
  • an outer-join includes the join of 2 sets but also includes items from one or both sets that are not in the other set (depending on whether a full outer-join or a left or right outer join is performed)
  • one set can also be subtracted from another to leave only elements from the first set that are not in the 2nd set

Let's move on from definitions to look at a few examples in the rest of this Oracle tutorial.

The use of the following data will help to make the examples more concrete:

Product

Id Name Price
1 Oracle tutorials 3
2 SQL tutorial 22
3 PL/SQL tutorial 43
4 UNIX tutorial 24
5 Oracle fundamentals 160
6 Advanced Oracle SQL tutorial 43

Order

Id Prod_id
1 1
2 4
3 6

The join of Product and Order on the product id would produce the following result:

Prod_Id Name Price Ord_Id
1 Oracle tutorials 3 1
4 UNIX tutorial 24 2
6 Advanced Oracle SQL tutorial 43 3

To achieve the union of Ord and Prod we have to cheat a little because the number and data types of the columns must be the same.

For this reason, "union" is not normally used to combine different tables in their entirety but instead to combine sub selections from one or more tables.

The following query:
Select prod_id,'dummy',ord_id from ord
UNION
Select prod_id,name,price from prod

would produce the following results

PROD_ID DUMMY ORD_ID
1 dummy 1
2 dummy 4
3 dummy 6
1 Oracle tutorial 3
2 SQL tutorial 22
3 PL/SQL tutorial 43
4 UNIX tutorial 24
5 Oracle fundamentals 160
6 Advanced Oracle SQL tutorial 43

The result of that query may not be particularly useful but it does show how the UNION command works. There are of course other operators and variations which we will be looking at those in later Oracle tutorials.

What is an object-oriented database ?

Object-oriented databases, as the name suggests, store and manage objects. Confusingly, Oracle has had "objects" in the database probably since the beginning as a generalisation of tables, views, indexes etc. , but in the context of object-oriented programming (and databases) an object is something that has both attributes (properties) and methods (programs stored with the object each of which performs a certain action or task). In a true object-oriented database would also belong to a class and would allow multilevel inheritance.

The versions of Oracle since Oracle 8 are object-relational hybrids because they support both relational and some object-oriented features. The relational features dominate at the moment, but this is changing as the industry begins to learn how to use the new technologies and as Oracle improves the object-oriented features with each new release.

Continue this tutorial with a look at basic sql commands, built-in Oracle data types, user-defined types and sql aggregate functions.

One of the best ways to learn more about Oracle is to attend a formal training course. Smartsoft Computing offer Oracle training in the UK both on and off-site as well as expert Oracle training and consultancy in New Zealand .