Manual Database Creation

In order to manually create a database, mandb,  follow the steps-

Startup the current database.

Create pfile from spfile.

SQL> create pfile=’ /home/app/oracle/product/10.2.0/db_1/dbs/initmandb.ora’ from spfile;

Note: If there are more than one database you should provide the path and name of spfile.

Create a database creation script as, controlfile script.

SQL> alter database backup controlfile to trace as =’/home/app/oracle/product/10.2.0/      db_1/dbs/mandbctl.sql’;

SQL>exit;

Edit the pfile ( dbname, controfile path, dump folders path)

[oracle@localhost ~]$ cd $ORACLE_HOME

[oracle@localhost db_1]$ cd dbs

[oracle@localhost dbs]$ pwd

/home/app/oracle/product/10.2.0/db_1/dbs

[oracle@localhost dbs]$ vi initmandb.ora

[oracle@localhost dbs]$ cat initmandb.ora

mandb.__db_cache_size=444596224

mandb.__java_pool_size=4194304

mandb.__large_pool_size=4194304

mandb.__shared_pool_size=150994944

mandb.__streams_pool_size=0

*.audit_file_dest='/home/app/oracle/admin/mandb/adump'

*.background_dump_dest='/home/app/oracle/admin/mandb/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/app/oracle/oradata/mandb/control01.ctl','/home/app/oracle/oradata/mandb/control02.ctl','/home/app/oracle/oradata/mandb/control03.ctl'

*.core_dump_dest='/home/app/oracle/admin/mandb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='mandb'

*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=mandbXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=201326592

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=605028352

*.undo_management='AUTO'

*.undo_tablespace='undotbs'

*.user_dump_dest='/home/app/oracle/admin/mandb/udump'

Edit the Controlfile Script

CREATE DATABASE mandb

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/home/app/oracle/oradata/mandb/redo01.log'  SIZE 50M,

  GROUP 2 '/home/app/oracle/oradata/mandb/redo02.log'  SIZE 50M,

  GROUP 3 '/home/app/oracle/oradata/mandb/redo03.log'  SIZE 50M

DATAFILE

          '/home/app/oracle/oradata/mandb/system01.dbf' size 100m,

          '/home/app/oracle/oradata/mandb/usrsers01.dbf' size 10m

sysaux datafile

          '/home/app/oracle/oradata/mandb/sysaux01.dbf' size 100m

undo tablespace undotbs

datafile  '/home/app/oracle/oradata/mandb/undo01.dbf' size 50m

default temporary tablespace temptbs

tempfile  '/home/app/oracle/oradata/mandb/temp01.dbf' size 50m

CHARACTER SET WE8ISO8859P1

;

Manually / using commands create the folders:- mandb, adump, bdump, cdump, udump as

/home/app/oracle/oradata/mandb/

[oracle@localhost dbs]$mkdir  /home/app/oracle/oradata/mandb

[oracle@localhost dbs]$mkdir  /home/app/oracle/admin/mandb

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/adump

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/bdump

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/cdump

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/udump

Startup the database testdb in nomount using pfile as

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL> startup pfile=’/ home/app/oracle/product/10.2.0/db_1/dbs/initmandb.ora’ nomount;

Run/Execute the Controlfile Script as

SQL>@/home/app/oracle/product/10.2.0/db_1/dbs/mandbctl.sql

Database Created

Run the following scripts for data dictionary, views, catalogs, and procedures

SQL>@/home/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

SQL>@/home/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql.

Database Management Systems, DBMS

Data

Unanalyzed raw facts, figures, objects and events said called as Data.

Information

Process of the data are called Information.

Database

A database is a collection of related information. A database is a logically coherent collection of data with some inherent meaning.

DBMS
Database Management system is a collection of programs that enables user to create and maintain a database.
Thus a DBMS is a general purposed s/w system that facilitates the process of defining constructing and manipulating a database for various applications. (Defining a data base involves specifying the data types, structures and constraints for the data to be stored in the data database.
Constructing a data base is the process of storing data itself on some storage medium that is controlled by DBMS. Manipulating a database includes such functions as querying the data base to retrieve specific data, updating the database to reflect the changes in the mini-world.

Catalog
A catalog is a table that contain the information such as structure of each file, the type and storage format of each data item and various constraints on the data. The information stored in the catalog is called Metadata . Whenever a request is made to access a particular data, the DBMS s/w refers to the catalog to determine the structure of the file.

Data Warehousing and OLAP
Data warehousing and OLAP (online analytical processing ) systems are the techniques used in many companies to extract and analyze useful information from very large databases for decision making .

Real Time Database Technology
These are all the techniques used in controlling industrial and manufacturing processes.

Program-Data Independence
Unlike in the traditional file sys. the structure of the data files is stored in the DBMS catalog separately from the access programs . This property is called program-data independence i.e. We needn’t to change the code of the DBMS if the structure of the data is changed. Which is not supported by traditional file sys .

ORDBMS
Object oriented RDBMS is a relational DBMS in which everything is treated as objects. User can define operations on data as a part of the database definition.

Program-Operation Independence
An operation is specified in two parts .
a) Interface (operation name and data types of its arguments).
b) Implementation (the code part)
The implementation part can be changed without affecting the interface. This is called
program-operation independence.

View
A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored .

OLTP
Online transaction processing is an application that involve multiple database accesses from different parts of the world . OLTP needs a multi-user DBMS s/w to ensure that concurrent transactions operate correctly.

DBA
A database administrator is a person or a group responsible for authorizing access to the database, for coordinating and monitoring its use, and for acquiring s/w and h/w resources as needed.

DB Designer
base and for choosing appropriate structure to represent and store this data .Data base designers are responsible for identifying the data to be stored in the data

Different types of End users
a) Casual end-users
b) Naive or parametric end users
c) Sophisticated end users
d) Stand alone users.

Aadvantages of using a DBMS
a) Controlling redundancy.
b) Restricting unauthorized access.
c) Providing persistent storage for program objects and data structures.
d) Permitting inferencing and actions using rules.
e) Providing multi-user interfaces.
f)  Representing complex relationships among data.

g) Enforcing integrity constraints.
h)Providing backups and recovery.

Disadvantages of using a DBMS
a) High initial investments in h/w, s/w, and training.
b) Generality that a DBMS provides for defining and processing data.
c) Overhead for providing security, concurrency control, recovery, and integrity functions.

Data Model

It is a collection of concepts that can be used to describe the structure of a database. It provides necessary means to achieve this abstraction. By structure of a database we mean the data types, relations, and constraints that should hold on the data.

Categories of Data Models
a) High-level or conceptual data models.
b) Representational data models.

c) Low-level or physical data models.
High level data models provide the concepts that are close to the way many users perceive data.
Representational data models are provide concepts that provide the concepts that may be understood by end users but that are not too far removed from organization of data in the database.
Physical data models describe the details of how data is stored in the computers.

Schema
The description of a data base is called the database schema , which is specified during database design and is not expected to change frequently . A displayed schema is called schema diagram .We call each object in the schema as schema construct.

Database Management Systems, DBMS

Types of Schema
a) internal schema.
b) Conceptual schema.
c) External schemas or user views.

Data Independency
Data independency is defined as the capacity to change the conceptual schema without having to change the schema at the next higher level. We can define two types of data independence:
a) Logical data independence, LPD

b) Physical data independence, PDI
LDI is the capacity to change the conceptual schema without having to change external schemas or application programs.

PDI is the capacity to change the internal schema without having to change conceptual (or external) schemas.

DBMS Languages
a) DDL (Data definition language)
b) SDL (Storage definition language)
c) VDL (View definition language)
d) DML (Data manipulation language)

Types of DBMS
a) RDBMS (Relational)
b) ORDBMS (Object Relational)
c) DDBMS (Distributed)
d) FDBMS (Federated)
e) HDDBMS (Homogeneous)
f) HDBMS (Hierarchical)
g) NDBMS (Networked)

Entity
An entity is a thing in the real world with an independent existence.

Attributes
These are the particular properties that describe an entity.

Types of Attributes
a)  Composite Vs simple attributes.
b) Single valued Vs multi-valued attributes.
c) Stored Vs derived attribute.
d) Null valued attributes.
e)Complex attributes.

f) What is difference between entity set and entity type?

Domain Value or Value Set of an Attribute
It is the set of values that may be assigned to that attribute for each individual entities .

Degree of a Relationship
The no of entities participating in that relation .

Recursive Relationship
It is the relationship where both the participating entities belong to same entity type .

Relationship Constraints
a) Cardinality Ratio. b) Participation Constraints.

Cardinality Ratio
The cardinality ratio for a binary relationship specifies the number of relationship instances that an entity can participate in.

Participation Constraint
The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type. This is of two types:
a) Total Participation.
b) Partial Participation.

Weak Entity Types
The entity types that do not have key attributes of their own are called weak entity types.
Rests are called strong entity types .The entity that gives identity to a weak entity is called owner entity. And the relationship is called identifying relationship. A weak entity type always has a total participation constraint with respect to its identifying relationship.

ER Diagram/ EER Diagram
This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

Specialization
It is the process of defining a set of subclasses of an entity type where each subclass contain all the attributes and relationships of the parent entity and may have additional attributes and relationships which are specific to itself.

Generalization
It is the process of finding common attributes and relations of a number of entities and defining a common super class for them.

Generalization and Specialization
a) Disjoint ness constraints.
b) Completeness constraints.

c) Total Specialization constraints. Disjointness Constraint specifies that the subclasses of the specialization must be disjoint .i.e. an entity can be a member of at most one of the subclasses of the specialization. The reverse of it is overlapping. Completeness Constraint is a participation constraint which may be
i) Total

ii) Partial Total specialization Constraint tells that each entity in the super class must be a member of some subclass in the specialization. And partial specialization constraint allows an entity not to belong to any of the subclasses .Thus we do have the following 4 types of constraints on specialization:
i) Disjoint, total

ii)Disjoint, partial
iii)Overlapping, total
iv)Overlapping, partial

Ternary Relationship
A relationship with a degree 3 is called a ternary relationship.

Aggregation and Association
Aggregation is an abstraction concept for building composite objects from their component objects. The abstraction of association is used to associate objects from several independent classes.

RAID Technology
Redundant array of inexpensive (or independent) disks. The main goal of raid technology is to even out the widely different rates of performance improvement of disks against those in memory and microprocessor. Raid technology employs the technique of data striping to achieve higher transfer rates. 
Hashing Technique
This is a primary file organization technique that provides very fast access to records on certain search conditions. The search condition must be an equality condition on a single field, called hash field of the file.
a) Internal hashing
b) External hashing
c) Extendible hashing
d) Linear hashing
e) Partitioned hashing

Types of Relational Constraints
a) Domain Constraints
b) Key Constraints

c) Entity Integrity Constraints
d) Referential Integrity Constraints Domain Constraints specify that the value of each attribute must be an atomic value from the domain of the attributes. Key Constraints tell that no two tuples can have the same combination of values for all their attributes. Entity Integrity constraint states that no primary key value can be null. Referential Integrity constraints states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation it is specified between two relations and is used to maintain the consistency among tuples of the two relations.

Super Key, Key, Candidate Key, Primary Key 
A Super Key specifies a uniqueness constrain that no two distinct tuples in a state
can have the same value for the super key. Every relation has at least one default super key. A Key is a minimal super key or the subset of the super key which is obtained after
removing redundancy. A relation schema may have more than one key .In this case
each key is called a candidate key. One of the candidate key with minimum number
of attributes is chosen as primary key.

Foreign Key
A key of a relation schema is called as a foreign key if it is the primary key of
some other relation to which it is related to.

Transaction
A transaction is a logical unit of database processing that includes one or more database access operations. If a Transaction is not complete it rolled back or cancelled.

Properties of Transaction
a) Atomicity
b) Consistency preservation
c) Isolation
d) Durability (permanence)

Basic database Operations
a) Write_item(x) – Insert Command
b) Read_item(x) – Select Command

Disadvantages of not controlling concurrency
a) Lost update problem
b)Temporary update(Dirty read) problem
c) Incorrect summary problem

Serial, Non serial Sechedule
A schedule S is serial if, for every transaction T participating in the schedule, all the operations of T is executed consecutively in the schedule, otherwise, the schedule is called non-serial schedule.