DBMS

Data: Collection of raw facts.

Database: A database is a collection of related information that is organized so that it can easily be accessed, managed and updated.

Data Base Management System (DBMS): To manage our databases we require DBMS. DBMS is a software program that is used to store, modify and retrieve the data from the database upon user request.

What's an RDBMS ?

This concept was first described around 1970 by Dr. Edgar F. Codd in an IBM research publication called "System R4 Relational".

A relational database uses the concept of linked two-dimensional tables which comprise of rows and columns. A user can draw relationships between multiple tables and present the output as a table again. A user of a relational database need not understand the representation of data in order to retrieve it. Relational programming is non-procedural.

What's procedural and non-procedural ?

Programming languages are procedural if they use programming elements such as conditional statements (if-then-else, do-while etc.). SQL has none of these types of statements.

In 1979, Relational Software released the world's first relational database called Oracle V.2

Earlier we used FMS(File Management system) but it has various drawbacks like:

1.    Data redundancy (duplicate data)

2.    In consistency of data

3.    Risk to data Integrity (Integrity Constraints

4.    Data Isolation

5.    Difficult access to the stores data.

6.    No Security

Thus to over come these drawbacks DBMS was introduced, other examples DBMS are Fox Base, Dbase, Fox pro.., etc.

Various Database Models:

1.    File Management System (FMS)

2.    Hierarchical Model( Has a DOS directory Structure hence can only be used Hierarchical data).

3.    Network Model.

4.    Relational Model.

Relational Ship is named association between database objects.

To be relational it must have

a)    One to One

b)    One to Many

c)    Many to Many Relations.

Hierarchical database does not satisfy the many to many relationships

Network database uses pointers which are not easy to access.

What's SQL ?

In 1971, IBM researchers created a simple non-procedural language called Structured English Query Language. or SEQUEL. This was based on Dr. Edgar F. (Ted) Codd's design of a relational model for data storage where he described a universal programming language for accessing databases.

In the late 80's ANSI and ISO (these are two organizations dealing with standards for a wide variety of things) came out with a standardized version called Structured Query Language or SQL. SQL is prounced as 'Sequel'. There have been several versions of SQL and the latest one is SQL-99. Though SQL-92 is the current universally adopted standard.

SQL is the language used to query all databases. It's simple to learn and appears to do very little but is the heart of a successful database application. Understanding SQL and using it efficiently is highly imperative in designing an efficient database application. The better your understanding of SQL the more versatile you'll be in getting information out of databases.

 Dr. E.F. Codd formulated 12 rules about relational theory in 1970 in his paper named “A RELATIONAL MODEl OF DATA FOR LARGE SHARED DATA BANKS”.

12 RULES:

1.    Information rule( name should not be same).

2.    Rule of guaranteed access (should know the table and column name and the primary key).

3.    Systematic treatment of null values (missing information).

4.    Database description rule (user information in system tables must be read only).

5.    Comprehensive Sub Language (understandable language SQL)

6.    Insert and Update rule.

7.    The view updated rule (partially satisfied by oracle).

8.     The Physical independence rule

9.    The logical data independence rule

10.  The integrity independence rule

11. the distribution rule

12. the non – version rule.

Any DBMS Which satisfies half these rules i.e., 6/12 is called a Relational DBMS. Oracle satisfies 11.5 rules.

An Entity is a real-world item or concept that exists on its own. The set of all possible values for an entity is the entity type.

An attribute of an entity is a particular property that describes the entity. The set of all possible values of an attribute is the attribute domain.

Relation Ship: A relationship type is a set of associations among entity types. For example, the student entity type is related to the team entity type because each student is a member of a team. In this case, a relationship or relationship instance is an ordered pair of a specific student and the student's particular physics team, such as (haszone, db01), where db01 is haszone's team number.

What's Database Normalization?

Normalization is the process where a database is designed in a way that removes redundancies, and increases the clarity in organizing data in a database.

Normalization of a database helps in modifying the design at later times and helps in being prepared if a change is required in the database design. Normalization raises the efficiency of the database in terms of management, data storage and scalability.

Now Normalization of a Database is achieved by following a set of rules called 'forms' in creating the database.

These rules are 5 in number (with one extra one stuck in-between 3&4) and they are:

1st Normal Form or 1NF:

Each Column Type is Unique.

2nd Normal Form or 2NF:

The entity under consideration should already be in the 1NF and all attributes within the entity should depend solely on the entity's unique identifier.

3rd Normal Form or 3NF:

The entity should already be in the 2NF and no column entry should be dependent on any other entry (value) other than the key for the table.

If such an entity exists, move it outside into a new table.

Now if these 3NF are achieved, the database is considered normalized. But there are three more 'extended' NF for the elitist.

These are:

BCNF (Boyce & Codd):

The database should be in 3NF and all tables can have only one primary key.

4^th Normal Form or NF:

Tables cannot have multi-valued dependencies on a Primary Key.

5^th Normal Form or 5NF:

There should be no cyclic dependencies in a composite key.

ORACLE DATA TYPES

Data type	Description	Max Size
VARCHAR2(size)	Variable length character string having max length size bytes. You must specify size	4000bytes min is 1
VARCHAR	VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.
CHAR(size)	Fixed length character data of length size bytes. This should be used for fixed length data.	2000 bytes Default and minsize is 1 byte.
NUMBER(p,s)	Number having precision p and scale s.	The precision p can range from 1 to 38. The scale s can range from -84 to 127.
DATE	Valid date range	from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP (fractional_seconds_precision)	The number of digits in the fractional part of the SECOND date time field.	Accepted values of fractional seconds precision are 0 to 9. (default = 6)
ROWID	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)	10 bytes
CLOB	Character Large Object	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BLOB	Binary Large Object	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)
BFILE	pointer to binary file on disk	8 TB to 128 TB (4 Gigabytes - 1) * (database block size)

Table: