Database Management System [BTCS411] Abhishek Vishwakarma

[20100BTCSICS07312]

PRACTICAL – 1
Introduction to Database Management System(DBMS)

Introduction
Database management system is software that is used to manage the database. The database
is collection of interested data which is used to retrieve, insert and delete the data efficiently.
It is also used to organize the data in the form of a tables, schema, views and reports etc.
For example – The college database organize the data about the admin, staff, student and
faculty.
People use software such as –
 DBASE IV or V
 Microsoft ACCESS
 ORACLE Database
 MySQL

History
It was developed in early 1960’s. In the late 1960’s IBM developed the Integrated
Management System which is standard database system used till day in many places. It was
developed database on the hierarchical model. It was during the year 1970 that the relational
database model was developed by Edgar Codd. Many of the database we use today are
relational based.

Three Layer Architecture

Three layered architecture of DBMS is a framework that helps in structuring database
management system from three aspects- users, DBA, storage. The whole purpose of three
layered structure is to implement abstraction in database systems. Change in one schema
must not affected the other schema. This architecture allows the DBA to design the schema
and implement secure access mechanism on store data. The three layer also called schema
which refers to representation of database at these three levels.

SVIIT, BTECH.(CSE) 1
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

This architecture has three levels:

1. External level
2. Conceptual level
3. Internal level

External level
It is also called view level. The reason this level is called “view” is because several users can
view their desired data from this level which is internally fetched from database with the help
of conceptual and internal level mapping.

Conceptual level
It is also called logical level. The whole design of the database such as relationship among
data, schema of data etc. are described in this level.

Internal level
The level is also known as physical level. This level describes how the data is actually stored
in the storage devices. This level is also responsible for allocating space to the data. This is
the lowest level of the architecture.

Database Management System Functions

The Database Management System performs the following functions:

1. Data Dictionary Management – The data dictionary stores the definitions of data
elements and their relationships. This information is termed as metadata.
2. Data Storage Management – The DBMS creates the complex structures required for
data storage. The users are feed from defining, programming and implementing the
complex physical data characteristics.
3. Data Transformation and Presentation – DBMS supports data independence.
Hence the DBMS translate logical request into commands that physically locate and
retrieve the requested data.
4. Security Management – The DBMS creates a security system that enforces user
security and data privacy within the database. Security rules determine the access
rights of the users.
5. Multiuser Access Control – The DBMS ensures that multiple users can access the
database concurrently without compromising the integrity of the database.
6. Backup and Recovery Management – The DBMS provide backup and data
recovery procedures to ensure data safety and integrity.
7. Data Integrity Mangement – The DBMS promotes and enforce integrity rules to
eliminate data integrity problems, thus minimizing the data redundancy and
maximizing data consistency.
8. Database Access Languages and Application Interface – The DBMS provides data
access via query language.
9. Database Communication Interfaces – Different users may access the database
through a network environment.

SVIIT, BTECH.(CSE) 2
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

MySQL Introduction
MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses.
MySQL is developed, marked and supported by MySQL AB, which is a Swedish company.
MySQL is becoming so popular because of many good reasons-
 MySQL is released under an open source license. So you have nothing to pay to use
it.
 MySQL uses a standard form of the well-known SQL data language.
 MySQL works on many operating systems and with many languages including PHP,
PERL, C, C++, JAVA, etc.
 MySQL works very quickly and works well even with large data sets.
 MySQL is customizable. The open-source GPL license allows programmers to
modify the MySQL software to fit their own specific environments.

MySQL History
 The mSQL database system was designed to connect the data that is stored in tables
using customized fast low level (ISAM) routines.
 ISAM refers to indexed sequential access method ISAM refers to indexed sequential
access method, which is a file management system. It is a technique that helps access
records in the tables sequentially, i.e in the same order in which the records were
entered into the table, or randomly with the help of an index. Every index can be used
to define a different order for the records in the table.
 ISAM was originally developed by IBM before the development of VSAM (Virtual
Storage Access Method) and relational databases.
 When mSQL was tested to see its efficiency, it was observed that mSQL was not
quick enough or flexible enough for the requirements in hand.
 MySQL was initially created for personal usage form mSQL based on the low-level
language ISAM.
 MySQL has been named after co-founder Monty Widenius’s daughter- My. The logo,
a dolphin is known a ‘Sakila’.

Different Data Types

Each column in a database table is required to have a name and a data type. An SQL
developer must decide what type of data will be stored inside each column a table. The data
type is a guideline for SQL to understand what type of data is expected inside of each
columns, and it also identifies how SQL will interact with the stored data.

In MySQL there are three main data types: string, numeric, and date and time.

String Types
Although the numeric and date types are fun, most data you’ll store will be in a string format.
This list describes the common string data types in MySQL.
 CHAR( )
 VARCHARA( )
 BLOB or TEXT
 TINYBLOB or TINYTEXT
 ENUM

SVIIT, BTECH.(CSE) 3
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

Numeric Type
MySQL uses all the standard ANSI SQL numeric data types, so if you’re coming to MySQL
from a different database system. The common numeric data types :
 INT
 TINYINT
 SMALLINT
 MEDIUMINT
 BIGINT
 FLOAT(M,D)
 DOUBLE(M,D)
 DECIMAL(M,D)
Where M represent length and D represent decimal

Date and Time

The MySQL date and time data types are
 DATE – YYYY-MM-DD
 DATE TIME – YYYY-MM-DD HH:MM:SS
 TIMESTAMP – YYYYMMDDHHMMSS
 TIME – HH:MM:SS
 YEAR(M)

Installation of MySQL
Step 1: Go to the official website of MySQL and download the community server edition
software.

Step 2: Next, there are two options available to download the setup. Choose the version
number for the MySQL community server, which you want.

Installing MySQL on windows

Step 1: After downloading the setup, unzip it anywhere and double click the
MSI installer .exe file. It will give the following screen:

SVIIT, BTECH.(CSE) 4
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

Step 2: In the next wizard, choose the Setup Type.

Step 3: Once we click on the Next button, it may give information about some features
requirements.

Step 4: In next wizard ask for confirmation confirmation of product click yes.

SVIIT, BTECH.(CSE) 5
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

After clicking on the Yes button, we will see the list of the products, click on the Execute
button.

Step 5: After completing the installation, click on the Next button.

Step 6:

SVIIT, BTECH.(CSE) 6
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

Step 7: Now, the MySQL installation is complete. Click on the Finish button.

Step 8: Open your MySQL Command Line Client; it should have appeared with a mysql>
prompt. If you have set any password, write your password here. Now, you are connected to
the MySQL server, and you can execute all the SQL command at mysql> prompt as follows.
For example: Check the already created databases with show databases command

MySQL vs Oracle
Oracle:
Oracle is a relational database management system (RDBMS). It was developed by Oracle
Corporation in 1980. It is the first database designed for grid computing that provides the
most flexible and cost-effective way to manage information and application. It runs on major
platforms like windows, unix , linux, and macOS. It is a relational database in which data is
accessed by user through application or query language called SQL.

SVIIT, BTECH.(CSE) 7
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

MySQL:
MySQL is an open source Relational Database Management System (RDBMS) based on
SQL. It runs on platforms like Linux, UNIX, and Windows.
S.No Oracle MySQL
1. It is developed by Oracle in 1980. It is developed by Oracle in 1995.
2. It is commercial. It is open source.
3. Server operating system for Oracle is Server operating system for MySQL is
AIX, HP=UX, Linux, OS X, Solaris, FreeBSD, Linus, solaris, Windows.
Windows.
4. Its primary database model is a Its primary database model is also
Realtional DBMS. Relational DBMS.
5. APIs and other access methods for Oracle APIs and other access methods for
is JDBC, ODBC, ODP.NET, Oracle Call MySQL is ADO.NET, JDBC, ODBC.
Interface (OCI). Proprietary native API.
6. It supports server-side scripting using It also supports server-side scripting.
user PL/SQL.
7. It supports partitioning methods using It supports partitioning methods using
horizontal partitioning. horizontal partitioning, sharding with
MySQL Cluster or MySQL Fabric.

SVIIT, BTECH.(CSE) 8
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

PRACTICAL – 2
Introduction to SQL
Introduction
SQL (Structured Query Language) is a standardized programming language that is used to
manage relational databases and perform various operations on the data in them. Initially
created in the 1970s, SQL is regularly used not only by database administrators, but also by
developers writing data integration scripts and data analysts looking to set up and run
analytical queries.
SQL is used for the following:
 Modifying database table and index structures.
 Adding, updating and deleting rows of data
 Retrieving subsets of information from within relational database management
systems.
SQL follows the following rules:
 Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
 Statements of SQL are dependent on text lines. We can use a single SQL statements
on one or multiple textline.
 Using the SQL statements, you can perform most of the actions in a database.
 SQL depends on tuple relational calculus and relational algebra.

History
The SQL programming language was developed in the 1970s by IBM researchers Raymond
Boyce and Donald Chamberlin. The programming language, known then as SEQUEL, was
created following Edgar Frank Codd’s paper. “A Relational Model of Data for Large Shared
Data Banks.” in 1970.
In his paper, Codd proposed that all data in a database be represented in relations.
Based on this theory, Boyce and Chamberlin came up with SQL. In Oracle Quick guides,
author Malcolm Coxall writes that the original version of SQL was designed to manipulate
and retrieve data stores in IBM’s original relational database management system, System R.
It wasn’t until several years later, however, that the SQL language was mode publicly
available. In 1979, a company called Relational Software, which later became Oracle,
commercially released its own version of SQL, called Oracle V2.
Since then, the American National Standards Institute (ANSI) and the International
Organization for Standardization have deemed SQL the standard language in relational
database communication. While major SQL vendors modify the language to their desires,
most base their SQL programs on the ANSI-approved version.

Types of SQL Statements

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

1. Data Definition Language (DDL)

DDL changes the structure of the table like creating a table, deleting a table, altering a
table, etc.
All the command of DDL are auto –committed that means it permanently save all the
changes in the database. Here are some commands that come under DDL:
 CREATE - It is used to create a new table in the database.

SVIIT, BTECH.(CSE) 9
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

Syntax: create table table_name (column datatypes[,….]);

 DROP: It is used to delete both the structure and record stored in the table.
Syntax: drop table table_name;
 ALTER: It is used to alter the structure of the database. This change could be
either to modify the characteristics of an existing attribute or probably to add a
new attribute.
Syntax:
To add a new column in the table
ALTER table table_name ADD column_name column-definition;
To modify existing column in the table:
ALTER table table_name MODIFY(column_definitions…);
 TRUNCATE: It is used to delete all the rows from the table and free the
space containing the table.
Syntax: TRUNCATE TABLE table_name;
 
2. Data Manipulation Language
DML commands are used to modify the database. It is responsible for all form of
changes in the database. The command of DML is not auto-committed that means it
can't permanently save all the changes in the database. They can be rollback. Here are
some commands that come under DML:

 INSERT: The INSERT statement is a SQL query. It is used to insert data into
the row of a table.
Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)  VALUES (value1
, value2, value3, .... valueN);  
INSERTINTO TABLE_NAME
VALUES (value1, value2, value3, .. valueN);  
 UPDATE: This command is used to update or modify the value of a column
in the table.
Syntax:UPDATE table_name SET [column_name1= value1,...column_name
N = valueN] [WHERE CONDITION]
 DELETE: It is used to remove one or more row from a table.
Syntax: DELETE FROM table_name [WHERE condition];  

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
 Grant: it is used to give user access privileges to a database.
Syntax:GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER,
ANOTHER_USER;
 Revoke: It is used to take back permissions from the user.
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and
UPDATE only. Here are some commands that come under TCL:
 Commit: Commit command is used to save all the transactions to the
database.

SVIIT, BTECH.(CSE) 10
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

DELETE FROM COUSTOMERS WHERE AGE = 25; COMMIT;

 Rollback: Rollback command is used to undo transactions that have not
already been saved to the database. Syntax: ROLLBACK;
 Savepoint: It is used to roll the transaction back to a certain point without
rolling back the entire transaction. Syntax: SAVEPOINT savepoint_name;

5. Data Query Language

DQL is used to fetch the data from the database. It uses only one command:
 Select: This is the same as the projection operation of relational algebra. It is
used to select the attribute based on the condition described by WHERE
clause.
Syntax: SELECT expressions FROM TABLES WHERE conditions;

SVIIT, BTECH.(CSE) 11
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

PRACTICAL – 3

Implementation of DDL
Introduction
The Data Definition Language is used to create and destroy databases and database objects.
These commands will primarily be used by database administrators during the setup and
removal phases of a database project. Let’s take a look at the structure and usage of four basic
DDL commands:

CREATE: This is used to create a relation.

Create user
Syntax : create user ‘username’ @ ‘localhost’ identified by ‘password’ ;

Create table

SVIIT, BTECH.(CSE) 12
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

ALTER:

Syntax: alter table relation_name ADD (new field_1 data_type(size), new field_2
data_type(size),..);

DROP table

SVIIT, BTECH.(CSE) 13
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

RENAME

SVIIT, BTECH.(CSE) 14
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

PRACTICAL – 4
Implementation of DML
Introduction
DML is short name of Data Manipulation Language which deals with data manipulation and
includes most common SQL statements such as INSERT, UPDATE, DELETE etc. and it is
store, modify, retrieve, delete and update data in database.
INSERT INTO
Syntax: insert into table_name (col1, col2, col3,…)values (value1, value2, value3, …);

SELECT
Syntax: select col1,col2,… from table_name
Or select * from table_name

SVIIT, BTECH.(CSE) 15
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

UPDATE
Syntax: update table_name SET col1 = value1, col2 = value2,… where condition;

DELETE
Syntax: delete from table_name where condition;

SVIIT, BTECH.(CSE) 16
Database Management System [BTCS411] Abhishek Vishwakarma
[20100BTCSICS07312]

SVIIT, BTECH.(CSE) 17