Data Base Management System

What is DBMS?
Advantages & Uses What is SQL? Commands & Queries

What is DBMS?

A Data Base Management System(DBMS) is a collection is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database , contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient.

Purpose of DBMS

1. To see why database management systems are necessary, let's look at a typical ``file-processing system'' supported by a conventional operating system.

The application is a savings bank:
o Savings account and customer records are kept in permanent system files.
o Application programs are written to manipulate files to perform the following tasks:
     - Debit or credit an account.
    - Add a new account.
    - Find an account balance.
    - Generate monthly statements.

2. Development of the system proceeds as follows:
o New application programs must be written as the need arises.
o New permanent files are created as required.
o But over a long period of time files may be in different formats, and
o Application programs may be in different languages.

3. So we can see there are problems with the straight file-processing approach:
o Data redundancy and inconsistency
    - Same information may be duplicated in several places.
    - All copies may not be updated properly.
o Difficulty in accessing data
    - May have to write a new application program to satisfy an unusual request.
    - E.g. find all customers with the same postal code.
    - Could generate this data manually, but a long job...
o Data isolation       
     - Data in different files.
    - Data in different formats.
    - Difficult to write new application programs.
o Multiple users
    - Want concurrency for faster response time.
    - Need protection for concurrent updates.
    - E.g. two customers withdrawing funds from the same account at the same time 
     account has $500 in it, and they withdraw $100 and $50. The result could be $350, $400              or $450 if no protection.
o Security problems
     - Every user of the system should be able to access only the data they are permitted to see.
     - E.g. payroll people only handle employee records, and cannot see customer accounts; tellers         only access account data and cannot see payroll data.
     - Difficult to enforce this with application programs.
o Integrity problems
     - Data may be required to satisfy constraints.
     - E.g. no account balance below $25.00.
     - Again, difficult to enforce or to change constraints with the file-processing approach.

These problems and others led to the development of database management systems.

Top


Advantages & Uses

Using a DBMS to manage data has many advantages:

Data independence: Application programs should be as independent as possible from details of data representation and storage. The DBMS can provide an abstract view of the data to insulate application code from such details.

Efficient data access: A DBMS utilizes a variety of sophisticated techniques to store and retrieve data efficiently. This feature is especially important if the data is stored on external storage devices.

Data integrity and security: If data is always accessed through the DBMS, the DBMS can enforce integrity constraints on the data. For example, before inserting salary information for an employee, the DBMS can check that the department budget is not exceeded. Also, the DBMS can enforce access controls that govern what data is visible to different classes of users.

Data administration: When several users share the data, centralizing the administration of data can offer significant improvements. Experienced professionals who understand the nature of the data being managed, and how different groups of users use it, can be responsible for organizing the data representation to minimize redundancy and for retuning the storage of the data to make retrieval efficient.

Concurrent access and crash recovery: A DBMS schedules concurrent accesses to the data in such a manner that users can think of the data as being accessed by only one user at a time. Further, the DBMS protects users from the effects of system failures.

Reduced application development time: Clearly, the DBMS supports many important functions that are common to many applications accessing data stored in the DBMS. This, in conjunction with the high-level interface to the data, facilitates quick development of applications. Such applications are also likely to be more robust than applications developed from scratch because many important tasks are handled by the DBMS instead of being implemented by the application.

USES of DBMS

Database is widely used all around the world in differnt sectors:

o Banking: For customer information, accounts loans and banking transactions.
o Airlines: For reservations and schedule information. Airlines were among the first to use database in a geographically disturbed manner-terminals situated around the world accessed the central database system through phone lines and other data networks.
o Universities: For student information, course registrations and grades.
o Credit card transactions: For purchases on credit cards and generation of monthly statements. o Telecommunications: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards and storing information about the communication networks.
o Finance: For storing information about holdings, sales and purchase of financial instruments such as stocks and bonds.
o Sales: For customer, product and purchase information.
o Manufacturing: For management of supply chain and for tracking production of items in factories, inventories of items in warehouses/stores and orders for items.
o Human Resources: For information about employees, salaries, payroll taxes and benefits and for generation of paychecks.
o Web based services:For taking web users feedback,responses,resource sharing etc.



Top

What is SQL?

SQL (STRUCTURED QUERY LANGUAGE) the standard command set used to communicate with the relational database management systems. All tasks related to relational data management-creating tables, querying the database for informations, modifying the data in the database, deleting them, granting access to users and so on – can be done using SQL.

SQL uses the combination of relational algebra and relational calculus constructs it has many other capabilities besides quering the database. It includes the feature of defining the structures of data, for modifying the data in the database and for specifying security constraints.

The different parts of SQL language are as follows:-

1. Data Definition Language (DDL)
A database schema is specified is by a set of definition expressed by a special language called data definition language. Some examples:
o CREATE - to create objects in the database
o ALTER - alters the structure of the database
o DROP - delete objects from the database
o TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
o COMMENT - add comments to the data dictionary
o RENAME - rename an object

2. Data Manipulation Language (DML)
A data manipulation language is a language that enables users to access or manipulate data or organized by appropriate data model.. Some examples:
o SELECT - retrieve data from the a database
o INSERT - insert data into a table
o UPDATE - updates existing data within a table
o DELETE - deletes all records from a table, the space for the records remain
o MERGE - UPSERT operation (insert or update)
o CALL - call a PL/SQL or Java subprogram
o EXPLAIN PLAN - explain access path to data
o LOCK TABLE - control concurrency

3. Data Control Language (DCL)
The sql sentences that are used to control the behavior of these objects are called DCL or data control language.
o GRANT - gives user's access privileges to database
o REVOKE - withdraw access privileges given with the GRANT command


Top

Commands & Queries

CREATE COMMAND













































































INSERT COMMAND











































SELECT COMMAND


























DELETE COMMAND























































UPDATE COMMAND









































ALTER COMMAND









































RENAME COMMAND


































DROP COMMAND









































Top


Clicky Web Analytics