Modeling and Design of Oracle databases

The ORACLE tutorial is: Modeling and Design of Oracle databases. To develop a database-based application system, the most critical step is the database modeling design on which the entire system is based. From logical to physical, the design is neglected in one link, the entire application system is built on a dangerous building. As the development process continues to deepen, it must face various difficulties at any time.

Unexpected risks: developers have to pay an unpredictable price to modify or redesign a database system that is not designed. Therefore, a good database design is essential for an efficient system.

I. Logical Modeling

The database design methods vary with specific databases, but the modeling stages are the same. Therefore, you can use some common tools, such as Rational rose and PowerDesigner, in this phase, the application system information to be implemented is obtained and analyzed based on system requirements for internal and external data analysis, in this way, we can effectively establish the data structure of the entire system (usually called the table structure in relational databases). On this basis, we can estimate and analyze the data volume, data traffic, and response speed of the database, in this way, the data model is generated. The specific operating principles are several database paradigms, user-specific requirements, and analysts' experience. They are based on the database performance, security, convenient management, and ease of development, the specific method varies with analyst's preferences and habits. You don't need tools. It is best to use tools to make the analysis process simple. The most important thing is to generate some diagrams, such as E-R diagrams, so that the analysis process is clear at a glance.

Ii. Physical Design

In this step, the design is related to the specific database used by the system, as well as the hardware and software platform running by the database. The purpose is to allocate physical space to the database as far as possible, this step is very important in the database design. It is related to the database data security and database performance. Specifically, this step includes the distribution of data files in the corresponding tablespace on the disk, determine the size of redolog files and rollback segments based on the size of the data volume, and then allocate them. The allocation of these files should follow some principles. In line with the principles conducive to backup and performance optimization, the principles are as follows (Taking ORACLE database as an example ):

1. Create different tablespaces for tables and indexes. Do not place objects of non-core oracle system components in the system tablespace to ensure that the data table space and index tablespace are on different disk drives.

2. Understand how end users access data. If possible, place objects that are frequently queried and frequently queried 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 split the objects and store them on multiple disks. You can use partitions. When defining tables with millions of rows on an operating system platform, be more careful because the size of database files is limited, which is caused by the operating system rather than oracle.

4. create at least two custom rollback tablespaces on each independent disk to store your own rollback segments. Arrange the order of rollback segments in the initialization file so that they can be switched between multiple disks.

5. Place the redo log file on a disk with fewer reads and writes. For each oracle instance, you need to create more than two redo log groups. The two members in the same group are placed on different devices.

6. determine the size of tables and indexes, which determines the size of the tablespaces required for saving them, and also determines which tablespaces are physically mounted on which disks and which tablespaces can be combined. The specific estimation method can be based on oracle formulas. Here, we need to define its storage parameters (such as pctfree and pctused) based on the specific characteristics and usage of each table ).

Iii. Database Parameter Design

Each database has default parameter settings when it is created. However, the parameter settings may vary depending on the specific application requirements. The default parameter settings often need to be changed based on the characteristics of the application system, for example, the operating system platform, instance number, memory size setting, thread mode, and backup mode of each database are different. The specific parameters must be modified as appropriate, this step is important to the database performance and to ensure that the database functions required by the application system are implemented.

Iv. interface problems with software development

The last thing to consider in database design is the interface problem between the software to be developed and the software to be selected. To prepare the interface program, some third-party software has been prepared, and some are required by the database itself, for example, jdbc, bde, ado, and other database interfaces mainly consider the availability and efficiency of interfaces. This step is mainly based on experience, because such products are constantly introduced and are all touted by various merchants. In practice, we must decide which one is the most suitable.

The above is a rough analysis of several important steps in the database modeling design. The whole design process is constantly improved. It is done by the database administrators, designers, and developers, but each has a different focus, the Database Administrator focuses on two or three steps of design. The designer focuses on one step of design, while the developer focuses on four steps of design, because the programming environment of some application systems is different from the actual application environment, we need to design two sets and pay attention to the compatibility and portability of the two sets.

The following describes the above steps based on the database design in a developed labor management system.

This system is applied to large enterprise groups, including dozens of sub-factories. The basic data is from each sub-factory. Each sub-factory has an access database that stores its own talent file information, which is maintained by each factory, it is regularly transmitted to the general database for query and statistics by labor departments ,. This is a branch-type database.

I. Logical Modeling

The first is the estimation and analysis of the data volume, data traffic, and response speed of the database. This step is also required for physical design. The main data of the labor system is the personnel file, the total data volume is about 100 MB in some aspects, with information such as staffing and various codes such as job sequence, wage grade, and factory and Mining Workshop. Most of the data input and maintenance are carried out in various factories and mines and then concentrated everywhere. Therefore, the data traffic is large and the response speed is generally required.

On the tool of logical modeling, the Rantional rose 2000 is used to design various use case views according to the user's requirements, generate the entity of the database in the logic view, and the E-R diagram between the entities, then generate Data modeler. After confirmation, generate a table with the corresponding user in the database created by Schema generation.

The detailed diagram is cumbersome and will not be shown here.

Ii. Physical Design

1. Because the employee archive information database is large and there are still photos, we divide them into two tables, put the photos in one table separately, and adopt the Partitioning technology to create partitions in the archives and photo tables, different partitions are placed in different tablespaces.

2. Because there are many codes in the employee archive information database, read operations must be performed with the encoding database during query. Therefore, the employee archive information table and the encoding database are not placed in a tablespace.

3. Create two rollback tablespaces, ten rollback segments, partitions in two tablespaces, and create a large rollback segment, because the employee archive information table is often modified and added in batches.

4. Place the redo log file on a disk with fewer reads and writes. Create three redo log groups and put the two members in the same group on two disks. The ARCHIVELOG backup method is used.

5. Determine the storage parameters of each table based on its nature. For example, there are many changes, deletions, and inserts in the employee archive information table. Therefore, set the size of Pctfree 10 and Pctused 80, estimate the approximate number of blocks required by the table and calculate the size of the data file that stores the tablespaces of these tables.

6. Establish a database security system based on users' needs. That is to say, we have summarized several permission-level users, established these users, and granted them corresponding limited permissions for developers to program.

3. parameter settings

Select large in the default initialization parameter file. Because there are many users, about 100.

SHARED_POOL_SIZE is related to the database size and application software. Set it to 1.5 times the default value.

DB_BLOCK_BUFFERS is set to 25 MB Based on the memory and transaction volume of the server used.

SORT_AREA_SIZE is set to 10 MB Based on the memory of the server used

LOG_BUFFER is 1.5 times the default value.

DBWR_IO_SLAVES is set to 1

ROLLBACK_SEGMENTS is set to 10 Based on the transaction volume. Put them on two disks respectively.

PROCESS 80

MTS_SERVERS 3

MTS_DISPATCHERS tcp, 3

ARCHIVE_LOG_START TRUE adopts ARCHIVE backup.

The confirmation of these parameters is temporary. No system can determine the parameters correctly before running, and it must be adjusted after the application is running.

Iv. Open

[1] [2] Next page

The ORACLE tutorial is: Modeling and Design of Oracle databases. Software issue

Because the development software of our system uses Borland c ++ builder 5, we use a relatively mature BDE interface, which is provided by Borland and has better performance.

The above is a general idea of Database Modeling and design based on the practice of database management and development over the years. I hope this will help you.

Previous Page [1] [2]