Library Information System Database Design

This assignment delves into the design of a Library Information System (LIS) database. It outlines various tables such as Borrower, BooksTitle, Loan, LIS, Reservations, and Authors, defining their attributes and relationships. A SQL query is provided to retrieve specific data, focusing on publications from 'Pearson Publications.' The document also describes user access levels (Members, Authors, Receptionist, Network Administrator) with CRUD permissions for different roles. Additionally, it explains database backup and recovery procedures, emphasizing the full backup method and complete recovery approach.

design a database

Table of Contents
1. Enterprise Rules..........................................................................................................................1
2. Tables............................................................................................................................................1
3. Table Fields..................................................................................................................................2
4. ER Diagram.................................................................................................................................4
5. Sample Data.................................................................................................................................5
6. Query............................................................................................................................................6
7. Form.............................................................................................................................................7
8. Report.........................................................................................................................................10
9. Password.....................................................................................................................................11
10. CRUD Access.........................................................................................................................12
11. Database backup and recovery procedures.........................................................................12
1

1. Enterprise Rules
The entity rules are used to describe the relationship between the organizations entities
with respect to the attributes domain, degree of relationship and membership class. It
sometime necessary to create the assumption about the degree of relationship and
membership class. These assumptions are must be documented as the data model part and it
checked by the system analyst for accuracy.
2. Tables
The Library Information System Database tables are listed below.
Book Title Table
Borrower Table
Table LIS
2

Table Loan
Table Reservations
3. Table Fields
Table fields are listed below.
For Book Title table,
3

For Borrower table,
For LIS table,
For Loan table,
4

For Reservations table,
4. ER Diagram
The library Information System entity relationship diagram is shown in below.
5

5. Sample Data
Tb_BooksTitle
Books
TitleI
D
Title Aut
hors
Publis
hers
Publicat
ionDate
ISBN Purcha
sePrice
Curre
ntPric
e
Borro
werID
Reserva
tionsID
BT01 Compu
ter
Netwo
rking
Sam
Joel
Sam
Joel
Public
ations
10-11-
2017
ISBN12
34MN
₹
150.00
₹
500.00
BR100 RE100
BT02 C++ Pear
son
Pearso
n
Public
ations
07-10-
2017
ISBN14
54ERN
₹
300.00
₹
600.00
BR101 RE101
BT03 Java
Progra
mming
Jose
ph
Josep
h
Public
ations
15-11-
2017
ISBN14
34HGN
₹
440.00
₹
800.00
BR102 RE102
BT04 Databa
se
System
s
John John
Public
ations
23-11-
2017
ISBN89
05MN
₹
450.00
₹
700.00
BR103 RE103
BT05 Oracle Mic
kle
Mickl
e
Public
ations
18-10-
2017
ISBN23
45MN
₹
356.00
₹
500.00
BR104 RE104
Tb_Borrower
BorrowerID Name Address
BR100 Mary Joseph Delhi
BR101 Sam Joel Sydney
BR102 John Raj Chennai
BR103 Jenniffer USA
BR104 Mickle US
6

Tb_LIS
LibraryID LibraryName Address BooksTitlesID LoanID
L100 Alexandria Library United
States
BT01 LO100
L101 Aloghar Library Bangladesh BT02 LO101
L102 Australian National University
Library
Australia BT03 LO102
L103 American Memorial Library Germany BT04 LO103
L104 Argostoli Public Library Bangladesh BT05 LO104
LoanID DueDate BookTitlesID
LO100 10-11-2017 BT01
LO101 25-10-2017 BT02
LO102 24-11-2017 BT03
LO103 04-10-2017 BT04
LO104 19-11-2017 BT05
Tb_Reservations
ReservationsID Name Address
RE100 Jennifer USA
RE101 Mirklin Germany
RE102 Mercy USA
RE103 Mickle Germany
RE104 John US
6. Query
This query is used to display the following information such as borrow ID, borrow name,
authors, due date and publications. It is shown in below
7

SQL Command
SELECT Tb_Borrower.BorrowerID, Tb_Borrower.Name, Tb_BooksTitle.Title,
Tb_BooksTitle.Authors, Tb_BooksTitle.Publishers, Tb_Loan.DueDate
FROM Tb_Loan INNER JOIN ((Tb_Borrower INNER JOIN Tb_BooksTitle ON
Tb_Borrower.BorrowerID = Tb_BooksTitle.BorrowerID) INNER JOIN Tb_LIS ON
Tb_BooksTitle.BooksTitleID = Tb_LIS.BooksTitlesID) ON Tb_Loan.LoanID =
Tb_LIS.LoanID
WHERE (((Tb_BooksTitle.Publishers)="Pearson Publications"));
7. Form
The Library Information forms are listed below.
8

The above form is display the book title table full information’s.
The above form is display the borrower’s table full information’s.
The above form is display the loan table full information’s.
9

The above form is display the LIS table full information’s.
The above form is display the Reservations table full information’s.
10

The above form is display the Authors information based on same publications.
8. Report
The Library Information system report is displayed in below. It is display the following
information such as borrow ID, borrow name, authors, due date and publications.
11

9. Password
First open the database in exclusive mode. After click the file to encrypt with password.
Finally set the password for Database.
12

10. CRUD Access
CRUD is the basic types of SQL command like Create, Read, Update, and Delete.
Members Read
Authors Read, Update
Receptionist Create, Read, Update and Delete
Network administrator Create, Read and Update
Here applied the exclusive lock to the database.
11. Database backup and recovery procedures
The Back and recovery plan in database should include the RDBMS with the enterprise
and should decide what needs to be backup and determine the appropriate backup types to
13

use the user data. The database backup first determine the backup schedule for the database.
The backup schedule is used to establish the data consistent. The backup schedule is ability to
restore the entire systems and reasonable amount of time. It also used to restore the individual
files that many inadvertently get deleted. Here using the full backup method to secure the
data for a library information system. Here using the complete recovery method to perform
the complete recovery on a database. It must the data, ensure that all data files user want to
recover are online, restore a backup for the entire database or file you want to recover and
must use a backup control file to open the data.
14