Wenty Library: Database System Design and Implementation Project

This project details the development of a database system for a large library with 15,000 books, organized into six sections. The system includes tables for book information (ID, publication year, author, borrower), author details, publisher details, and member information, all linked with unique IDs and foreign keys. The business case justifies the system's need for efficient record management and automatic updates. An ER diagram visually represents the database structure, showing tables for books, copies, authors, publishers, and members, along with their relationships and primary/foreign keys. SQL coding is provided for creating the database and tables, including data types and constraints. A Gantt chart is also considered, outlining the project's timeline and tasks, such as data analysis, ER diagram creation, SQL coding, and database implementation. The student used SQL Server to create tables and design the ER diagram for accuracy. The final project aims to streamline library operations through efficient data management and retrieval.

Introduction
Provide a justification of why this system needs to be developed: This system need to
developed this because we have a big library in the library we have around 15K books and a
book have many copies as like as we see in every library. We divide library in sex part. In
part one we have library all information like book id, in which year book was publication,
who was the written and who was take this library’s book. We have all information related
to library. Every table have unique id with some have foreign key this type all information is
save here. When ever we want the data related the library just click one and got it all
related data.
Description of business case, justification & business benefits from implementing this
system: Automatic update records of available books, members and transactions of the
members.
ER Diagram: -
Here we information store about book include unique_ID, title, ISBN, publication_year,
author & publisher. Here have Library’s has over 15K book. A book might have many copy as
like as we seen in every library. Copy of a book have uniquely_identified (ID) by copy ID but
the copy_id follows the main book with similar name, cover and book_ID. An author is
character by author_ID name and date_of_birth. There Author_id is Unique or primary key
we use here in Author_Id. In the publisher data set is referenced by unique publisher_ID,
name & addresses. As usually we seen a book is written by one or many author but some
time one book written that’s why we don’t use the primary key in author. A publisher
publishes many books. We have data set in member who can borrow one or limited books
but the member should have unique_id or primarykey constraint. Library member are
identified by member_ID primarykey. The library store’s each member’s_name,
member’s_address, member’s_date_of_birth and contact_number. An member can take
one or many books. We should have all information with reservation_date is also being
record. We Draw the ER_Diagram from Draw io open the search media and search draw.io
for ER_diagram and any other diagram. When ever you go on the site then one pop show.
Each time a member_borrows a book, system_records the member_ID, the book
copy_borrowed, the date of the book borrowed and the expected date to return the book .

Click on create new_diagram then select the diagram which one you want to create. Click on
software option at Right end side showing. When we click here eight_diagram we see here,
select the entity_relationship diagram and pres the create option. There we show different-
2 tables. First of all, we should be select the table and changing in the tables name options.
Changing the tables options name and other variables. Here we should be show the primary
key. we have only one primary_key Book_id and other are not use more constraint and
index. Same choose the seconds tables and changes the variables and tables name with
need primary key or foreign key. Similar did for all the tables and variables. All above
information should be follow related to table we use here. For the best analysis We use SQL
server, in the sql server easy to draw the diagram. First of all we need to create the tables in
SQL server then draw the ER diagram. Possibility that the draw diagram is not correct but
first, we create table in SQL and then draw the diagram it is 100% correct-diagram we draw
with we have all data about the primary key & the secondary keys. We create some data as
char and some num Variables.
Entity_Relationship_Diagram: - For the Database diagram we use SQL server 2017. We have
some tables on base of foreign keys & primary key in almost every tables with variables type
and allows null.

SQL Coding: -
create database [Wenty Library]
use [Wenty Library]
We assign the primary key to book_id and foreign key member_id reference with member
tables variables name member_id. Float mean numeric values insert and nvar char mean
char value data inserting.
/*first table*/create table Library
(
[Book ID] float Primary key,
[Title ISBN] nvarchar(255),
[Publication Year] datetime2,
[Author] nvarchar(255),
[Publisher] nvarchar(255),

[Member_id] float,
foreign key ([Member_id]) references Member([Member_id])
)
We assign the primary_key to Copy_id Float means numeric values insert and nvarchar
means char value data inserting.
create table [Book Copy]
(
[Copy ID] float primary key,
[Book ID] float,
foreign key ([book id]) references Librarys([book id])
)
Here we assign the primary key to [Author id] and foreign key [Book id] reference with
Library table variables name [book id]. Float mean numeric value insert and nvarchar means
char value data inserting.
Create table [Author]
(
[Author id] float primary key,
[Author name] nvarchar(255),
[Date of birth] datetime2,
[Book id] float,
foreign key ([book id]) references Librarys([book id])
)
We did similarly for all tables.
Gantt_chart: - For Gantt_Chart we should consider the books data, author and publication
date etc.

Green Earth
Red Earth
Jones Shnow
Success by Nadain
shadaw great
jim shaw
18/08/2016 06/03/2017 22/09/2017 10/04/2018 27/10/2018 15/05/2019 01/12/2019 18/06/2020
Axis Title
Axis Title