The Oracle tutorial you are looking at is: talking about the modeling and design of Oracle database. To develop a database based application system, one of the most critical step is the whole system based on the database modeling design, from the logical to physical, a link neglected design, the entire application system is like building on the dangerous buildings, with the development of the process of continuous deepening, it is at any time to face a variety of difficult
At the risk of anticipation, developers have to pay unpredictable costs for modifying or redesigning a database system that is not well designed. Therefore, a good database design is necessary for efficient systems.
A. Logical modeling
Database design methods vary according to the specific database, but the modeling phase of the same, so you can use some common tools, such as rational rose,powerdesigner, this phase is mainly based on the needs of the system, to obtain and analyze the application system information to be implemented, The analysis of the internal and external relationship of the data can effectively establish the data structure of the whole system (commonly referred to as table structure in the relational database), and then estimate and analyze the data quantity, data flow rate and response speed of the database. The specific operating criteria are several paradigms of the database, the user's specific needs and the analyst's experience, from the database performance, security, convenient management, easy to develop and so on, the specific method because of the analyst's preferences and habits, you can use tools, the best tool to make the analysis process simple, the most important is to generate some graphs, such as e-R diagram, let the analysis process at a glance.
Second, physical design
This step of the design and the system will be specific to the use of the database, but also with the database to run the hard, software platform, the purpose is to give the database as far as possible to allocate physical space, this step in the database design is very important, related to database data security and database performance, specifically, This step includes the corresponding table space of data files on the disk allocation, but also according to the size of the data to determine the size of the Redolog file, rollback segment, and then distribution, the allocation of these files to follow some principles, in favor of backup, conducive to the principle of performance optimization, The principles are as follows (for example, Oracle database):
1. Create a different table space for tables and indexes, prohibit the placing of objects in the system tablespace that are not core Oracle system components, and ensure that the datasheet space and Index table space are on different disk drives.
2, understand how end users access data, if possible, will often query and frequently query objects on different physical disks.
3. When a database contains large objects that allow users to access different data elements in parallel, it is advantageous to partition objects on multiple disks. Partitions can be used. When you define a table with millions of rows on an operating system platform, you need to be more careful because the size of the database file is limit, which is caused by the operating system rather than by Oracle.
4. Create at least two user-defined rollback table spaces on separate disks to store the user's own rollback segment. Arrange the order of the rollback segments in the initialization file so that they switch between multiple disks.
5. Put the redo log file on a disk with less reading and writing. For each Oracle instance, more than two redo log groups are established, and two members of the same group are placed on different devices.
6, the size of the table and index, which determines the size of the table space required to save them, also determines which table space is physically installed on which disk and which table space can be combined. The specific estimation method can be based on some Oracle formulas, and it also defines its storage parameters such as (pctfree,pctused) according to the specific characteristics and uses of each table.
Third, the design of database parameters
Each database is set up with the default parameter settings, but for specific application requirements parameter settings may be different, the default parameter settings often need to be based on the characteristics of the application system needs to change, such as each database's operating system platform, number of instance, the size of various memory settings, the threading method, Different ways of backup, the specific parameters must be the most appropriate modification, this step is important to the performance of the database, but also to ensure that the application system required database functions to achieve an important step.
Iv. interface issues with software development
The final consideration of database design is the interface problem with the development software to be selected, to prepare the interface program, some Third-party software has been prepared, and some of the database itself to have, such as jdbc,bde,ado, such as the interface with the database, the main consideration is the availability of interface, efficiency issues. This step is mainly from experience, because this product is constantly introduced, but also through the touts of the merchants, in practice to decide which is the most suitable.
The above is a database modeling design of several important steps of the rough analysis, the whole design process is continuously improved, is the database administrator, designers, developers together to complete, but each has a different focus, the database administrator focused on the 2, 3 steps of the design, designers focused on the 1-step design, And developers focus on the 4-step design, because some applications of the programming environment and the actual application environment is different, so do two sets of design, and pay attention to two sets of design compatibility portability.
The following is a further explanation of the above steps in the context of a database design in a labor management system that is actually being developed.
The system is applied to large enterprise groups, including dozens of branches, the basic data are from each branch, each branch has Access database, storage of their own talent file information, maintained by the plant itself, regularly uploaded to the general database, for the Labor Department of the room for inquiries, statistics. This is a partial database.
A. Logical modeling
The first is the amount of data on the database, data flow, and response speed estimation analysis, this step for physical design is also necessary, the main data of the labor system is the personnel file, about 100 trillion or so, there are personnel and other information plus work series, wage grade, factories and mines and other coding, The total amount of data is about 300 trillion in some respects. Data input, maintenance most in the factories and mines, and then concentrated everywhere, so the data flow is very large, the response speed requirements in general.
On the logic modeling tools, using Rantional Rose 2000, according to the needs of users, design a variety of use case view, in logic view to generate the database entity, and the entity of the E-R diagram, and then generate Data Modeler, determined later, Generate tables with schema generation the corresponding users in the database under construction.
Detailed diagrams are cumbersome and are not shown here.
Second, physical design
1, because the staff file information base is very large, there are photos, so it divided into two tables, the photos are placed in a separate table, and the use of zoning technology, in the file table, photo table on the partition, different partitions are placed in different table space.
2, because the staff file information base has a lot of code, query and code library to carry out the read operation, so do not put the staff profile and code library in a table space.
3, set up two rollback table space, 10 rollback segment, partition in two table space, establish a relatively large rollback section, because the Employee File information table will often have batch modification and add.
4. Put the redo log file on a disk with less reading and writing. Three redo log groups were established, with two members of the same group placed on two disks. Because the choice is the Archivelog backup way.
5, according to the nature of each table, determine its storage parameters, such as the Staff File information table modification, deletion, insert are more, so put its pctfree 10,pctused 80 size, estimated it roughly required block number, calculate the table space to store the size of the data file.
6, according to the needs of users, set up a set of database security system. This is to summarize several permissions levels of users, establish these users, and give these users a corresponding limit, for developers to use when programming.
Third, the parameter setting
In the default initialization parameter file, select Large. Because of the number of users, 100 or so.
Shared_pool_size is related to the size of the database and to the application software. This is set to 1.5 times times the default value.
The db_block_buffers is set to 25 megabytes depending on the amount of memory and transactions of the server being used.
Sort_area_size based on the memory of the server used, set to 10 megabytes
Log_buffer is 1.5 times times the default value.
Dbwr_io_slaves set to 1
The rollback_segments is set at 10 according to the size of the transaction volume. On two disks, respectively.
PROCESS 80
Mts_servers 3
Mts_dispatchers tcp,3
Archive_log_start TRUE is backed up in ARCHIVE mode.
The determination of these parameters is temporary, and no system can be used to determine the parameters correctly before it is run and needs to be adjusted after the application is running.
Four, with open
[1] [2] Next page
The Oracle tutorial you are looking at is: talking about the modeling and design of Oracle database. The interface problem of sending software
Because our system development software uses is Borland C++builder 5, therefore uses is the more mature BDE interface, it is the Borland company provides, the performance is relatively good.
The above is based on years of database management, development practice summed up the database modeling and design of the general idea, hope to be helpful to everyone.
prev [1] [2]
