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

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


In order to maximize the use of system resources to perform work as efficiently and rapidly as possible the views and indices to be used were confirmed and some of the views were decided to materialize.

In the application a user can search a book based on its authors, title and subject as well as ISBN number. Therefore by creating indices on tilte, authors and subjects the database can access relevant entries more efficiently. Since the table book_details is not updated frequently this will not cause for any low performance of the database.

book_details
create index book_title on book_details(title);
create index book_publisher on book_details(publisher);
create index book_category on book_details(category);

cd_details
create index cd_title on cd_details(title);
create index cd_publisher on cd_details(publisher);
create index cd_category on cd_details(category);

borrows
create index member_index on borrows(member_index);

According to the design of our database it was required to join several tables in order to fulfill search requests. For example when finding a book by its ISBN number we happened to join book_details table with authors table to get details of the book. In addition, to find the availability of that book it was necessary to join item table and book table with previous tables making total number of tables to be joined is four. Searching a book is one of main functionalities and that is done frequently, joining tables at each search is highly expensive execution. Therefore it was more convenient to make a view joining necessary tables as follows and materialize the view since it is not updated very frequently.

CREATE VIEW bookSearch AS SELECT items.id, bd.ISBN, bd.title, bd.edition, bd.publisher, bd.category, authors.author_name_1, authors.author_name_2, authors.author_name_3, items.borrowed
FROM items, books, authors, book_details AS bd
WHERE items.id = books.item_id
AND books.ISBN = bd.ISBN
AND authors.ISBN = bd.ISBN;

Similarly a view to read data in joined tables, item, cd and cd_details was created

CREATE VIEW cdSearch AS SELECT items.id, cd.ISSN, cd.title, cd.edition, cd.publisher, cd.category,items.borrowed
FROM items, cds,cd_details AS cd
WHERE items.id = cds.item_id
AND cds.ISSN = cd.ISSN ;

Following views were created to search member details efficiently

CREATE VIEW memberSearch AS SELECT s.student_index, m.first_name, m.last_name, m.email,
PASSWORD , s.batch, m.department, s.registered_date, m.number_of_books_allowed, s.total_fine
FROM students AS s, members AS m
WHERE s.student_index = m.member_index;


CREATE VIEW staffSearch AS SELECT s.staff_index, m.first_name, m.last_name, m.email,
PASSWORD ,s.position, m.department, s.year_of_appoinment, m.number_of_books_allowed
FROM staff AS s, members AS m
WHERE s.staff_index = m.member_index;

To ensure the persistence and security of data backup capabilities were added to the software.

Book Search Window