DBMS Basics

A) DATABASE MANAGEMENT SYSTEM (DBMS) 

The software used for the management, maintenance and retrieval of the data stored in a database is called DBMS.
Database management systems frees the programmer from the need to worry about the organization and location of data. The primary goal of a DBMS is to provide an environment which is both convenient and efficient to use in retrieving and storing information.

B) A DATABASE STORAGE HIERARCHY

i) DATABASE
A collection of data files integrated and organised into a simple comprehensive file system, which is arranged to minimize duplication of data and to provide conenient access to information within that system to satisfy a wide variety of user needs.

ii) DATA FILES
A file is a number of related records that are treated as a unit.
eg:- A collection of all employee records for one company would be an employee file.

iii) RECORDS
A collection of related items of data that are treated as a unit.
eg:- An employee record would be collection of all fields of one employee.
->Record is sometimes referred as tuple.

iv) FIELD
Individual element of data is called Field.
eg:- Bank cheque consist of following field cheque no, date, payee, numeric amt, signature, bank,etc.
->Field is sometimes referred as Data item.


C) BASIC DBMS TERMS

i) RELATION
A relationship is an association among several entities
eg:- A cusst_Acct relationship associates a customer with each account that she or he has.

ii) REDUNDANCY
If same piece of information is stored in database for number of times the database is said to be redundant. We should check our database should not be redundant as it wastes make our disk space, reduced efficiency of database, require more processing time, and their are chances of Inconsistency due to it in our database.
eg:-If we have to tables emp_details (contains details of employee) and Payroll(contains Payment details to employee), than if we include details of employee in payroll table, than it is said to be redundancy as same piece of information is repeated.

iii) INCONSISTENCY
Inconsistency is various copies of the same data may no longer agree. Inconsistency occurs due to redundancy, so redundancy should be reduced . Though we cannot elimnates the redundancy , but we can reduced it upto certain level.
eg:- If we have details of employee stored in emp_details and payroll table than while updating information we should check that both tables are updated or not, if we update the address of one employee in emp_details and same details is not updated in payroll table, than database is said to be in inconsistent state.


iv) PROPAGATING UPDATES
Propagating updates ensures users that any change made to record of one files, automatically made to other files. This process is known as Propagating updates. Where the term "Updates" is used to cover all the operations of creation, deletion and modification.
-we can avoid inconsistency by using propagating update technique.

v) INSTANCES
The collection of information stored in the database at a particular moment in time is called an instances of the database.

vi) SCHEMES
The overall design of the database is called the database schemes. Schemes are changed infrequently, if at all.

vii) USERS
There are four different types of database system users.

Application programmers:- A person who prepares application program are called application programmer. Application programs operates on the data in all the usual ways: retrieving information, creating new information, deleting or changing existing information.

Sophisticated Users:- Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. Each such query is submitted to a query processor whose function is to take a DML statement and break it down into instructions that the database manager understands.

Specialized Users:-Some sophisticated users write specialized database application that do not fit into the traditional data processing framework. Among these application are computer-aided design systems, knowledge-base and expert systems, systems that store data with complex data types
eg:-For Graphics and Audio data.

End Users:-Unsophisticated users interact with the system by invoking one of the permanent application programs that have been written previously.Thus they are persons who uses the information generated by a computer based system. Retrival is the most common function for this class of user.


D) KEYS concept in DBMS
A Key is a single attribute or combination of two or more attributes of an
entity set that is used to identify one or more instances of the set.

i) PRIMARY KEY:-A primary key is a field that uniquely identifies each record in a table. As it uniquely identify each entity, it cannot contain null value and duplicate value.
eg:-Consider the customer table, which has field :customer_number, customer_socialsecurity_number, and customer_address.here customer_number of each entity in customer table is distinct so customer-number can be a primary key of customer-table.

ii) SUPER KEY :- If we add additional attributes to a primary key, the resulting combination would still uniquely identify an instance of the entity set. Such augmented keys are called superkey.
A primary key is therefore a minimum superkey.

iii) CANDIDATE KEY:-A nominee's for primary key field are know as candidate key.
eg:-From above example of customer table, customer_socialsecurity_number is candidate key as it has all characteristics of primary key.

iv) ALTERNATE KEY:-A candidate key that is not the primary key is called an Alternate key.
eg:- In above example, customer_socialsecurity_number is a candidate key but not a primary key so it can be considered as alternate key.

v) COMPOSITE KEY:- Creating more than one primary key are jointly known as composite key.
eg:-In above example, if customer_number and customer_socialsecurity_number are made primary key than they will be jointly known as composite key.

vi) FOREIGN KEY:- Foreign key is a primary key of master table, which is reference in the current table, so it is known as foreign key in the current table. A foreign key is one or more columns whose value must exist in the primary key of another table.
eg:-Consider two tables emp(contains employees description) and emp_edu(contains details of employee's education), so emp_id which is primary key in emp table will be referred as foreign key in emp_edu table.


E) NORMALIZATION
It is important to understand the concept of normalization before switch on to the creation of table and its manipulation.

Normalization is the process of grouping data into logical related groups.
Normalization is the process of reducing the redundancy of data in a relational database.
A database that is not normalized may include data that is contained in one or more different tables for no apparent reason. This could be bad for security reasons, disks space usage, speed of queries, efficiency of database updates, and may be most importantly, data integrity. A database before normalization is one that has not been broken down logically into smaller, more manageable tables.

BENEFITS OF NORMALIZATION
Normalization provides numerous benefits to a database.

Some of the major benefits include:

• Wider the overall database organization.
• The reduction of redundant data.
• Data Inconsistency can be avoided.
• A much more flexible database design.
• A better handle on database security.