Please help to finish the requirement in the blew introduction, and prove some d

Question

Please help to finish the requirement in the blew introduction, and prove some data which can be insert into the table blew(including prove insert sql query), thank you so much.

Introduction

A library has a collection of 120000 items. Most of them, but not all, may be borrowed. There are about 80000 different titles. Thus, some title has multiple copies. Each title has an identifier, a name and a type. There may be a lot of descriptive information attached to each title. These may include information about the authors, the publisher and the number of pages. Each descriptive piece of data has a name. For example, the name of the publisher information is ‘PUBL’. What descriptive data may be used in describing a title depends on the type of the title. The descriptive data used for books may not be the same than the ones used for movies. Some of the elements are mandatory, some are optional. Introduction of new descriptive data elements should be made possible without changing the structure of the database. There are also items specific information that do not depend on the type of the item. They include where the item is located (department, compartment, self) and when and with what price it has been purchased. Each item has a unique number. The library has about 20000 customers. Standard customer information like name and address should be registered of all customers. Each customer has been assigned a unique customer identifier. Customers may borrow items from the library. They may also make reservations for the titles. When an item is returned the system should inform the first customer in reservation queue of the return. When an item is borrowed the date of borrowing and the latest date of return is registered. If the item is returned a marking is made in the borrowing record. Records of the borrowing are never deleted.

Project Requirements:

1. MODEL REQUIREMENT: Identify the entities, attributes and relationship between the entities and draw the conceptual model.

2. Draw a ER digram

table:

RentalInfo (UserID, ISBN, BarCodeID, DateRented, DateReturned, DateDue, TotalFine)
Book (ISBN, BookTitle, Author, PublisherID, Language, Category, Description,
BookCover)
Publisher (PublisherID, PublisherName)
BookCopy (BarCodeID, ISBN, Status, PurchasePrice, PurchaseDate)
User (UserID, UserName, UserAddress, UserPhone, UserIC,UserRegDate, AvailableBook, UserPass, UserPhoto, UserEmail, UserExpiredDate)
Reservation (UserID, BarCodeID, DateRented)
LibraryDetail (Libno, libname, libadd1, libadd2,libposcode,libstate, libtown, libphone, libfax, libemail, libweb)
News(ID, date, content)
ActivityLog (LogID, LogEmp, LogContent)
LostBook (ID, BarCodeID*, UserID*, ISBN*, LostDate)
Admin (Admin_ID, AdminName, AdminLevel, Password, AdminID, AdminContact, AdminEmail, AdminAddress)

Explanation / Answer

Queries

Write a query to display the book code, publication, price and supplier name of the book witch is taken frequently.

Ans: select b1.book_code,b2.publication,b2.price,b3.supplier_name FROM(select b1.book_code FROM Library_BOOK_ISSUE b1 GROUP BY book_code HAVING count(b1.BOOK_CODE) > 1) b1 JOIN Library_BOOK_DETAILS b2 ON b1.book_code = b2.book_code JOIN Library_SUPPLIERS_DETAILS b3 ON b2.supplier_id = b3.supplier_id

Write a query to display the member id, member name, date of registration and expiry date of the members whose membership expiry date is before MAR 2017.
Ans: select member_id,member_name,DATE_REGISTER,DATE_EXPIRE from Library_MEMBERS where DATE_EXPIRE < '2017-03-01'

Write a query to display the member id, member name who’s City is PUNE or MUMBAI. Hint: Display the member name in title case with alias name 'Name'.
Ans: select member_id,member_name as 'Name' from Library_MEMBERS where CITY='PUNE' or CITY='MUMBAI'

Write a query to display the supplier id and supplier name of the supplier who has supplied minimum number of books. For example, if “der Store” supplied 2 books, “LM Store” has supplied 5 books and “Lib Store” has supplied 1 book. So “Library Store” has supplied minimum number of books, hence display the details as mentioned below.
Example:
SUPPLIER_ID     SUPPLIER_NAME
S04                            Lib STORE
ANS: select S.SUPPLIER_ID,SUPPLIER_NAME from Library_BOOK_DETAILS B inner join Library_SUPPLIERS_DETAILS S on B.SUPPLIER_ID = S.SUPPLIER_ID where S.SUPPLIER_ID in (select SUPPLIER_ID from Library_BOOK_DETAILS group by SUPPLIER_ID having count(SUPPLIER_ID)=(select MIN(cnt) from (select count(*) as cnt from Library_BOOK_DETAILS group by SUPPLIER_ID)as T))

* Add new book

INSERT INTO Book VALUES(isbn,title,author,publisher,language1,category,desc,bookcover)

#query for log table after adding into book table

INSERT INTO [activitylog](logemp,logtime,logactivity) VALUES(frmMain.lblEmpID.Text , DateTime.Now ,updownQuantity.Value.ToString() book(s) with ISBN " & isbn & " )

*UPDATE Book

UPDATE Bookcopy SET purchaseprice= txtprice.Text WHERE barcodeid= txtinvi2.Text

#after updating into Book table now store log to the

INSERT INTO [activitylog](logemp,logtime,logactivity) VALUES('frmMain.lblEmpID.Text ,DateTime.Now ,'Edit detail of book with barcodeID txtinvi2.Text ' )

* Return Book

UPDATE rentalinfo SET totalfine= totalFine , datereturned = Now.ToString("d") WHERE rentalinfo.userid =userID AND rentalinfo.barcodeid= barcodeID

Example :- librarymembers who have taken the same book more than one time.

select distinct m.name from member AS m join ( select memberNo, isbn, count(*) AS borrow_times
    from borrowed group by memberNo, isbn having borrow_times > 1 ) as b on m.memberNo = b.memberNo

E R Diagram is showing Error I have Drawn but not able to upload dueto character restriction of answer please upvote my answer for my efforts.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.