Introduction

Digital Libraries are a boost these days. Nowadays everyone can get access to any book they want, through these digital libraries.

However, did anyone thought how these libraries work at the backend? Lots of data and other record management strategies are used to find the most effective mechanism for storing these data and records such that the required functionality is achieved. Without a thorough, documented records management strategy, organizations experience misunderstandings and data loss.

While going through multiple projects of database management systems for libraries, I came up with my personally developed database schema, specially designed for a digital library. I have formulated some new designs such that effective data storage and data retrieval process takes place.

In this article, I am going to explain how I created a Digital Library Management System using SQL as Database Query Language and MySQL Workbench as Database Management System.

PS: This is just an outline skeleton of the Library Database Management System, examining various assumptions and working of a Digital Library

What is SQL?

https://media.giphy.com/media/vISmwpBJUNYzukTnVx/giphy.gif

SQL stands for Structured Query Language. It is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. Initially developed 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.

Why use SQL?

Easy modification of database table and index structures,
Adding, updating and deleting rows of data,
Retrieving subsets of information from within relational database management systems.

What is MySQL?

https://media.giphy.com/media/13chvmRrJkgyWc/giphy.gif

MySQL is an open-source relational database management system. As with other relational databases, MySQL stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language, more commonly known as SQL.

Why use MySQL?

Developers can install MySQL in minutes, and the database is easy to manage.
It is free of cost and available on the official website for download.
It supports the multi-engine storage feature which facilitates database administrators to configure the database in a way to balance the workload. Hence, it makes the database flawless in terms of performance.
It has a user-friendly interface with a lot of self-management features and different automated processes like configuration and administration-related tasks, which allows users to do the job effectively from Day 1.

Overview of the Project

Digital Library Management System is a database management system designed for e-library or digital libraries. These digital libraries have a huge collection of various types of books and users who access those books. This project is made by taking up various assumptions of how a digital library database management system works.

The Digital Library works like one user has to make an account in the library to access the books. Once made an account, the user can search through a variety of books present in the library. Once a user finds the desired book, he/she will issue that book for some time, read it and then submit the book before the deadline. If not, submitted before the deadline, then it might lead to the cancellation of the account of the user. Thus we can see that this digital library works identically to any offline library.

Schema Structure

The database designed for Digital Library Management System consists of five tables:

Books: This table contains all records, details and other information about the books and novels present in the digital library.
Publishers: This table contains the information and other records about the publishers of the books present in the digital library.
Library Users: This table contains all the records and information about the users who have created an account on the digital library. It consists of basic information like name, age, date of account activation, etc.
Active Library Users: This table contains information about all those users who have created an account on the digital library and have currently issued a book for themselves.
Confidential Data: This table contains the personal information of the registered users which is not to be accessed by everyone. Example: login passwords etc.

All these tables have a limited number of tuples which can be increased in the future. The contents of these tables and used are related to one another which we will see once we analyze the Entity Relationship Model (ER Model) for the following database.

Role Based Access Control (RBAC) is provided to different departments of the digital library inorder to access and work on their respective tables. No department could access the contents of table beyond it's access.

Other than this, the database consists of five views of special attributes, each of which is crafted out from their respective tables for easy retrieval of data:

Classic: This view consists of details of all books where the domain of the book is classic.
Story: This view consists of details of all books where the domain of the book is story.
Fiction: This view consists of details of all books where the domain of the book is fiction.
Male: This view contains the records of all library users where the gender of the user is male.
Female: This view contains the records of all library users where the gender of the user is female.

These views are limited and are created just for testing purposes. More views can be created based on the requirements in the future.

Creating Database

Let us start with creating a database in MySQL. To create a database, open MySql Workbench. It looks like the following:

On the left-hand side, we see the list of all the schemas and databases present. Click on the following button on the top left corner of the window.

This will create a new screen where we can write and execute our SQL queries and SQL commands.

Next, type the following SQL query to create the Library database.

-- Creating a database named Library
create database Library;

An empty database name Library is created.

If there are multiple databases present, then we have to implicitly select one of the databases and work on that. In our case, it can be done by executing the following SQL command

-- Using the current database
use Library;

Our database 'Library' is created and is ready to use.

Creating Tables for our Database

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record.

In our project, we have 5 tables:

Books: In this table, all records of the books such as book id, book name, book domain, author of the book, and the publisher id are present. To create a table, we execute the following SQL query.
```
 -- Creating table named books for storing book records in library
 CREATE TABLE `books` (
     `book_id` INT NOT NULL,
     `book_name` VARCHAR(30),
     `book_domain` VARCHAR(30),
     `author` VARCHAR(20),
     `publisher_id` INT NOT NULL UNIQUE,
     PRIMARY KEY (`book_id`)
 );
```
Here, the 'book_id' is the primary key of the table. in this case, we have taken an assumption that the 'publisher_id' of each book is unique and can not be a null entry.

This table is accessed only by the department that administers the books and its publisher records of the digital library.

To retrieve data present in the books table, we execute the following SQL query.
```
 -- Retrieving values from table books
 SELECT * FROM books;
```
The output returns all the entries and records present in the books table.
Publishers: In this table, all details of the publisher of each book such as publisher id, publisher name, and the publisher address are present. To create a table, we execute the following SQL query.
```
 -- Creting table publisher for storing the records of publishers of books
 CREATE TABLE `publisher` (
     `publisher_id` INT     NOT NULL,
     `publisher_name` VARCHAR(20),
     `publisher_address` VARCHAR(20),
     PRIMARY KEY (`publisher_id`),
     FOREIGN KEY (`publisher_id`) REFERENCES books(`publisher_id`)
 );
```
Here, the 'publisher_id' is the primary key of the table. Also, the 'publisher_id' of the publisher table is acting as a foreign key, which references the 'publisher_id' of the books table. This concept becomes useful while performing Join operations in SQL database.

This table is accessed only by the department that administers the books and its publisher records of the digital library.

To retrieve data present in the publisher table, we execute the following SQL query.
```
 -- Retrieving values from table publisher
 SELECT * FROM publisher;
```
The output returns all the entries and records present in the publisher table.
Library Users: In this table, all details of the users who have made an account on the digital library such as user id, first name, last name, sex, city, occupation of the user, date of birth and the date of account creation are present. To create a table, we execute the following SQL query.
```
 -- Creating table names library_users for library_users registering their account
 CREATE TABLE `library_user` (
     `u_id` INT NOT NULL,
     `fname` VARCHAR(50),
     `lname` VARCHAR(50),
     `sex` VARCHAR(50),
     `city` VARCHAR(50),
     `occupation` VARCHAR(50),
     `DOB` DATE,
     `join_date` DATE,
     PRIMARY KEY (`u_id`)
 );
```
Here, the 'u_id' is the primary key of the table. This table contains all the records which public for a user. These records do not contain any values which harm the user's privacy in any manner.

This table is accessed only by the department that administers the users and their account records in the digital library.

To retrieve data present in the books table, we execute the following SQL query.
```
 -- Retrieving values from table library_user
 SELECT * FROM library_user;
```
Active Library Users: This table contains the records of all the library users who have currently a book or other novel issued on their 'u_id'. It contains details such as user id of the user, the book id of the book, the date of issuing the book and the deadline date of returning the book. If the user fails to return the book before the deadline, it may lead to the cancellation of his/her account from the digital library. To create a table, we execute the following SQL query.
```
 -- Creating table named active_library_users for storing records of all library_users who are currently issuing a book.
 CREATE TABLE `active_library_user` (
     `u_id` INT NOT NULL,
     `book_id` INT NOT NULL,
     `Issued_date` DATE NOT NULL,
     `return_date` DATE NOT NULL,
     PRIMARY KEY (`u_id`),
     FOREIGN KEY (`u_id`) REFERENCES library_user(`u_id`),
     FOREIGN KEY (`book_id`) REFERENCES books(`book_id`)
 );
```
Here, the 'u_id' is the primary key of the table. We have two foreign keys in this table. One foreign key 'u_id' of active_library_user references to the 'u_id' of the library_user table, and another foreign key 'book_id' references to the 'book_id' of the books table. This concept becomes useful while performing Join operations in SQL database. In our project, we have taken the assumption that one library user can issue only one book at a time. Users are not allowed to issue multiple books at the same time. One must return the book to issue a new one.

This table is accessed only by the department that administers the users, books issued by them and the records related to it in the digital library.

To retrieve data present in the books table, we execute the following SQL query.
```
 -- Retrieving values from table active_library_user
 SELECT * FROM active_library_user;
```
Confidential Data: This table contains all the confidential records of the users having an account on the digital library. It includes fields such as the mobile number, email id and password of the user with which the user does the account login and logout process. To create a table, we execute the following SQL query.
```
 -- Creating a table name confi_data to store all the confidential data of the library users which inaccessible to everyone except the databse administrator. 
 create table confi_data (
     u_id INT,
     mobile_no VARCHAR(50),
     email VARCHAR(50),
     Password VARCHAR(50),
     PRIMARY KEY (`u_id`),
     FOREIGN KEY (`u_id`) REFERENCES library_user(`u_id`)
 );
```
Here, the 'u_id' is the primary key of the table. Also, the 'u_id' of the confi_data table is acting as a foreign key, which references the 'u_id' of the library_user table. This concept becomes useful while performing Join operations in SQL database.

This table should only be accessed only by the database administrator of the library users table.

To retrieve data present in the books table, we execute the following SQL query.
```
 -- Retrieving values from table named confi_data
 Select * from confi_data;
```

EER Model of the Database

What is EER Model?

EER is a high-level data model that incorporates extensions to the original ER model. Enhanced ERD are high-level models that represent the requirements and complexities of complex databases.

Why use EER Model?

EER creates a design more accurate to database schemas.
It reflects the data properties and constraints more precisely.
It includes all modeling concepts of the ER model.
The diagrammatic technique helps for displaying the EER schema.
It includes the concept of specialization and generalization.
It is used to represent a collection of objects that is the union of objects of different entity types.

To generate the EER Model in MySQL Workbench, click on 'Databases on the top left of the window.

Select Reverse Engineer.

A pop-up window as shown below occurs. Click on Next.

Click on Next.

Select the schema for which you want to create the model. In our case, it is Library. Click on Next.

Click on Next and then Execute.

The screen will pop up and will show the EER Model for our Digital Library Management System.

The connections are shown as how one table is connected with the other. The yellow boxes contain the views present in the database.

The Relationship connectives are defined as follows:

Conclusion

We created a digital library management system using the MySql database. We learned how to use MySql to create schemas and run SQL queries.

I would like to invite all tech enthusiasts to go through my project and collaborate with me and advice for some enhancements to the project. It would be highly appreciated.

The Digital Library Management System's source code is available on GitHub.

I'm interested to see what you come up with!

So this is it. Do like, comment and share if you like this article.

Do follow me for reading more such technical blogs, content and articles.

Digital Library Management System.

Introduction