abstract
Book management system is a typical information management system (m is), and its development mainly includes the establishment and maintenance of background database and the development of front-end application programs. For the former, it is required to establish a database with strong data consistency and integrity and good data security. For the latter, the application is required to be fully functional and easy to use.
Therefore, according to the requirements of the open library, I have conducted in-depth research and application on MS SQL Server2000 database management system, SQL language principle, Delphi application program design and Delphi database technology, mainly completed the demand analysis, functional module division and database mode analysis of the book management system, and designed the database structure and application program. The running results of the system prove that the book management system designed in this paper can meet the needs of borrowers, librarians and senior managers.
The first chapter briefly introduces the development of database application system and book management system, and analyzes the work that should be done in developing book management system.
The second chapter systematically analyzes the design of database and the use of SQL language, which lays the foundation for a deeper understanding of database application.
The third chapter studies the specific development tool Delphi 6.0, and systematically introduces its database components, the application of SQL language in Delphi and other key database programming technologies.
The fourth chapter analyzes the application requirements of the library management information system, and according to the database design theory, the system requirements specification, local er diagram, global ER diagram, system relationship model and sub-model are given step by step. The database is established by MS SQL Server2000.
The fifth chapter has carried on the concrete program design, specifically divided the operation authority of three types of users, and designed three kinds of operation interfaces. The functions of database table browsing, record addition, deletion and modification, report generation, multi-database table connection operation, multi-condition query and fuzzy query, flexible update operation of unrenewable query result set, master-slave table operation and password maintenance are realized. Finally, the system can also import databases to operate any database with the same structure.
This design makes full use of the powerful functions of Delphi 6 and MS SQL Server2000 database technology, and improves the programming efficiency and reliability.
Keywords: database, SQL language, MS SQL Server, Delphi6,
Database component, book management, form, list view component
24
37
5.2 Settings of Data Module Form
1. 1 Introduction to database application system development
Before developing database application system, we should understand the basic concept of developing database, and have a clear understanding and understanding of database structure, steps, development system and methods of developing database application programs.
The goal of database application system development is to build a product that meets the long-term needs of users. The main process of development is to understand the needs of users and then transform them into effective database design. Turn the design into actual databases, which are fully functional and efficient in application.
Database technology has always been a very important topic in the neighborhood research of computer software, which came into being in the 1960s. In the past 30 years, database technology has developed rapidly and has formed a relatively complete theoretical system and a large number of practical systems. Moreover, in recent years, with the rapid growth of the World Wide Web (WWW) and the rapid development of Internet technology, database technology has become one of the hottest technologies.
1. 1. 1 database
Figure 1. 1 shows the main components of the database system. Database is handled by DBMS (Database Management System) and used directly or indirectly by developers and users through applications. It mainly includes four elements: user data, metadata, index and application metadata.
1.1.1.1user data
At present, most mainstream database management systems represent user data as relationships. Now think of relationships as data tables. The columns of the table contain fields or attributes, and the rows of the table contain records corresponding to entities in the business environment. Not all relationships meet the requirements equally, and some relationships are more structural than others. The second chapter describes a process that produces a good structural relationship, which is called standardization.
In order to compare the difference between poor structure and good structure, this paper takes the relationship between books and book borrowers in the book management system designed in this paper as an example. Suppose the design relationship R 1 (library card number, name, gender, ID number, contact number, book number, title, book category, author, publishing house, publication date, remarks, price and quantity); The problem with this relationship is that it has data on two different subjects, namely, book borrowers and books. The relationship formed in this way will have problems when it is revised. Because the borrower may borrow more than one book, if a certain field (such as contact number) of the borrower changes, then the book record (possibly more than one book) borrowed by the borrower will definitely change, which is not good. Therefore, data is best represented by two relationships. Now, if the borrower changes the contact number, he only needs to change the row corresponding to the relationship (table) user. Of course, if you want to generate a report showing the names of books and the contact numbers of their borrowers, you need to combine the rows of these two tables. The results show that it is better to store the relationships separately and combine them when generating the report than to store them in the synthesis table.
User (library card number, name, gender, ID number, ID card, contact number, etc.)
Books (book number, title, book category, author, publishing house, publication date, remarks, price, quantity)
1. 1. 1.2 yuan data.
A database is self-describing, which means that it contains a description of its structure, which is called metadata. Because DBMS products are used to store and manipulate tables, most products store metadata in the form of tables, sometimes called system tables. These system tables store the tables in the database, indicating how many columns there are in each table, which column is the primary key, and the description of the data type of each column. It also stores indexes, keywords, rules and other parts of the database structure.
Storing metadata in tables is not only effective for DBMS, but also convenient for users, because they can use the same query tools as user data to query metadata. The SQL language introduced in the second chapter of this paper can be used for both metadata and user data.
1. 1. 1.3 index
The third type of data improves the performance and accessibility of the database, which is usually called overhead data. Although other types of data structures are sometimes used, such as linked lists, it is mainly an index. Indexes can be used to quickly sort and access data. Take the book list in my library management information system as an example to illustrate.
Suppose the data is arranged in ascending order by book number on the disk, and the user wants to print a book data report sorted by book title. Therefore, all data need to be extracted and sorted from the source table, which is a very time-consuming process unless the table is very small. Alternatively, you can create an index on the "Book Name" field, and the entries of the index are sorted by "Book Name", so that the indexed entries can be read out and used to access the book data in sequence.
Indexes are used to access data quickly. For example, users only want to access students whose book category value is "0 1" in the book list. If there is no index, you must search the entire source table; But with an index, you can find the index entry and use it to pick all the appropriate rows.
Indexes help with sorting and searching, but this comes at a price. Every row in the book table changes, and the index must also change, which means that the index is not arbitrary and should be saved when it is really needed.
1. 1. 1.4 application metadata
The fourth data stored in the database is application metadata, which is used to store user forms, reports, queries and other forms of query components. Not all DBMS support application components, and the DBMS that supports components does not necessarily store the structures of all components as application metadata in the database. However, most modern DBMS products store these data as part of the database. Generally speaking, database developers and users do not have direct access to application metadata. Instead, they process the data through tools in the DBMS.
MS SQL Server2000 supports application metadata, such as forms and stored procedures.
1. 1.2 database management system
Database management system (DBMS) refers to a software system that manages data in a database system. DBMS is the core component of database system. All operations on the database, including definition, update and various controls, are carried out through DBMS. DBMS is always based on some data model, which can be regarded as the concrete realization of some data model on computer system. According to different data models, database management systems can be divided into hierarchical, network, relational and object-oriented. MS SQL Server2000 is a relational database management system.
Relational model. The relationship model mainly uses two-dimensional table structure to represent entity set, and foreign keys to represent the relationship between entities. A relational model is a collection of several relational patterns. The relational schema is equivalent to the record type mentioned above, and its instance is called relationship, and each relationship is actually a two-dimensional table.
The biggest difference between relational model and hierarchical and grid model is that key codes are used to navigate data instead of pointers. The table is easy to understand, and the details such as storage structure and access technology are not involved in programming. The relational model is a mathematical model. SQL language is the standardized language of relational database and has been widely used.
As shown in figure 1. 1, the features and functions of DBMS can be divided into three subsystems: design tool subsystem, operation subsystem and DBMS engine.
The design subsystem has a tool set, which can simplify the creation of the database and its applications. It usually contains tools for generating tables, forms, queries and reports. DBMS products also provide programming languages and programming language interfaces.
The running subsystem processes the application components developed by the design subsystem. It contains a running processor, which is used to deal with the data interaction between the form and the database, as well as to answer queries and print reports.
The DBMS engine accepts requests from the other two components and converts them into operating system commands to read and write data on physical media. The DBMS engine also involves transaction management, locking, backup and recovery.
1. 1.3 Create a database
1. 1.3. 1 database schema
The database schema defines the structure, tables, relationships, domains and business rules of the database. Database mode is a design on which databases and applications are based.
A domain is a collection of possible values. You must determine the field for each column of each table. In addition to the physical format of the data, it is also necessary to determine whether some fields are unique to the table.
The last element of database schema is business rules, which are the constraints on business activities that need to be reflected in databases and database applications. Business rules are an important part of the schema, because they specify the constraints that the allowed data values must meet no matter what data changes the DBMS engine receives. Regardless of whether the invalid data change request is from a form user, a query/modification request or an application, the DBMS should reject it.
Unfortunately, different DBMS products implement business rules in different ways. In some cases, DBMS products do not have the ability to implement the necessary business rules, and they must be coded into applications.
Create a table
1. 1.3.3 define the contact.
1. 1.4 application components
Database applications include forms, queries, reports, menus and applications.
1.2 book management system
Today's era is a rapidly developing information age. All walks of life are inseparable from information processing, which is the environment in which computers are widely used in information management systems. The biggest advantage of a computer is that it can manage information. Using computer control information not only improves work efficiency, but also greatly improves its safety.
Especially for complex information management, computers can give full play to their advantages. Computer information management is closely related to the development of information management system, which is the premise of system management. This system is designed to better manage library information.
As a distribution center of information resources, the library has a large number of books and user borrowing materials, including a large number of information data management. At present, many libraries are initially used, and even computers have not been used for information management. According to the survey, they used to manage information mainly by manual processing based on paper media such as text and tables. Statistics and verification of book borrowing (such as borrowing days, over-limit days, etc.) are often carried out by manually checking the library card, and manually calculating and copying the borrower's borrowing authority and borrowing days. Data information processing has a large workload and is prone to errors; Because of the large amount of data, it is easy to lose and difficult to find. Generally speaking, there is a lack of systematic and standardized information management means. Although some libraries have computers, they are not used for information management and their resources are idle, which is the basic environment for the development of management information systems.
Data processing is manual, with heavy workload and high error rate, and it is not easy to change after making mistakes. The library manages the book borrowing manually, but the management of book borrowing information is complicated because of the large amount of information. Generally, the borrowing of books is recorded on the library card, and the quantity and content of books are recorded in the file. It was only then that the staff and managers of the library became clear. After a long time, if they want to make further inquiries, they have to look through many materials, which is time-consuming and laborious. It is even more difficult to revise books that were written a long time ago.
Based on this problem, I think it is necessary to establish a book management system to make book management standardized, systematic and procedural, avoid the randomness of book management, improve the speed and accuracy of information processing, and query and modify books timely, accurately and effectively.
1. 1 Work done by the system
1) Understand the current situation of application development tools.
Programming foundation of Delphi 6.0
3) Microsoft SQL Server Foundation
4) designing a database; Design interface
5) Develop a database. Some functions realized by the database are as follows
L striking display of data and data description;
L Multi-conditional query, multi-record retrieval and fuzzy query;
L data files are imported into data forms in a certain storage format and stored in the database after data integrity verification;
Database security design;
Design of database, data interface and interface.
1.3 the work done in this paper
The introduction briefly introduces the structure and development of database application system, and analyzes the characteristics and tasks of library management information system design.
The second chapter introduces the design and paradigm analysis of database, systematically introduces SQL language, and paves the way for designing and understanding application programs.
The third chapter introduces the database programming technology of Delphi 6.0, the application of SQL language in Delphi 6.0 and the establishment of MS SQL Server.
The fourth chapter analyzes the application requirements of the book management system, designs the database structure of the system, and divides and refines the system functions according to the requirements.
In chapter 5, according to the design results of chapter 4, a specific application program is designed by using MSSQL Server2000 and Delphi 6.0.
The summary part introduces the design experience and programming experience, and points out the shortcomings and improvement direction of the system design.
The second chapter is the theoretical basis of database.
A successful information management system is based on many conditions, and database is one of the most important conditions and key technologies.
The database design involved in the information management system is divided into five steps: database demand analysis, conceptual design, logical design, physical design and load test.
(1) The task of database requirement analysis is to stream business management documents into data streams, divide the boundaries between topics, draw DFD diagrams, and complete corresponding data dictionaries.
(2) The task of conceptual design is to draw the entity-relationship diagram of this topic from DFD and list the outline tables of each entity and relationship.
(3) The task of logic design is to determine the table name attributes of each entity and relationship from the E-R diagram and the corresponding outline table.
(4) The task of physical design is to determine the type, width and value range of all attributes, design the primary key of basic tables, and translate all table names and field names into English (many softwares can support Chinese fields now, such as MS SQL Server, which is my Chinese field name), so as to realize the establishment of physical database and complete the database physical design dictionary.
(5) The loading test runs through the whole process of program testing, and the whole work of inputting, modifying, querying and processing can be regarded as the loading test of the database.
To design a good information management system database, in addition to meeting the functions required by the system, the following principles must be followed:
The fewer the number of basic tables, the better.
As few primary keys as possible. Keys are tools for connecting tables. The fewer primary keys, the simpler the connection between tables.
As few fields as possible.
The design of all basic tables should conform to the third paradigm as much as possible.
In database design, how to deal with many-to-many relationships and how to design primary keys are two difficult problems that need to be considered emphatically. Next, we focus on the key technologies and implementation difficulties of this topic from the aspects of SQL application, database design paradigm and query optimization and solve them.
2. 1 Database System Design and Paradigm Analysis
The main task of information system is to obtain the information needed by management through a large amount of data, which requires storing and managing a large amount of data. Therefore, the establishment of a good data organization structure and database, so that the whole system can quickly, conveniently and accurately call and manage the required data, is one of the main indicators to measure the quality of information system development.
2. 1. 1 database system design
Database design is mainly about the logical design of database, that is, organizing data according to certain classification, grouping system and logical level, which is user-oriented. When designing a database, it is necessary to synthesize the archive data and data requirements of various departments of the enterprise, analyze the relationship between the data, and design a data model with appropriate scale, less data redundancy, high access efficiency and satisfying various query requirements according to the functions and description tools provided by DBMS.
The steps of database design are:
(1) Definition of database structure: At present, some database management systems (DBMS) are operational DBMS supporting online transaction CLTP (collecting, processing and storing transaction data), some large DBMS can support data warehouse and online analytical processing CLAP (a data processing operation supporting decision-making), some databases are relational, and some databases can support object-oriented databases. Defines the database structure for the selected DBMS.
(2) Data table definition: Data table definition refers to defining the structure of data tables in the database. The logical structure of data table includes: attribute name, type, representation, default value, check rule, keyword, whether it can be empty, etc. Relational database should be designed according to the requirements of relational standardization, but in order to achieve high efficiency, the degree of standardization should be determined according to the application environment and conditions. Data table design should not only meet the requirements of data storage, but also add some fields or temporary data tables that reflect relevant information, operational responsibilities and intermediate data.
(3) Organization of storage equipment and storage space: determine storage location, storage path, storage equipment, etc. Data, backup scheme, and how to ensure the consistency and data integrity of multiple versions.
(4) Setting of data usage rights: according to the different usage needs of users, determine the user usage rights of data to ensure data security.
(5) Data dictionary design: A data dictionary is used to describe the design of the database, which is convenient for maintenance and modification.
In order to better organize data and design a practical application database, we should pay attention to the following issues:
Standardized reorganization of data structure: standardized expression of data, which will be discussed in detail later.
Establishment of relational data structure: After the basic data structure is standardized and reorganized, the relational structure of the whole data must be established. After this step is completed, the design of database and data structure is basically completed. Only by substituting the contents of data analysis and data dictionary into the designed overall relational structure of data can the standardized database architecture be established.
The establishment of relational data structure involves three aspects: determining the key index items of association and establishing the association table; Determine the single parent record structure; Establish the relational structure of the whole database.
Determination of (1) link relationship
After the above data standardization and reorganization, we can ensure that every basic data table (we call it table for short) is standardized, but these single tables can't fully reflect things. Usually, we need the overall index data of the index system to fully reflect the problem. That is to say, in each segment of these basic tables, the attributes of different sides of the same thing are stored. So how does the computer system know which records in which tables should correspond to which records in other tables, and they represent the same thing? Therefore, when designing data structure, it is necessary to determine the data record relationship between tables. This data relationship between tables is generally realized by the connection between primary keywords or auxiliary keywords. Because only the main keyword in each table can uniquely identify this record value in the table (because other data field functions in the table depend on the main keyword according to the requirements of the third normal form), you can uniquely identify the storage locations of different attributes of something in different tables by connecting tables with keywords.
(2) Determine a single father-son relationship structure
The so-called determination of a single paternal relationship structure is to eliminate the many-to-many (hereinafter referred to as M:N) phenomenon in various established tables, that is, to make the relationship between records in all tables as tree-like as possible (only one trunk can send out several branches, not several trunks can send out several branches alternately). The so-called "paternal line" refers to the parent relational table of the table. Eliminating many-to-many relationship can be solved by E-R diagram, and this situation can also be avoided in system analysis.
The method to eliminate this M:N situation is also very simple. Just add a table between the two tables, and the original M:N relationship becomes M: 1, 1:N relationship.
Determine the security attributes of data resources:
The general database management system provides us with the function of defining data security and confidentiality. The security functions provided by the system generally have eight levels (0-7 levels) and four different ways (read-only, write-only, delete and modify), allowing users to define each table freely using the four ways of these eight levels.
There are usually the following methods to define security confidentiality:
A. In principle, all documents are defined as Level 4, and the office with particularly high personal priority (terminal or computer access account) can be defined as a level higher than Level 4, and vice versa.
B. Statistical files (tables) and data input files are usually defined as written only for this workstation and read only for other workstations.
C confidential documents such as finance are generally defined as writable, modifiable and erasable only for intermediate workstations (such as finance department), and read-only for other workstations, and not everyone can read them, only those with the same level and high level can read them.
2. 1.2 Analysis of Database Design Paradigm
Establishing a good data indexing system is the most important part of establishing data structure and database. A good data indexing system is a necessary condition for establishing a DB, but it is not a sufficient condition. We can think that an indicator class in the established indicator system is a basic table in the relational database, and each specific indicator under this indicator class is a field in this basic table. However, it is obviously not the best to build the database directly in this way. Before establishing the database, the data structure in the index system must be reorganized in a standardized way.
A. Standardized forms of data organization
In the standardized expression of data, a group of interrelated data is generally called a relationship, and each data indicator item under this relationship is called a data element. This relationship is a basic table when it is implemented in a specific database, and the data element is a field in the basic table. The standardized expression also stipulates that a data element must be defined as a key in each basic table, which can uniquely identify other related data elements in the table. In the normalization theory, the table is two-dimensional, and it has the following four properties:
L In any column in the table, data items should belong to the same attribute (as shown in the figure, each column contains the same attribute data of different contract records).
L The lines in the table are different, and duplicates are not allowed (each line in the figure is a different contract record).
L In the table, the order of rows is irrelevant (as shown in the figure, each row contains contract records, so it doesn't matter which contract is put first).
L in the table, the order of columns is irrelevant, but it cannot be repeated (as shown in the figure, it doesn't matter who comes first, but it cannot be repeated or renamed).
After the standardized definition of table form, there are five standardized definitions of data structures, which are named standardized patterns and paradigms. Of these five paradigms, only the first three are generally used, which is enough for common systems. Moreover, these five paradigms are "upward compatible", that is, the data structure that conforms to the fifth paradigm automatically conforms to the first, second, third and fourth paradigms, and the data structure that conforms to the fourth paradigm automatically conforms to the first, second and third paradigms, and so on.
First normal form (1st NF) means that there are no duplicates in the same table. If there are duplicates, they should be deleted. This process of deleting duplicates is called normalization. In the development method discussed in this paper, 1st NF is actually meaningless. Because our standardized indexing system and table process automatically ensure that all? /div & gt；
Modeling design and implementation of library management system based on UML Recommended CAJ download.
Design and Implementation of English Title Modeling of Library Management System Based on UML
Author Yu Shuxiang;
English author Xiang (Shazhou Institute of Technology; Zhangjiagang 215600);
Shazhou Vocational Institute of Technology;
Journal of Shazhou Vocational Institute of Technology, editorial office mailbox, included in the source magazine CJFD of ASPT, 02, 2005.
Journal of Shazhou Institute of Technology English Edition
Unified modeling language for Chinese keywords: object-oriented; Library management system; Modeling design;
English keywords UML object-oriented; Library management system; Modeling design;
Based on the development of library management system, this paper discusses the application of UML in software architecture modeling. System requirements are represented by use case diagrams, and the structural logic and behavior logic of the system are represented by design class diagrams, sequence diagrams and activity diagrams. This paper introduces the modeling design and implementation process of the library management system based on UML in detail.
Based on the development of library management system, this paper discusses the application of UML in software architecture modeling, and explains in detail the modeling design and implementation process of library management system based on UML. Use case diagrams are used to represent the requirements of the system, and class diagrams, sequence diagrams and activity diagrams are used to represent the structural logic and behavior logic of the system.
DOI CNKI:SCN:32- 1525 . 0 . 2005-02-006