A
Micro Project
on
DBMS Analysis and Comparison
Submitted in partial fulfillment of the requirement for the award of
Diploma of Engineering
in
Computer Engineering
by
[Link] shinde
under the guidance of
Mrs. Akshata Raut
Department of Computer Engineering
December 2021
CERTIFICATE
VIVA COLLEGE OF DIPLOMA ENGINEERING&TECHNOLOGY
VIRAR (W)
2021-22
This is to certify that the micro project entitled “DBMS Analysis and Comparison”
has been submitted under the guidance of Mrs. Akshata Raut in partial fulfillment
of the requirement for the award of Diploma of Engineering in Computer
Engineering from Maharashtra State Board of Technical Education.
“DBMS Analysis and Comparison”
[Link] shinde
Project Guide H.O.D
Mrs. Akshata Raut Mr. Nikhil Asolkar
INDEX
Sr. No. Name of the topic Page no.
PART –A PLAN
1 Brief Introduction 1
2 Aim of the Micro-Project 1
3 Resources Required 2
PART –B OUTCOMES
1 Brief Description 3
2 Aim of Micro-Project 5
3 Course Outcomes Integrated 5
4 Procedure Followed 6
5 Resources Used 8
6 Outputs of the Micro-Projects 8
7 Skill Developed 9
PART-A PLAN
1.0 Brief Introduction
____________________________________________________
A database management system (DBMS) is system software for creating and managing
databases. The DBMS provides users and programmers with a systematic way to create, retrieve,
update and manage data.
A DBMS makes it possible for end users to create, read, update and delete data in a database.
The DBMS essentially serves as an interface between the database and end users or application
programs, ensuring that data is consistently organized and remains easily accessible.
In particular, each DBMS is characterized by a set of diverse functional and non-functional
features and specs each having their advantages and disadvantages. One of which is performance
which determines how fast a DBMS can process and execute queries. They are namely MS SQL
Server 2008, Oracle 11g, IBM DB2, MySQL 5.5, and MS Access 2010.
2.0 AIM of Micro-Project
____________________________________________________
The main purpose for this project is to analyze and compare the performance of widely used
database management systems. Technologies have been implemented under various test
conditions and their behaviors analyzed. The measurement factors are Memory Requirements
and Execution time for performing various types of queries. These measurements have been
taken on same database schema for the both technologies. The same sets of queries have been
analyzed by gradually increasing the number of records in the database. Performance impact of
size increment have also been measured and compared.
3.0 Resources Required
____________________________________________________
Sr. No Name of Resource Specification Qty Remarks
1 Computer 500GB HDD, 1
4 Gb RAM,
AMD processor,
Windows 7 OS
2 Oracle 10g Express Edition 1
PART-BOUTCOME
1.0 Brief Description
____________________________________________________
DBMS short for database management system plays a major role in most real-world projects that
require storing, retrieving, and querying digital data. For instance, dynamic websites, accounting
information systems, payroll systems, stock management systems all rely on internal databases
as a container to store and manage their data . Many software development firms are today
developing and producing DBMS systems that cost between zero dollars in case of free and
open-source DBMSs, and thousands of dollars in case of proprietary DBMSs. In particular, each
DBMS is characterized by a set of diverse functional and non-functional features and specs each
having their advantages and disadvantages. One of which is performance which determines how
fast a DBMS can process and execute queries. They are namely MS SQL Server 2008, Oracle
11g, IBM DB2, MySQL 5.5, and MS Access 2010.
This section discusses the history, versions, and features of the different DBMSs under test. They
are respectively MS SQL Server 2008, Oracle 11g, IBM DB2, MySQL 5.5, and MS Access
2010.
1.1. MS SQL Server 2008
Microsoft SQL Server is a relational database management system (RDBMS) produced by
Microsoft. Its primary query language is Transact-SQL, an implementation of the ANSI/ISO
standard Structured Query Language (SQL) used by both Microsoft and Sybase. Microsoft SQL
Server supports atomic, consistent, isolated, and durable transactions. It includes support for
database mirroring and clustering. An SQL server cluster is a collection of identically configured
servers, which help distribute the workload among multiple servers. SQL server also supports
data partitioning for distributed databases, in addition to database mirroring which allows the
creation of mirrors of database contents, along with transaction logs, on another instance of SQL
Server, based on certain predefined triggers
1.2 Oracle 11g
Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle), is a relational
database management system (RDBMS) released by Oracle Corporation, and it comprises at
least one instance of the application, along with data storage. An instance comprises a set of
operating system processes and memory structures that interact with the storage. In addition to
storage, the database consists of online redo logs which hold the transactional history. Processes
can in turn archive the online redo logs into archive logs, which provide the basis for data
recovery and for some forms of data replication. The Oracle RDBMS stores data logically in the
form of table-spaces and physically in the form of data files. At the physical level, data files
comprise one or more data blocks, where the block size can vary between data files. Oracle
features data dictionary, indexes, and clusters. Versions Subsequent to 10g, introduced grid
computing capabilities in which an instance application can use CPU resources from another
node in the grid .
1.3 IBM DB2
DB2 is one of IBM's lines of relational database management system which runs on Unix,
Windows, or Linux server machines. DB2 can be administered from either a command-line or a
GUI interface. The command-line interface requires more knowledge of the product but can be
more easily scripted and automated. The GUI is a multi-platform Java client that contains a
variety of wizards suitable for novice users. DB2 supports both SQL and XQuery. DB2 has
native implementation of XML data storage, where XML data is stored as XML for faster access
using XQuery. DB2 also supports integration into the Eclipse and Visual Studio .NET integrated
development environments. An important feature of DB2 DBMS is the error processing in which
SQL communications area structure is used within the DB2 program to return error information
to the application program after every API call for an SQL statement.
1.4 MySQL 5.5
MySQL is a free, open-source, multithreaded, and multi-user SQL database management system
which has more than 10 million installations. The basic program runs as a server providing
multi-user access to a number of databases. MySQL includes a broad subset of ANSI SQL 99, as
well as extensions, cross-platform support, stored procedures, triggers, cursors, updatable views,
and X/Open XA distributed transaction processing support. Moreover, it supports two phase
commit engine, independent storage engines, SSL support, query caching, replication with one
master per slave, many slaves per master, embedded database library, and ACID compliance
using the InnoDB cluster engines [10].
2.0 AIM of Micro-Project
____________________________________________________
The main purpose for this project is to analyze and compare the performance of widely used
database management systems. Technologies have been implemented under various test
conditions and their behaviors analyzed. The measurement factors are Memory Requirements
and Execution time for performing various types of queries. These measurements have been
taken on same database schema for the both technologies. The same sets of queries have been
analyzed by gradually increasing the number of records in the database. Performance impact of
size increment have also been measured and compared.
3.0 Course Outcomes (CO)
____________________________________________________
b) Create and manage database using SQL command.
e) Apply security and confidentiality on given database.
4.0 Procedure Followed
____________________________________________________
Different SQL queries were executed over the different five DBMSs under test. In fact, these
queries have different level of complexity; they range from simple type to very complex type. It
is worth noting that all five databases are populated with dummy 1,000,000 records of data prior
to starting the testing process.
Query #1
This is a very simple query whose task is to retrieve rows without any conditions or joins:
SELECT * FROM Item;
Query #2
This query is used to test the sorting operation for each DBMS:
SELECT Customer.cu_id, Customer.cu_name , Customer.cu_telephone , Customer.cu_fax ,
Customer.cu_email FROM Customer ORDER BY Customer.cu_id , Customer.cu_name DESC ,
Customer.cu_telephone DESC, Customer.cu_fax , Customer.cu_email DESC ;
Query #3
This query tests the capabilities of the DBMSs under test to execute UPDATE statements with
complicated conditions:
UPDATE Item SET Item.it_price = Item.it_price * 0.1 AND Item.it_qtity = 10 AND
Item.it_description = "TV" WHERE Item.it_id> 10 AND Item.it_expirydate> 1/1/2007 AND
Item.it_expirydate< 1/1/2008 AND Item.it_manufacturer = "Philips" OR Item.it_manufacturer =
"Sharp" OR Item.it_manufacturer = "Toshiba";
Query #4
This final query executes a DELETE query over the selected DBMSs:
DELETE FROM Invoice WHERE Invoice.in_date> 1/1/2006 AND Invoice.in_date< 1/1/2007
AND Invoice.in_description LIKE '%vtt%' AND Invoice.in_totalinletter LIKE '%USD' AND
Invoice.in_total = Invoice.in_totalafterdiscount AND Invoice.in_ totalafterdiscount<> 33.1 OR
NOT Invoice.in_cu_id>= 5 AND Invoice.in_id BETWEEN 1 AND 10000 OR Invoice.in_id<
71/2 AND Invoice.in_total+33 <> 5 AND Invoice.in_total = Invoice.in_totalafterdiscount – 112;
5.0 Resources Used
____________________________________________________
Sr. No Name of Resource Specification Qty Remarks
1 Computer 500GB HDD, 1
4 Gb RAM,
AMD processor,
Windows 7 OS
2 Oracle 10g Express Edition 1
6.0 Outputs of Micro-Projects
____________________________________________________
FEATURE Oracle MySQL SQL Server
INTERFACE GUI, SQL SQL GUI, SQL, Various
LANGUAGE Many, including C, Many, including C, Java, Ruby, Python,
SUPPORT C#, C++, Java, Ruby, C#, C++, D, Java, VB, .Net, and PHP
and Objective C Ruby, and Objective
C
OPERATING Windows, Linux, Windows, Linux, OS Windows
SYSTEM Solaris, HP-UX, OS X, FreeBSD, Solaris
X, z/OS, AIX
LICENSING Proprietary Open source Proprietary
7.0 Skill Developed
____________________________________________________
Various database management systems have been studied successfully.
With this study we successfully evaluate various performances of various database management
systems by using different queries.