Please design a database table structure of library loan management system

Please design a library Management system database table structure, you can record basic user information, book information, borrowing and returning information; the number of data tables is not more than 6; Please draw a table description table structure (need to describe the field name of each field, field type, field meaning description);

In the database design, you should:

1. Ensure the uniqueness of each user;

2. To ensure the uniqueness of each book; Each book corresponds to a number of books that are not equal to each book; guarantee the uniqueness of each book;

3. In the Library information table, the borrowing behavior and the book book should be considered, and the borrowing period should be considered.

4. Ensure the reference integrity between the Library information table and the user table and the book information table;

5. Limit the maximum number of books that can be borrowed per user

6. Guaranteed to automatically generate unique identities if new users register or book storage

7. Support for the following series of reporting requirements:

(without specific instructions, you do not need to write implementation statements, but in the database design, ensure that these reports can be implemented with up to one SQL statement)

A) Daily statistical report: the same day the number of books, the same day the number of books report;

b) Real-time reporting:

I. The number of loans and the number of loans available for each of the current books;

Ii. List of all overdue books and users in the current system and their extended days

III. The number of books borrowed by all users in the current system, listed by users (including those who do not have a borrowing behavior); write the SQL statement that implements this requirement:

Database application:

Write a series of SQL statements describing the complete library behavior and the book return behavior, and ensure that the execution integrity of this series of SQL statements

The next question is the most important test ability, if not completed we will not be able to give the results of the evaluation! So please write a detailed answer and ensure that the answer is a program that can be executed. e-Mail the results to the [email protected] mailbox within two days

In combination with your design in the second question, a database implementation requires the use of a three-layer structure or a multilayer structure, which requires object-oriented thinking to be programmed and, if possible, a set of template mechanisms to implement.

Features: Lists the current loan books, sorted by date

Numbered User name Title Book Number Loan date

1. Zhang Jin da dyeing workshop 12576587 2004-9-1

2. Liu Xing Journey to the 32131098 2004-9-2

......

Please design a database table structure of library loan management system