Question

This project supposed to develop a database system for a library for a university. The system contains information about books, book authors, students, and faculty. Students and faculty are allowed to borrow books from the library. You should design the system to contain entities and attributes to store data that makes the system able to report the requested information by executing the queries. SQL Queries: 1. List the names and email for students who borrowed books along with the number of books they have borrowed. 2. List the names and departments for instructors who have borrowed books. 3. List all the books that are borrowed along with the borrower name and borrowing date. 4. List the names and email for all borrowers who have not returned the books. 5. List all books for a particular author. 6. List all books that belong to “Information Technology” field.

Answer 1

userborrowbooks BorrowId INT(11) Userld INT (11) Bookid INT(11) BorrowDate DATETIME books BookId INT (11) > BookTitle VARCHAR(100) Book Description VARCHAR(255) > DatePublish DATETIME Quantity INT (11) NoofPages INT (11) Publisher!d INT(11) Languageld INT (11) language Languageld INT (11) 「 버 ◇ LanguageName VARCHAR(255) ◇ Return Date DATETIME ReturnDateDue DATETIME l booksbycategory BookId INT (11) l publisher PublisherId INT (11) PublisherName VARCHAR(50) PublisherPhone VARCHAR(11) PublisherFax VARCHAR(11) ◇ CategoryId INT(11) booksbyauthor userS UserId INT (11) UserName VARCHAR (100) UserSurname VARCHAR (100) Bookid INT(11) ◇ Author! d INT(11) ◇ UserEmail VARCHAR(255) UserBirthday DATETIME PhoneNumber VARCHAR (11) Address TEXT IsInstructor TINYINT(1) Department VARCHAR( 100) author Authorid INT(11) AuthorName VARCHAR (100) AuthorSurn AthorBirthdate DATETIME ca tegory CategoryId INT (11) CategoryWam e VARCHAR(100) ◇ CategoryDescription VARCHAR(255) name VARCHAR(100)

SQL queries:

#1

select A.UserId, B.UserName, B.UserEmail, count(A.BookId) noofbooksborrowed

from userborrowbooks as A join users as B

on A.UserId = B.UserId

group by A.UserId, B.UserName, B.UserEmail;

#2

select A.UserId, B.UserName, B.Department

from userborrowbooks as A join users as B

on A.UserId = B.UserId

where B.IsInstructor=1;

#3

select A.BookId, c.BookTitle, B.UserName, A.BorrowDate

from userborrowbooks as A join users as B

on A.UserId = B.UserId

join books c

on A.BookId = c.BookId;

#4

select A.UserId, B.UserName, B.UserEmail

from userborrowbooks as A join users as B

on A.UserId = B.UserId

where A.ReturnDate is null;

#5

select A.BookId, A.BookTitle

from books as A join booksbyauthor as B

on A.BookId = B.BookId

join author as C

on B.AuthorId = C.AuthorId

where C.AuthorName='';

#6

select A.BookId, A.BookTitle

from books as A join booksbycategory as B

on A.BookId = B.BookId

join category as C

on B.CategoryId = C.CategoryId

where C.CategoryName='Information Technology';