VISVESVARAYA TECHNOLOGICAL UNIVERSITY

BELAGAVI-590018

“DBMS Mini Project”

(Subject Code: 17CSL58)
ON
“ONLINE BOOK STORE MANAGEMENT SYSTEM”
Submitted in partial fulfilment for 5th semester for the Award of Degree of

BACHELOR OF ENGINEERING
IN
INFORMATION SCIENCE AND ENGINEERING
BY

MURALI R (1EP17IS027)
RAGHAVENDRA R (1EP17IS033)
UNDER THE GUIDANCE OF
Prof.VANDANA N S
Prof.ANSHULA B A
Dept. of ISE, EPCET

Department of Information Science and Engineering

Jnana Prabha Campus, Bidarahalli,
Bangalore – 560 049
2019-2020
 (Affiliated to Visvesvaraya Technological University, Belagavi)
Jnana Prabha Campus, Bidarahalli,
Bangalore – 560 049

DEPARTMENT OF INFORMATION SCIENCE & ENGINEERING

CERTIFICATE
This is to certify that the DBMS Mini Project(17CSL58) entitled “ONLINE BOOK STORE
MANAGEMENT SYSTEM” is a bonafide work carried out by MURALI R, bearing USN
1EP17IS027, RAGHAVENDRA R, bearing USN 1EP17IS033 in partial fulfilment of
5th semester for the award of, Bachelor of Engineering in Information Science and
Engineering under Visvesvaraya Technological University, Belagavi during the year 2019-
2020. This report has been approved as it satisfies the academic requirements in respect of
DBMS Mini Project work prescribed for the award of the said degree.

GUIDE HOD PRINCIPAL

Prof.Vandana N S Prof.Kemparaju N Dr.Prakash S
Prof.Anshula B A Prof & Head Principal
Asst Professor’s

External examiner’s Date and sign

1)
2)
 ACKNOWLEDGEMENT

Any achievement, be it scholastic or otherwise does not depend solely on the individual
efforts but on the guidance, encouragement and cooperation of intellectuals, elders and friends.
We would like to take this opportunity to thank them all.

First and foremost we would like to express our sincere regards and thanks to Mr.
Promod Gowda and Mr. Rajiv Gowda, CEO’s, East Point Group of Institutions, Bangalore,
for providing necessary infrastructure and creating good environment.

We express our gratitude to Dr.Prakash S, Principal, EPCET who has always been a
great source of inspiration.

We express our sincere regards and thanks to Prof.Kemparaju N, Professor and Head
of Department of Information Science and Engineering, EPCET, Bangalore, for his
encouragement and support.

We are grateful to acknowledge the guidance and encouragement given to us by

Prof.Vandana N S, Assistant Professor, and Prof.Anshula B A, Assistant Professor
Department of Information Science and Engineering, EPCET, Bangalore, as the project
coordinator and guides who have rendered a valuable assistance.

We also extend our thanks to the entire faculty of the Department of Information
Science and Engineering, EPCET, Bangalore, who have encouraged us throughout the course
of the project work .

Last, but not the least, we would like to thank our family and friends for their inputs to
improve the project.

MURALI R (1EP17IS027)
RAGHAVENDRA R (1EP17IS033)
 ABSTRACT
The main objective of the project is to create an online book-store that allows users to search
and purchase a book online based on title, author and subject. The selected books are displayed
in a tabular format and the user can order their books online through credit card payment. Using
this website, the user can purchase a book online instead of going out to a book store and
wasting time. There are many online book stores like Powell’s, Amazon which were designed
using Html. I want to develop a similar website using .My SQL and PHP. The Online Book
Store Website provides customers with online shopping through a web browser. A customer
can, create, sign in to his account, place items into a shopping cart and purchase using his credit
card details. People often face the difficulty of non-availability of good quality content books
to learn concepts or skills. They also have ambiguity about authors or which publications best
suit their purpose.

Our aim is to design and create a Database Management System to provide a platform for
borrowing or purchasing books which are subject related for the sake of learning, which are
easily accessible and details about them are also easily available.This system simplifies the
task of checking for available subject related books. It makes it easier to avail information
regarding the publisher or the author. This system allows the customer to only avail or
borrow books which are available in the online store, hence avoiding the confusion. The
Administrator will have additional functionalities when compared to the common user. He
can add, delete and update the book details, book categories, member information and also
confirm a placed order
 CONTENTS

CHAPTER TOPICS PAGE NO.

NO.
1 Introduction 1
1.1 Introduction to SQL
1.2 Introduction To Frontend Software
1.3 About MySQL
1.4 PHP + MySQL Database System
2 Requirement Specifications 3
2.1 Hardware requirements.
2.2 Software requirements.
3 Tool Description 4
3.1 Overview of Front End
3.2 Overview of Back End
4 Requirement Analysis 8
4.1 E-R Diagram.
4.2 Schema Diagram.
5 Implementation and coding 10
5.1 Source code
5.2 Connectivity of PHP to Database
6 Testing 18
6.1 Testing Process
6.2 Testing objectives
6.3 Test Cases
7 Snapshots 22

Conclusion 21

References
 LIST OF FIGURES

Figure No. Figure Name Page No.

4.1 ER Diagram 8
4.2 Schema Diagram 9
7.1 Main Menu 14
7.2 Admin Menu 14
7.3 Publisher Menu 16
7.4 Book Details Menu 18
7.5 Checkout Menu 19
7.6 Contact Menu 20
DATABASE MANAGEMENT SYSTEM

CHAPTER 1

INTRODUCTION

1.1 Introduction to SQL

SQL (Structured Query Language) originally called SEQUEL (Structured English Query
Language) was designed and implemented at IBM Research as the interface for an
experimental relational database system. SQL is now the standard language for commercial
relational DBMSs. SQL is a comprehensive database language; It has statements for data
definitions, queries and updates. Hence it is both a DDL and a DML. SQL is a language to
request data from a database, to add, update, or remove data within a database ,or to manipulate
the meta data of the database .In addition ,it has facilities for defining views on database, for
specifying security and authorization, for defining integrity constraints, and for specifying
transaction controls. It also has rules for embedding SQL statements into a general-purpose
programming language such as Java or C/C++.

Commonly used statements are grouped into the following categories:

Data Query Language (DQL)
SELECT – Used to retrieve certain records from one or more tables.
Data Manipulation Language (DML)
INSERT – Used to create a record.
UPDATE – Used to change certain records.
DELETE – Used to delete certain records.
Data Definition Language (DDL)
CREATE – Used to create a new table, a view of a table, or other object in database.
ALTER – Used to modify an existing database object, such as a table.
DROP – Used to delete an entire table, a view of a table or other object in the database.
Data Control Language (DCL)
GRANT – Used to give a privilege to someone.
REVOKE – Used to take back privileges granted to someone

1.2 Introduction to Front End Software

PHP (recursive acronym for PHP : Hypertext preprocessor) is a widely-used open source
general-purpose scripting language that is especially suited for web development and can
be embedded into HTML. PHP’s development is focused on server-side scripting .Instead

Dept of ISE, EPCET 1 2019

DATABASE MANAGEMENT SYSTEM

of lot of commands to output HTML, PHP pages contain HTML with embedded code that
does “something”.
The PHP code is enclosed in special start and end processing instructions <? Php and ?>
that0 allow you to jump into and out of “PHP mode” .The best thing in using PHP are that
it is extremely simple for a rookie, but offers many advance features for professional
programmer.

1.3 About MySQL:

• MySQL is a database system used on the web

• MySQL is a database system that runs on a server
• MySQL is ideal for both small and large applications
• MySQL is very fast, reliable, and easy to use
• MySQL uses standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
• MySQL is developed, distributed, and supported by Oracle Corporation
• MySQL is named after co-founder Monty Widenius’s daughter: My

The data in a MySQL database are stored in tables. A table is a collection of related data,
and it consists of columns and rows.

Databases are useful for storing information categorically. A company may have a
database with SEVERAL tables:

1.4 PHP + MySQL Database System

• PHP combined with MySQL are cross-platform (you can develop in Windows and
serve on a Unix platform)
• MySQL is the de-facto standard database system for web sites with HUGE volumes
of both data and end-users (like Facebook, Twitter, and Wikipedia).
Another great thing about MySQL is that it can be scaled down to support embedded database
applications.

Dept of ISE, EPCET 2 2019

DATABASE MANAGEMENT SYSTEM

CHAPTER 2

REQUIREMENT SPECIFICATIONS

2.1 Hardware Requirements:

Processor Brand : Intel

Processor Type : Core i3

Processor Speed : 2 GHz

Processor Count :1

RAM Size : 512 MB

Memory Technology : DDR3

Computer Memory Type : DDR3 SDRAM

Hard Drive Size : 500 MB

2.2 Software Requirements:

Operating system : Windows 10

Application server : PHP

Front end : PHP, CSS styles

Browser : Google chrome, Mozilla Firefox

Connectivity : Internet

Database connectivity : MySQL

Dept of ISE, EPCET 3 2019

DATABASE MANAGEMENT SYSTEM

CHAPTER 3

TOOL DESCRIPTION

3.1 OVERVIEW OF FRONT END

An important issue for the development of a project is the selection of suitable front-
end and back-end. When we decided to develop the project we went through an extensive study
to determine the most suitable platform that suits the needs of the organization as well as helps
in development of the project.

The aspects of our study included the following factors.

Front-end selection:

1. It must have a graphical user interface that assists employees that are not from IT
background.

2. Scalability and extensibility.

3. Flexibility.

4. Robustness.

5. According to the organization requirement and the culture.

6. Must provide excellent reporting features with good printing support.

7. Platform independent.

8. Easy to debug and maintain.

9. Event driven programming facility.

10. Front end must support some popular back end like MySQL.

According to the above stated features we selected PHP and CSS as the front-end for
developing.

Dept of ISE, EPCET 4 2019

DATABASE MANAGEMENT SYSTEM

3.1.1 INTRODUCTION TO CSS (Cascade style sheets):

Cascading Style Sheets (CSS) is a style sheet language used for describing
the presentation of a document written in a markup language like HTML.CSS is a cornerstone
technology of the World Wide Web, alongside HTML and JavaScript.

CSS is designed to enable the separation of presentation and content,

including layout, colors, and fonts. This separation can improve content accessibility, provide
more flexibility and control in the specification of presentation characteristics, enable
multiple web pages to share formatting by specifying the relevant CSS in a separate .css file,
and reduce complexity and repetition in the structural content.

CSS (Cascading Style Sheets) is used to style and lay out web pages — for example, to
alter the font, color, size and spacing of your content, split it into multiple columns, or add
animations and other decorative features. This module gets you started on the path to CSS
mastery with the basics of how it works, including selectors and properties, writing CSS rules,
applying CSS to HTML, how to specify length, color, and other units in CSS, cascade and
inheritance, and debugging CSS.

3.1.2 INTRODUCTION TO PHP:

PHP is a server- side scripting language, that is used to develop Static websites or
Dynamic websites or Web applications. PHP stands for Hypertext Pre-processor, that earlier
stood for Personal Home Pages. PHP scripts can only be interpreted on a server that has PHP
installed. The client computers accessing the PHP scripts require a web browser only. A PHP
file contains PHP tags and ends with the extension “.php”.

PHP is open source and free. Short learning curve compared to other languages such as
JSP, ASP etc. Large community document most web hosting servers support PHP by default
unlike other languages such as ASP that need IIS. This makes PHP a cost-effective choice.
PHP is regular updated to keep abreast with the latest technology trends.

Other benefit that you get with PHP is that it’s a server-side scripting language; this
means you only need to install it on the server and client computers requesting for resources
from the server do not need to have PHP installed; only a web browser would be enough. PHP
has in built support for working hand in hand with MySQL. You can still use PHP with Postgres

Dept of ISE, EPCET 5 2019

DATABASE MANAGEMENT SYSTEM

Oracle MS SQL Server ODBC etc. PHP is cross platform; this means you can deploy your
application on a number of different operating systems such as windows, Linux, Mac OS etc.

3.2 OVERVIEW OF BACK END

Back End Selection:

1. Multiple user support.

2. Efficient data handling.

3. Provide inherent features for security.

4. Efficient data retrieval and maintenance.

5. Stored procedures.

6. Popularity.

7. Operating System compatible.

8. Easy to install.

9. Various drivers must be available.

10. Easy to implant with the Front-end.

According to above stated features we selected MySQL as the backend.[1]

The technical feasibility is frequently the most difficult area encountered at this stage. It is
essential that the process of analysis and definition be conducted in parallel with an assessment
to technical feasibility.[2] It centers on the existing computer system (hardware, software etc.)
and to what extent it can support the proposed system.

3.2.1 INTRODUCTION TO PHP SERVER:

PHP is a software distribution which provides the Apache web server, MySQL
database (actually MariaDB), PHP and Perl (as command-line executable and Apache
modules) all in one package. It is available for Windows, MAC and Linux systems. No
configuration is necessary to integrate PHP with MySQL.

Dept of ISE, EPCET 6 2019

DATABASE MANAGEMENT SYSTEM

PHP is a free and open-source cross-platform web server solution stack package
developed by Apache Friends, consisting mainly of the Apache HTTP Server, MariaDB
database, and interpreters for scripts written in the PHP and Perl programming languages.
Since most actual web server deployments use the same components as PHP, it makes
transitioning from a local test server to a live server possible.

Once PHP is installed, it is possible to treat a local host like a remote host by
connecting using an FTP client. Using a program like FileZilla has many advantages when
installing a content management system (CMS) like Joomla or WordPress. It is also
possible to connect to localhost via FTP with an HTML editor.

3.2.2 INTRODUCTION TO MySQL:

MySQL is the world's most popular open source database. With its proven
performance, reliability and ease-of-use, MySQL has become the leading database choice
for web-based applications, used by high profile web properties including Facebook,
Twitter, YouTube, Yahoo! and many more. Oracle drives MySQL innovation, delivering
new capabilities to power next generation web, cloud, mobile and embedded applications.

MySQL is a fast, easy-to-use RDBMS being used for many small and big
businesses. MySQL is developed, marketed 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 is a very powerful program in its own right. It handles a large subset of the
functionality of the most expensive and powerful database packages. 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
very friendly to PHP, the most appreciated language for web development. MySQL
supports large databases, up to 50 million rows or more in a table. The default file size
limit for a table is 4GB, but you can increase this (if your operating system can handle it)
to a theoretical limit of 8 million terabytes (TB).

MySQL is customizable. The open-source GPL license allows programmers to

modify the MySQL software to fit their own specific environments.

Dept of ISE, EPCET 7 2019

DATABASE MANAGEMENT SYSTEM

CHAPTER 4

REQUIREMENTS ANALYSIS

4.1 ER Diagram

ER Diagram: ER Diagram is a high-level conceptual data model diagram. Entity-Relation

model is based on the notion of real-world entities and the relationship between
them. ER modelling helps you to analyse data requirements systematically to produce a well-
designed database.[2]

Figure 4.1 : ER Diagram for Smartphone Management Arena

Dept of ISE, EPCET 8 2019

DATABASE MANAGEMENT SYSTEM

4.2 Schema Diagram

Schema diagram: A schema diagram is the skeleton structure that represents the logical
view of the entire database. It contains a descriptive detail of the database.[2]

ADMIN

Name Pass

BOOKS

book_isbn book_title book_author book_image book_descr book_price Publisherid

CUSTOMERS

Customerid Name address City zip_code Country

ORDERS

Order_id customerid amount date ship_name ship_address ship_city ship_zip_code ship_country

Order_items

Ordere_id book_isbn item_price quantity

PUBLISHER

Publisherid publisher_name

Figure 4.2 : Schema Diagaram for Smartphone Management System

Dept of ISE, EPCET 9 2019

DATABASE MANAGEMENT SYSTEM

CHAPTER 5
IMPLEMENTATION AND CODING
5.1 SOURCE CODE:

Table Creation and Insertion:

________________________________________________________________________
-- phpMyAdmin SQL Dump
-- version 4.4.12
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Server version: 5.6.25
-- PHP Version: 5.6.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT

*/;
/*!40101 SET
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION
*/;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `www_project`
--
-- --------------------------------------------------------
--
-- Table structure for table `admin`
--
CREATE TABLE IF NOT EXISTS `admin` (
`name` varchar(20) COLLATE latin1_general_ci NOT NULL,
`pass` varchar(40) COLLATE latin1_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
--
-- Dumping data for table `admin`
--
INSERT INTO `admin` (`name`, `pass`) VALUES
('admin', 'd033e22ae348aeb5660fc2140aec35850c4da997');
-- --------------------------------------------------------
--
-- Table structure for table `books`
--
CREATE TABLE IF NOT EXISTS `books` (
`book_isbn` varchar(20) COLLATE latin1_general_ci NOT NULL,
`book_title` varchar(60) COLLATE latin1_general_ci DEFAULT NULL,

Dept of ISE, EPCET 10 2019

DATABASE MANAGEMENT SYSTEM

`book_author` varchar(60) COLLATE latin1_general_ci DEFAULT NULL,

`book_image` varchar(40) COLLATE latin1_general_ci DEFAULT NULL,
`book_descr` text COLLATE latin1_general_ci,
`book_price` decimal(6,2) NOT NULL,
`publisherid` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
--
-- Dumping data for table `books`
--
INSERT INTO `books` (`book_isbn`, `book_title`, `book_author`, `book_image`,
`book_descr`, `book_price`, `publisherid`) VALUES
('978-0-321-94786-4', 'Learning Mobile App Development', 'Jakob Iversen, Michael Eierman',
'mobile_app.jpg', 'Now, one book can help you master mobile app development with both
market-leading platforms: Apple''s iOS and Google''s Android. Perfect for both students and
professionals, Learning Mobile App Development is the only tutorial with developer training
courses. If you''re new to iOS, you''ll also find an easy, practical introduction to Objective-C,
Apple''s native language.', '20.00', 6),
('978-0-7303-1484-4', 'Doing Good By Doing Good', 'Peter Baines', 'doing_good.jpg', 'Doing
Good by Doing Good shows companies how to improve the bottom line by implementing an
companies delineate a plan of action for getting it done.', '20.00', 2),
('978-1-118-94924-5', 'Programmable Logic Controllers', 'Dag H. Hanssen',
'logic_program.jpg', 'Widely used across industrial and manufacturing automation, world
examples. The design tool, CoDeSys, also features a built in simulator / soft PLC enabling the
reader to undertake exercises and test the examples.', '20.00', 2),
('978-1-1180-2669-4', 'Professional JavaScript for Web Developers, 3rd Edition', 'Nicholas C.
Zakas', 'pro_js.jpg', 'If you want to achieve JavaScript''s full potential, it is critical to understand
its nature, history, and limitations. To that end, this updated version of the '20.00', 1),
('978-1-44937-019-0', 'Learning Web App Development', 'Semmy Purewal',
'web_app_dev.jpg', 'Grasp the fundamentals of web application development by building a
practice problems, full examples, and mental models of the development workflow. Ideal for a
college-level course, this book helps you get started with web app development by providing
you with a solid grounding in the process.', '20.00', 3),
('978-1-44937-075-6', 'Beautiful JavaScript', 'Anton Kovalyov', 'beauty_js.jpg', 'JavaScript is
arguably the most polarizing and misunderstood programming language in the world. turning
the most feared features into useful tools, or how JavaScript can be used for self-expression.',
'20.00', 3),
('978-1-4571-0402-2', 'Professional ASP.NET 4 in C# and VB', 'Scott Hanselman', process
smoother and more efficient.', '20.00', 1),
('978-1-484216-40-8', 'Android Studio New Media Fundamentals', 'Wallace Jackson',
'android_studio.jpg', 'Android Studio New Media Fundamentals is a new media primer
covering concepts central to multimedia production for Android including digital imagery,
media content and new media data optimization is so important.', '20.00', 4),
('978-1-484217-26-9', 'C++ 14 Quick Syntax Reference, 2nd Edition', ' Mikael Olsson',
'c_14_quick.jpg', 'This updated handy quick C++ 14 guide is a condensed code and syntax. It
has short, simple, and focused code examples. This book includes a well laid out table of
contents and a comprehensive index allowing for easy review.', '20.00', 4),
('978-1-49192-706-9', 'C# 6.0 in a Nutshell, 6th Edition', 'Joseph Albahari, Ben Albahari',
'c_sharp_6.jpg', 'When you have questions about C# 6.0 or the .NET CLR and its core
Framework assemblies, this bestselling guide has the answers you need. C# has become a

Dept of ISE, EPCET 11 2019

DATABASE MANAGEMENT SYSTEM

language of unusual flexibility and breadth since its premiere in 2000, but this continual
Nutshell guide is considered the definitive reference on C#.', '20.00', 3);
-- --------------------------------------------------------
--
-- Table structure for table `customers`
--
CREATE TABLE IF NOT EXISTS `customers` (
`customerid` int(10) unsigned NOT NULL,
`name` varchar(60) COLLATE latin1_general_ci NOT NULL,
`address` varchar(80) COLLATE latin1_general_ci NOT NULL,
`city` varchar(30) COLLATE latin1_general_ci NOT NULL,
`zip_code` varchar(10) COLLATE latin1_general_ci NOT NULL,
`country` varchar(60) COLLATE latin1_general_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
--
-- Dumping data for table `customers`
--
INSERT INTO `customers` (`customerid`, `name`, `address`, `city`, `zip_code`, `country`)
VALUES
(1, 'a', 'a', 'a', 'a', 'a'),
(2, 'b', 'b', 'b', 'b', 'b'),
(3, 'test', '123 test', '12121', 'test', 'test');
-- --------------------------------------------------------
--
-- Table structure for table `orders`
--
CREATE TABLE IF NOT EXISTS `orders` (
`orderid` int(10) unsigned NOT NULL,
`customerid` int(10) unsigned NOT NULL,
`amount` decimal(6,2) DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ship_name` char(60) COLLATE latin1_general_ci NOT NULL,
`ship_address` char(80) COLLATE latin1_general_ci NOT NULL,
`ship_city` char(30) COLLATE latin1_general_ci NOT NULL,
`ship_zip_code` char(10) COLLATE latin1_general_ci NOT NULL,
`ship_country` char(20) COLLATE latin1_general_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
--
-- Dumping data for table `orders`
--
INSERT INTO `orders` (`orderid`, `customerid`, `amount`, `date`, `ship_name`,
`ship_address`, `ship_city`, `ship_zip_code`, `ship_country`) VALUES
(1, 1, '60.00', '2015-12-03 13:30:12', 'a', 'a', 'a', 'a', 'a'),
(2, 2, '60.00', '2015-12-03 13:31:12', 'b', 'b', 'b', 'b', 'b'),
(3, 3, '20.00', '2015-12-03 19:34:21', 'test', '123 test', '12121', 'test', 'test'),
(4, 1, '20.00', '2015-12-04 10:19:14', 'a', 'a', 'a', 'a', 'a');
-- --------------------------------------------------------
--

Dept of ISE, EPCET 12 2019

DATABASE MANAGEMENT SYSTEM

-- Table structure for table `order_items`

--
CREATE TABLE IF NOT EXISTS `order_items` (
`orderid` int(10) unsigned NOT NULL,
`book_isbn` varchar(20) COLLATE latin1_general_ci NOT NULL,
`item_price` decimal(6,2) NOT NULL,
`quantity` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
--
-- Dumping data for table `order_items`
--
INSERT INTO `order_items` (`orderid`, `book_isbn`, `item_price`, `quantity`) VALUES
(1, '978-1-118-94924-5', '20.00', 1),
(1, '978-1-44937-019-0', '20.00', 1),
(1, '978-1-49192-706-9', '20.00', 1),
(2, '978-1-118-94924-5', '20.00', 1),
(2, '978-1-44937-019-0', '20.00', 1),
(2, '978-1-49192-706-9', '20.00', 1),
(3, '978-0-321-94786-4', '20.00', 1),
(1, '978-1-49192-706-9', '20.00', 1);
-- --------------------------------------------------------
--
-- Table structure for table `publisher`
--
CREATE TABLE IF NOT EXISTS `publisher` (
`publisherid` int(10) unsigned NOT NULL,
`publisher_name` varchar(60) COLLATE latin1_general_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci;
--
-- Dumping data for table `publisher`
--
INSERT INTO `publisher` (`publisherid`, `publisher_name`) VALUES
(1, 'Wrox'),
(2, 'Wiley'),
(3, 'O''Reilly Media'),
(4, 'Apress'),
(5, 'Packt Publishing'),
(6, 'Addison-Wesley');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `admin`
--
ALTER TABLE `admin`
ADD PRIMARY KEY (`name`,`pass`);
--
-- Indexes for table `books`
--

Dept of ISE, EPCET 13 2019

DATABASE MANAGEMENT SYSTEM

ALTER TABLE `books`

ADD PRIMARY KEY (`book_isbn`);
--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
ADD PRIMARY KEY (`customerid`);
--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
ADD PRIMARY KEY (`orderid`);
--
-- Indexes for table `publisher`
--
ALTER TABLE `publisher`
ADD PRIMARY KEY (`publisherid`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
MODIFY `customerid` int(10) unsigned NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
MODIFY `orderid` int(10) unsigned NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `publisher`
--
ALTER TABLE `publisher`
MODIFY `publisherid` int(10) unsigned NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=7;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */
________________________________________________________________________

4.2 Connectivity of PHP to Database

______________________________________

<?php
function db_connect(){
$conn = mysqli_connect("localhost", "root", "", "www_project");

Dept of ISE, EPCET 14 2019

DATABASE MANAGEMENT SYSTEM

if(!$conn){
echo "Can't connect database " . mysqli_connect_error($conn);
exit;
}
return $conn;
}

function select4LatestBook($conn){
$row = array();
$query = "SELECT book_isbn, book_image FROM books ORDER BY
book_isbn DESC";
$result = mysqli_query($conn, $query);
if(!$result){
echo "Can't retrieve data " . mysqli_error($conn);
exit;
}
for($i = 0; $i < 4; $i++){
array_push($row, mysqli_fetch_assoc($result));
}
return $row;
}

function getBookByIsbn($conn, $isbn){

$query = "SELECT book_title, book_author, book_price FROM books
WHERE book_isbn = '$isbn'";
$result = mysqli_query($conn, $query);
if(!$result){
echo "Can't retrieve data " . mysqli_error($conn);
exit;
}
return $result;
}

function getOrderId($conn, $customerid){

$query = "SELECT orderid FROM orders WHERE customerid =
'$customerid'";
$result = mysqli_query($conn, $query);
if(!$result){
echo "retrieve data failed!" . mysqli_error($conn);
exit;
}
$row = mysqli_fetch_assoc($result);
return $row['orderid'];
}

function insertIntoOrder($conn, $customerid, $total_price, $date, $ship_name,

$ship_address, $ship_city, $ship_zip_code, $ship_country){
$query = "INSERT INTO orders VALUES
('', '" . $customerid . "', '" . $total_price . "', '" . $date . "', '" . $ship_name . "', '"
. $ship_address . "', '" . $ship_city . "', '" . $ship_zip_code . "', '" . $ship_country . "')";

Dept of ISE, EPCET 15 2019

DATABASE MANAGEMENT SYSTEM

$result = mysqli_query($conn, $query);

if(!$result){
echo "Insert orders failed " . mysqli_error($conn);
exit;
}
}

function getbookprice($isbn){
$conn = db_connect();
$query = "SELECT book_price FROM books WHERE book_isbn = '$isbn'";
$result = mysqli_query($conn, $query);
if(!$result){
echo "get book price failed! " . mysqli_error($conn);
exit;
}
$row = mysqli_fetch_assoc($result);
return $row['book_price'];
}

function getCustomerId($name, $address, $city, $zip_code, $country){

$conn = db_connect();
$query = "SELECT customerid from customers WHERE
name = '$name' AND
address= '$address' AND
city = '$city' AND
zip_code = '$zip_code' AND
country = '$country'";
$result = mysqli_query($conn, $query);
// if there is customer in db, take it out
if($result){
$row = mysqli_fetch_assoc($result);
return $row['customerid'];
} else {
return null;
}
}
function setCustomerId($name, $address, $city, $zip_code, $country){
$conn = db_connect();
$query = "INSERT INTO customers VALUES
('', '" . $name . "', '" . $address . "', '" . $city . "', '" . $zip_code . "', '" .
$country . "')";

$result = mysqli_query($conn, $query);

if(!$result){
echo "insert false !" . mysqli_error($conn);
exit;
}
$customerid = mysqli_insert_id($conn);
return $customerid;
}

Dept of ISE, EPCET 16 2019

DATABASE MANAGEMENT SYSTEM

function getPubName($conn, $pubid){

$query = "SELECT publisher_name FROM publisher WHERE publisherid =
'$pubid'";
$result = mysqli_query($conn, $query);
if(!$result){
echo "Can't retrieve data " . mysqli_error($conn);
exit;
}
if(mysqli_num_rows($result) == 0){
echo "Empty books ! Something wrong! check again";
exit;
}

$row = mysqli_fetch_assoc($result);
return $row['publisher_name'];
}
function getAll($conn){
$query = "SELECT * from books ORDER BY book_isbn DESC";
$result = mysqli_query($conn, $query);
if(!$result){
echo "Can't retrieve data " . mysqli_error($conn);
exit;
}
return $result;
}
?>

Dept of ISE, EPCET 17 2019

DATABASE MANAGEMENT SYSTEM

CHAPTER 6

TESTING

This chapter gives the outline of all testing methods that are carried out to get a bug free
system. Quality can be achieved by testing the product using different techniques at
different phases of the project development. The purpose of testing is to discover errors.
Testing is the process of trying to discover every conceivable fault or weakness in a work
product. It provides a way to check the functionality of components sub assemblies and/or
a finished product. It is the process of exercising software with the intent of ensuring that
the Software system meets its requirements and user expectations and does not fail in an
unacceptable manner. There are various types of test. Each test type addresses a specific
testing requirement.

6.1 Testing Process

Testing is an integral part of software development. Testing process certifies whether the
product that is developed compiles with the standards that it was designed to. Testing
process involves building of test cases against which the product has to be tested.

6.2 Testing Objectives

The main objectives of testing process are as follows.

• Testing is a process of executing a program with the intent of finding an error.

• A good test case is one that has high probability of finding undiscovered error.
• A successful test is one that uncovers the undiscovered error.

6.3 Test Cases

The test cases provided here test the most important features of the project.

Dept of ISE, EPCET 18 2019

DATABASE MANAGEMENT SYSTEM

6.3.1 Test cases for the project

Testing the Online Book Store Management System:

Sl No Test Input Expected Results Observed Results Remarks

“CSE Bookstore” Menu

Keep the curser
on Display the “Welcome to Displayed the “Welcome to
1 Ok Pass
“CSE online Bookstore screen” online Bookstore screen”
Bookstore”
“Admin Login” Menu
Enter on “Admin Display the “name and Displayed the “name and
Ok Pass
Login” password” menu password” menu
Enter Without Error Message “Name or Displayed Error Message
Ok Pass
Password Pass is empty!” “Name or Pass is empty!”
Enter the name Display “add new book Displayed “add new book
Ok Pass
and password menu” menu”
2 Edit and the
Amount from
270 to 500
Price Amount to change Price Amount changed from
For book Ok Pass
from 270 to 500 270 to 500
“Programmable
Logic
Controllers”
“Publisher” Menu
To Display “List of Displayed “List of Publisher”
Enter “Publisher” Ok Pass
Publisher” Menu
Select a Publisher To Display “List of
Displayed “List of books” from
from the List books” from WROX Ok Pass
WROX publisher
“WROX” publisher
3 Select “Get To Display “Purchase Displayed “Purchase and add
Ok Pass
Details” and add to Cart” to Cart”
Enter “Purchase Display “Check out
Displayed “Check out Menu” Ok Pass
/Add to cart” Menu”
Change quantity ‘2’ to
Changed quantity ‘2’ to ‘5’ Ok Pass
‘5’
“BOOKS” Menu
To Display the “Full Displayed the “Full Catalogues
4 Enter “BOOKS” Ok Pass
Catalogues of Books “ of Books “

Dept of ISE, EPCET 19 2019

DATABASE MANAGEMENT SYSTEM

Enter on the To Display “Purchase Displayed “Purchase and add

Ok Pass
selected BOOK and add to Cart” to Cart”
Enter “Purchase Display “Check out
Displayed “Check out Menu” Ok Pass
/Add to cart” Menu”
“PURCHASE” “Purchase /Add to cart”
Enter
“PURCHASE”
5 Display “PURCHASE” Displayed “PURCHASE” Ok Pass
from “Purchase
/Add to cart”
Enter the details
(name, address,
Display “PAYMENT”
city, zip, country) Displayed “PAYMENT” Menu Ok Pass
Menu
on Purchase
menu
Enter the
Payment details Display “Thank You,
Displayed “Thank You, Your
on Purchase Your ORDER Ok Pass
ORDER Confirmed”
Confirmation Confirmed”
Menu
“CONTACT’ Menu
Enter
6 “CONTACT’ Display “Contact” menu Displayed “Contact” menu Ok Pass
Menu
Enter the details
Details updated in the
on the “contact” Updated in the enquiry tab Ok Pass
enquiry
menu and submit
“My CART” Menu
Enter “My Display “Check out
7 Displayed “Check out Menu” Ok Pass
CART” Menu”
Enter “Go to
Display “Purchase`” Displayed “Purchase” Ok Pass
check out”
Enter
“PURCHASE”
Display “PURCHASE” Displayed “PURCHASE” Ok Pass
from “Purchase
/Add to cart”
Enter the details
(name, address,
Display “PAYMENT”
city, zip, country) Displayed “PAYMENT” Menu Ok Pass
Menu
on Purchase
menu

Dept of ISE, EPCET 20 2019

DATABASE MANAGEMENT SYSTEM

Enter the
Payment details Display “Thank You,
Displayed “Thank You, Your
on Purchase Your ORDER Ok Pass
ORDER Confirmed”
Confirmation Confirmed”
Menu

Dept of ISE, EPCET 21 2019

DATABASE MANAGEMENT SYSTEM

CHAPTER 7

SCREENSHOTS
7.1 MAIN MENU:

Fig. 7.1- Main Menu

7.2 Admin Menu:

Fig. 7.2.1- Admin Login Menu

Dept of ISE, EPCET 22 2019

DATABASE MANAGEMENT SYSTEM

Fig. 7.2.2- Admin Add/Delete Menu

Fig. 7.2.3- Admin Book Details Menu

Dept of ISE, EPCET 23 2019

DATABASE MANAGEMENT SYSTEM

7.3 Publisher Menu:

Fig. 7.3.1- List Of Publisher Menu

Fig. 7.3.2- Publisher Wrox Catalog Menu

Dept of ISE, EPCET 24 2019

DATABASE MANAGEMENT SYSTEM

Fig. 7.3.3- Publisher Wiley Catalog Menu

Fig. 7.3.2- Publisher O’Reily Catalog Menu

Dept of ISE, EPCET 25 2019

DATABASE MANAGEMENT SYSTEM

7.4 Book Details MENU:

Fig. 7.4.1- Book Catalog Menu

Fig. 7.4.2- Book Details Menu

Dept of ISE, EPCET 26 2019

DATABASE MANAGEMENT SYSTEM

Fig. 7.4.3- Book Cart Details Menu

7.5 Checkout Menu:

Fig. 7.5.1- Book Checkout Details Menu

Dept of ISE, EPCET 27 2019

DATABASE MANAGEMENT SYSTEM

7.6 Contact MENU:

Fig. 7.6.1- Contact Details Menu

7.7 My Cart MENU:

Fig. 7.7.1- My Cart Menu

Dept of ISE, EPCET 28 2019

DATABASE MANAGEMENT SYSTEM

CONCLUSION
Research and development are continuous processes; this is same in computer and software
development. However the effectiveness and efficiency of this new system provide room for
further improvement. As early mentioned some of the objectives of this project were not
actualized due to some limitation. The outline clearance system developed will offer greater
opportunity in school management. All transaction or payment with regards to student’s
clearance can be carried out online.

This is a database project designed to maintain an online bookstore, In this era, everything
including books of study are being digitalized and hence, it is required to maintain an
effective method of storing bookstore details.

Instead of using the conventional style of paper-based records and documentation we now
prefer electronic methods are preferred for stock maintenance, automatic updation of records
while billing and reliable electronic documentation.

In traditional system, there were lot of irregularities founds in generating data to where as in
modified and computerized system in every problem overcome with the press of button. This
system provides the security from loss, disclosure, modification and destruction of data. This
system provides integrity of proper functioning of programs.

In this program, books can be distributed among customers efficiently by the click of a
button. All essential data like address, date, author, publisher are maintained and the purpose
of maintaining the online bookstore is fulfilled.

Dept of ISE, EPCET 29 2019

DATABASE MANAGEMENT SYSTEM

REFERENCES

[1] Silberschatz, Sudarshan, and Korth.Database System Concepts.

[2] Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems, 7th Edition.
2017, Pearson.

Dept of ISE, EPCET 30 2019