Library management system

Library Management System

Library Management System is a software used to manages the catalog of a library.  This helps to keep the records of whole transactions of the books available in the library.

In this post, I will teach you how to design an ER diagram of the library management system its entities, attributes, and queries.
Firstly we should note down its entities, attribute and draw ER diagram roughly in the copy.

Entities and Attributes:

1. Categories - id, category_name
2. Shelfs - id, floor_no, block_no
3. Departments - id, department_name
4. Students - id, name, email, address, phone_no, department_id(fk)
5. Borrowers - id, student_id
6. Staffs - id, name, address, gender, phone_no, designation
7. Books - id, isbn, title, edition, author, shelf_id(fk), category_id(fk)
8. Issues - id, date, expiry_date, borrower_id(fk), staff_id(fk), book_id(fk)
9. Returns - id, date, book_id(fk),staff_id(fk),borrower_id(fk)
10. Fines - id, fine_amount

ER Diagram

Click Here to get a clear image.

SQL QUERRY:

CREATE DATABASE library_management_system;

USE library_management_system;

CREATE TABLE staffs(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100),

    address VARCHAR(100),

    gender VARCHAR(10),

    phone_no VARCHAR(20),

    designation VARCHAR(100)

)engine=InnoDB;

CREATE TABLE department(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    department_name VARCHAR(100)

)engine=InnoDB;

CREATE TABLE students(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(255),

    email VARCHAR(100),

    address VARCHAR(100),

    phone_no VARCHAR(20),

    department_id INT UNSIGNED,

    CONSTRAINT department_student_id_fk

    FOREIGN KEY (department_id)

    REFERENCES departments(id)

)engine=InnoDB;

CREATE TABLE categories(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    category_name VARCHAR(100)

)engine=InnoDB;

CREATE TABLE borrowers(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    student_id INT UNSIGNED,

    CONSTRAINT student_borrower_id_fk

    FOREIGN KEY (student_id)

    REFERENCES students(id)

)engine=InnoDB;

CREATE TABLE shelfs(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    floor_no INT(5),

    block_no INT(5)

)engine=InnoDB;

CREATE TABLE issues(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    issue_date DATE,

    expiry_date DATE,

    book_id INT UNSIGNED,

    CONSTRAINT book_issue_id_fk

    FOREIGN KEY (book_id)

    REFERENCES books(id),

    borrower_id INT UNSIGNED,

    CONSTRAINT borrower_issue_id_fk

    FOREIGN KEY (borrower_id)

    REFERENCES borrowers(id),

    staff_id INT UNSIGNED,

    CONSTRAINT staff_issue_id_fk

    FOREIGN KEY (staff_id)

    REFERENCES staffs(id)

)engine=InnoDB;

CREATE TABLE books(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    isbn INT(20),

    title VARCHAR(100),

    edition VARCHAR(5),

    author VARCHAR(100),

    language VARCHAR(50),

    shelf_id INT UNSIGNED,

    CONSTRAINT shelf_book_id_fk

    FOREIGN KEY (shelf_id)

    REFERENCES shelfs(id),

    category_id INT UNSIGNED,

    CONSTRAINT category_book_id_fk

    FOREIGN KEY (category_id)

    REFERENCES categories(id)

)engine=InnoDB;

CREATE TABLE returns(

    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    date DATE,

    book_id INT UNSIGNED,

    CONSTRAINT book_return_id_fk

    FOREIGN KEY (book_id)

    REFERENCES books(id),

    borrower_id INT UNSIGNED,

    CONSTRAINT borrower_return_id_fk

    FOREIGN KEY (borrower_id)

    REFERENCES borrowers(id),

    staff_id INT UNSIGNED,

    CONSTRAINT staff_return_id_fk

    FOREIGN KEY (staff_id)

    REFERENCES staffs(id)

)engine=InnoDB;

CREATE TABLE fines(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    fine_amount INT(10)

)engine=InnoDB;

CLICK HERE to get word file of the above query.

CLICK HERE to get an ER Diagram file which is created from the draw.io offline app.

CLICK HERE to get data dictionary of a library management system.

If you find any mistake please report to this email address. KEEP LEARNING...THANK YOU 

Share on Facebook

Share on Twitter

Share on Google+

Share on LinkedIn

Subscribe to receive free email updates: