Given the following description Your application, should handle the following: • School…
- User
Given the following description
Your application, should handle the following: • School operating the library. For each school, the following details should be registered: School name, Address, City, Phone number, Email, Full name of the School Director, Full name of the responsible School Library Operator. Each school unit (through the School Library Operator) is responsible for registering the school’s library available books in the system. • Books with their respective data (title, publisher, ISBN1 , authors, number of pages, summary, available copies/ inventory, image, thematic category, language, keywords). Each book has one or more authors and belongs to one or more categories. • Application Users: For each user, the system must verify their identity when accessing the application (via username/password) and each user can change his own password. o Network School Library Administrator (Administrator): Registers Schools and approves/appoints School Library Operators. They can create a backup copy of the entire database and restore the system from it. o School Library Operator. Operators are responsible for the operation of the School Library at the school unit level. Operators have the ability to process all the information of the books included in the system and to add new ones. They also supervise reservations and loans, either collectively or by searching by user. (Delayed returns are displayed separately). They can record the return of a borrowed title. They can record the loan of a copy when there is a reservation for that particular title, provided that the user's loan limits are met and that no returns are delayed. They also record loans without reservations, by searching for the user and title, if the user meets the loan criteria mentioned above and if there are available copies. o All school students, as well as the professors, can register and use the system. Approval from Operator is required for registration. After approving each user, the Operator prints out the borrower's card and delivers it to the user. Additionally, the Operator is authorized to delete or disable user accounts in accordance with the library's policy. Educators have the ability to modify their personal information, while students are only able to view it.
• Book borrowing: Each user of the system, at the level of the school unit, can view available books, evaluate them and request to borrow a copy. Each student user can borrow up to two books per week, while professors can borrow one per week. Borrowing/returning of books is handled by the Operator. In case a copy of the book is not available, the user's request (reservation) is put on hold and is served upon the return of a copy. • Reservations: Regarding reservations, the borrowing limits apply, e.g., two reservations per week for students. A reservation cannot be made if a book has not been returned on time or if the same user has already borrowed the title. Users have the option to cancel any current reservations. Additionally, reservations have a time frame of one week and are automatically canceled once it expires. • Reviews: Users can share their thoughts about a book in a written review and provide a rating using the Likert2 scale. Reviews written by students will be published upon approval by the Operator. In addition to inputting the aforementioned information, all users of the application must have the ability to manage information, including a search mechanism and options for updating or deleting it (CRUD). Can you give me the syntax for the following queries in mysql
.List with the total number of loans per school (Search criteria: year, calendar month, e.g. January). 4.1.2.For a given book category (user-selected), which authors belong to it and which teachers have borrowed books from that category in the last year? 4.1.3.Find young teachers (age < 40 years) who have borrowed the most books and the number of books.
Find authors whose books have not been borrowed. 4.1.5.Which operators have loaned the same number of books in a year with more than 20 loans? 4.1.6.Many books cover more than one category. Among field pairs (e.g., history and poetry) that are common in books, find the top-3 pairs that appeared in borrowings. 4.1.7.Find all authors who have written at least 5 books less than the author with the most books. All books by Title, Author (Search criteria: title/ category/ author/ copies). 4.2.2.Find all borrowers who own at least one book and have delayed its return. (Search criteria: First Name, Last Name, Delay Days). Find Average Ratings per borrower and category (Search criteria: user/category) 4.3.1.List with all books (Search criteria: title/category/author), ability to select a book and create a reservation request. 4.3.2.List of all books borrowed by this user.modify their personal information, while students are only able to view it.
- Assistant
- List with the total number of loans per school (Search criteria: year, calendar month, e.g. January).
SELECT s.school_name, COUNT(l.loan_id) as total_loans FROM school s JOIN school_library_operator o ON s.school_id = o.school_id JOIN loan l ON o.operator_id = l.operator_id WHERE YEAR(l.borrow_date) = year AND MONTH(l.borrow_date) = month GROUP BY s.school_name;- For a given book category (user-selected), which authors belong to it and which teachers have borrowed books from that category in the last year?
SELECT a.author_name, t.teacher_name FROM book_category c JOIN book b ON c.category_id = b.category_id JOIN book_author a ON b.book_id = a.book_id JOIN loan l ON b.book_id = l.book_id JOIN teacher t ON l.user_id = t.teacher_id WHERE c.category_name = category AND YEAR(l.borrow_date) BETWEEN year(DATE_SUB(NOW(), INTERVAL 1 YEAR)) AND year(NOW()) GROUP BY a.author_name, t.teacher_name;- Find young teachers (age < 40 years) who have borrowed the most books and the number of books.
SELECT t.teacher_name, COUNT(l.loan_id) as total_books FROM teacher t JOIN loan l ON t.teacher_id = l.user_id WHERE TIMESTAMPDIFF(YEAR, t.birthdate, CURDATE()) < 40 GROUP BY t.teacher_name ORDER BY total_books DESC;- Find authors whose books have not been borrowed.
SELECT a.author_name FROM book_author a LEFT JOIN loan l ON a.book_id = l.book_id WHERE l.book_id IS NULL GROUP BY a.author_name;- Which operators have loaned the same number of books in a year with more than 20 loans?
SELECT o.operator_name, COUNT(l.loan_id) as total_loans FROM school_library_operator o JOIN loan l ON o.operator_id = l.operator_id WHERE YEAR(l.borrow_date) = year GROUP BY o.operator_name HAVING total_loans > 20;- Find the top-3 pairs that appeared in borrowings among field pairs (e.g., history and poetry) that are common in books.
SELECT bc1.category_name AS category_1, bc2.category_name AS category_2, COUNT(DISTINCT l.loan_id) AS total_loans FROM book b JOIN book_category bc1 ON b.category_id_1 = bc1.category_id JOIN book_category bc2 ON b.category_id_2 = bc2.category_id JOIN loan l ON b.book_id = l.book_id GROUP BY bc1.category_name, bc2.category_name ORDER BY total_loans DESC LIMIT 3;- Find all authors who have written at least 5 books less than the author with the most books.
SELECT a.author_name FROM book_author a JOIN book b ON a.book_id = b.book_id GROUP BY a.author_name HAVING COUNT(b.book_id) >= (SELECT COUNT(b.book_id) - 5 FROM book_author a2 JOIN book b2 ON a2.book_id = b2.book_id GROUP BY a2.author_name ORDER BY COUNT(b2.book_id) DESC LIMIT 1);- All books by Title, Author (Search criteria: title / category / author / copies).
SELECT b.title, a.author_name, c.category_name, b.available_copies FROM book b JOIN book_author a ON b.book_id = a.book_id JOIN book_category c ON b.category_id = c.category_id;- Find all borrowers who own at least one book and have delayed its return (Search criteria: First Name, Last Name, Delay Days).
SELECT u.first_name, u.last_name, DATEDIFF(NOW(), l.return_date) as delay_days FROM user u JOIN loan l ON u.user_id = l.user_id WHERE l.return_date < NOW() GROUP BY u.first_name, u.last_name;- Find Average Ratings per borrower and category (Search criteria: user/category).
SELECT u.user_name, c.category_name, AVG(r.rating) as average_rating FROM user u JOIN review r ON u.user_id = r.user_id JOIN book b ON r.book_id = b.book_id JOIN book_category c ON b.category_id = c.category_id GROUP BY u.user_name, c.category_name;- List with all books (Search criteria: title/category/author), ability to select a book and create a reservation request.
SELECT b.title, a.author_name, c.category_name FROM book b JOIN book_author a ON b.book_id = a.book_id JOIN book_category c ON b.category_id = c.category_id;- List of all books borrowed by this user.
SELECT b.title FROM book b JOIN loan l ON b.book_id = l.book_id WHERE l.user_id = user_id;