catalogs
3.1.2 Functional requirements analysis
3.2.2 ERD (Entity Relationship Diagram)
3.3.1 Conversion rules for E-R models to relational models
3.3.2 Conversion of the E-R diagram to a database relational table as shown in Figure
3.4.3 Important Stored Procedures
One,summaries
- Design of teaching management system, requires basic information management, such as: readers, librarians, reading room, books, bookshelves, books categorized, publishers, fines, system administrators, booksellers and other basic information.
- Basic Functions:
Role 1: Reader
select courses
Search for books
Borrowing and returning books
Role 2: Librarian
Role 3: Purchaser
Role 4: System Administrator
data backup
system optimization
elimination of dead bodies
3. Borrowing and returning books requires taking into account information about the semester, time, librarians, readers, etc.: borrowing books, returning books, date and time linkage, purchasing books, warehousing, shelving books, taking books off the shelves, taking inventory, and destroying them.ii,preamble
Libraries as a distribution center of information resources, books and users borrow a lot of information, including a lot of information data management, today, there are a lot of libraries are the initial development of the use of, or even not yet use the computer for information management, according to the survey that they used to manage information based on the text, forms, and other paper media, manual processing, the situation of the book loan (such as the number of days of loan, more than the loan of the number of days) The statistics and audit of the book borrowing status (such as the number of days of borrowing and the number of days over borrowing) are all inquired manually by means of library card.
For the borrowing authority, the number of days of borrowing is calculated manually and transcribed manually. The data processing workload is large and prone to errors. Due to the large amount of data easily lost, and not easy to find. In general, there is a lack of systematic and standardized management tools. Although some libraries have computers, but not yet due to information management, did not play its effectiveness, resource idleness is more prominent, which is the basic environment for the development of information management systems. Based on this problem, we believe that it is necessary to establish a library management system to standardize library management.
The system has several major modules: book information entry, book loan management, reader information management, book information query, the main functions of each module are as follows.
Book Information Entry: The book information entry adopts the network query method, by scanning the ISBN code of the book through the barcode gun, the system will automatically search the book information and then enter the book information into the system. Through experiments, it takes about 20-40 seconds to enter a book, which is 90% more efficient than the original cumbersome manual entry, greatly reducing the labor intensity of librarians and breaking the bottleneck of entry in the book management informationization of primary and secondary schools.
Book Borrowing Management: According to the library card number or library card number provided by the readers to borrow books, book return operations. Reader Information Management: Registering readers, canceling readers, checking borrowing records and other operations. Book Information Query: Including importing and exporting book information, querying books according to their titles, classifications, publishers, price ranges, publication dates, classifying statistics, forming superior reports, etc. [1].
iii,main part
3.1 Needs analysis
3.1.1 Data needs analysis
The Library Management Information System (LMIS) is required to accomplish the following functions.
(1) Input of readers’ basic information, including library card number, reader’s name, reader’s gender, etc.
(2) Inquiry and modification of readers’ basic information, including readers’ library card numbers, readers’ names, readers’ genders, and so on.
(3) Development of book category standards, input of category information, including category numbers and category names.
(4) Inquiry and modification of book category information, including category number and category name.
(5) Input of book inventory information, including book number, book name, book category, author’s name, publisher’s name, publication date, and registration date.
(6) Borrowing information is entered, including the reader’s library card number, book number, and date of borrowing.
(7) Query and modification of borrowing information, including library card number, library card number, reader’s name, book number, book name, date of borrowing, etc. [2].
3.1.2 Functional requirements analysis
1. Daily work management: including book borrowing management, book return management
(1) Borrowing management:The main function is to record the borrowing card number and book number for the process of borrowing books. Insert a book borrowing record in the database, the record includes the student number, book ID, the date of lending, return period.
(2) return management: the main function is to enter the library card number, book ID, in the loan registration form to find the appropriate records, the corresponding data will be inserted into the record to return the book record, and at the same time will be deleted from the loan record, and saved in the history of borrowing record table.
2. Basic data maintenance management: including library data maintenance, student data maintenance
(1) Book information maintenance: including the entry of new books, as well as the modification and deletion of existing book information.
(2) Student Information Maintenance: Includes the entry of new student information, as well as the modification and deletion of existing student information.
3. Query management: including library information query, student information query, borrowing history query.
(1) Book information query Book category, book number, book title, author, publisher [3].
(2) Student information query: the required query based on the corresponding student information. Library card number, student name, student number.
(3) Borrowing history record query Borrowing license number, book number, date of borrowing, date of returning.
3.2 Outline design
3.2.1 Data dictionary
(1) Librarian
Table 1 List of librarians
|
listings |
data type |
figure |
Whether or not it is empty |
|
job number |
Varchar |
12 |
no |
|
cryptographic |
Varchar |
50 |
no |
|
name and surname |
Varchar |
12 |
is |
|
distinguishing between the sexes |
Varchar |
2 |
no |
|
telephones |
Varchar |
12 |
is |
|
note |
Varchar |
50 |
is |
|
home address |
Varchar |
100 |
is |
(2) Readers
Table 2 Table of Readers
|
listings |
data type |
figure |
Whether or not it is empty |
|
Reader (computing) |
Varchar |
50 |
no |
|
Reader’s Name |
Varchar |
50 |
no |
|
Gender of readers |
Varchar |
20 |
no |
|
telephones |
Varchar |
12 |
is |
|
Maximum borrowing capacity |
Int |
no |
|
|
faculty |
Varchar |
50 |
no |
|
commencement date |
Datetime |
is |
|
|
expiry date (of document) |
Datetime |
is |
(3) Table of books
Table 3 List of books
|
listings |
data type |
figure |
Whether or not it is empty |
|
job number |
Varchar |
50 |
no |
|
ISBN |
Varchar |
13 |
no |
|
Add Time |
Datetime |
is |
|
|
Availability |
Char |
4 |
is |
(4) Student table
Table 4 Table of students
|
listings |
data type |
figure |
Whether or not it is empty |
|
job number |
Varchar |
50 |
no |
|
student number (of a reader) |
Varchar |
50 |
no |
|
ISBN |
Varchar |
50 |
no |
|
Confirmation of loans and repayments |
Varchar |
50 |
no |
(5) Borrowing Form
Table 5 Borrowing table
|
listings |
data type |
figure |
Whether or not it is empty |
|
job number |
Bigint |
1,1 |
no |
|
ISBN |
Varchar |
50 |
no |
|
student number (of a reader) |
Varchar |
50 |
no |
|
Date of loan |
Datetime |
no |
|
|
Date of return |
Datetime |
no |
|
|
True return date |
Datetime |
is |
|
|
Renewal or non-renewal |
Varchar |
50 |
is |
|
note |
Varchar |
100 |
is |
(6) Books
Table 6 List of books
|
listings |
data type |
figure |
Whether or not it is empty |
|
Book No. |
Bigint |
1,1 |
no |
|
ISBN |
Varchar |
50 |
no |
|
reputation as calligrapher |
Varchar |
50 |
no |
|
author |
Varchar |
50 |
is |
|
publishers |
Varchar |
50 |
is |
|
prices |
Money |
is |
|
|
date of publication |
Datetime |
is |
|
|
stockpile |
Int |
is |
|
|
summary |
Text |
is |
|
|
state of affairs |
Varchar |
50 |
is |
3.2.2 ERD (Entity Relationship Diagram)
Figure 1.1: Main ER diagram
3.2.3 DFD data flow diagrams
Layer 1 data flow diagram:
Three-layer data flow diagram:
Figure 2.3 Three-tier data flow diagram
Four layers of data flow:
Figure 2.4 Four-layer data flow diagram
3.3 Logic Design
3.3.1 Conversion rules for E-R models to relational models:
(1) Conversion of entity types
Converts each entity type into a relationship schema where the attributes of the entity are the attributes of the relationship and the entity identifier is the key of the relationship.
(2) Conversion of contact types
① If the link between entities is 1:1
It is possible to add the attributes of the key and link type of two entity types converted to either of the two relational schemas to the attributes of the other relational schema.
② if the link between entities is 1:n
Then the attributes of the key and link type of the relationship schema converted from the n-terminal entity type are added to the relationship schema converted from the 1-terminal entity type.
(iii) If the link between entities is m:n
Then the contact type is also converted to a relational schema with the attributes being the keys of the two end entity types plus the attributes of the contact type, and the keys being the combination of the two end entity keys.
3.3.2 The E-R diagram is converted to a database relational table as shown in Fig:
(1) Book Information Sheet
Table 7 Book information sheet
(2) Student Information Sheet
Table 8 Student information sheet
(3) Administrator Information Sheet
Table 9 Administrator information table
(4) Borrowing Information Form
Table 10 Borrowing information table
(5) Book Category Information Sheet
Table 11 Table of information on categories of books
3.4 Physical design
The task of database physical design is to select the appropriate physical structure of the application environment for the database logical model, i.e., the logical structure of the database, obtained in the previous stage, both to determine the database storage that effectively implements the logical structure model, to determine the storage structure and access method used on the physical device, and then to evaluate the performance of this storage style, modify the design, and finally, after a number of iterations, to obtain a better-performing storage model. The physical design of the database includes the design of the record storage structure and the design of the storage path [4].
3.4.1 DDL language
① Build a database:
create database libary
② Build the table:
Creating a Book Information Table
CREATE TABLE dbo.jy (
Lno nchar(10) Primary key ,
Lname nchar(20)NULL ,
Wr nchar(10)NULL ,
Ldate smalldate NULL ,
Lprees nchar (20)NULL
Lbz nchar(20)NULL
）
Creating a Reader Information Form
CREATE TABLE dbo.reader (
Rno nchar (10) Primary key ,
Rname nchar (10)NOT NULL，
Rsex nchar(1)NOT NULL ,
Rdep nchar(20)NOT NULL，
Rage int NULL
）
Creating a Loan Information Form
CREATE TABLE dbo.jy (
Rno nchar(10) Primary key ,
Lno nchar(10) Primary key ,
data smalldate NULL ,
Ldata smalldate NULL
）
Creating a User Table
CREATE TABLE dbo. manage (
Pwd nchar(10) Primary key ,
Id nchar(10)NULL ,
）
CREATE TABLE UserInfo(
LibNum Int NOT NULL PRIMARY KEY,
Name varchar (20)NOT NULL,
Sex varchar (20)NOT NULL,
Type varchar (20)NOT NULL,
）
CREATE TABLE Book(
BoNum INT NOT NULL PRIMARY KEY,
Author varchar (15) NOT NULL,
BookName varchar (25) NOT NULL,
Price varchar (30)NOT NULL,
Press varchar (20)NOT NULL,
Storage varchar (25) NOT NULL,
Published varchar (30) NOT NULL,
）
CREATE TABLE Borrow(
BorNum INT NOT NULL PRIMARY KEY,
BorrowNum varchar (30)NOT NULL,
BorrowTime varchar (20) NOT NULL,
BookNum varchar (35) NOT NULL,
)
3.4.2 Views
Figure 3.1 View 1
Figure 3.2 View 2
3.4.3 Important Stored Procedures
INSERT UserInfo
VALUES(‘411420020′,’Zhang San’, male’, student’)
INSERT UserInfo
VALUES(‘411420021′,’Wang Li’, female’,’Student’)
INSERT UserInfo
VALUES(‘411420022′,’Li Si’, male’,’student’)
INSERT UserInfo
VALUES(‘411420023′,’Ma Yang’, female’, student’)
Figure 3.3
INSERT Borrow
VALUES(‘411420020 ,’0101001′,’2015.6.7″,’2015.7.7’)
INSERT Borrow
VALUES(‘411420021′,0101002′,’2015.6.8′,2015.7.7’)
INSERT Borrow
VALUES(‘411420022′,’0101003′,2015.6.9′,2015.7.9’)
Figure 3.4
INSERT Book
VALUES(01010001 ”Fundamentals of Relational Databases” Xu Renfeng “∵’Higher Education Press’ 29.60′,2000.9.8′,2001.8.9′,’Wang Ming’)
INSERT Book
VALUES(01010002′,’Crazy AJAX Lecture Notes’,’Li Gang’,’Electronic Industry Publishing House’,’60.00′,2009.8.7′,2009.10.8′,’Li Zhe’)
INSERT Book
VALUES(01010003′,’JAVA’,’Language Programming’,’Zheng Li’,’Tsinghua University Press’. ‘44.50’,2009.9.6′,’2009.11.11′,’Wang Lei’)
Figure 3.5
create view Info_view(barcode number, book title, ISBN number, whether it is lent or not, how many times it has been lent)
As
select bno , bname , bisbn , islent , times
from Informationcreate view Book_view(ISBN number, book title, category number, shelf number, price, publisher, picture, book description)
as
select bisbn , bname , tpno , bshelf , price , publisher,picture,intro
from Book
create view Book_Type_view(category number, category)
as
select tpno , tp
from Book_Type
Details of whether a book is available for loan
create view info3_view(book name, shelf position, barcode number, checked out or not, on shelf or not)
as select
information.bname ,book.bshelf ,information.bno ,information.islent ,information .ishere
From book,information where information.bisbn=’978-7-5617-6282-O’ andinformation.bisbn=book . bisbn
Search by book title keyword
create view info1_view(title, ISBN number, price, publisher, shelf space)
as select distinct book. bname , book.bisbn , book.price,book.publisher , book.bshelf
from book where book. bname like ‘% Relational Database base %’ From book where book. Bname like ‘% Relational Database base %’
Count the total number of books in category A (and so on for other categories)
select count(bno) from information where bisbn in ( select bisbn from bookwhere Tpno=’A’)
Total number of books counted
select count (bno) from information
Counting the number of times each book is borrowed
select times from information where bno = ‘02010001’
View information about the relational database book in the Book table:
SELECT*FROM BookWHERE BoNum=’1010001′
Figure 3.6
Find information about Zhang San:
SELECT*FROM UserInfoWHERE LibNum=’411420020′
Four,reach a verdict
Through the efforts of this period of time, basically completed the basic functions of the library management system: administrators on the management of book information and the management of reader information, mainly on which the table (book information table dbo.libary and reader information table reader table dbo.reader) information to add, query and delete. However, some of the functions are not realized, such as the reader’s registration of the system, login, borrowing and searching and other functions.
At the same time, through these days of program and report writing, I also found a lot of my own shortcomings, there are a lot of loopholes in their own knowledge, I saw that their practical experience is still relatively lack of, the ability to link theory to practice is still relatively fragile. In particular, the knowledge and skills required to write a large program are lacking. After the program is compiled, it has to be debugged and modified, and this step is also very critical, as a good program is produced only after countless modifications and debugging. Our program basically meets the requirements, but there are still some places need to be improved, in the future we should read more books but also strengthen the practice of practice, in order to further improve their programming ability.