Library Management System: Database Project - Part IV - Database Implementation


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


Data Definition Language (DDL) of the database.

create table items
    (
     id                  INT PRIMARY KEY,
     borrowed            boolean,
     received_as         varchar(50)    
        );
   
create table book_details
    (
     ISBN                varchar(15) PRIMARY KEY,
     title               varchar(50) not null,
     edition             varchar(15),
     publisher           varchar(50),
     category            varchar(50),
     price               float(7,2)
        );
       
create table books
    (
     item_id         int PRIMARY KEY,
     ISBN            varchar(15) ,   
     foreign key (item_id) references items(id),
     foreign key (ISBN) references book_details(ISBN)
        );   

create table cd_details
    (
     ISSN                 varchar(15) PRIMARY KEY,
     title                varchar(50) not null,
     edition              varchar(15),
     publisher            varchar(50),
     category             varchar(50),
     price                float(7,2)
        );

create table cds
    (
     item_id           int PRIMARY KEY,
     ISSN              varchar(15) ,
     foreign key (item_id) references items(id),
     foreign key (ISSN) references cd_details(ISSN)
        );

   
create table members
    (
     member_index       varchar(10)  PRIMARY KEY,
     first_name         varchar(50) not null,
     last_name          varchar(50) not null,
     department         varchar(20) not null,
     password           varchar(15) not null,
     email              varchar(50),
     number_of_books_allowed int
        );

create table students
    (
     student_index      varchar(10)  PRIMARY KEY,
     batch              varchar(5) not null,
     total_fine         int,
     registered_date   date,
     foreign key (student_index) references members(member_index)
        );
   
create table staff
    (
     staff_index           varchar(10)  PRIMARY KEY,
     year_of_appoinment    DATE,
     position              varchar(20) not null,
     foreign key (staff_index) references members(member_index)
        );
   
create table authors
    (
    ISBN                  varchar(15)  PRIMARY KEY,
    author_name_1         varchar(20) not null,
    author_name_2         varchar(20),
    author_name_3         varchar(20),
    author_name_4         varchar(20),
    foreign key (ISBN)    references book_details(ISBN)
       );

create table borrows
    (
     member_index         varchar(10),
     item_id              int,
     borrowed_date        DATE not null,
     fine                 float(7,2),
     remarks              varchar(50),
     foreign key (member_index) references members(member_index),
     foreign key (item_id) references items(id),
     primary key (member_index,item_id)
        );
   
create table locations
    (
     id                INT PRIMARY KEY,
     shelve_ID         varchar(6),
     row_number        int,
     section           varchar(15) not null
        );
   
create table item_is_at
    (
     ISBN             varchar(15) PRIMARY KEY,
     location_id      int,   
     foreign key (location_id) references locations(id),
     foreign key (ISBN) references book_details(ISBN)
        );
   
create table operator
    (id               varchar(10) PRIMARY KEY,
     username         varchar(50) not null,
     password         varchar(15) not null,
     email            Varchar(128) not null,
     position         varchar(64)    
        );

Comments

Post a Comment

Popular posts from this blog

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

Image
  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...
Read more

Library Management System: Database Project - Part I - Introduction

Image
  LMS   Library Management System for Imaginary University    2nd year Database Project Database management is a wide area of computer science which is essential in most of the applications in order to store and handle data proper and efficient way. Library management system is one of a most frequently used application of database management systems where there is a chunk of information to store and record daily. Background University of ABC is a imaginary university of about 12000 students and 1000 of staff. The library system of the university facilitates all students, academic and non academic staff by providing access to the library. Anyone of the university can read books or other documents at the library, but in order to borrow them one should register and get the membership. The membership is valid only for a year and that should be updated afterwards. After getting the membership, a member can borrow 4 books at a time and maximum burrowed period...
Read more

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

Image
  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);...
Read more