Work in a team to design a database from scratch for a small fictitious business
ID: 3855005 • Letter: W
Question
Explanation / Answer
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.
Columns:
Binding_ID: This column contains the Unique number that was given to each type of binding.
Binding_Name: This column give the names of different types of binding.
Category_Details:
This includes the Category ID and Category Name. The Category ID servers as a primary key.
Columns:
Category _ID: This column contains the Unique number that was given to each type of Category.
Category _Name: This column give the names of different types of categories.
Borrower_Details:
This table contains the details of all the persons who lent a book from the library. Each Student will be given a Unique borrower ID. All the library related activity for a particular person will be captured based on the Borrower ID. This table will be used to track the borrowing records. The borrower ID will serve as a primary key here.
Columns:
Borrower_ID: Unique ID given to each Student.
Book_ID: This column contains the book ID which was give to the borrower.
Borrowed_From_Date: The date on which the book was given a particular borrower.
Borrowed_To_Date: The date on which that book was supposed to be returned back or should be renewed.
Actual_Return_date: The date on which the borrower returned the book to the library.
Issued_by: The ID of the Librarian who issued book to the borrower.
staff_Details:
This table contains the details of the staff in the Library. Each Staff member will be given a unique User ID which serves as a Primary Key.
Columns
User_ID: The unique ID given to each staff member present in the Library.
User_Name: The Name of the staff member.
Is_Admin: Just checking user is admin or not.
Designation: The role of the staff member in the library such as librarian, assistant, etc.
Student_Details:
This table contains the details of all the students they are eligible for availing Library facilities. Each student will be provided with a unique Student ID and Borrower ID. The student ID will be Primary Key, whereas Borrower_ID and Phone_no will be Unique.
Columns:
Student_id: Unique ID given to Each Student.
Student_Name: The Name of the Student.
Sex : Gender of the Student either Male or Female.
Date_Of_Birth: The Date of Birth of the student.
Borrower_ID: The borrower ID assigned to each student.
Department: This is contains student department.
Contact_Number: Contact number of the student.
Shelf_Details:
This table contain the position of the book…That means which floor and shelf the book is situated.
Column:
Shelf_Id: Contains the shelf number.
Floor: Which floor the shelf is situated.
Library Management System (SQL Commands)
Creating table “Book_Details”:
CREATE TABLE Book_Details
(
ISBN_Codeint PRIMARY KEY,
Book_Titlevarchar(100),
Language varchar(10),
Binding_Idint,
No_Copies_Actualint,
No_Copies_Currentint,
Category_idint,
Publication_yearint
)
Inserting Some Data in “Book_Details” :
INSERT INTO Book_details
VALUES('0006','Programming Concept','English',2,20,15,2,2006);
Creating table “Binding_Details”:
CREATE TABLE Binding_details
(
Binding_idint PRIMARY KEY,
Binding_Namevarchar(50)
)
Describe Binding table:
Describe binding_details
Inserting Some data in Binding Table:
INSERT INTO Binding_DetailsVALUES(1,'McGraw Hill);
INSERT INTO Binding_DetailsVALUES(2,'BPB Publication');
All Data of Binding Table:
select *from binding_Details
[Binding]
Creating Relationship Between Book and Binding Table:
ALTER TABLE Book_details
ADD CONSTRAINT Binding_ID_FK FOREIGN KEY(Binding_Id) REFERENCES Binding_Details(Binding_Id);
Checking Relationship:
selectb.Book_Title, e.binding_name
fromBook_Detailsb, Binding_Details e
whereb.binding_id = e.binding_id;
[Realtionship]
Creating Category Table:
CREATE TABLE Category_Details
(
Category_Idint PRIMARY KEY,
Category_Namevarchar(50)
)
Inserting some data in Category Table:
INSERT INTO CATEGORY_DETAILS VALUES(1,'Database');
INSERT INTO CATEGORY_DETAILS VALUES(2,'Programming Language');
Building Relationship between Book & Category Table:
ALTER TABLE Book_details
ADD CONSTRAINT Category_Id_FK FOREIGN KEY(Category_Id) REFERENCES Category_Details(Category_Id);
Checking Relationship:
selectb.Book_Title,e.Category_Name
fromBook_Detailsb,Category_Details e
whereb.binding_id = e.Category_id;
[Realtionship]
Creating Borrower Table:
CREATE TABLE Borrower_Details
(
Borrower_Idint PRIMARY KEY,
Book_Idint,
Borrowed_From date,
Borrowed_TO date,
Actual_Return_Date date,
Issued_byint
)
Inserting Some data in Category Table:
Insert into BORROWER_DETAILS VALUES(1,0004,'01-Aug-2014','7-Aug-2014','7-Aug-2014',1)
Insert into BORROWER_DETAILS VALUES(2,6,'02-Aug-2014','8-Aug-2014',NULL,1)
Building Relation Between Book & Borrower Table:
ALTER TABLE Borrower_details ADD CONSTRAINT Book_Id_FK FOREIGN KEY(Book_Id) REFERENCES Book_Details(ISBN_Code);
Checking Relationship:
selectBorrower_Details.Borrower_id,Book_Details.Book_title
fromBorrower_Details,Book_Details
whereBorrower_Details.book_id=Book_Details.ISBN_Code
[Realtionship]
ALTER TABLE Borrower_Details
ADD CONSTRAINT Issued_by_FK FOREIGN KEY(Issued_by) REFERENCES Staff_Details(Staff_Id);
Creating Staff Table :
CREATE TABLE Staff_Details
(
Staff_Idint PRIMARY KEY,
Staff_Namevarchar(50),
Password varchar(16),
Is_Adminbinary_float,
Designation varchar(20)
)
Inserting some data in Staff Table:
Insert into STAFF_DETAILS values (1,'Tarek Hossain','1234asd',0,'Lib_mgr');
Insert into STAFF_DETAILS values (2,'Md.Kishor Morol','iloveyou',0,'Lib_clr');
All Data of Staff table:
select * from staff_details
[All]
Creating Student Table:
Create TABLE Student_Details
(
Student_Idvarchar(10) PRIMARY KEY,
Student_Namevarchar(50),
Sex Varchar(20),
Date_Of_Birth date,
Borrower_Idint,
Department varchar(10),
contact_Numbervarchar(11)
)
Inserting Some Data in Student Table:
Insert into STUDENT_DETAILS values ('13-23059-1','Ahmed,Ali','Male','05-Oct-1995',1,'CSSE','01681849871');
Insert into STUDENT_DETAILS values ('13-23301-1','MOrol MD.Kishor','Male','03-Jan-1994',2,'CSE','01723476554');
All Data of Student Table:
select *from student_details
[All]
Building Relationship between student and Borrower table:
ALTER TABLE student_details
ADD CONSTRAINT borrower_id_FK FOREIGN KEY(Borrower_Id) REFERENCES Borrower_Details(Borrower_Id);
Checking Full Relationship:
select student.student_id, student.student_name, book.Book_Title, staff.staff_name, b.Borrowed_To
fromstudent_Detailsstudent, Staff_Detailsstaff, Borrower_Detailsb, book_details book
wherestudent.Borrower_id = b.Borrower_id and book.ISBN_Code = b.book_id and b.Issued_by = staff.Staff_id;
[All]
Adding Shelf Table:
Create Table Shelf_Details
(
Shelf_idint PRIMARY KEY,
Shelf_Noint,
Floor_Noint
);
Inserting Some Data from Shelf Table:
Insert into Shelf_DetailsValues(1, 1, 1);
Insert into Shelf_DetailsValues(2, 2, 10001);
Insert into Shelf_DetailsValues(3, 1, 10002);
All Data In Shelf Table:
select*from Shelf_Details;
[All]
Adding Relationship Between Shelf and Book Table:
ALTER TABLE Book_Details
ADD(Shelf_Idint);
UPDATE Book_Details set Shelf_Id = 1
where ISBN_CODE = 4;
UPDATE Book_Details set Shelf_Id = 2
where ISBN_CODE = 6;
ALTER TABLE Book_Details
ADD CONSTRAINT Shelf_Id_FK FOREIGN KEY(Shelf_Id) REFERENCES Shelf_Details(Shelf_Id);
Combined all Relationship:
select student.student_id, student.student_name, book.Book_Title, staff.staff_name, b.Borrowed_To, shelf.shelf_No
fromstudent_Detailsstudent, Staff_Detailsstaff, Borrower_Detailsb, book_detailsbook, Shelf_Details shelf
wherestudent.Borrower_id = b.Borrower_id and book.ISBN_Code = b.book_id and b.Issued_by = staff.Staff_id and book.Shelf_Id = shelf.Shelf_Id
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.