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,
[Link] 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 controls the creation, maintenance and use of
a database. A DBMS allows different 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
duplicate data 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 System
automatically takes care of backup and recovery.
Data Consistency : Data Consistency means there should be
multiple 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 for storing and managing information.
1. Item : - Item is about which information is stored in the
database.
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
date that you put in while adding information to your
database
For example, Database : Employee
mp_Code Emp_Name Emp_Address Emp_Designat
io
Emp_ContactNo Emp_Salary
E001 ABC Meerut Manager 9876543210 Rs.5000
0
Item : Employee
Field : Emp_Code , Emp_Name , Emp_Address ,
Emp_Designation ,
Emp_ContactNo , Emp_Salary
Record :
E001 ABC Meerut Manager 987654321 Rs.50000
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
linked tables.
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
data we are going to store in the database.
Categories of data types:- Data types can be broadly
classified into 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 datatypes 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) (Comparisions are not case
sensitive)
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 datatypes 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 a database.
Common DML Statements are:-
1. SELECT :- Used for retrieval of information from the
database.
2. INSERT :- Used for insertion of new information into
the database.
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 to get it.
2. Non Procedural :- The user only specifies what data is
needed.
Note:- A popular data manipulation language is
SQL (Structured Query Language.)
In this article on SQL Commands, I am going to consider
the below database as an example, to show you how to
write commands.
Emp_ID Emp_Name Emergency_conNam Phone_No Address City Country
e
01 Shanaya Abhinay 9898765612 Oberoi Mumbai India
Street 23
2 Anay Soumya 94321567 Marathalli Delhi India
83 House No
23
3 Preeti Rohan 97642345 Queens Bangalore India
19 Road 45
CREATE : To create Table
CREATE TABLE Employee_Info
DROP : To Delete
DROP DATABASE Employee (complete information
present in the database will be lost)
DROP TABLE TableName (complete information present
in the table will be lost)
ALTER : This statement is used to add, delete, modify
columns in an existing table
ALTER TABLE
Employee_Info ADD BloodGroup varchar(255);.
INSERT : This statement is used to insert new records into
the table.
INSERT INTO Employee_Info
VALUES ('02', 'Anay','Soumya', '9432156783', ' Marathalli
House No 23', 'Delhi', 'India');
UPDATE : This statement is used to modify the records
already present in the table
UPDATE Employee_Info
SET EmployeeName = 'Aahana', City= 'Ahmedabad'
WHERE EmployeeID = 1;
DELETE : This statement is used to delete the existing
records in a 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 * FROM Employee_Info;
****