Database Design Project for Library Information System, University

This project details the design of a database for a Library Information System. It begins with enterprise rules and proceeds to define the database tables, including Book Title, Borrower, LIS, Loan, and Reservations. Detailed table fields are provided for each table. An Entity-Relationship (ER) diagram visually represents the relationships between these tables. Sample data is provided to populate the tables, demonstrating how information would be stored. The project includes a sample SQL query to retrieve specific information from the database, along with examples of forms and reports for data input and output. It also outlines password protection and CRUD (Create, Read, Update, Delete) access control for different user roles. Finally, it covers database backup and recovery procedures, emphasizing the importance of data consistency and the use of full backups and complete recovery methods.

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