Database Experts oracle | mysql | postgre | mssql | db2

Short Oracle Tutorial For Beginners

Introduction

This is just a brief Oracle tutorial for beginners, to provide a short history of databases and Oracle’s role in them, explain relational theory and give you an idea on how relational databases work with a few examples. There is also a very brief discussion of object-oriented design as it applies to databases.

Time and space don’t permit an in-depth discussion of all the features available in Oracle, but if you would like to learn more just contact us and ask for our free Oracle tutorial mini-course.

History of Databases – From Trees To Objects

The storage and management of data is probably the biggest headache for all businesses. It has been so for a long while and is likely to continue for a long while too. As companies try to store more and more details about their customers and their buying habits and as regulatory requirements for storing more data for longer, so companies will need to store and manage more and more data. The only way this can be done at a reasonable cost is by the use of computers.

In the late 1960s/early 1970s, specialised data management software appeared – the first database management systems (DBMS). These early DBMS were either hierarchical (tree) or network (CODASYL) databases. These early systems were very complex and inflexible and so it adding new applications or reorganising the data was very difficult and time-consuming.

In 1970 the relational data model was defined by E.F. Codd (see “A Relational Model of Data for Large Shared Data Banks” Comm. ACM. 13 (June 6, 1970), 377-387). This delivered a solution to the problems of tree and network databases due to the concept of normalisation which involves the separation of the logical and physical representation of data.

In 1974 IBM started a project called System/R to prove the theory of relational databases. This led to the development of a query language called SEQUEL (Structured English Query Language) later renamed to Structured Query Language (SQL) for legal reasons and now the query language of all databases.

In 1978 a prototype System/R implementation was evaluated at a number of IBM customer sites. By 1979 the project finished with the conclusion that relational databases were a feasible commercial product.

IBM’s research into relational databases had also come to the attention of a group of engineers in California who were so convinced of the potential that they formed a company called Relational Software, Inc. in 1977 to build such a database. Their product was called Oracle and the first version for VAX/VMS was released in 1979, thereby becoming the first commercial rdbms, beating IBM to market by 2 years.

In the 1980s the company was renamed to Oracle Corporation. Throughout the 1980s, new features were added and performance improved as the price of hardware came down and Oracle became the largest independent rdbms vendors. By 1985 they boasted of having more than 1000 installations.

As relational databases became accepted, companies wanted to expand their use to store images, spreadsheets, etc. which can’t be described in 2-dimensional terms. This led to the Oracle database becoming an object-relational hybrid in version 8.0, i.e. a relational database with object extensions, enabling you to have the best of both worlds.

What is a relational database?

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:

* 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:

Dept

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

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

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.