Author: Sky's the key
From: http://blog.9cbs.net/chensheng913/Archive/2004/08/24/83848.aspx
1. Introduction Many people think that object-oriented concepts and relational databases are inconsistent with each other and cannot be combined. In fact, the opposite is true! After flexible use, a relational database can provide an excellent implementation for the object-oriented (OO) model. The same model can be used to develop programming code and relational database structures. Relational database technology is far-reaching, powerful, but it makes you believe more more than many developers. Single table is simple and easy to understand, intuitive. But consisting of hundreds of tables (this is common) application should be quite difficult. This is the usefulness of the OO model. The OO model makes you deeply think about the problem. The OO model provides a problem with a problem of Super Structure (SUPERSTRURE), which can then be implemented with a lower layer of the relational database. This article discusses the relational database technology, rather than concentrating on specific products. We will not discuss physical design details (such as storage allocation and physical aggregation) because they are dependent on the product. Implementing the UML model with relational databases: mapping structure (Section 2) and mapping function (Section 3). Section 4 invisibly expands the expansion of object-oriented to relational databases. Section 5 summarize this article. 2. Structure Map to Table UML Object Model is essentially an extension entity-relational (ER) model. The way to use the ER model of the design database is generally accepted, while we use the UML object model in an approximate but more powerful way. The main advantage of the OO model is the same model of programming and database. Moreover, as a way to consider functionality (Section 3), we emphasize the navigation of the OO model. This section shows how to implement the main configuration of the UML object model. 2.1 Identity The first step in implementing the object model is to process the identity. We start with a few terms. 1) Candidate Key is a combination of one or more attributes that uniquely determines a record in a table. The attribute set in a candidate key must be minimized; unless the uniqueness is destroyed, the attribute cannot be deleted from the candidate button. The attribute in the candidate button cannot be empty. 2) Primary Key is a particularly selected candidate key to preferentially refer to the record. 3) Foreign Key is a reference for a candidate key. The foreign key must include a value of each element property, or it must be empty. Foreign key used to achieve associations and generalization. You should define a primary key for each table, although there is an exception. We strongly recommend that all foreign keys only points to primary keys instead of other candidates. Define the primary key has two basic methods: 1) based on the existing identifier. You should add an object identifier property for each class table and set it to the primary key. The primary key of each associated table includes one or more related classes identifiers. The existing identifier has the advantage of individual attributes, and the placeholder is small and the size is the same. As long as your relational database management system (RDBMS) is supported, there is no performance disadvantage based on the existing identifier. (Most RDBMS provides a valid allocation order number based on the presence of the identifier.) The only disadvantage is based on the existence of the identifier is not inherently meaningful during maintenance. 2) The value based on the value. A combination of some real-world attributes determines each object. Value-based identification has different advantages. The primary key is inherently meaningful for the user, it is easy to debug and database maintenance. On the other side, the value-based primary key is difficult to change. A change in primary key needs to spread to many foreign keys. Some objects do not have an identifier in the real world. We recommend that you use the existing identifier in the RDBMS app over 30 classes. The existence and value-based identity is a feasible option for all RDBMS applications.
2.2 Domain (Property Type) Attribute Type is the term in UML and corresponds to the domain of the domain in the database. Compared to direct data types, the domain is upgraded to a more consistent design and facilitate the location of the application. The simple domain is easy to implement. You just define the corresponding data type and size. And each of the properties of the domain, you must add a SQL query clause for each domain constraint. Some examples of simple domains are: Name, long character (longstring) and phone number (Phone-number). A enumeration domain limits a property in a range of values. The enumeration domain is more complex than the simple domain, and the chart 1 shows four ways. Chart 1: Implementation method
2.3 Under normal circumstances, we map each class into a table, each attribute map to a column. You may require some additional columns due to a generated identifier (based on the existing identifier), hidden association (Section 2.5) and General Declare (Section 2.5). 2.4 Association Now we discuss the implementation of the association. We have divided our statements into proposed mappings (we used mapping), optional mapping (we can read mapping) and disseminative mappings (we have to avoid errors). All of our examples use the existing identifier. 2.4.1 Recommended mapping is multi-related. With a special table (Chart 2), a multi-to-many association is implemented. The associated primary key is the merger of the primary key of each class. Those omitted numbers (...) indicate that the properties that are not displayed in the model. The primary key is displayed in black body. A pair of associations. Hide a foreign key in "Multi" table (Figure 3). The role name becomes part of the foreign key attribute name. Zero or one-on-one association. Hide the foreign key in the "zero or one" table (Figure 4). Other one-on-one association. Hide foreign keys in either table.
Chart 2: Recommended Implementation: Special Multi-to-Multi-related Table
Chart 3: Recommended Implementation: Hidden Correlation
Figure 4: Recommended implementation: hidden zero or one-on-one
Optional mapping is normal to use suggested mappings. But some occasionally, optional mapping is more appropriate. Special table. You can also achieve a pair of and one-on-one association with a special table (Figure 5). The special form gives you more unified design and greater scalability. In any case, special association table breaks the database and adds the number of tables. In addition, special associative tables cannot force a lower multiplex limits to "one".
Chart 5: Optional Implementation: Special Pair of X Correlation Table
Unscent mappings We have noticed some developers to choose a defective mapping. We must pay attention to these mappings to avoid it. merge. Do not merge multiple classes, do not force the association into a separate table (Chart 6). This reduces the number of tables, but interferes with the third paradigm. Two hidden associations. Don't hide twice a one-on-one association, each hide in a class (Figure 7). This is redundant and helpless. The same properties. Do not use the same properties to implement multiple associated roles (Chart 8). The same properties make programming and reduce scalability. Ultrafining now we discuss generalization. We only discuss a single inheritance here. The simplest method of the recommended mapping is to map the superclass and each subclass as a table. All tables share a common primary key. The application must perform subclasses, because RDBMS support. (For detailed descriptions of the latter, see Section 4.) Special Table. Mapping superclass and each subclass as a table (Chart 6). All tables share a common primary key. The discriminator indicates the appropriate superclass table for each subclass record. Chart 6: Recommended Implementation: Separated superclass and subclass table
An optional mapping is generally available in an optional mapping. eliminate. You can optimize classes that don't have other attributes except for primary keys (Figure 7). This reduces the number of tables, but provides fewer regular implementations. Reduce superclass properties. You can remove the superclass table and copy the superclass attribute to each subclass (chart 8). This will have the advantage of describing each object as a table. In any case, it will cause redundancy of the database structure, and you may need to search for multiple subcatetles when you find an object. Add subclass properties. As a third option, you can remove the subclass table and store all subclass properties into the superclass table (Figure 9). This describes each object in a table, but interferes with the second paradigm.
Chart 7: Optional Implementation: Eliminating Unnewable Subclass
Chart 8: Optional Implementation: Reduce Super Class Properties
Figure 9: Optional Implementation: Increase Subclass Properties
Reference Integrity Once you have established a table, you should define the meaning of the reference integrity action to clarify the meaning of the object model. (Do not use the SQL trigger to implement reference integrity!) If you use the existing identifier, you will not need to propagate the results of the update. We recommend the following reference integrity principles: generalization. Classification from the deletion of foreign keys generated in the generalization implementation. Hidden associations minimizing diversity is zero. Set the foreign key to empty, but sometimes you may have to delete it. Hidden associations, minimizing diversity is empty. You can level a deleted result or forbidden to delete it. Association Table. The deletion of the record in the form of a single level in our class. However, sometimes we prohibit one delete. We have just briefly and reference integrity because it is a high-level topic. References have more explanations Z and examples. The final step of the index implementation of the database structure is to join the index to adjust the database performance. Normally, you should define a unique index for each primary key and a candidate button. (Most RDBMs as the side effects of the SQL primary key and the candidate key constraints to establish a unique index.) You should also establish an index for each foreign key constrained by the primary or candidate. We emphasize the importance of indexes. The index of foreign keys and primary keys makes it possible to quickly traverse in the object model. You must include these indexes that you will make the user feel discouraged. You should join an index in your database start design phase, as they are easy to join and have no good reason to postpone addition. Database Administrators (DBA) may define additional indexes for a query that is requested. DBA may also use product-related adjustment mechanisms. Paradigm Paradigm is an effective method for improving data consistency in relational database design. Our book 3 discusses the paradigm, but our question is slight about this problem. We will use this article opportunities to clarify our point of view. If you are not familiar with paradigm, you can skip this section. Our description is for relational designers, they are trying to adapt to their original skills with object-oriented. Paradigm is the accurate principle of correctly designed relational databases. Similarly, they are independent of using what development techniques - based on attribute, based on entity design, object-oriented design or what else. In the past, the use of attribute design-based methods, developers had to pay very attention to paradigms; paradigm provides a basis for grouping data. Conversely, paradigm is not important for object-oriented (or physical) development. If you use an OO method and your model has been well thoughtful, then you are making a meaningful unit, which is essentially a paradigm. If you like, you can still check the paradigm, but this check is unnecessary. Abstract Chart 10 summarizes the mapping rules we have already stated. The complete example of these mapping rules, including a UML object model that can be found in this complete extension (Adobe Acrobat PDF file). Chart 10: Summary of recommended mapping rules
Provide three main purposes to the SQL command object model for database applications. structure. Object model indicates database structure. We have explored this aspect in the second section. Constraint. The object model also indicates an important constraint that can be stored. The matching implementation must work hard to cater to these constraints. The processing method of our mapping rules and the reference integrity in the second section pointed out many constraints. (An additional UML structure that is not argued herein can get more constraints.) Potential estimate. An object model indicates potential estimation; it is about which inquiry and how to formulate a blueprint. The third section will briefly clarify the third purpose. The object model is not just a passive data structure, but instead they can help you think about the functionality of an app. You can speak many of its functions based on the traversal object model. For example, we think about our traversal when we check the use case for a model. This emphasizes the estimation of object models is particularly important for RDBMS applications because traversal expressions can be mapped directly to SQL code. The Object Constraint Language (OCL) helps express traversal. Point symbol navigation from objects to objects and objects to properties. Square brackets represent filters for object set. We join the colon (:) operator to represent the spread of generalization; because we use multiple tables to achieve a generalized inheritance, clear traversal is useful. The traversal expression example in Chart 11 is based on the UML object model we created (see this article (Adobe Acrobat PDF file), and we map them to SQL code. We start SQL programming variables with colons. Chart 11: Object model traversal and SQL code example
The OO extended database community to RDBMS is interested in the OO expansion of RDBMS. Products and SQL standards are trying to join the OO expansion. We will briefly present the direction of this technology. Abstract Data Type (ADT). This is a good idea to extend the ability of RDBMS. Developers use many names for this technology, such as Oracle Cartridge and Informix Data Blades. The disadvantage of ADT is that they tighten you to a specific developer; ADT's category exceeds SQL standards. Therefore, you should only use it when the benefits of ADT are obvious. There are some chaos in how ADT is suitable for database development. If you use the OMT development process, you can use the properties to implement the simple domain, and use ADT to implement complex governments. You should still implement classes using a table. SQL3 pointer. The latest SQL standard version, SQL3, joined a pointer symbol as a data type. Obviously, its intention is to support navigation and object-oriented. Our reviews for SQL3 pointers is that they grafted, it can be ignored. More in-depth criticism is that the pointer is theoretically ridiculous, increasing complexity and does not expand SQL expression. CJ Date discussed this in the last September target / relational meeting. Ok, then we are coldly treating the abstract data type and the accusation of the SQL pointer. But we prefer object-oriented technology and relational databases. There are two extensions for RDBMS to make them easier for OO technology. We will be happy to see that RDBMS developers add these capabilities into their products. The extended reference integrity action supports generalization. The current reference integrity mechanism is one-way. In order to fully support generalization, we need a two-way mechanism. In this way, a superclass record can rely on a subclass record. Also, a subclass record can rely on a super-class record. We can best explain this by example. Figure 12 extracted in our financial case study in 3. We use asset superclass to unify some of the universal data and functions that do not display in the excerpt. An asset can be a stock or stock privilege. A stock can have many stock privileges. For example, IBM stocks can have many places or privileges that reach the price and expiration date.
Figure 12: Examples of reference integrity and generalization
