Online Library Management System: DBMS, SQL and Data Integrity Report

This report provides a comprehensive overview of Database Management Systems (DBMS). It begins by outlining the components of a DBMS and explores relational data modeling approaches. The report then delves into the principles of relational DBMS, including Codd's principles and the use of Entity Relationship diagrams. It further explains how data is stored in a relational DBMS using SQL to create, extract, present, and modify data, along with implementing data integrity for business logic. The report also covers the application of normalization principles and functional dependencies. Additionally, it explains transaction management, query processing, physical storage, and database indexing. Finally, it addresses societal issues related to information privacy. The report uses the scenario of an Online Library Management System to illustrate these concepts and provides relevant SQL statements for database operations. The report is a student contribution to Desklib, a platform providing AI-based study tools for students.

DBMS

Table of Contents
1) Components of DBMS and data modelling approaches.........................................................3
2) Explain principles of relational DBMS...................................................................................3
3. Data stored in a relational DBMS by applying SQL to create database tables, extract,
present and modify data, and implement integrity that reflect business logic.............................5
4) Apply principles of normalisation and functional dependencies............................................7
5) Explain concept of transaction management, query processing, physical storage and
database indexing.........................................................................................................................7
6) Explain societal issues around information privacy................................................................7
REFERENCES................................................................................................................................8

1) Components of DBMS and data modelling approaches
In the business scenario, It can be used the relational modelling approach that help for
creating and designing a dynamic database system in appropriate manner.
There are different components of database which is described as below :-
 Software- they are set of programs that help in managing and controlling of database. It
includes operating system, data applications, etc. (Components of database, 2016).
 Hardware- They are physical devices which act as interface between user and
computers. It contains CPU, server, I/O devices, etc.
 Data- It is the raw data that is processed, stored, collected, etc. in a database.
 Procedure – They are instruction and guidelines that how to use, manage and operate
database.
 Data manager- It helps in managing of data in database and providing recovery of data
in case of failure of system (Mahajan & et.al., 2018).
 Data dictionary – It contains information about database itself. It ensures that data flows
through defined process.
2) Explain principles of relational DBMS
In RDBMS, usually Codd’s principles are followed that are as follows :-
 Information is represented in table form with primary key, columns, etc.
 The database schema can be changed by end user without recreating it.
 Any single operation should be able to update, delete, retrieve, etc.
 The metadata must be stored as regular data.
 The row processing done must follow same rules as in set processing operations.

Entity relationship diagram is basically graphical representation of different entities and also
creates relationship to each other. It is mainly used in the computing regarding company of
information within database system.
According to ER Diagram, it is clearly determined the entities and attributes that shows
the relationship between them.
For Example-
In author_table:
Author id is a primary key that represent as unique key in the database system. It also
creates a relationship with the another table such as Book table. Author id is represents as foreign
key in book table.
In Book_table:
Book id is a primary key that represent as unique key in the database system. It also
creates a relationship with the member table those who are registered a particular book through
the online library management system. Book id is represents as Foreign key in member table.
In member_table:
Member_id is a primary key that represent as unique key in the database system. It also
creates a relationship with book table. In this way, it will create a relationship between book
table and member table.

In student_table:
Student_id is a primary key that represent as unique key in the database system. It also
creates a relationship with book table because every student can issue the book through the
online library management system.
3. Data stored in a relational DBMS by applying SQL to create database tables, extract, present
and modify data, and implement integrity that reflect business logic
In relational Database management system, it is required for creating tables in the form of
columns. It also extracts, modify and present the data in the database system.
As per the scenario, Online library management system that can design a database system
that help for creating update the information in the form of columns.
These are the database table that can create by using Database tools to delete and modify
the values in proper manner. Relational database system help for business scenario to provide the
information and details about the books to the people. It also uses the SQL (Structural Query
language) to perform different operations and functions in database system.
Database tables:
Author_table:
SQL Statement-
SELECT author.[author id], author.[author name], author.[author publisher], author.[year of
publisher]
FROM author;
Book_table:

SQL Statement-
SELECT book.[book id], book.[book price], book.[book name], book.[book issue date], book.
[book subject], book.[author id], book.[student id]
FROM book;
Member_table:
SQL Statement-
SELECT member.[regist id], member.[name], member.[phone no], member.[age], member.
[Email], member.[book id]
FROM member;
Student_table:
SQL Statement-
SELECT student.[student id], student.[student name], student.[student email], student.[student
phone], student.[student city], student.[department], student.[book id]
FROM student;

4) Apply principles of normalisation and functional dependencies
In order to organize data in database normalisation is used. This is done by creating tables
and defining relationship between them. Also, it is used in removing all anomalies and
maintaining consistency of database. It is done by using different programming languages such
as Java, C, C++, etc.
Functional dependencies are set of constraints between two variables in relation.
5) Explain concept of transaction management, query processing, physical storage and database
indexing
 Transaction management – It is a set of related operations that are performed logically.
Basically, they are related to financial transactions and contain many low level tasks
(Bursztyn & et.al., 2016). It works in ACID concept on RDBMS to ensure its integrity
and accuracy.
 Query processing – it is a step by step procedure in which translation of high level
queries is done into low level. This is done by using algebra and executing query.
 Physical storage – A concept of storing data on physical devices such as server, hard
disk, etc.
 Database indexing – It is a technique that is used to retrieve records from database on
certain attributes. It helps in recovering data in effective manner. There are three types of
indexing that is done.
6) Explain societal issues around information privacy
Social issues include system quality which enables in misuse of data and information. The
systems may not be designed according to data type. Thus, it leads to improper storage of data.
Another issue that exist is lack of technology which results in data breach.
So, security constraints are implemented by designing web applications. Moreover,
changes in policies related to information privacy are made.

REFERENCES
Books and Journals
Bursztyn, D. & et.al., (2016). Teaching an RDBMS about ontological constraints. Proceedings of
the VLDB Endowment, 9(12), pp.1161-1172.
Mahajan, D. & et.al., (2018). In-RDBMS hardware acceleration of advanced
analytics. Proceedings of the VLDB Endowment, 11(11), pp.1317-1331.
Online
Components of database. 2016. [Online] Available through : <
https://www.dataentryoutsourced.com/blog/components-of-a-database-management-
system/ >