At a glance:

• Database Management
  • What is Database?
• DBMS Models
  • Data Models
• RDBMS Components
  • Relational Database Management System(RDBMS)
  • RDBMS Structure
  • Difference: DBMS and RDBMS
• Server Functions and Feature
  • The Oracle Instance Architecture
  • The System Global Area (SGA)
  • The Oracle Database Architecture
  • Oracle 8:Oracle in ORP
• Codd’s Rules
  • Entity-Relationship Model
  • Mapping Cardinalities
  • E-R Diagram
  • Normalisation
• Oracle Components & Tools
  • Advantages of Oracle
  • Products of Oracle
  • ORACLE as an RDBMS
• Oracle Datatypes

1.1 What is Database?

A Database can be defined in many ways. It is an organized collection of information stored in a particular format. It is an integrated set of
interrelated data. It is a repository of relevant information and so on. It is a tool for organizing, sorting, maintaining, calculating, combining, and
retrieving information. Thus you can use an ORACLE database for organizing:

• Mailing lists
• Customer, Client, and Supplier lists
• Personnel information
• Inventories
• Accounts
• Payables and Receivables
• A Variety of other Information

An example of a database is the Visiting Cards file. Each visiting card represents a person. It contains information about the Name of the person, the company name, the Address and their Phone numbers etc. When we have several visiting cards, we need to store them in proper order. Now visiting card Database can be as follows:

          Person’s Name

         Company Name

          Address

          Phone Number

We have now fixed the format in which we will store information about the visiting cards. This format is termed as STRUCTURE of Database.

If we consider the Database consisting of Horizontal rows and Vertical columns, then information from one visiting card will appear in a row. These could be many such rows. The columns in the table will represent Person Name, Company name, etc.

The format can be as follows:

NAME                   COMPANY                    ADDRESS                       PHONE

R.R.Patel               Mukund Ltd.                    L.B. Shastri Rd.                3945967

P.D.Nichal            Raymonds                         M.G. Road, Baroda          4386687

Database management System must be able to handle large amount of data of preferably in a multiuser environment so that more than one user can concurrently access the same data. This may demand a sophisticated, mighty and powerful 4GL (Fourth Generation Language) databases like Oracle. Oracle supports largest of databases and provides full Control on space usage and supports large number of concurrent users executing a variety Of database applications operating on the same data.

Object-oriented databases are a relatively new type of system that supports the object-oriented development paradigm. The primary goal of object-oriented thinking is to read the level of abstraction so that it is more natural to design and build an information management system. For example, in an object-oriented database, complex data structures called object closely model the entities in a business system while methods match the business operations that act upon the objects in the system. So, database stores instances of customers and sales order line items, a database stores instances of customers and sales order objects. Associated methods stored in the database describe how to add, change,and delete customer and sales order objects.

An efficient system that organizes, manages and manipulates the database is called a Database Management system. In order to manage data in the database, we need software. This software is called Database Management system (DBMS). It provides facilities to the user to access the database with ease. It also provides the user to manipulate the data like Updating, Querying, Generating reports and defining database i.e. to store data on some physical storage medium. The basic operations which can be performed in a database with ease using a DBMS are:

• Adding the Data
• Modifying the Data
• Sort the Data
• Delete the Data
• Search the Data

The structure of a database is dependent on data models. A data model is a collection of conceptual tools that describe the data, data relationship and consistency constraints. These are many data models that are widely in use.Some of them are:

1. Object based logical models.
2. Physical data models.
3. Record based logical models.
4. Hierarchical model.
5. Network model.
6. Relational model.

2.1.1      Object based logical model:

Object bases logical models are used in describing the data at the conceptual and view levels. They are characterized by the fact that have fairly structuring capabilities and allow data constraints to be specified explicity. There are different models, and more are likely to come.

2.1.2. Physical data models

Physical data models are used to describe the data at lowest level. In contrast to the logical model, there are very few physical data models in use. Two of the widely known ones are Unifying model and Frame Memory.

2.1.3. Record based logical models:

In record based models, the database is structured in fixed format records of several types. These models specify the overall logical structure of the database and are used in describing the databases at conceptual level.

2.1.4. Hierarchical Model:

In this model records and links represent the relationship among data. Hierarchical model allows one-to-one and one-to-many relationships and has the ability to handle large amount of data. The major disadvantage with this model is that the querying is complicated and wastage of storage space due to duplication of data. Data inconsistency may result when updating the data. It does not allow many-to-many relationships.

2.1.5. Network Model:

The network data model is similar to hierarchical model, except that one data have more than parent. The records in the database can be organized as a collection of arbitrary graphs. Unlike Hierarchical model, it can be used to represent many-to-many relationships. Any record in this database is allowed to own sets of other type of record.

2.1.6. Relational Model:

The Relational data model is currently the most popular one in the database management systems because it is conceptually simple and understandable by information systems professionals and many end-users. The relational data model is developed by E.F. Codd in 1970 is based on relation, two-dimensional table. The relational model represents the data and their relationships by a collection of tables. The tables are called “Tuples” and the columns as “Attributes”. It allows many-to-many relationships. Data redundancy is controlled to a greater extent in this model.

A relational table must meet some special properties to be part of the relational model. Data stored in 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. A good example is a Vehicle identification Number (VIN). If this were stored as one column, it would violate the information principle because it would contain many pieces of information, such as make, model, origin of plant, and so on. Whether practice overrules theory is a design choice in such cases, although in
most cases, this turns out to be bad news for data integrity.

Data stored under columns must be of the same data type. Each row is unique. (No duplicate rows). Columns have no order to them. Rows have no order to them. Columns have a unique name.

Relational database management system is based upon the relational model. It supports relation databases i.e. database can be represents in form of tables. The goal of a relational database design is to generate a set of relation schemes that allow the user to retrieve information without redundancy and allows retrieval process easily. The data is stored in the form of tuples and attributes, this ensures data integrity and offers complete flexibility in database design. Let us understand this with an example.

Employee

The above table gives the information about a list of employees working in a firm. Every Employee number corresponds to a particular employee. Emp. No., name, job, salary, join date, dept. no. and dept name are called the columns or the attributes of the table employee. For each attribute there is a set of values, called the domain of that attribute. For example, for the emp. No. attribute, the domain is the set of all employee numbers and the dept name attribute the domain is the set of all department names and so on. In the above relation employee, there are eight tuples, thus each row identify the details of a particular employee. For example, the first row specifies that employee number is 100, named Allen who is a manager in marketing department earning 4500 and had joined the firm on 25th January 1989.

The oracle data dictionary is a group of table and views the contain information about the database. These tables and views are created by oracle when the database is created. The data dictionaries describes tables, columns, indexes, clusters, users access privileges and other objects in the database. The objects that make up data dictionary are mostly synonyms for tables and views. You can read dictionary tables with standard SQL
queries. Since the data dictionary tables are themselves described in the data dictionary in the data dictionary the dictionary may be queried to determine the names of its own tables, columns, and so forth.

To avoid chaos in any complex system, someone has to be in charge. The person responsible for a database is called database administrator (DBA). The DBA has a special set of privileges that give him complete control over the database. Any system more than one user should have a DBA. The job of DBA includes.

• Creating primary database storage structure
• Modifying the structure of the database
• Backing up and restoring the database
• Monitoring and re-establishing database and external files
• Controlling and monitoring user access to the database
• Manipulating the physical location of database
• Maintain security of database
• Create users and assign them their rights

The complexity of these tasks for the system and for the DBA dictates that a central documentation system be incorporated into any relational DBMS.

The Advantage of RDBMS

• It provides minimum data redundancy and minimum data inconsistency.
• It eliminates the difficulty in accessing and manipulating data.
• It offers uniform security and privacy control.
• Data sharing can be accomplished easily.
• It provides data integration.

4.1 The Oracle instance Architecture

Work done by the databaseis handled by a complex set of memory structure and operating system processes called the instance. Every oracle database has an instance associated with it. A database is mounted by only one instance.

Discrete processes perform specialized tasks within that work together to accomplish the goals of the instance. Each process has a separate memory block that it uses to store private variables, address stacks, and other runtime information. The processes use a common shared memory area in which to do its work a section of memory that can be written to and read from at the same time by many different programs and processes. This memory block is called the system global area. (SGA).