Library management system PDF

Preview

Summary

Complete database design and proposal for Library management system project with ERD design , tables, normalization and SQL queries...

Description

Table of Contents Introduction......................................................................................................................................2 ERD.............................................................................................................................................3 Normalization..............................................................................................................................3 1st normal form.............................................................................................................................4 2nd normal form............................................................................................................................4 3rd normal form............................................................................................................................5 SQL queries.................................................................................................................................6 Mapping.......................................................................................................................................8 Insertion into tables....................................................................................................................10 Queries.......................................................................................................................................11 References..................................................................................................................................15

Introduction Our aim is to design database for library management system. It is used by librarians to administer the library with the help of a computerized system that will be used to manage the records of book and students. This system will consist of different entities such as books, students, staff, books status and department of students. All these entities have a relation between them that will make the system easier and efficient in the management of records. Staff can issue books to students who belongs to different departments. This is a computerized system that allows users to manage the library's day-to-day operations in a digital format. It reduces the likelihood of paper work being misplaced, destroyed, or taking an excessive amount of time. It can help users manage transactions or records more effectively while also saving time. Our report will cover the following aspects of Library management system.    

ERD Normalization Mapping SQL queries

Structured Query Language (SQL) is a query language for communicating with relational databases. Despite its detractors, SQL has emerged as the industry standard for searching and manipulating data in relational databases.

ERD

Normalization Normalization technique is used to break the large tables into smaller tables so that redundant data can be avoided. Normalization avoids different types of insertion, updating, deletion anomalies. Anomalies can create error while interacting with database.

Eliminate repeating groups and separate each book id

1st normal form A table will be in first normal form if these rules applied    

Set atomic characteristics and columns. The data in fields should all be from the similar data type. Each column in a table should have its own name. It also makes no difference in which order the data is stored.

2nd normal form   

A table in the Second Normal Form must meet the following requirements: It must be written in the first normal form; and It must be free of partial dependencies.

The Department fields are totally dependent on the primary keys, and we can establish two other relations from the Student Table. DEp id

Student_ID

Name

Issue_date

Expiry_date

Phone

BIT45

Ahmad ali

8-9-21

18-8-21

9953264322

3rd normal form

The properties of a table in the Third Normal Form are as follows:  

The table must meet the rules of second normal form; There should be no transitive dependencies.

Student_ID

Address

City

State

Phone

BIT45

Emirates college

Dubai

Dubai

9953264322

SQL queries

Mapping 1.

2.

3.

4.

5.

Insertion into tables Insert into Books(ISBN, Book_name, Rack_no, Author, Book_edition) Values('B2463','Computer','23','Adward',2);

Insert into Books(ISBN, Book_name, Rack_no, Author, Book_edition) Values('C0775','Database','67','Thomas',7);

Insert into staff(S_id,Name,designation, phonenumber ) Values('BIT65444','Talha','Librarian', 997325644); Insert into staff(S_id,Name,designation, phonenumber ) Values('BYT6435','Mosa','Incharge', 98964232);

Insert into book_status(issue_id, stud_id, issue_date, book_id) Values('Er75','BIT64467','9-2-2021', 'B567');

Insert into book_status(issue_id, stud_id, issue_date, book_id) Values('Er67','AS6567','8-6-2021', 'BG866');

Insert into department(dep_no, dep_name) Values('Dep08','IT'); Insert into department(dep_no, dep_name) Values('Dep7','CS');

Queries 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

References C. J. Date, A. Kannan and S. Swamynathan, An Introduction to Database Systems, Pearson Education, Eighth Edition, 2009. Abraham Silberschatz, Henry F. Korth and S. Sudarshan, Database System Concepts, McGrawHill Education (Asia), Fifth Edition, 2006. Shio Kumar Singh, Database Systems Concepts, Designs and Application, Pearson Education, Second Edition, 2011. Peter Rob and Carlos Coronel, Database Systems Design, Implementation and Management, Thomson Learning-Course Technology, Seventh Edition, 2007. Patrick O’Neil and Elizabeth O’Neil, Database Principles, Programming and Performance, Harcourt Asia Pte. Ltd., First Edition, 2001. Atul Kahate, Introduction to Database Management Systems, Pearson...