Data Base

Databases are designed to offer an organized mechanism for

storing, managing and retrieving information. They do so
through the use of tables.
It is an well organized collection of information about an
entity or organization.

Data Base System: It allow the user to access and manipulate

the data contained in the database in convenient and
effective manner.

DBMS: The software that takes care of database. It maintains

creation, addition, deletion, modification of files.
Data Base Concepts
 Advantages of Data Base
Reduced data redundancy
Reduced updating errors and increased consistency
Greater data integrity and independence from
applications programs
Improved data access to users through use of host
and query languages
Improved data security
Reduced data entry, storage, and retrieval costs
Facilitated development of new applications
program
 Disadvantages of Data Base
Database systems are complex, difficult, and time
consuming to design
Substantial hardware and software start-up costs
Damage to database affects virtually all applications
programs
Extensive conversion costs in moving from a file-
based system to a database system
Initial training required for all programmers and
users.
 Relational Data Base System

A relational database organizes data in tables (or

relations). A table is made up of rows and columns.
A row is also called a record (or tuple). A column is
also called a field (or attribute).
A database table is similar to a spreadsheet.
However, the relationships that can be created
among the tables enable a relational database to
efficiently store huge amount of data, and
effectively retrieve selected data.
 Relational Data Base System ( terminology)
Entity: It is an object/thing which can be distinctly
identified . It may be person or thing or item.
Relation: The table in a database is called relation
where the records are stored.
Tuple: The records in a relation are called tuple. It
can be called records or rows also.
Attribute: The column or fields in a relation is called
attributes.
Cardinality: The number of records present in a
table.
Degree/Arity: The number of fields in a table.
 Relational Data Base System ( Terminology)
Primary Key: it is a field or combination of fields through
which a record can uniquely identified in a table. The
primary key fields should not be null.
Candidate Key: All attribute combinations in a table that
can serve as primary key are called candidate key. They
behave the candidate for the primary key.
Alternate Key: From the different group of candidate key,
the user has to decide one group as primary key. The other
groups are called alternate key.
Unique Key: The domain of the field will be unique. The
values may be null.
Foreign Key: A non key attribute of a table whose value
derives from primary key of another table .
 Relational Algebra
Algebra is a formal structure consisting of sets and
operations on those sets. Relational algebra is a formal
system for manipulating relations.
 Operands of this algebra are relations.
 Operations of this algebra include the usual set operations
Union Union all Intersect Minus Cartesianproduct
 Operations (since relations are sets of tuples), and special
operations defined for relations
1. selection
2. projection
3. Join ( equi join, natural join, inner join)
 Relational Algebra

Union
R UNION S
Includes all tuples that are in R or S or both.
Duplicate tuples are removed( consider only once).
For a union operation R U S to be valid, two
conditions must hold:
The relation R and S must be of the same arity, i.e.
they must have the same number of attributes.
The domains of the ith attribute of R and the ith
attribute of S must be the same for all i.
 Unionall
R UNIONALL S
Includes all tuples that are in R or S or both.
Duplicate tuples are not removed. For a union
operation R U S to be valid, two conditions
must hold:
The relation R and S must be of the same arity, i.e.
they must have the same number of attributes.
The domains of the ith attribute of R and the ith
attribute of S must be the same for all i.
 Intersect
R intersect S
Includes all tuples that are common for R and S.

The relation R and S must be of the same arity,

i.e. they must have the same number of
attributes.

The domains of the ith attribute of R and the ith

attribute of S must be the same for all i.
 minus
R minus S
Includes all tuples that are only belong R but not S.

The relation R and S must be of the same arity, i.e.

they must have the same number of attributes.

The domains of the ith attribute of R and the ith

attribute of S must be the same for all i.
 Relational Algebra

Cartesian Product
The Cartesian Product is also an operator which works on
two sets. It is sometimes called the CROSS PRODUCT or
CROSS JOIN or UNRESTRICTED JOIN. It combines the
tuples of one relation with all the tuples of the other
relation.
 Relational Algebra

Selection
Selects tuples from a relation whose attributes meet the
selection criteria, which is normally expressed as a predicate.
R2 = σ (R1,P) select
That is, from R1 we create a new relation R2 containing
those tuples from R1 that satisfy (make true) the
predicate P.
 Relational Algebra

Projection
Chooses a subset of the columns in a relation, and
discards the rest.
R2 = ∏(R1,D1,D2,...Dn) project
That is, from the tuples in R1 we create a new relation
R2 containing only the domains D1,D2,..Dn.
 SQL
What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
 SQL is an ANSI (American National Standards Institute) standard
What Can SQL do?
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
 SQL

SQL can be divided into four parts:

Data Definition Language (DDL)
commands related to databases, table etc
Data Manipulation Language (DML)
Commands Related to record operations
Data Control language ( DCL)
commands specifying access rights to relations
Transaction Control Language( TCL)
Commands related to transaction controls
 SQL

Data Definition Language (DDL)

The DDL part of SQL permits database tables to be created or
deleted. It also defines indexes (keys), specifies links between
tables, and imposes constraints between tables. The most
important DDL statements in SQL are:

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
 SQL

Data Manipulation Language (DML)

 SELECT - extracts data from a database

 UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database
 SQL Data Types

There are so many data types available in SQL.

To store any character :
char
varchar
varchar2
( minimum one character and upto 255 characters)
To store numerical value:
number
int
decimal
To store data information:
date ( dd-mmm-yyyy)
 MySQL Data Types
Class Data types Description
Text CHAR(size) A fixed length string between 1 to 255 characters.

VARCHAR(size) A variable length string between 1 to 255 characters. It

saves memory.
BINARY
VARBINARY
TEXT
TINYTEXT
MEDIUMTEXT
LONGTEXT
ENUM
SET

Numeric INT Used to store whole numbers

DECIMAL(M,N) It stores the number with or without the fractional
BIGINT part. M denotes the number of significant digits and n
MEDIUMINT denotes the digits to the right side of the decimal
SMALLINT point.
TINYINT
FLOAT
REAL
BOOLEAN
 MySQL Data Types
Class Data types Description
date Date Stores the date type information.
DATESTAMP Combination of date and time.
DATETIME Time in HH:MM:SS format
TIME A 2 or 4 digit format
YEAR
 Working on MySQL
Creating a database
Create database school; // school is the database to be created.
Create database if not exists school; // to avoid error if database
exists
For opening the database
use school;
To display database lists available
show databases;
To close database
close databases;
To delete a database
Drop database school;
To see the table list inside the database
Show tables;
 Creating Table

SQL CREATE TABLE Syntax

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type( size),
....
);
The data type specifies what type of data the column can
Hold and size denotes the space required for it.
 Creating Table

Create table student

(
Roll int(2), // size may not be given
Name char(20),
Address varchar(30),
DOB date,
Clas int,
Sec char(1),
Per decimal(6,2)
);
 Creating Table

The table may be created from an existing table.

CREATE TABLE STD SELECT * FROM STUDENT;

// std will be created from student
 Deleting Table

Drop table tablename;

E.g. drop table student;

Drop table if exists tablename;

e.g. drop table if exist student;
 Modifying Table Structure (Alter Table)
The ALTER TABLE statement is used to add, delete, or
modify columns in an existing table.
To delete a column in a table:
ALTER TABLE table_name DROP COLUMN column_name ;
To change the data type of a column in a table:
ALTER TABLE table_name MODIFY column_name datatype(size);
You can rename a field
Alter table student change per percentage decimal(6,2);
You can change the name of the table.
Alter table student rename to student_rec;
Rename table student to student_rec;
 Modifying Table Structure (Alter Table)
To add a column in a table:
ALTER TABLE table_name ADD column_name datatype(size);
e.g. Alter table student Add sex char(1);
The field will be added at end of table by default. If you want to add
the field at your choice, you can use first/after existing column name.
For more fields to be added, you can use the above command.

VIEWING TABLE STRUCTURE:

Desc tablename;
e.g. desc student;
or
show columns from student;
 DML Commands
INSERTING RECORDS:
(i) INSERT INTO TABLENAME VALUES( value1, value2…………….);
The corresponding values are to put within braces. If the datatype of
field is char or date, the value should be placed within single cotta(‘)
otherwise value should be put as it is.
INSERT INTO STUDENT VALUES( 12,’tafs’,’subroto park’,1955-08-30,98.5);
ii)Insert into tablename( field1, field2) values(value1, value2);
INSERT INTO STUDENT (ROLL,NAME,DOB ) VALUES( 12,’tafs’,’1955-08-30’);
We can insert more records into a table by giving a single command
also.
INSERT INTO TABLENAME VALUES( values for record1), ( values for record2 ), ( values for
record 3);
Inserting record from an existing table:
INSERT INTO STD SELECT * FROM STUDENT;
 DML Commands RETRIEVING RECORDS (select)
To display all records, all fields
Select * // * for all fields. We can use all in place of *
From tablename;
To display all records, selected field(s)
Select field1, field2…
From tablename;
To display selected records, all fields
Select *
From tablename
Where condition;
 DML Commands RETRIEVING RECORDS (select)
To display selected records and selected fields
Select field1, field2
From tablename
Where condition;
For giving conditions, we should know the operator
Mathematical operator +, -, *, /
Relational operator <, <= , >, >=, =, <>
Logical operator AND, OR, NOT
Comparision operator LIKE, BETWEEN,IN

Using LIMIT clause:

Select * from student limit 5; // it displays first 5 records
Select * from student limit 5,10; // it displays records from slno 6 to 10
 DML Commands
DELETING RECORDS:
Delete from tablename; //for all records
e.g. delete from student;
Delete from tablename
Where condition; //for selected records
e.g. delete from student where per<33;
MODIFYING RECORDS:
Update tablename
Set columnname=value
[ where condition];
Multiple fields can be updated. Multiple records can be
updated also.
e.g. update student set per=98.99;
 DML Commands Some Clauses
FROM: indicates the query from which table.
WHERE: uses to give any criteria or condition.
DISTINCT: to get distinct values of field ( eliminating redundant
data)
Select distinct(fieldname)
From tablename;
e.g. select distinct clas from student;
IN: query from predefined values
Select *
From tablename
Where fieldname in( value1, value2……….);
e.g. select * from student where clas in (8,10,12);
 DML Commands Some Clauses
BETWEEN: for query between two specific ranges
Select * From tablename Where fieldname between
value1 and value2; // ( both values are inclusive)
e.g. select * from student where per between 70 and 90;
LIKE: query on the basic of character value. Two wild card
characters are used.
% any character and any number of characters
_ Any character and only one character
Select *
From tablename
Where columnname like ‘s%’;
 DML Commands Some Clauses
ORDER BY: To arrange records either in ascending or
descending order of certain field. By default it is ascending
order. To make it descending, we have to mention desc at
end of field.
Select *
From tablename
Order by fieldname [desc];
We can use multiple fields for ordering
Select *
From tablename
Order by fieldname1 [desc], fieldname2 [desc];
// within field1, record will be arranged on basis of field2
Putting text in the statement:
Select columnname1 ‘text1’ , columnname2
From tablename;
COLUMN ALIAS:
Select name, per*5 as total from student;
The NULL Values
NULL is something of a typeless value. It is used to mean no
value. NULL value do not allow any arithmetic operation on
them. A condition that compares column value to NULL, does
not work properly. To handle such situation MySQL provides
operators.
IS NULL: it returns true of column value is NULL
IS NOT NULL: It returns true of column value is not NULL
e.g. select * from student where name is NULL;
SQL Functions
SQL has many built-in functions for performing calculations on data.
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values
in a column.

Useful aggregate functions:

AVG() - Returns the average value
COUNT() - Returns the number of rows
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum
The SUM() Function
The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name
The AVG() Function
The AVG() function returns the average value of a numeric column.
SELECT AVG(column_name) FROM table_name
SQL COUNT() Function
The COUNT() function returns the number of rows that matches a
specified criteria.
COUNT(column_name) function returns the number of values (NULL
values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of
distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name

The MAX() Function

The MAX() function returns the largest value of the selected column.
SELECT MAX(column_name) FROM table_name

The MIN() Function

The MIN() function returns the smallest value of the selected column.
SELECT MIN(column_name) FROM table_name
The GROUP BY Statement
The GROUP BY statement is used in conjunction with the
aggregate functions to group the result-set by one or
more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
[HAVING column_name operator value]
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the
customers. We use the following SQL statement:
SELECT Customer,SUM(OrderPrice)
FROM Orders
GROUP BY Customer
The result-set will look like this:
Customer SUM(OrderPrice)
Hansen 2000
Nilsen 1700
Jensen 2000
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword
could not be used with aggregate functions.
SQL HAVING Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
SQL HAVING Example
We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find if any of the customers have a total order of
less than 2000.
We use the following SQL statement:
SELECT Customer, SUM(OrderPrice)
FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

The result-set will look like this:

Customer SUM(OrderPrice)
Nilsen 1700
QUERY FROM MULTIPLE TABLES: (joins)
Select *
From table1, table2
Where table1.columnname= table2.columnname;
( For query, there should be a common fieldname in both tables)

Using alias:
Select *
From table1 t1, table2 t2
Where t1.columnname= t2.columnname;
Table : dept Table: Emp
Deptno# Empno#
deptname Ename
Deptno
salary
Deptno Deptname empno ename deptno Salary
10 English 101 ABC 11 35000
11 Maths 102 PQR 14 36000
12 Physics 103 XYZ 10 31000
13 Chemistry
14 Computer
Suppose we give the command
SELECT * from emp, dept;
Then we will get all possible combinations of records of both tables,
which is called CARTESIAN PRODUCT of records.
If table A has 3 records( cardinality) and table B has 4 records the
cardinality of Cartesian product of a and B will be 3X 4=12
Suppose we give the following command then we will get only the
corresponding records of both tables.
SELECT empno, ename, emp.deptno, deptname, salary
from emp, dept
where emp.deptno=dept.deptno;
Instead of using table name, we can use the alias name for the table.
SELECT empno, ename, e.deptno, deptname, salary
from emp e, dept d
where e.deptno=d.deptno;
QUERY FROM MULTIPLE TABLES: (union)
Select * from table1
union
select * from table2;
[Where table1.columnname= table2.columnname;]
( For query, both table structure should be same)
It will display the records from both tables( duplicate
record(if any) comes only once)
Like union all set relation operations can be used such as
( union all, intersect, minus)
JOINs are of three types
Equi join
Non equi join
Natural join
Equi join: In this type of joins, columns are compared for equality.
SELECT empno, ename, e.deptno, deptname, salary
from emp e, dept d
where e.deptno=d.deptno;

Non equi join : In this type of joins, columns are compared for non
equality.
Natural Join: To avoid repeated column, we can give the command like
SELECT emp.*, deptname
from emp e, dept d
where e.deptno=d.deptno;