ORACLE DATABASE FEATURES

Oracle provides a number of features such as data concurrency and consistency, manageability, backup and recovery, client-server architecture, business intelligence, database security, and data integrity. These features are discussed next.


Data Concurrency and Consistency
Data concurrency is the property which implies that the data can be accessed by multiple users at the same time. Data consistency is a property that ensures that each user can see a consistent view of data, which means the user can view the changes made by him in his own transactions as well as the transactions of other users.


Manageability
Oracle manages the database itself and it tunes the database automatically. Oracle has the following self-managing database features:

1. Automatic undo management.
2. Dynamic memory management.
3. Oracle-managed files.
4. Free space management.
5. Multiple block sizes.
6. Recovery management system.

Backup and Recovery
It is one of the most important features of Oracle. The backup of data is created to protect data in case the system crashes or the data gets corrupt. It consists of files such as datafiles, control files, redo logs files, and so on. There are different types of backups available in Oracle 11g.


Recovery means restoring a backup file into a specified location. In case of crash of database, Database Administrator recovers the data by using the recovery method. 


Client-Server Architecture
The Oracle database allows multiprocessing, which is also known as distributed processing system. In this system, many users access data at the same time. This system reduces the load on the processor as the tasks are assigned to different processors. In the client-server architecture, the database system is divided into two parts: client machine and server machine. 


Business Intelligence
Oracle provides various business intelligence features such as data warehousing, ELT (extraction, transformation, and loading), materialized views, table compression, parallel execution, bitmap indexes in data warehousing, analytic SQL, and so on. 


Database Security
Oracle provides security features for controlling unauthorized access to database. These features include prevention of unauthorized data access, prevention of unauthorized access to schema objects, and storing all user actions. Oracle associates each database user with a schema by the user’s name. By default, each database user creates and has access to all objects in the corresponding schema.


Data Integrity
Data integrity allows you to specify certain rules for the quality of the data, which the data in a database needs to satisfy. If a user inserts data that doesn't meet the specified requirements, Oracle will not allow the user to insert the data. Therefore, it is important that the data adheres to the set of rules determined by the database administrator or the application developer. There are five data integrity constraints in Oracle that enable you to enforce the rules on the data. These constraints are Not Null, Unique Key, Primary Key, Foreign Key, and Check.

INTRODUCTION

A database is a collection of data that is used to store and retrieve related information. The Oracle database is the fourth generation relational database management system, which manages a large amount of data and provides multi-user environment. Thus, multiple users can concurrently access the same data in a database.


Oracle provides several useful features for database developers such as integrity constraints, transaction controls, stored procedures and packages, database triggers, cost-based optimizer, shared SQL, locking, and sequences. Basically, oracle database is divided into two structures: logical and physical. Both the structures are managed separately in Oracle.


The Oracle database is a relational database and is therefore called Relational Database Management System (RDBMS). Any RDBMS must possess certain characteristics, which are as follows:

• Constraints
• Objects
• Operations
• Data Integrity
• Database Normalization

DATABASE NORMALIZATION

Database normalization is defined as the rules that help in building relational database. In the database building process, the redundant data is eliminated and the related data is stored in the table. Basically, this process is used to organize data so that its performance is optimum. The process of normalization involves breaking a single table into two or more tables and defining the relationships between them. The purpose of isolating data in more than one table is to add, delete, and modify fields in one table and then propagate the  data through the rest of the database.


Normalization guidelines are divided into five normal forms:

• 1st Normal Form
• 2nd Normal Form
• 3rd Normal Form
• 4th Normal Form
• 5th Normal Form

First Normal Form
The first normal form describes the basic rules to normalize a database. It follows the rules listed below:

• Remove duplicate columns from a table.
• Segregate tables with a specific group of data.

Consider the following Student table:

StudentID	Course
12345	4100,4200,4300
54321	2100,2200,2300

In Student table, the Course field is a multi-valued attribute and has no single value for any field. Now, consider this Student table:

StudentID	Course1	Course2	Course3
12345	4100	4200	4300
54321	2100	2200	2300

The Course1, Course2, Course3 fields represent repeating groups and to brought it to First Normal Form we will store the data in following ways.

StudentID	Course
12345	4100
12345	4200
12345	4300
54321	2100
54321	2200
54321	2300

In the first two designs of the data, the students enrolled for a certain course is difficult. Say I want to do the following:


Tell me all of the students enrolled in course 4100.  In the first design, you'll have to pull all of the course data and parse it somehow. And in the second design, you'll have to check 3 different fields for course 4100. In the final design, a simple Select StudentID from Student where Course=100.

Second Normal Form
The second normal form is defined to remove duplicate data from a table. These are as follows:

1. It includes all rules of the first normal form.
2. Remove the data from the table that applies to multiple rows and then arrange it in the other table by creating relations.

Third Normal Form

The third normal form is defined to remove the columns that do not depend upon constraints. It has the following rules:

1. It includes all rules of the second normal form.
2. It removes those columns that do not depend upon the Primary keys.

RETATIONAL MODEL

The Relational Model was developed by E. F. Codd in 1969. This model was created based on the concepts of Set theory of Mathematics. The purpose of creating this model was to rearrange the unordered data of a table in an ordered form by creating relations between tables. When a database is designed based on the Relational Model, it is called Relational database. There are many advantages of the relational database, some of which are as follows:

1. Creating a data report becomes easy as the data it consists of is in the ordered form
2. User entry becomes easier as it restricts the entry of illegal data
3. Updation and deletion can be restricted as the model follows some integrity rules
4. Changes can easily be made in a database schema
5. Retrieving and summarizing the data becomes easier

Integrity Rules 

There are certain keys defined in Oracle 11g to follow the integrity rules. These are called constraints. Integrity rules bind a database with different constraints. According to the Relational Model, single row of a table should be unique; otherwise, it will violate the rule. For example, they do not let a duplicate record enter in a table. Some of the important constraints are as follows:

1. Unique
2. Not Null
3. Primary key
4. Foreign key
5. Check

These constraints are discussed next.

Unique

The Unique constraint applied on a column ensures that the column does not accept any duplicate entries. It indicates that every record in that field should be unique. However, it can accept a Null entry.

Not Null

The Not Null constraint applied on a column ensures that does not accept any Null value. You cannot leave a value blank in this column.

Primary Key

The Primary key constraint is a combination of the Unique and Not Null constraints. If the Primary key constraint is applied on a column, the column will not accept the duplicate and Null values.

Foreign Key

The Foreign key constraint is a property that guarantees the dependency of data values of one column of a table with another column of a table. A foreign key constraint, also known as referential integrity constraint, is declared for a column to ensure that the value in one column is found in the column of another table with the primary key constraint. The table containing the foreign key constraint is referred to as the child table, whereas t he table containing the referenced key (Primary key) is referred to as the parent table. The foreign key reference will be created only when a table with the primary key column already exists.

Check

The Check constraint ensures that all values inserted in a column satisfy the specified condition. This constraint checks the data against the expression defined in the INSERT and UPDATE statements.

DATABASE DESIGN

Database design is a process of modeling a database. While designing a database, first you need to identify the tables to be created, then the columns to be created in those tables, the information to be stored in the tables, and finally how to link each table with one another. Mainly, there are two tasks involved in designing a database: entity-relationship model and normalizing a database. In this section, you will learn about entity-relationship modeling, relational database, and normalization.

Entity-Relationship Model

The Entity-Relationship (ER) modeling is a database modeling method that is used to produce a conceptual view of the data model of the system or the relational database. The graphical representation of the ER model is called the Entity-Relationship (ER) diagram. These diagrams represent the interrelationships between entities in a database. The ER diagrams use symbols that represent three types of information: boxes are commonly used to represent entities; diamonds are normally used to represent attributes; and lines are used to represent the relationship between entities.

Entity

An entity is an object or concept about which you want to store information. For example, the entity employee can be represented as given below:

Attribute

An attribute is associated with an entity. In the following example, the number and name of an employee are the attributes of that employee.

Relationships

Relationships indicate how two entities share information in a database structure. Consider the following example, which shows the relationship between two entities, Employee and Department.

The three relationships that can exist between entities are one-to-one, one-to-many, and many-to-many.

One-to-one relationship between two tables occurs when for one record in the first table, there is exactly one corresponding record in the other related table. Figure below shows the one-to-one relationship.

One-to-one relationship

One-to-many relationship between two tables occurs when a record in the first table has linked records in the second table, but each record in the second table may have only one corresponding record in the first table. Figure below shows the one-to-many relationship.

One-to-many relationship

Many-to-many relationship between any two tables occurs when each record in one table may have many linked records in the other table and vice-verse. Figure below shows the many-to-many relationship.

Many-to-many relationship

For example:

Following is a database that contains information about the residents of a city. The ER diagram shown in Figure below contains two entities: PEOPLE and CITIES. There is a one-to-one relationship Live In between PEOPLE and CITIES. PEOPLE have names and CITIES have populations, as shown in below figure.

ER diagram showing the one-to-one relationship between PEOPLE

ORACLE DATABASE ARCHITECTURE

Most of the organizations use the Oracle database to store and maintain data. It is also used to retrieve related information. 


The database has physical and logical structures. The physical storage can be managed without affecting the access to the logical structure as both these structures are separate.


Client/Server Architecture
In the client/server architecture, a database and its application is divided into two parts: front end or client side and back-end or server side.

The Client/Server Architecture

The Client
The client is the front end that runs a database application and is responsible for the interaction between the user and the database server through display mode, keyboard, or mouse. The client fetches the request input from the user, requests data for processing, and presents and analyzes the data managed by the server.


The Server
The server runs the Oracle database. It provides access to the data requested by the client application. In the client/server architecture, the shared data is stored on the server-side.


In the client/server architecture, the client application submits the database requests in the form of SQL and PL/SQL statements and the server application processes those statements and returns the result to the client application.


Multitier Architecture
The multitier architecture is divided into three parts: clients, application servers, and database servers.

Multitier Architecture Environment

The Client
The client or client application submits a request for an operation to be processed on the database server.


The Application Server
The application server is responsible for providing data access to the client. It processes some queries, and thus removes some of the loads from the database server. It serves as an interface between the client and the database server.


The Database Server
The database server processes the requested data of the application server for the client. The database server stores the entire data maintained by Oracle applications.

Physical Database Structures
The physical structure of the Oracle database is determined by the operating system files that form the database. The Oracle database contains some special types of files: Datafiles, Control files, and Redo Log files. These files determine the actual physical storage of data in the database.

Datafiles

Datafiles are physical files that store the data of all logical structures in the database. Oracle database can have one or more physical datafiles. The physical datafiles constitute a logical unit called a tablespace. A physical datafile can be associated with only one tablespace or one database. Datafiles are divided into the following components: Segments, Extents, and Data blocks. The essential characteristics of datafiles are as follows:

1. If the database is running out of space, the size of datafiles will increase automatically.
2. One or more datafiles form a logical unit, called a tablespace.

The first block in a datafile is meant for the header. The header block contains important information such as file size, block size, tablespace, and creation time. Whenever you open the Oracle database, Oracle checks whether or not the information about the datafile header matches with the information stored in the control file.


Control File
Oracle database has a control file that contains the entries showing the status of the physical structure of the database. If the physical structure of a database is changed, as in the case of the creation of a new redo log file or datafile, the database's control file gets modified to reflect these changes.
The control file contains the following types of information:

1. Name of a database
2. The time of the creation of a database
3. The names and locations associated with datafiles and redo log files
4. Information about tablespaces
5. Log record (sequence information, etc)
6. Information about the archived log and the current archive log mode
7. Information about the redo log
8. Information about the copy of a datafile
9. The current log sequence number
10. Information about checkpoints

In Oracle, each time you start a database instance, the control file of the database instance is used to identify the information about it. The control file also ensures that redo log files are opened for database operations. The location of the control file is specified by the control_files init param. Oracle allows users to integrate control files so that multiple copies of the control file
can be written to protect them against any failure.


Redo Log Files
In addition to datafiles and control files that store the database and show the status of the stored data, each Oracle database contains a set of two or more redo log files. The redo log files store all changes made to the data, including either committed or uncommitted changes. These changes are saved to the redo before being permanently incorporated into datafiles. The redo log file is divided into two parts: online redo log files and archived redo log files.

Online Redo Log Files
Each Oracle database has a set of two or more online redo log files that contains a redo record, which is otherwise known as redo entry. The redo record contains a group of change vectors, each of which specifies the changes made in a single block of the database. The Oracle database maintains the redo log files to minimize the loss of the database. The online redo log files store the record of the changes made to the data in the database. For example, if you change the salary of an employee in the employee table, Oracle will generate the redo record containing the change vector. The change vector will indicate the changes made in the data block for the table, the rollback data block, and the transaction table of the rollback block, and it can be used for recovery.


Archived Redo Log Files
In Oracle, you can save the filled group of redo log files to one or more offline destinations that are known as the archived redo log.

Oracle can run in either of these two modes: ARCHIVELOG and NOARCHIVELOG.


ARCHIVELOG - In this mode, Oracle archives the filled online redo logs files before reusing them in the cycle.


NOARCHIVELOG - In this mode, Oracle does not archive the filled online redo log files before reusing them in the cycle. 


Parameter Files
A parameter file contains a list of initialization parameters and their respective values. These parameters and values are used when an Oracle instance is started. The initialization parameters are stored either in parameter files (PFILEs) or server files (SPFILEs).


Alert and Trace Log Files
In Oracle, the server and background processes create special debugging files, called trace files. When a process encounters an internal error, the information related to the error is stored in the trace file.


The alert file is a special trace file. It contains the record of all significant database events and operations such as the CREATE, ALTER, and DROP on a database, tablespace, rollback data, all instance startups and shutdowns, and so on.


Logical Database Structures
Logical structures mainly consist of the data blocks, extents, segments, and tablespaces. These structures are discussed next.

Oracle Storage Structures

Data Block
A data block is the smallest unit of data storage. Data block, also known as database block, physically contains the data of logical database structures such as tables, views, and indexes. The size of a database block is specified for each Oracle database while it is being created.


Extents
Extents are the logical units of the database storage. It consists of a number of contiguous data blocks.


Segments
A segment is a container that contains objects such as tables, indexes, and so on. A segment is a set of extents allocated for a certain logical structure. For example, tables are contained in data segments, the indexes are contained in index segments. A segment and all its extents are stored in one tablespace.


Tablespaces
A tablespace is a logical storage unit that groups related data blocks, extents, and segments together. A database is logically divided into two or more tablespaces. Each tablespace has one or more datafiles and these datafiles are physically stored in the database. These datafiles store the data of all logical structures of a tablespace. 


Tables, indexes, store procedures, and other groups that belong to a tablespace are stored in the Oracle database. The tablespace builds the link between the Oracle database and the filesystem, in which the data of table, index, or store procedure is stored. 


Oracle database contains the default tablespaces such as SYSTEM and SYSAUX. These tablespaces are automatically created when you install Oracle. There are three types of tablespaces in Oracle and they are listed below:

1. Permanent tablespaces
2. Undo tablespaces
3. Temporary tablespaces