CN112527771A - City management-oriented database design method - Google Patents
City management-oriented database design method Download PDFInfo
- Publication number
- CN112527771A CN112527771A CN202011462044.8A CN202011462044A CN112527771A CN 112527771 A CN112527771 A CN 112527771A CN 202011462044 A CN202011462044 A CN 202011462044A CN 112527771 A CN112527771 A CN 112527771A
- Authority
- CN
- China
- Prior art keywords
- data
- database
- management
- design
- city management
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000013461 design Methods 0.000 title claims abstract description 44
- 238000000034 method Methods 0.000 title claims abstract description 31
- 238000007726 management method Methods 0.000 claims abstract description 50
- 238000013500 data storage Methods 0.000 claims abstract description 13
- 238000013523 data management Methods 0.000 claims abstract description 10
- 238000012545 processing Methods 0.000 claims abstract description 9
- 238000013507 mapping Methods 0.000 claims description 5
- 230000006378 damage Effects 0.000 claims description 3
- 238000011084 recovery Methods 0.000 claims description 3
- 238000012216 screening Methods 0.000 claims description 3
- 238000010276 construction Methods 0.000 abstract description 7
- 230000009286 beneficial effect Effects 0.000 abstract description 2
- 230000002708 enhancing effect Effects 0.000 abstract description 2
- 238000005065 mining Methods 0.000 abstract description 2
- 238000010586 diagram Methods 0.000 description 7
- XLYOFNOQVPJJNP-UHFFFAOYSA-N water Substances O XLYOFNOQVPJJNP-UHFFFAOYSA-N 0.000 description 4
- 238000009825 accumulation Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000013439 planning Methods 0.000 description 2
- 238000009430 construction management Methods 0.000 description 1
- 238000012938 design process Methods 0.000 description 1
- 238000007667 floating Methods 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/211—Schema design and management
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q50/00—Information and communication technology [ICT] specially adapted for implementation of business processes of specific business sectors, e.g. utilities or tourism
- G06Q50/10—Services
- G06Q50/26—Government or public services
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Business, Economics & Management (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Tourism & Hospitality (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- Educational Administration (AREA)
- Development Economics (AREA)
- Software Systems (AREA)
- Health & Medical Sciences (AREA)
- Economics (AREA)
- General Health & Medical Sciences (AREA)
- Human Resources & Organizations (AREA)
- Marketing (AREA)
- Primary Health Care (AREA)
- Strategic Management (AREA)
- General Business, Economics & Management (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
A design method of a database facing city management comprises the steps of firstly designing the database function according to business functions and application requirements, enabling the database function to be capable of conducting data management, data storage, data retrieval and data uploading/exporting, having a huge data processing function, processing structured data stored in the database, then conducting logic design and physical design on the processed structured data, and finally designing a database table structure according to city management business processes and application requirements, presenting business table and dimension table structure contents, and further achieving management of all data information; the database designed by the invention is beneficial to enhancing the data management capability of the existing platform, ensures the performance requirements of mass data storage, retrieval and uploading in the background, provides a data base for stable operation of the system, analysis and mining of future city management data and construction of the smart city database, and effectively improves the utilization rate of data resources.
Description
Technical Field
The invention relates to the technical field of database design, in particular to a database design method facing city management.
Background
The urban planning construction management work achievement in China is remarkable, urban planning laws and regulations and enforcement mechanisms are basically formed, but some outstanding problems exist in the urban management process, especially the city legal management force in the urban management law enforcement process is not enough, and phenomena of illegal construction, allocation and arrangement points, disordered parking and placement of motor vehicles/non-motor vehicles and the like often exist, so that the urban civilized construction is greatly restricted.
In order to solve the problems, many domestic video analysis manufacturers build various city management video analysis systems for assisting in fine and intelligent city management and achieving certain effect, but mainly achieve software functions in the system construction process, and do not fully consider the comprehensive design of a city management system database and the potential value of city management data.
Disclosure of Invention
The technical problem to be solved by the present invention is to provide a database design method for city management to solve the above problems in the background art.
The technical problem solved by the invention is realized by adopting the following technical scheme:
a database design method facing city management comprises the following specific steps:
1) database functional design
Designing a database function according to a service function and an application requirement, adopting an open source database MySQL or Oracle database system, supporting various operating systems such as AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, OS/2Wrap, Solaris, Windows and the like, providing a management tool for managing, checking and optimizing database operation, enabling the management tool to effectively perform data management, data storage, data retrieval and data uploading/exporting, and having a function of processing tens of millions of records so as to meet the application requirement of a user;
1.1 data management
Namely, the management of data resources is realized, and the data is mainly classified, organized, encoded, stored, retrieved and maintained, and the method specifically comprises the following steps: data security control, namely, preventing data leakage and damage caused by illegal use of data and ensuring the security and confidentiality of the data; integrity control of data, namely, setting some integrity rules to ensure correctness, validity and compatibility of the data; concurrent control, i.e. when multiple users access or modify the database at the same time, preventing mutual interference to provide incorrect data for the users and damaging the database; data recovery, i.e. when a database is corrupted or data is unreliable, the system has the ability to recover the database from an erroneous state to the correct state at the latest moment;
1.2 data storage
Storing relevant data in a service system, and adopting a mode of combining database storage and local storage to meet the performance requirements of multi-path concurrent data storage and data retrieval according to the urban management application requirements;
the structured data stored in the database table includes: fields such as branch office, street, camera IP, camera alias, time, violation/violation event name, and picture storage local path; storing historical alarm picture information in a local disk; the business system can read pictures from a local disk according to the local path field of the pictures stored in the database, and performs operations such as retrieval, uploading and the like;
1.3 data retrieval
The method is characterized in that data query and retrieval aiming at various conditions (such as dimensions according to bureaus, streets, camera IP, camera alias, time, violation/violation event names and the like) are realized, a user realizes classification retrieval of city management violation/violation data according to a certain screening condition, if the occurrence condition of a certain type of events (such as violation parking, street allocation, river channel water area safety, river channel water area flotage, lawn practice and the like) under a certain camera within a certain time range needs to be retrieved, a retrieval result can be output after the time and the camera IP conditions are combined, and the user can conveniently and rapidly position and view;
1.4 data upload/export
The method is characterized in that data uploading/exporting is realized according to user requirements, and early warning information related to violation/violation events, especially warning pictures, is uploaded to a city management comprehensive command platform, so that a user can conveniently and timely check the related information of the warning events, including event types, occurrence time, places, event pictures and the like; in addition, the user can also export data in batches according to dimensions such as branch offices, streets, time, violation/violation event names and the like which are interested in the user;
2) database logic design
Associating keywords in the structured data processed in the step 1) with retrieval keywords, and realizing logic design between tables through logic design;
3) database physical design
Performing database physical structure mapping on the database subjected to the logic design in the step 2) to establish a database physical structure mapping relation;
4) database table structure design
Designing a database table structure according to the urban management business process and application requirements, and finally presenting the structural contents of a business table and a dimension table;
by building the database table relationship, the robust design of the database is realized, all data information is managed, relevant information such as the time, the place, the camera position and the like of the occurrence of the urban management violation/violation event is uniformly organized, the calling by a user is facilitated, a high-grade retrieval function can be provided according to the user requirement to check alarm information, and the final retrieval result is uploaded to an urban management comprehensive law enforcement platform to realize the ordered management of mass evidence obtaining data;
meanwhile, the database supports self expansion of storage, so that the data can be effectively stored, the application requirements, the information requirements and the processing requirements of users are met, the accumulation of city management service data is realized, data isolated islands are avoided, finally, the city management data and all departments can be shared, repeated construction is avoided, and the fund is saved.
Has the advantages that: the database designed by the invention is beneficial to enhancing the data management capability of the existing platform, ensures the performance requirements of mass data storage, retrieval and uploading in the background, provides a data base for stable operation of the system, analysis and mining of future city management data and construction of the smart city database, and effectively improves the utilization rate of data resources.
Drawings
FIG. 1 is a schematic diagram of a database design process according to a preferred embodiment of the present invention.
FIG. 2 is a functional diagram of a database according to a preferred embodiment of the present invention.
FIG. 3 is a diagram illustrating database logic in accordance with a preferred embodiment of the present invention.
FIG. 4 is a physical diagram of a database in a preferred embodiment of the invention.
FIG. 5 is a diagram illustrating data retrieval according to a preferred embodiment of the present invention.
Fig. 6 is a diagram illustrating data uploading in a preferred embodiment of the invention.
Detailed Description
In order to make the technical means, the creation characteristics, the achievement purposes and the effects of the invention easy to understand, the invention is further explained below by combining the specific drawings.
A database design method facing city management comprises the following specific steps:
1) database functional design
Designing a database function according to a service function and an application requirement, adopting an open source database MySQL or Oracle database system, supporting various operating systems such as AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, OS/2Wrap, Solaris, Windows and the like, providing a management tool for managing, checking and optimizing database operation, enabling the management tool to effectively perform data management, data storage, data retrieval and data uploading/exporting, having a function of processing tens of millions of records,
to meet the application requirements of users, the database function design is as shown in fig. 2:
1.1 data management
Namely, the management of data resources is realized, and the data is mainly classified, organized, encoded, stored, retrieved and maintained, and the method specifically comprises the following steps: data security control, namely, preventing data leakage and damage caused by illegal use of data and ensuring the security and confidentiality of the data; integrity control of data, namely, setting some integrity rules to ensure correctness, validity and compatibility of the data; concurrent control, i.e. when multiple users access or modify the database at the same time, preventing mutual interference to provide incorrect data for the users and damaging the database; data recovery, i.e. when a database is corrupted or data is unreliable, the system has the ability to recover the database from an erroneous state to the correct state at the latest moment;
1.2 data storage
Storing relevant data in a service system, and adopting a mode of combining database storage and local storage to meet the performance requirements of multi-path concurrent data storage and data retrieval according to the urban management application requirements;
the structured data stored in the database table of the embodiment includes: fields such as branch office, street, camera IP, camera alias, time, violation/violation event name and picture storage local path; storing historical alarm picture information in a local disk; the service system can read the picture from the local disk according to the local path field of the picture stored in the database, and perform operations such as retrieval, uploading and the like;
1.3 data retrieval
The method is characterized in that data query and retrieval aiming at various conditions (such as dimensions according to bureaus, streets, camera IP, camera alias, time, violation/violation event names and the like) are realized, a user realizes classification retrieval of city management violation/violation data according to a certain screening condition, if the occurrence condition of a certain type of event (such as violation parking, street allocation, river channel water area safety, river channel water area floating object, lawn practice and the like) under a certain camera within a certain time range needs to be retrieved, a retrieval result can be output after the time and the camera IP conditions are combined, and the user can conveniently and rapidly position and view the data, as shown in figure 5;
1.4 data upload/export
The method is characterized in that uploading/exporting of data is realized according to user requirements, and early warning information related to violation/violation events, particularly warning pictures, is uploaded to a city management comprehensive command platform, so that a user can conveniently and timely check related information of the warning events, including event types, occurrence time, places, event pictures and the like; in addition, the user can also export data in batches according to dimensions such as branch office, street, time, violation/violation event names and the like which are interested in the user, and the data is uploaded to a functional process, as shown in fig. 6;
2) database logic design
Taking the selection of an alarm information table as an example, based on PowerDesigner, the design flow of the logic structure diagram of the demonstration database is shown in FIG. 3, and the alarm information table comprises fields such as alarm events, camera IP, alarm event types, street names of the alarm events, alarm picture storage positions and the like; the camera information table comprises fields such as a camera IP, a camera alias, a camera manufacturer, a camera type and a street name of the camera; the street information table comprises street names and fields of companies to which the street names belong, the IP field of the camera in the alarm information table is associated with the IP of the camera in the camera information through logic design, and the street names in the alarm information table are associated with the street names in the street information table so as to realize logic design between the table and the alarm information table;
3) database physical design
Taking the alarm information table as an example, demonstrating the mapping relationship of the physical structure of the final database, as shown in fig. 4;
4) database table structure design
Designing a database table structure according to city management business processes and application requirements, and finally presenting the structural contents of a business table and a dimension table, as shown in tables 1, 2 and 3;
table 1 database alarm information table structure
Table 2 database street information dimension table structure
Table 3 database dimension table structure
By building the database table relationship, the robust design of the database is realized, all data information is managed, relevant information such as the time, the place, the camera position and the like of the occurrence of the urban management violation/violation event is uniformly organized, the calling by a user is facilitated, a high-grade retrieval function can be provided according to the user requirement to check alarm information, and the final retrieval result is uploaded to an urban management comprehensive law enforcement platform to realize the ordered management of mass evidence obtaining data;
meanwhile, the database supports self expansion of storage, so that the data can be effectively stored, the application requirements, the information requirements and the processing requirements of users are met, the accumulation of city management service data is realized, data isolated islands are avoided, finally, the city management data and all departments can be shared, repeated construction is avoided, and the fund is saved.
Claims (8)
1. A method for designing a database for city management is characterized in that the database function is designed according to business functions and application requirements, so that the database can be used for data management, data storage, data retrieval and data uploading/exporting, the method has the function of processing huge amount of data, the structured data stored in the database is processed, the processed structured data is subjected to logic design and physical design, and finally, a database table structure is designed according to business processes and application requirements of city management, and the structural content of a business table and a dimension table is presented, so that all data information is managed.
2. The city management-oriented database design method according to claim 1, characterized by comprising the following specific steps:
1) design database functionality
Designing a database function according to a business function and an application requirement, providing a management tool for managing, checking and optimizing database operation, effectively performing data management, data storage, data retrieval and data uploading/exporting, having a function of processing huge data, and processing structured data stored in a database;
2) database logic design
Associating keywords in the structured data processed in the step 1) with retrieval keywords, and realizing logic design between tables through logic design;
3) database physical design
Performing database physical structure mapping on the database subjected to the logic design in the step 2) to establish a database physical structure mapping relation;
4) database table structure design
Designing a database table structure according to city management business processes and application requirements, and finally presenting the structural contents of a business table and a dimension table;
by building the database table relationship, the robust design of the database is realized, and all data information is managed.
3. The method for designing a database for city management according to claim 2, wherein in step 1), a source database MySQL design is adopted.
4. The method for designing a database for city management according to claim 2, wherein in step 1), a source database Oracle database system is adopted for design.
5. The city management-oriented database design method according to claim 2, wherein in the step 1), the data management specifically includes data security control, namely, preventing data leakage and destruction caused by illegal use of data, and ensuring the security and confidentiality of data; data integrity control, namely, setting an integrity rule to ensure the correctness, validity and compatibility of data; concurrent control, i.e. when multiple users access or modify the database at the same time, preventing mutual interference to provide incorrect data for the users and damaging the database; data recovery, i.e., when a database is corrupted or data is unreliable, the system has the ability to recover the database from an erroneous state to the correct state at the latest time.
6. The method for designing the city management oriented database according to claim 2, wherein in the step 1), the data storage is performed by storing the related data in the service system, and the database storage and the local storage are combined to meet the performance requirements of the multi-path concurrent data storage and data retrieval according to the application requirements of the city management.
7. The city management-oriented database design method according to claim 2, characterized in that in step 1), data retrieval, namely data query and retrieval aiming at various conditions, is realized, a user realizes classification retrieval of city management violation/violation data according to certain screening conditions, and a retrieval result is output, so that the user can conveniently and quickly locate and check.
8. The city management oriented database design method according to claim 2, wherein in the step 1), the data uploading/exporting is to upload the search results according to the user requirements, so that the user can check the search results in time, and export the search results in batch.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011462044.8A CN112527771A (en) | 2020-12-12 | 2020-12-12 | City management-oriented database design method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011462044.8A CN112527771A (en) | 2020-12-12 | 2020-12-12 | City management-oriented database design method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112527771A true CN112527771A (en) | 2021-03-19 |
Family
ID=74999294
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011462044.8A Pending CN112527771A (en) | 2020-12-12 | 2020-12-12 | City management-oriented database design method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112527771A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113239016A (en) * | 2021-06-01 | 2021-08-10 | 通号智慧城市研究设计院有限公司 | Database design assistance apparatus and method |
| CN114218196A (en) * | 2021-12-17 | 2022-03-22 | 江西洪都航空工业集团有限责任公司 | Interface database design method for flight training safety |
-
2020
- 2020-12-12 CN CN202011462044.8A patent/CN112527771A/en active Pending
Non-Patent Citations (1)
| Title |
|---|
| 张国梁: "南康市城市建设管理信息系统的研究", 《中国优秀硕士学位论文全文数据库 信息科技辑》, pages 21 - 65 * |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113239016A (en) * | 2021-06-01 | 2021-08-10 | 通号智慧城市研究设计院有限公司 | Database design assistance apparatus and method |
| CN113239016B (en) * | 2021-06-01 | 2024-04-02 | 通号智慧城市研究设计院有限公司 | Database design assistance apparatus and method |
| CN114218196A (en) * | 2021-12-17 | 2022-03-22 | 江西洪都航空工业集团有限责任公司 | Interface database design method for flight training safety |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CA2712028C (en) | Geospatial database integration using business models | |
| US7966287B2 (en) | Apparatus, system, and method for dynamic database driven document synchronization | |
| JP5826831B2 (en) | Data mart automation | |
| US20200356688A1 (en) | Data access authority management method, apparatus, terminal device and storage medium | |
| CN112527771A (en) | City management-oriented database design method | |
| US20140207774A1 (en) | Virtual Building Browser Systems and Methods | |
| Abel et al. | Spatial join strategies in distributed spatial DBMS | |
| CN102402526A (en) | Geographic information database management method and system | |
| Adler | DB2 Spatial Extender-Spatial data within the RDBMS. | |
| CN102982186B (en) | Based on maintaining method and the system of the range partition table of oracle database system | |
| CN103593449A (en) | Method and system for recycling database resources | |
| US7729997B2 (en) | Determination of an entity's assets associated with an event | |
| CN102169497A (en) | Method and device for managing metadata through bitmaps | |
| EP3036637A1 (en) | Method and system for automatic management of dynamically allocated memory in a computing unit | |
| CN103761188A (en) | Automated testing method for electronic file management system | |
| Lyu et al. | Research and design of intelligent parking management system based on UML technology | |
| CN112559871A (en) | Information query method and system and server equipment | |
| CN108460076A (en) | Equipment and approaches to IM and model | |
| CN111753000A (en) | Water supply network information system | |
| CN115718571B (en) | Data management method and device based on multidimensional features | |
| CN110888856A (en) | Big data analysis system | |
| Cai | Research on application of computer database technology in information management | |
| CN109003209A (en) | Intellectual property lawsuit case management system | |
| CN106649841A (en) | Implement method for construction of GIS-based building library system | |
| Mennecke et al. | Spatial data in the data warehouse: A nomenclature for design and use |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |