Question

Create database for school library.
Hint; create fields for book title, cost ,provider, availability ,etc..

Answer 1

Index

Introduction

Tables

Book_Details

Binding_Details

Category_Details

Borrower_Details

Student_Details

Staff_Details

Student_Details

Shelf_Details

Normalization

ER-Diagram

SQL Command (select, update, insert, delete)

Creating table “Book_Details”:

Inserting Some Data in “Book_Details”

Creating table “Binding_Details”:

Creating Relationship Between Book and Binding Table:

Creating Category Table:

Building Relationship between Book & Category Table:

Creating Borrower Table:

Building Relation Between Book & Borrower Table

Creating Staff Table

Building Relationship between student and Borrower table:

Checking Full Relationship

Adding Shelf Table

Adding Relationship Between Shelf and Book Table

Combined All Relationship

Library Management System

Introduction

The Database Library System is intended to Automate the library activities such as creating a new borrower, giving books to the borrowers, maintaining the details of all the item that were available in the books . This also helps the librarians by providing information such as total copies available each book, list of books that belong to a particular category (Short, Long Loan, Reference items, etc).

Tables

Book_Details:

This is the master table for all the books that are available in the Library. This table contains the complete list of books that are available in the library. Each Book id provided with a unique ISBN which serves as a primary key. The book details include the ISBN, Book Title, the year in which that particular book was published, the type of binding either soft cover or hard cover and the category.

Columns

ISBN: This is unique ID given to every book .Since there may be a large no. of books with same TITLE, this ISBN no. will help us to distinguish between books of same title.

Book_Title: Provides the name of the book.

Publication_year: Contains the year of publication in ‘YY’ format (eg:2009à09)

Language: Contains the language in which this book was published.

Category_Type

This column contains the Category ID whose details can be fetched form the category_master table. The category ID is a Unique number given to each category.

Binding _Id

This column contains the Binding ID whose details can be fetched form the Binding_Detailstable.The Binding ID is a Unique number given to each type Binding.

No_Of_Copies_Actual: This column contains the total no. of copies of each book that were initially present.

No_Of_Copies_Current: This column contains the total no. of copies of each book that were currently available .

Binding_Details:

This table is the Master table for the binding types.This includes the binding ID and Binding Name. The Binding ID serves as a primary key.