UNIT-3 : Database Management System (DBMS)

Database – A database is an organized collection of data. For example:- In a stationary shop,

detailed records of the materials available in the shop is database. Similarly in a computerized
system, we need to maintain several files, we would used database programs such as Microsoft
Access, OpenOffice.org Base, and MySQL. These database programs are used to organize the data as
per our needs in the computer system.

Database Management System (DBMS) - A database management system is a

software package with computer programs that controlsthe creation, maintenance and use of a
database. A DBMS allowsdifferent user application programs to concurrently access the same database.
Some of the DBMSs are Oracle, IBM DB2, Microsoft SQL server, Microsoft Access, PostgreSQL,
MySQL, FoxPro and SQLite.
Advantages of Database:-
 Reduces Data Redundancy: no chance of encountering duplicatedata
 Sharing of Data: the users of the database can share the data among themselves
 Data Integrity: Data integrity means that the data is accurate and consistent in the database
 Data Security: Only authorised users are allowed to access the database and their identity is
authenticated using a username and password.
 Privacy: The privacy rule in a database states that only the authorized users can access a
database according to its privacy constraints.
 Backup and Recovery: Database Management Systemautomatically takes care of
backup and recovery.
 Data Consistency: Data Consistency means there should bemultiple mismatching copies
of the same data.
Data can be organized into two types:-
 Flat File: Data is stored in a single table. Usually suitable for less amount of data.
 Relational: Data is stored in multiple tables and the tables are linked using a common field.
Relational is suitable for medium to large amount of data.
 Database Servers – Database servers are dedicated computers that hold the actual databases
and run only the DBMS and related software. Databases on the database servers are accessed
through command line or graphic user interface tools referred to as Frontends; database servers
are referred to as Back-ends. Such type of data access is referred to as Client-server model.
-:RDBMS:-
A relational database management system (RDBMS) is a database management system that is
based on the relational model. In the relational model of a database, all data is represented in
terms of tuples (rows), grouped into relations (tables). A database organized in terms the
relational model is a relational database.
Database Concepts:- Database contains objects that are used forstoring and managing
information.
1. Item : - Item is about which information is stored in thedatabase.
2. Field:- Each question that we ask about our item is a Field.
3. Record:- Record is a set of information (made up of fields)stored in your database about
one of the items.
4. Value:- Value is the actual text or numerical amount or datethat you put in while
adding information to your database.
 For example:- Database : Employee

Emp_Code Emp_Name Emp_Address Emp_Designatio Emp_ContactNo Emp_Salary

E001 ABC Meerut Manager 9876543210 Rs.50000
 Item : Employee
 Field : Emp_Code , Emp_Name , Emp_Address , Emp_Designation ,Emp_ContactNo ,
Emp_Salary
 Record :
E001 ABC Meerut Manager 987654321 Rs. 50,000

 Value : E001 , ABC , Meerut , Manager , 9876543210 , Rs. 50,000

5. Key Field :- Key Field is a value in a Field that uniquely identifies the record. Eg. E001
which is unique to every employee.
Important Question :- How data is organized in a RDBMS ?
Ans :- In RDBMS, data is organized in the form of inter linkedtables.
TABLE :- A table is a set of data elements that is organized using a model of vertical
columns and horizontal rows. Each row is identified by a unique key index or the key
field.
COLUMNS OR FIELD :- A column is a set of data values of a particular simple type, one for each
row of the table. For eg. Emp_Code, Emp_Name, Emp_Address etc.
ROWS OR RECORDS OR TUPLES :- A row represents a single, data items in a table. Each row in
a table represents a set of related data, and every row in the table has the same
structure.
DATA TYPES:- Datatypes are used to identify the type of datawe are going to store in
the database.
Categories of data types :- Data types can be broadly classifiedinto five categories:-
1. Numeric Types
2. Alphanumeric Types
3. Binary Types
4. Date Time
5. Other variable Types

NUMERIC TYPES:- They are used for describing numeric values like mobile number, age, etc.
The different types of numeric data types available are-
1. Boolean (Yes/No) 6. Numeric
2. TinyInt (Tiny Integer) 7. Decimal
3. SmallInt (Small Integer) 8. Real
4. Integer 9. Float
5. BigInt (Big Integer) 10. Double
ALPHANUMERIC TYPES:-
The list of different data types available in alphanumeric types are
1. LongVarChar (Memo) (Long Text)
2. Char (Text-fix) (Small Text)
3. VarChar (Text) (Text of specified Length)
4. VarChar_IgnoreCase (Text) (Comparison are not casesensitive)
BINARY TYPES:-
Binary types are used for storing data in binary formats. It can be used for storing photos, music
files or (in general file of any format) etc.
The list of different data types available in Binary types are :-
1. LongVarBinary (Image)
2. Binary (Binary (fix)
 3. VarBinary (Binary)
DATE TIME:-
Date time data types are used for describing date and time values for the field used in the table of
a database. It can be used for storing information such as date of birth, date of admission
etc.
The list of different data types available in Date Time type are:-
1. Date (Stores month, day and year information)
2. Time (Store hour, minute and second information)
3. Timestamp (Stores date and time information)
PRIMARY KEY:- A primary key is a unique value that identifies a row in a table. These keys are
also indexed in the database, making it faster for the database to search a record.
FOREIGN KEY:- The foreign key identifies a column or set of columns in one (referencing) table
that refers to a column or set of columns in another (referenced) table.
Note:- The “one” side of a relation is always the parent, and provides the PK(Primary Key)
Attributes to be copied. The “many” side of a relation is always the child, into which the
FK (Foreign Key) attributes are copied.
Memorize it : one, parent, PK (Primary Key) ; many, child , FK(Foreign Key)
There are two types of languages:-
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)

DATA DEFINITION/DESCRIPTION LANGUAGE:- It is a standard for commands that define the

different structures in a database. DDL statements create,modify and remove database objects such
as tables, indexes and users.
Common DDL Statements are:-
1. Create: - Used to create database objects.
2. Alter: - Used to modify database objects.
3. Drop: - Used to delete database objects.
DATA MANIPULATION LANGUAGE:- It is a standard for commands that enables users to
access and manipulate data in adatabase.
Common DML Statements are:-
1. SELECT: - Used for retrieval of information from the database.
2. INSERT: - Used for insertion of new information into thedatabase.
3. DELETE: - Used for deletion of information in the database.
4. UPDATE: - Used for modification of information in the database.

Types of DML:-
1. Procedural: - The user specifies what data is needed and how toget it.
2. Non Procedural: - The user only specifies what data is needed.
Note: - A popular data manipulation language is SQL (StructuredQuery Language.)
In this article on SQL Commands, I am going to consider thebelow database as an example, to show
you how to write commands.
 Employee_Info:-

Emp. ID Emp. Emergency Phone Address City Country

Name Contact Name Number

01 Shanaya Abhinay 9812765612 Oberoi Mumbai India

Street 23
02 Anay Soumya 9412156783 Marathi Delhi India
House No 23
03 Preeti Rohan 9712345679 Queens Road 45 Bangalore India

CREATE : To create Table CREATE TABLE Employee_Info

DROP : To Delete
DROP DATABASE Employee (complete informationpresent in the database will be lost)
DROP TABLE Table Name (complete information presentin the table will be lost)
TRUNCATE Table Employee_Info (your information will belost, but not the table)
 ALTER : This statement is used to add, delete, modify columns inan existing table

ALTER TABLE
Employee_Info ADD
BloodGroup varchar(255);.
 INSERT : This statement is used to insert new records into thetable.
INSERT INTO Employee_Info
VALUES ('02', 'Anay','Soumya', '9432156783', ' MarathalliHouse No 23', 'Delhi', 'India');
 UPDATE : This statement is used to modify the records alreadypresent in the table
UPDATE Employee_Info
SET EmployeeName = 'Aahana', City= 'Ahmedabad' WHEREEmployeeID = 1;
 DELETE : This statement is used to delete the existing records ina table
DELETE FROM Employee_Info WHERE EmployeeName='Preeti';
 SELECT : This statement is used to select data from a database and the data returned is
stored in a result table, called the result-set.
SELECT EmployeeID, EmployeeName FROM Employee_Info;
(*) is used to select all from the table SELECT * FROMEmployee_Info;