Database Management System

• Database management system are used when large quantities of data that needs to be tracked in an organization. Classical paper based data management systems cause inefficient retrieval and in accurate storage of valuable data.
•  A database is an organized collection of data provides you with the software tool you need to organize and access that data in a flexible manner.






DBMS example

• Access - Microsoft
• DB/2 - IBM
• Informix
• MS SQL Server
• MySQL - open source etc....

• It include facilities to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents.






Tables

• Tables comprise the fundamental building blocks of any database.
• A table column is referred as a field which represents different characteristics of stored data.
• Table Row is referred as a record which represents individual data items.
• Each record is identified using a unique field called a Primary key.






Queries

• Queries are used to retrieve data from the database as required.  
• Queries are written using a language called Structured Query Language (SQL).
• SQL dwfines the methods used to create and manipulate relational databases on all major platforms.
• SQL comes in many flavors. Oracle databases utilize their proprietary PL/SQL. Microsoft SQL Server makes use of Transact-SQL. However, all of these variations are based upon the industry standard ANSI SQL. 








Structured Query Language

• SQL commands can be divided into two main sub languages.
• The Data Definition Language (DDL) contains the commands used to create and destroy databases and database objects. (Tables, Procedures) etc...
• After the database structure is defined with DDL, database administrators and users can utilize the Data Manipulation Language to insert, retrieve and modify the data contained within it.  






Data Definition Language

• Create statement

              This statement is mainly used for creating databases and tables.

          e.g. 1 To create a database payroll

                           create database payroll  

     

          e.g. 2 To create a table employee with following fields

                        

Field Name	Data Type
Empid	Integer
Empname	Text
Age	Integer
Salary	integer

                        create table employee (Empid int,Empname varchar(50), age int, salary int);

       USE

• USE Statement is used to select a database from the list of database management system.

      e.g.  we want to select payroll database
                USE payroll;
  

     Drop

• DROP statement is used to remove a database / table/ procedure from the database available in database management system.

     Data Manipulation Language (DML)

• The data Manipulation Language (DML) is used to retrieve, insert and modify database information.
• INSERT

              Insert statement is used to insert records into a database.

       e.g. To insert a record into the above created table employee

                   insert into employee values (001,'Saman Silva', 34,12000);

• SELECT

            Allow database users to retrieve the specific information they desire from an operational database. Consider the above create table with sample data 

Empid	Empname	Age	Salary
001	Saman Silva	34	12000
002	Kamal Perera	30	20000
003	Mala Fernando	26	10000

       
         Retrieve all the records

            select * from employee

       Retrieve employees whose salary is greater than 15000

            select * from employee where salary>15000

       Find the employee name whose empid is 003
               select empname from employee where empid=003