IGNOU Solved Assignments RDBMS Lab

Question 2: Assume that you are developing a prototype database of the IGNOU library management system, for that you need to create the following tables:

Book Records
Book details
Member details
Book issue details

Structure of the tables are given below:

Table Name	Attribute Name
Book Records	Accession Number; ISBN Number
Books	ISBN Number; Author; Publisher; Price
Members	Member Id; Member Name; Maximum books that can be issued Maximum days for which book can be issued
Book Issue	Member Id; Accession Number; Issue Date; Return Date>

You must create constraints, including referential integrity constraints, as appropriate. Please note accession number is unique for each book. A book, which has no return date, can be considered as issued book. Enter suitable data into the tables. Now perform following tasks using SQL:

Ans:

Insert data in all the three tables (use insert).
Ans: Insert book record (1, 2),
Insert appropriate description associated with each table and the column (use comment).
Ans:Insert ‘Book’ (2, Nush, Am, 50)
Display the structure of the tables.
Ans:Insert’Members’ (1, Dilli);
Display the comments that you have inserted.
Ans:Select book record froms;
Using SELECT statement, write the queries for performing the following function:
1. Get the list of all books (No need to find number of copies).
  Ans:Select bookno from book. Booknmame
2. List of books issued on 01-Jan-2005.
  Ans:Select book date from Book where book date = “1-Jan-2005”
3. Display member ID and the list of books that have been issued to him/her from time to time. Ans:Select Member Id from. Member where M. MemberID
4. Find the number of copies available of a book of given ISBN number.
  Ans: Select total book from Book record
5. Get the member ID and name of the members to whom no more books can be issued, because they have already got as many books issued as the number for which they are entitled.
  Ans: Select member Id & M.name from member where M. Member ID *select m.name from Book Issue.