1:: RDBMS Concepts in a glance

Following is a glance of RDBMS concepts. Will keep you posted in detail further.

Database – A organize collection of related (inter-related) information.
In more precise way, “A large set of structured data”. A data in the database can be shared and accessed concurrently.

DBMS – A computer program (or a set of) used to create and manage the structure of database (organizing, storage and retrieval of data). A convenient and efficient way to store, retrieve and modify information.

Flat Files – A tradition file approach where records have no structured relationship. The main disadvantage of tradition file approach wasdata in/security and data redundancy .

Hierarchical database – One of the primary databases used before the advent of Relational theory.
—- Stores data in tree structure format
—- Hierarchy between parent and child nodes
—- Data defined as structure of node
—- One to many relationship between parent and child node
—- Predefined relationship and rigid structure

Network Database – Another database used before the advent of Relational theory.
—- Same structure as Hierarchical but has many to many relationship between nodes

Relational Data Model – A data-model conceived by Dr E F Codd in his paper “A Relational Model of Data for Large Shared Data Banks” to work with large database. It is based on the mathematical set theory and uses a collection of tables (Relations – A two-dimension structure) to represent data and relationships between those data.

You can find a summary of Dr Codd’s paper here.

Relational Database – A database that presents data in the form of rows and columns.

Entity Relationship Model – ER diagrams illustrate the logical structure of data, entity, their organization and relationship in the database.

Entity - An object in real-world. For example, employee, projects etc.

Table/Relation – A collection/set of rows (tuples) and columns (attributes) in which data is stored. A table generally modeled/describes a real world entity.

Row/Tuple – A row represent a group of related data. For example, the data of an employee.

Column/Attribute – Characteristics or feature of an entity. For example, an employee can have a name, an id and salary which describe it.

Relationship – Associate entities to each other in meaningful way.

RDBMS – A DBMS to manage Relational database (which stores data in form of related tables). It also provides relational operators andquery language to manipulate the data stored into the tables.

Codd’s 12 rules – A set of 13 rules define by Dr. E F Codd, used to determine if a DBMS can be qualified as a RDBMS.

• Foundation Rule/Zero Rule
• Information Rule
• Guaranteed Access Rule
• Systematic Treatment of Null Values
• Dynamic On-line Catalog Based on the Relational Model
• Comprehensive Data Sublanguage Rule
• View Updating Rule
• High-level Insert, Update, and Delete
• Physical Data Independence
• Logical Data Independence
• Integrity Independence
• Distribution Independence
• Nonsubversion Rule

Normalization – It is a process of minimizing the inconsistency, ambiguity and redundancy between the data in tables so that the operation performed on those data much more efficient in terms of performance and space requirement to store those data.

Normal Form (NF) – Wikipedia states ,”The normal forms (abbrev. NF) of relational database theory provide criteria for determining a table’s degree of vulnerability to logical inconsistencies and anomalies“.

A good introduction to normalization and normal forms (1NF, 2NF, 3NF…) can be found on IBM developerworks.

Candidate Key – A set of one or more columns that can uniquely identifies a row in a table.

Primary Key – An attribute (or set of attributes) with a value that uniquely identifies each row in a table. A primary key usually chose from the candidate keys. The attributes other than the primary key attributes in a table/ relation are called non-key attributes.

Foreign Key – A column or combination of columns in a relation that is used to establish and enforce a relationship between two tables. A FOREIGN KEY of a table usually points to a PRIMARY/UNIQUE KEY in the same table(self referencing) /another table. The foreign key can have duplicate or null values.

Entity Integrity Constraint – No primary key attribute can be NULL and it must contain unique value.

Referential Integrity Constraint – A foreign key value must match an existing primary key value or the foreign key value must be NULL.

NULL – A value that is unavailable, unassigned or unknown. A null is not same as zero or a blank space.

Structured Query Language (SQL) – A set-based, 4GL programming language used to interact with Relational Databases.
SQL is a non-proprietary language, which is governed by SQL Standards committees from International Standards Organization (ISO) and American National Standards Institution (ANSI). Oracle supports ANSI standard SQL.

Using SQL, one can perform following tasks (but not limited to)-
—- Querying data
—- Modifying, updating, inserting data
—- Creating, Dropping, altering the structure of tables
—- Securing and authenticating access to database and its objects

PL/SQL – It is the Oracle’s Procedural Language extension to Oracle SQL. It includes sub-set of SQL commands and used for writing application logic.

SQL* Plus – A client application program provided by Oracle to perform SQL commands against an Oracle database. It has a command-line user interface.

SQL Developer – A GUI-based tool and a convenient way to perform basic database tasks. One can connect to Oracle database as well as some third-party databases using SQL Developer.

Categories of SQL Statements

Data Definition Language
• To define and create new database objects
• To remove objects that is no longer required
• To change the structure of an existing object

Data Manipulation Language
• To insert data into the table
• To modify or update data from the table
• To retrieve, delete data from the table

Data Control Language
• To control access to the database and data in it
• To enforce data security

Transaction Control Language
• To manage the changes made by DML statement

Session Control Statements
• To dynamically changes the properties of a user session

System Control Statement
• To changes the properties of the database instance

Embedded SQL Statements
• SQL statement those are incorporating in procedural programming language such as C, C++, COBOL, java, FORTRAN etc.
• It is an approach to embed DDL, DML, and transaction control statements within a procedural language program.