Getting Started with Oracle part-3

So far, we have learned where to get the Oracle Database software and how to install. We will be learning all the topics in detail as mentioned in the article "Getting Started with Oracle Part-2". We will be providing the links to external site where it's appropriate, please follow the topics in the order they are published.

Discuss

Overview of Relational Databases, SQL and PL/SQL:

A little background on the evolution of databases and database theory will help you understand the workings of SQL. Database systems store information in every conceivable business environment. From large tracking databases such as airline reservation systems to a child's baseball card collection, database systems store and distribute the data that we depend on. Until the last few years, large database systems could be run only on large mainframe computers. These machines have traditionally been expensive to design, purchase, and maintain. However, today's generation of powerful, inexpensive workstation computers enables programmers to design software that maintains and distributes data quickly and inexpensively.

A relational model organizes DATA into TABLES and only TABLES.A row and column intersection is called a "cell" The columns are placeholders, having data types such as character or integer.The rows themselves are the data.

A Relational table must meet the following criteria:

1. The data stored in the cells must be atomic. Each cell can only hold one piece of data.When a cell contains more than one piece of information this is known as information coding
2. Data stored under columns must be of the same data type
3. Each row is unique (No duplicate rows)
4. Columns have no order in them
5. Rows have no order in them
6. Columns have a unique name
7. Two fundamental integrity rules:
   1. Entity Integrity rule :States that the primary key cannot be totally or partially empty.
   2. Referential Integrity rule : States that the foreign key must either be null or match currently existing value of the primary key that it references.

The major objective of physical design is to eliminate or at least minimize contention .

Follow these rules to avoid contention :

1. Separate Tables and Indexes
2. Place large Tables and Indexes on disks of their own
3. Place frequently joined tables on separate disks, or cluster them.
4. Place infrequently joined tables on the same disks if necessary (if your short on disks)
5. Separate the RDBMS software from tables and indexes.
6. Separate the Data Dictonary from tables and indexes.
7. Separate the (undo) rollback logs and redo logs onto their own disks if possible.
8. Use RAID 1 for undo or redo logs
9. Use RAID 3 or 5 for Table Data.
10. Use RAID 0 for indexes.

Oracle helps with the problem of object-orientated developement and RDBMS back-end situation , with the following built-in object-orientated capabilities:

1. Relationships as Datatypes
2. Inheritance
3. Collections as Datatypes, including nesting (containers)
4. User-defined (extensible) datatypes
5. Improved large objects (LOBs)

Oracle extended the already complex RDBMS with the following:

1. Object Types: Records or classes
2. Object Views :
3. Object Language: Extensions to the Oracle SQL and PL/SQL
4. Object APIs : Objects supported through Oracle precompilers PL/SQL, OCI.
5. Object Portability : Through the object type translator (OTT) which can port for example an Oracle8 object type to a C++ class.

Also user defined datatypes can be built on any of the built-in datatypes plus previously user-defined datatypes. When creating user-defined datatypes these can be used :

1. As a column of a relational table
2. As an attribute within another object type.
3. As part of an object view of relational tables.
4. As the basis for an object table.
5. As the basis for PL/SQL variables.

Describe the use and benefits of PL/SQL

PL/SQL is a Procedural Language extension to Oracle's version of ANSI standard SQL. SQL is non-procedural language , the programmer only describes what work to perform. How to perform the work is left to the "Oracle Optimizer", in contrast PL/SQL is like any 3GL procedural language, it requires step by step instructions defininig what to do next. PL/SQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL. This results in a robust, powerful language suited for designing complex applications.

Please download these CBT Tutorials(PDF Format) : There are related Oracle 9i, there is not much difference between 8i and 9i with the Database concepts.

This module introduces the basic concepts of Relational Databases and the architecure of Oracle Database.

Discuss this in our Forums