UNIT III - DATABASE MANAGEMENT

DATABASE CONCEPTS
 A Database is a collection of DATA/INFORMATION that is organized
so that it can be easily accessed, managed and updated.

 In Database ,Data is organized into rows, columns and tables, and it is

indexed to make it easier to find relevant information. It works like a
container which contains the various object like Tables, Queries, Reports
etc. in organized way.

WHY DO WE NEED DATABASE?

 To manage large chunks of data: if size of data increases into
thousands of records, it will simply create a problem to manage. Database
can manage large amount of data.
 Accuracy: Through validation rule in database, data accuracy can be
maintained.
 Ease of updating data: With the database, we can flexibly update the
data according to our convenience. Moreover, multiple people can also edit
data at same time.
 Security of data: With databases we have security groups and privileges
to restrict access.
 Data integrity: In databases, we can be assured of accuracy and
consistency of data due to the built-in integrity checks and access controls.
Database Management System(DBMS)
 A DBMS refers to a software that is responsible for storing, maintaining and
utilizing database in an efficient way.
 A Database along with DBMS software is called Database
System.
 Example of DBMS software are Oracle, MS SQL Server, MS Access,
 Paradox, DB2 and MySQL etc.
 MySQL is open source and freeware DBMS.
Advantages of Database System
 Databases reduces Redundancy
It removes duplication of data because data are kept at one place and all the
application refers to the centrally maintained database.
 Database controls Inconsistency
When two copies of the same data do not agree to each other, then it is called
Inconsistency. By controlling redundancy, the inconsistency is also controlled.
 Database facilitate Sharing of Data
Data stored in the database can be shared among several users.
 Database ensures Security
Data are protected against accidental or intentional disclosure to unauthorized
person or unauthorized modification.
 Database maintains Integrity
It enforces certain integrity rules to insure the validity or correctness of data. For
eg. A date can’t be like 31/31/2000.

Data Model- Way of data representation

Data model is a model or presentation which shows How data is organized ? or
stored in the database. A data is modeled by one of the following given-
 Relational Data Model
In this model data is organized into Relations or Tables (i.e. Rows and Columns).
A row in a table represents a relationship of data to each other and also called a
Tuple or Record. A column is called Attribute or Field.
 Network Data Model
In this model, data is represented by collection of records and relationship among
data is shown by Links.
 Hierarchical Data Model
In this model, Records are organized as Trees. Records at top level is called Root
record and this may contains multiple directly linked children records.
 Object Oriented Data Model
In this model, records are represented as a objects. The collection of similar types
of object is called class.
RELATIONAL DATABASE
 A relational database is a collective set of multiple data sets
organized by tables, records and columns. Relational database
establish a well-defined relationship between database tables.
Tables communicate and share information, which facilitates
data search ability, organization and reporting.
 A Relational database use Structured Query Language (SQL),
which is a standard user application that provides an easy
programming interface for database interaction.

RELATIONAL DATABASE TERMS

 Relation (Table)
A Relation or Table is Matrix like structure arranged in Rows and Columns. It
has the following properties-
 Atomicity : Each column assigned a unique name and must have
atomic(indivisible) value i.e. a value that cannot be further subdivided.
 No duplicity: No two rows of relation will be identical i.e. in any two
rows value in at least one column must be different.
All items in a column are homogeneous i.e. same data type.
 Ordering of rows and column is immaterial.
  Domain - It is collection of values from which the value is derived for a
column.
 Tuple / Entity / Record - Rows of a table is called Tuple or Record.
 Attribute/ Field- Column of a table is called Attribute or Field.
 Degree - Number of columns (attributes) in a table.
 Cardinality - Number of rows (Records) in a table.

KEYS IN A DATABASE
Key plays an important role in relational database; it is used for identifying
unique rows from table & establishes relationship among tables on need.
Types of keys in DBMS
 Primary Key – A primary is a column or set of columns in a table that
uniquely identifies tuples (rows) in that table.
 What is a unique key?
A Unique key constraint uniquely identified each record in the database.
This provides uniqueness for the column or set of columns.
A Primary key constraint has automatic unique constraint defined on it.
But not, in the case of Unique Key.
There can be many unique constraint defined per table, but only one
Primary key constraint defined per table.
 Candidate Key –It is an attribute or a set of attributes or keys
participating for Primary Key, to uniquely identify each record in that
table.
 Alternate Key – Out of all candidate keys, only one gets selected as
primary key, remaining keys are known as alternate or secondary keys.
 Foreign Key – Foreign keys are the columns of a table that points to the
primary key of another table. They act as a cross-reference between tables.

Primary Key It will not accept null and repeated values

Unique Key It will accept null values but not repeated values
Null It will accept null values
Not Null It will not accept null values
Candidate Key Alternate to Primary key
 STRUCTURED QUERY LANGUAGE
 SQL is an acronym of Structured Query Language. It is a standard language
developed and used for accessing and modifying relational databases.
 The SQL language was originally developed at the IBM research
laboratory in San José, in connection with a project developing a
prototype for a relational database management system called
System R in the early 70s.
SQL is being used by many database management systems.
Some of them are:
 MySQL
 PostgreSQL
 Oracle
 SQLite
 Microsoft SQL Server
Advantages of using SQL
 Interactive Language-This language can be used for communicating
with the databases and receive answers to the complex questions in seconds.
 Multiple data views-The users can make different views of database
structure and databases for the different users.
 Portability-SQL can be used in the program in PCs, servers, laptops, and
even some of the mobile phones and even on different dbms softwares
 No coding needed-It is very easy to manage the database systems
without any need to write the substantial amount of code by using the
standard SQL.
 Well defined standards-Long established are used by the SQL
databases that is being used by ISO and ANSI. There are no standards
adhered by the non-SQL databases.
MySQL is currently the most popular open source database software. It is a
multi-user, multithreaded database management system. MySQL is especially
popular on the web.

MySQL Features
Open Source & Free of Cost:
It is Open Source and available at free of cost.
 Portability:
Small enough in size to install and run it on any types of Hardware and OS like
Linux,MS Windows or Mac etc.
 Security :
Its Databases are secured & protected with password.
 Connectivity
Various APIs are developed to connect it with many programming languages.
 Query Language
It supports SQL (Structured Query Language) for handling database.
Types of SQL Commands
 DDL (Data Definition Language)
To create database and table structure-commands like
CREATE , ALTER , DROP etc.
 DML (Data Manipulation Language)
Record/rows related operations. Commands like SELECT....,
INSERT..., DELETE..., UPDATE.... etc.
 DCL (Data Control Language) used to manipulate
permissions or access rights to the tables. commands like
GRANT, REVOKE etc.
 TCL(Transactional control Language)
Used to control the transactions. Commands like COMMIT,
ROLLBACK, SAVEPOINT etc.

Data type in MySQL

 Numeric Data Types:
o INTEGER or INT – up to 11 digit number without decimal.
o SMALLINT – up to 5 digit number without decimal.
o FLOAT (M,D) or DECIMAL(M,D) or NUMERIC(M,D)
Stores Real numbers upto M digit length (including .) with D decimal places.
e.g. Float (10,2) can store 1234567.89
 Date & Time Data Types:
o DATE - Stores date in YYYY-MM-DD format.
o TIME - Stores time in HH:MM:SS format.
 String or Text Data Type:
o CHAR(Size)
A fixed length string up to 255 characters. (default is 1)
o VARCHAR(Size)
A variable length string up to 255 characters.
Char, Varchar, Date and Time values should be enclosed with single
(‘ ‘)or double ( “”) quotes inMySQL. varchar is used in MySQL and
varchar2 is used in Oracle.

Database Commands in MySql

 Getting listings of available databases

mysql> SHOW DATABASES;

 Creating a Database

Syntax: Create database database_name;

 To use the created database

Syntax: use database_name;

 Deleting a database
mysql> DROP database <databasename>;
 to remove table
mysql> drop table <tablename>;
 To show list of tables in opened database
mysql> SHOW TABLES;
  Creating a table in the database is achieved with
CREATE table statement.

Syntax: create table table_name(column_name1 data_type1,column_name2

data_type2......,column_nameN data_typeN);

 The command DESCRIBE is used to view the structure

of a table.

Syntax: describe table_name; or desc table_name;

 Adding rows to the table

Syntax: insert into table_name values(value1,value2.....valueN);

  To view the contents of the table

Syntax: select * from Table_name;

  Retrieving single column

Syntax: select column_name from table_name;

 Retrieving multiple column

Syntax: select column_name1, column_name2 from table_name;

WHERE condition that the data must satisfy;
• Comparison operators are: < ; <= ; = ; != or <> ; >= ; >

• Logical operators are: AND ; OR ; NOT

 Selecting rows by using the WHERE clause in the SELECT

command

Syntax: select column_name from table_name where condition;

 Selecting rows with null values in specific column

Syntax: select column_name from table_name where condition is null;

mysql> select * from emp where designation is null;

 BETWEEN- to access data in specified range

 IN- operator allows us to easily test if the expression in the list of values.

 Pattern Matching – LIKE Operator

A string pattern can be used in SQL using the following wild card
 % Represents a substring in any length
 _ Represents a single character
Example:
 ‘A%’ represents any string starting with ‘A’ character.
 ‘%A’ represents any string ending with ‘A’ character.
 ‘_B%’ represents any string having second character ‘B’
 'A%O' represents any values that start with "A" and ends with "O"
A pattern can be used with LIKE operator.
  To display data after removal of duplicate values from specific
column.
Syntax: select distinct column_name from table_name;

 Deleting selected rows from a table using the DELETE command

Syntax: delete from table_name where condition;

 To modify or update entries in the table use the UPDATE

command
Syntax: update table_name set column_name=value where condition;
Creating Table with Constraints
The following constraints are commonly used in SQL:
 NOT NULL -It Ensures that a column cannot have a NULL value
 UNIQUE - It Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
Uniquely identifies each row in a table
 FOREIGN KEY - It Uniquely identifies a row/record in another
table CHECK - It Ensures that all values in a column satisfies a
specific condition
 DEFAULT - It Sets a default value for a column when no value is
specified
 INDEX - It is Used to create and retrieve data from the database very
quickly

mysql> CREATE TABLE Persons (ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,FirstName varchar(255),Age int,
City varchar(255) DEFAULT ‘Jaipur');
 Table Name : ip1

Field Type Null Key Default Extra

Name Varchar(20) YES NULL
Mobile Int(11) YES NULL
Email Varchar(20) YES NULL

____________________________________________________________________

_____________________________________________________________________

_____________________________________________________________________

Table Name: ip2

Field Type Null Key Default Extra

Name Varchar(20) NO
Mobile Int(11) NO PRI NULL
Email Varchar(20) YES NULL

_______________________________________________________________________________

________________________________________________________________________________

________________________________________________________________________________
 Table Name: ip3

Field Type Null Key Default Extra

Name Varchar(20) YES UNI NULL
Mobile Int(11) YES NULL

Email Varchar(20) YES NULL

_______________________________
______________________________________________________

________________________________________________________________________________

___________________________________________________________________
________________________

Write down the constraints used above,

Altering Table
The SQL ALTER TABLE command is used to add, delete or modify
columns in an existing table. You should also use the ALTER TABLE
command to add and drop various constraints on an existing table.
Syntax
 The basic syntax of an ALTER TABLE command to add a New
Column in an existing table is as follows.
ALTER TABLE table_name ADD column_name datatype;

 The basic syntax of an ALTER TABLE command to DROP

COLUMN in an existing table is as follows.
ALTER TABLE table_name DROP COLUMN column_name;

 The basic syntax of an ALTER TABLE command to change the

DATA TYPE of a column in a table is as follows.
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
 SQL FUNCTIONS

Functions

Single row functions Multiple row

functions

MYSQL ORDER BY
The ORDER BY clause is used in a SELECT statement to sort results either in
ascending or descending order.

Syntax : SELECT Column_Names FROM Table_Name

ORDER BY {Column1}[ASC | DESC] {Column2}[ASC | DESC]
Eg:
Suppose we are having emp table with following data.
Query 1 : Sorting in Ascending order

Query 2 : Sorting in Descending order

Query 3 : Ordering data on multiple columns
 MYSQL GROUP BY

In practical applications many times there arises a need to get reports based on
some groups of data. SQL provides GROUP BY Clause to handle all such
requirements.

The SQL GROUP BY Clause is used along with the group functions to retrieve
data grouped according to one or more columns.

We often use the GROUP BY clause with aggregate functions

such as SUM, AVG, MAX, MIN, and COUNT. The aggregate function
that appears in the SELECT clause provides information about each
group.

Syntax: SELECT column_name, aggregate_function(column_name)

FROM table_name
GROUP BY column_name;
1. To Display the categories in Designation.

2. To Display number of employees in each category of Designation.

3. To Display designation and sum of salary of all the categories of employees.

4. Using where clause in GROUP BY Clause:

Syntax - SELECT column_name,aggregate_function(column_name) FROM

table_name WHERE column_name operator value GROUP BY
column_name;

Eg:

5. Using ORDER BY clause in GROUP BY Clause:

Syntax - SELECT column_name,aggregate_function(column_name) FROM

table_name WHERE column_name operator value GROUP BY
column_name ORDER BY colum_name;

Eg:
 MYSQL GROUP BY WITH HAVING

Having clause is used to filter data based on the group functions.

Syntax: SELECT column_name,aggregate_function(column_name)

FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Eg:
Aggregate Functions & NULL- Perform operation over set of values
Consider a table emp1 having following records
 Aggregate function will not take NULL into consideration.
 NULL is simply ignored by all the aggregate functions.
1. COUNT() - This function is used to count the number of values in a column.

Eg:

2. MAX() - This function is used to get the maximum value from a column.
Eg:

3. MIN() - This function is used to get the minimum value from a column.
Eg:

4. AVG() - This function is used to get the average value of a numeric column.
Eg:

5. SUM() - This function is used to get the sum of a numeric column.

Eg:

Cartesian product (X)/cross join

Cartesian Product is denoted by X symbol. Lets say we have two
relations R1 and R2 then the cartesian product of these two
relations (R1 X R2) would combine each tuple of first relation R1 with
the each tuple of second relation R2.
Eg:
JOIN – Join is used to fetch data from two or more tables, which
is joined to appear as single set of data. It is used for combining
column from two or more tables by using values common to both
tables.

Types of JOIN
Following are the types of JOIN that we can use in SQL:
 Equi Join or Inner Join
 Natural Join
 Left Join
 Right Join
Consider the table(s) Suppliers and Orders.

Equi Join or Inner Join :

This is a simple JOIN in which the result is based on matched data as per the
equality condition specified in the SQL query.

Syntax:

Select column_list from table1,table2 where

table1.column_name=table2.column_name;

[or]

Select * from table1 join table2 [ON (Join condition)]

Eg:

Query:

Select * from suppliers,orders where suppliers.supplier_id=orders.supplier_id;

[or]

Select * from suppliers join orders on suppliers.supplier_id=orders.supplier_id;

Natural Join :
The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a
way that, columns with the same name of associated tables will appear once only.

Note: Don't use ON clause in a natural join.

Syntax:

Select * from suppliers table1 natural join table2;

Query:

Select * from suppliers natural join orders;

Left Join :
The LEFT JOIN is a particular type of join that selects rows from both left and
right tables that are matched, plus all rows from the left table (Table A) even with
no matching rows found in the right table (Table B).

Syntax:

Select column_name(s) from table1 Left join table2 on

table1.column_name=table2.column_name;

Query:

Select * from suppliers left join orders on suppliers.supplier_id=orders.supplier_id;

Right Join :
The RIGHT JOIN is a particular type of join that selects rows from both left and
right tables that are matched, plus all rows from the right table (Table B) even with
no matching rows found in the left table (Table A).

Syntax:

Select column_name(s) from table1 Right join table2 on

table1.column_name=table2.column_name;
Query:

Select * from suppliers right join orders on

suppliers.supplier_id=orders.supplier_id;

INTERFACE OF PYTHON WITH AN SQL DATABASE

A database is nothing but an organized collection of data. Data is organized into
rows, columns and tables and it is indexed to make it easier to find relevant
information. All companies whether large or small use databases. So it become
necessary to develop project/software using any programming language like
python in such a manner which can interface with such databases which support
SQL. Generalised form of Interface of python with SQL Database can be
understood with the help of this diagram. Form/any user interface designed in any
programming language is Front End where as data given by database as response is
known as Back-End database.SQL is just a query language, it is not a database.
Why choose Python for database programming
Following are the reason to choose python for database programming
• Programming more efficient and faster compared to other languages.

• Portability of python programs.

• Support platform independent program development.

• Python supports SQL cursors.

• Python itself take care of open and close of connections.

• Python supports relational database systems.

• Porting of data from one dbms to other is easily possible as it support large
range of APIs for various databases.

Here we are using mysql as back end database because of it is open source,free
and portable and widely used. Any one of mysql-connector or MySQLdb can be
used for database programming.
mysql-connector
MySQL-Connector enables Python programs to access MySQL databases, using
an API that is compliant with the Python Database API Specification v2.0 (PEP
249). It is written in pure Python and does not have any dependencies except for
the Python Standard Library.
Steps to use mysql-connector
1. Download Mysql API ,exe file and install it
2. Install Mysql-Python Connector (Open command prompt and execute
command) >pip install mysql-connector
3. Now connect Mysql server using python
4. Write python statement in python shell import mysql.connector
If no error message is shown means mysql connector is properly installed

For database interface/database programming ,connection must be established.

Before establishing connection there must be mysql installed on the system and a
database and table is already created. In following way we can establish a
connection with mysql database through mysql.connector.
After successful execution of above statements in python following out will be
displayed

Otherwise an error message will be shown.

Cursor object :
The MySQLCursor class instantiates objects that can execute operations such as
SQL statements. Cursor objects interact with the MySQL server using a
MySQLConnection object.

How to create cursor object and use it

 Through line 4 we are creating a database named school if it is already not

created with the help of cursor object.
 Line 5 executes the sql query show databases and store result in mycursor
as collection ,whose values are being fetched in x variable one by one.
On execution of above program school database is created and a list of available
databases is shown.
How to create table at run time
Table creation is very easy ,if we are already well versed in sql table creation
then we have to just pass the create table query in execute() method of cursor
object. But before table creation we must open the database. Here we are opening
database school(through connect() method) before student table creation.

On successful execution of above program a table named student with four fields
rollno,name,age will be created in school database.
We can check student table in mysql shell also,if required.

How to change table structure/(add,edit,remove column of a table)

at run time
To modify the structure of the table we just have to use alter table query.Below
program will add a column Grade in the student table.
Above program will add a column Grade in the table student and will display the
structure of the table
How to insert record in a table at run time
How to search records of a table at run time
Below statement demonstrate the use of select query for searching specific record
from a table.

How to fetch all records of a table at run time

MySQLCursor.fetchall() Method
The method fetches all (or all remaining) rows of a query result set and
returns a list of tuples. If no more rows are available, it returns an empty list.
How to fetch one record of a table at run time
MySQLCursor.fetchone() Method
This method retrieves the next row of a query result set and returns a single
sequence, or None if no more rows are available. By default, the returned tuple
consists of data returned by the MySQL server, converted to Python objects.

MySQLCursor.fetchmany() Method rows =

cursor.fetchmany(size=1)
This method fetches the next set of rows of a query result and returns a list of
tuples. If no more rows are available, it returns an empty list.
rowcount : Rows affected by Query. We can get number of rows affected by the
query by using rowcount. We will use one SELECT query here.

buffered=True
We have used my_cursor as buffered cursor. my_cursor =
my_connect.cursor(buffered=True)
This type cursor fetches rows and buffers them after getting output from MySQL
database. We can use such cursor as iterator. There is no point in using buffered
cursor for single fetching of rows.If we don’t use buffered cursor then we will
get -1 as output from rowcount.

How to delete record of a table at run time

How to update record of a table at run time
Manage Database Transaction
Database transaction represents a single unit of work. Any operation which
modifies the state of the MySQL database is a transaction.
Python MySQL Connector provides the following method to manage database
transactions.
commit – MySQLConnection.commit() method sends a COMMIT statement to
the MySQL server, committing the current transaction.
rollback – MySQLConnection.rollback revert the changes made by the current
transaction.
AutoCommit – MySQLConnection.autocommit value can be assigned as
True or False to enable or disable the auto-commit feature of MySQL. By default
its value is False.