What is Oracle standardization?

Oracle standardization is an improvement or extension of the relational model. Oracle standardization is also a process of improvement based on the first draft of the relational model and a specific method.

A column or a group of columns-Y is considered to be related to another column or a group of column X) functions. If X is assigned to a group of given values, the unique value of Y is determined. If Y is related to the X function, X determines Y. Generally, X-> Y is written. Of course, the most obvious example is that a primary key in a relational table can uniquely determine the value of a row in the table. However, other correlation may also exist, but they are not the result of the primary key. The main purpose of standardization is to eliminate all function-related issues caused by not primary keys in the relational model.

The following are three main reasons for Oracle standardization, which are often mentioned in many database analysis and design books:

■ Maintain data integrity. This may be the most important reason. It can fully explain why standardization is required. Because the data is stored only once, it remains correct and consistent. In other words, you do not have to save multiple data backups. Otherwise, various backups of the same data item may not be synchronized and may eventually require heavy application control, because the automatic Integrity Mechanism of RDBMS cannot be adjusted. Many legacy systems are in this status.

■ Create a model that is as unrelated to the application as possible. In other words, standardization only enhances the concept that the relational model should be data-driven rather than Program-driven. For most databases, this means that the database design remains stable and intact when the processing needs are changed. The application requirements should be independent of the database logic.

■ Reduces storage requirements and constantly lays the foundation for improving query performance ). In addition to foreign keys, full standardization eliminates all redundancy in link design. Unnecessary data backup also requires unnecessary secondary storage requirements. In addition, the more data that is stored and may be searched, the more system time required, and the worse the performance.

To better understand Oracle standardization, we need to improve several concepts:

1. First paradigm
The first paradigm (1NF) does not contain repeat groups, which means that the data stored in a unit must be a single, simple value and cannot retain more than one piece of information. For clarity, the information principle does not allow duplicate groups in a column, while 1NF requires that duplicate groups not exist in one row, duplicate information contained in duplicate columns or columns is not allowed. To achieve a higher standardization level, a non-key column is required. In a narrow sense, a non-key column is defined as a column that is not part of a primary key. in a broad sense, a non-key column is defined as a column that is not part of any candidate key. In this case, select a narrow definition. Essentially, a column set in a table can be considered to be composed of a primary key and the remaining items. Any part of the remaining items are non-key columns.

2. Second paradigm
2NF In the second paradigm) is not completely related. Each non-key column depends on the full primary key. If the primary key is a composite key, it includes all its columns.

3. Third paradigm
The third paradigm (3NF) does not transmit correlation. Non-key columns that do not depend on other non-key columns. If the non-key columns used by a table depend on keys and full keys, the table is in the 3NF format. After the repeat group is eliminated, each non-primary key column depends on the key and the full key. This is 2NF and 3NF only depends on the key.

4. BoyceCodd Paradigm (BoyceCoddNormalForm, BCNF)
The BoyceCodd paradigm includes incomplete correlation of non-inversion, which is sometimes not formally called 31/2 NF). The primary key and any part of it do not depend on a non-key attribute. Because what you get is strictly defined as a non-key, 3NF considers the candidate key issue, and your table is always in the BCNF form.

There are also the fourth and more advanced paradigms. Technically, the Oracle Standardization Theory has exceeded the BCNF level. Generally, books related to database analysis and design have reached the 5NF level. 4 NF processes multi-value-related MVPs), while 5 NF processes connection-related JD. Although the theory of these paradigms is a little beyond the scope of this book, you should know that if each of the mivs is an FD, this table is in the 4NF form; if each JD is the result of its link key, the table is in the form of 5NF.

Suggestion: at least work for BCNF, and then get physical database design compensation if necessary, which will guide you into the next topic. If possible, study the fourth and fifth paradigms and try to achieve these levels of standardization through your efforts. Your goal as a database system administrator is to increase the degree of standardization as much as possible, but use as few entities as possible to achieve them. This is a challenge, because the higher the standard form, the more entities are generated.

1. Oracle clever acquisition of related specified records
2. How does OraclePL read BLOB fields from the database?
3. Application of Oracle Mandatory Access Control Model
4. System conflicts in Oracle forced access
5. Performance Optimization Strategies for Oracle databases in the design and development phase