Library Management System: Database Project - Part III - Database Design


  LMS
  Library Management System for Imaginary University
  2nd year Database Project


There are Four main entities

Item : a book or as CD which can be borrow from the library
Member : a registered member of the library who will be a student or a staff member
Location : the physical location where an item is stored in the library
Operator : a library staff member who will interact with members of the library. That is they will add/update data about members, transactions and items in the library

Entity Relationship Diagram (ER Diagram)


ER diagram

There can be many copies of the same item (Book or CD). However since we need to identify each item separately so that each of them are given a unique id which is the 'item_id'.
Storing common details of the same copies of items make redundancy, the details of items are decomposed into separate tables named 'book_details' and 'CD_details'.

A member can borrow four books (or CDs) at a time and an item can be borrowed by only one member at a time. Therefore the relation between items and members is many to one.

The relationship, borrows, will record details of a transaction and stored as the history of each transaction. The attribute, returned date of the relationship borrows will be null until the item is returned.

Books and CD’s are stored physically in the place designated by the location. A location can be uniquely identified by the location_id which includes shelf number, row number and section number. Therefore the combination of those three attributes will be the primary key for the entity, location_id. However since there can be about 10-20 books in a section of a shelf the relationship, item_location is many to one from item to location.

Operator entity is used in authenticating operators when using the system, which is essential to confirm security of the system. That does not have any relationship with other entities.

All the entities are checked and confirmed to be normalized in the first and third normal forms.


Database Schema

Database schema





Comments

  1. How can we Create one assertion for the following Constraint:
    "No member can borrow more than three books at a time"

    ReplyDelete
    Replies
    1. it is better to add that kind of assertion in code level rather than the db level

      Delete
  2. How can we Create one assertion for the following Constraint:
    "No member can borrow more than three books at a time"

    ReplyDelete
    Replies
    1. it is better to add that kind of assertion in code level rather than the db level

      Delete
  3. 1. what is the table name for the attributes of issued_date due_date()
    fine() etc. which on top of the borrows table.

    2.what is the purpose or use of borrowed, received_as attributes in the item table.

    3.what is the purpose or use of status attributes in the book table.

    ReplyDelete
  4. how can we find out which book is free? it might be issued earlier and we have it's id in relationship table. won't it take time to put so much check e.g. if we find last issued entry has submission time

    ReplyDelete

Post a Comment

Popular posts from this blog

Library Management System: Database Project - Part I - Introduction

Library Management System: Database Project - Part V - Finalize Database