Wenty Library Database: ER Diagram, SQL, and Gantt Chart Project

This project details the development of a library database system. It begins with a justification for the system's need, emphasizing efficient management of books, members, and transactions. The solution includes ER diagrams created using Draw.io, illustrating the relationships between entities such as books, authors, publishers, and members. SQL code is provided for creating the database and tables, defining primary and foreign keys, and specifying data types. The SQL code includes tables for library books, book copies, authors, publishers, members, and borrowing transactions. The project also incorporates a Gantt chart, likely for project management or task scheduling related to the library system's implementation. The project demonstrates a practical application of database design principles and SQL implementation in a library context.

Provide a justification of why this system needs to be developed: They are using the information
system to maintain the information on their books, members and transactions efficiently. The
system provides facilities to update records of available books, members and transactions of the
members (issuing and returning of books) with printing, exporting and searching facilities. Here we
have six table and every table have different variable. Primary key and foreign key also we add here.
Every constraint uses with correctly and on best way.
Provide a description of the business case and justification about the business benefits from
implementing this system: Automatic update records of available books, members and transactions
of the members.
Case Study 2 ER Diagram: -
Library has over 15,000 books. Information stored about books include unique ID, title, ISBN,
publication year, author and publisher.A book might have many copies as like we see in every
library. Each copy of a book is uniquely identified by copy ID but the copy id follows the main book
with same name, cover and book ID.A book is written by one or many authors but some time one
book written by one Author. An author is characterized by author ID name and date of birth. There
Author id is Unique or primary key we use here in Author Id.A publisher is referenced by unique
publisher ID, name and address.A publisher publishes many books.A member can borrow one or
many books but the member should have unique id or primary key constraint. Library members are
identified by member ID primary key. The library stores each member’s name, address, date of birth
and contact number.Each time a member borrows a book, system records the member ID, the book
copy borrowed, the date book borrowed and the expected date the book will be returned.A member
can reserve one or many books. The reservation date is also being recorded. We create ER Diagram
from Draw.io. open the google chrome and search draw.io for ER diagram and any other diagram.
When go on site then one pop showing. Click on create new diagram then select the diagram which
one you want to create. Click on software at right side. Eight Diagram showing, select the entity-
relationship diagram and push the create. There we shoeing different-different table. First select the
table and change in the table name option. Change the table option name and other variable. Here is
to show the primary key. Here we have only one primary key Book id and other are not use any
constraint. Same choose the second table and change the variable and table name with need
constraint. Similar did for all table and variable. All above provide information related to table we
use here.

Entity Relationship Diagram: -For Data base diagram we use SQL server.

SQL Coding: -
createdatabaseWenty_Library
useWenty_Library
createtableLibrarys
(
Book_IDfloatPrimarykey,
Title_ISBNnvarchar(255),
Publication_Yeardatetime2,
Author nvarchar(255),
Publisher nvarchar(255),
Member_idfloat,
foreignkey (Member_id)references Member(Member_id)
)
Here we assign the primary key to book id and foreign key member id reference with member table
variable name member id. Float mean numeric value insert and nvarchar mean char value data
inserting.
createtableBook_Copy
(
Copy_IDfloatprimarykey,
Book_IDfloat,
foreignkey (book_id)referencesLibrarys(book_id)
)
Here we assign the primary key to Copy id Float mean numeric value insert and nvarchar mean char
value data inserting.
Createtable Author
(
Author_idfloatprimarykey,
Author_namenvarchar(255),
Date_of_birthdatetime2,
Book_idfloat,
foreignkey (book_id)referencesLibrarys(book_id)
)
Here we assign the primary key to Author id and foreign key Book id reference with Librarys table
variable name book id. Float mean numeric value insert and nvarchar mean char value data
inserting.
Createtable Publisher
(
Publisher_idfloatprimarykey,
Publisher_Namenvarchar(255),
Publisher_Addressnvarchar(255),
Book_Idfloat,
foreignkey (book_id)referencesLibrarys(book_id)
)
Here we assign the primary key to publisher id and foreign key book id reference with librarys table
variable name Book id. Float mean numeric value insert and nvarchar mean char value data
inserting.
createtable Member

(
Member_idfloatprimarykey,
Member_Namenvarchar(255),
Member_addressnvarchar(255),
Member_date_of_birthdatetime2,
Member_Mobilefloat
)
Here we assign the primary key to Member id. Float mean numeric value insert and nvarchar mean
char value data inserting.
createtable borrows
(
Member_idfloat,
Book_Copy_Idfloat,
date_of_borrowsdatetime2,
Return_Book_datedatetime2
foreignkey (book_Copy_id)referencesBook_copy(Copy_id)
)
Here we not assign primary key because no need for this and foreign key Book copy id reference
with Book copy table variable name Copy id. Float mean numeric value insert and nvarchar mean
char value data inserting.
Case Study 4ER Diagram: -
Entity Relationship Diagram: -
Gantt chart: -For Gantt Chart we consider same books data, publication date, Author etc.

Green Earth
Red Earth
Jones Shnow
Success by Nadain
shadaw great
jim shaw
8/18/2016 3/6/2017 9/22/2017 4/10/2018 10/27/2018 5/15/2019 12/1/2019 6/18/2020
Axis Title
Axis Title