Given the following description can please Define all necessary constraints to ensure correctness of the database. These include
integrity constraints, keys, referential integrity, domain integrity, and user-defined constraints and appropriate indexes for the tables and justify your choice based on their usefulness
for the queries they are used in                                                • 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).