Foreign Keys

In the company Sales system (see Figure 10.26), the link from Customer to Account is not held in one component. The Accounts system doesn t have a notion of Customer. But we can decide to use reference fields to cross-link them as foreign keys. Within each component, the CID (customer identifier) attributes uniquely identify one Person, Customer, and Account. The CID type itself is understood by all the components (and may be just a... 

Single-valued object attributes are stored as a foreign key referring to another table. 

Multivalued associations cannot be stored directly. Instead, they are stored as an inverse foreign key, and a join is performed to traverse the association. 

The contribution of this paper is the definition of four different kinds of components for distributed event collection, according to the way they interact with the database and with the outside world. It also generically defines three types of information that are handled by the application. This structure is very important in terms of security, because it allows a strong separation of privileges at the table and column level (for databases that support it), between insertions, modifications, access and deletion. We are also able to effectively leverage the consistency mechanisms of SQL (foreign keys and references) to ensure that event information remains consistent over time. 

Figure 7.47 Polished and etched section, showing fracture profile (F), weld overlay (W), foreign key material (K) and original shaft material (S)...

All Primary Keys Foreign Keys Relationships are Preserved... 

Key Field. Field in a table that uniquely identifies rows in the table (primary key) or contributes to uniquely identifying the rows (secondary or composite key), or that connects the given row to data in another table (foreign key). Key fields are usually indexed for rapid lookup and retrieval. 

Fig. 5.2 An abstract specification of the mapping as a set of correspondences (dashed arrows denote foreign-key constraints)...

Target tgds, i.e., tgds the only use target symbols these are typically used to specify foreign-key constraints on the target ... 

The target tgd encode the foreign key on the target. The target egd simply states that symbol is key for Company. 

Consider the example of Fig. 5.4a, which is a variation of the example presented previously. Here, the first source consists of only the three relational tables Public-Company, Public-Grant, and Contact, while the target consists of only the table Company. As before, the intra-schema lines represent schema constraints, and in the particular example are foreign key constraints. The red dotted inter-schema lines represent the correspondences. Note that the appearance of an attribute with the same or similar name in both schemas does not necessarily mean that the two attributes represent the same fact. For instance, consider the attributes symbol and id. Although in the companies world these terms may be used interchangingly, in the specific example, the lack of a line among them may be justified by a case in which the attribute id may represent the fiscal number of the company while the attribute symbol may be the symbol with which the company appears in the stock exchange. 

Further chase steps on the association using the foreign key constraints on the attributes manager and assistant will further expand the association into the form ... 

Bonifati A, Chang EQ, Ho T, Lakshmanan L, Pottinger R, Chung Y (2010) Schema mapping and query translation in heterogeneous P2P XML databases. VLDB J 19(2) 231-256 Cabibbo L (2009) On keys, foreign keys and nullable attributes in relational mapping systems. In EDBT. ACM, NY, pp 263-274... 

On the other hand, there is little support to propagate schema changes to dependent schema objects, such as views, foreign keys, and indexes. When one alters a table, either the dependent objects must themselves be manually altered in some way, or the alteration must be aborted. The latter approach takes the majority of the time. For instance, SQL Server aborts any attempt to alter a column if it is part of any index, unless the alteration is within strict limits - namely, the alteration is a widening of a text or binary column. Dropped columns simply cannot participate in any index. DB2 has similar restrictions Oracle invalidates dependent objects like views so that they must be revalidated on next use and fails to execute them if they do not compile against the new schema version. 

To add a new relational table with the primary key of the hierarchy and the new attributes of the type as column, plus a foreign key to the parent type s table (the table-per-type mapping strategy). 

The BioMart data model is a simple modular schema composed of a central table, linked to its satellite tables by primary/foreign key relations. The schema can be normalized but typically includes denormalizations to achieve maximum query response optimizations. All the BioMart metadata is stored in XML configuration files on the database servers. The metadata files can be readily created and modified using MartEditor, a Java-based configuration editor. 

This kind of relationship between two tables is called a one-to-many relationship because for any one compound in the epa.compound table, there could be many rows in the logP table, related by the cid column. Once this type of relationship is established between two tables, it easily accommodates other tables of data. For example, if several molecular weight values for each compound were to become necessary, a new table for molecular weight could be created that would use the same compound id to relate to the compound table. The column used to relate two tables is called the key column. In this example, it would be called a primary key in the epa.compound table and a foreign key in the epa.logP table. Keys are discussed further in a later section of this chapter. 

One very important use of the index is to speed up access to key columns. It is recommended to create an index on the primary and foreign key columns. This is important because it speeds up the methods used to relate the rows between the tables. This method is called joining the tables. 

Efficiently joining tables requires careful consideration of primary key and foreign key columns, uniqueness, and indexing. While it is not necessary to use a primary key and its related foreign key column when joining tables, that is a very common, useful, and efficient way to join tables. 

Of course, it will be important to store the chemical structure itself. Clearly, a complete chemical structure cannot be represented using basic SQL data types, such as numeric or text. While a compound name (text) might be considered a good representation of molecular structure, there are better ways to represent molecular structure. In this chapter, a compound will simply be identified with a unique compound id serving as a foreign key to a more complete representation of structure in another table. The following chapters will show ways that chemical structures can be fully integrated into the tables of a database. This chapter concentrates on the proper use of chemical data and ways in which multiple relational tables can be used. 

Figure 6.3 shows the relationship between the pubhcem.nci h23 and pubchem.substance tables in the form of an entity-relationship diagram (ERD). The primary key substance.substance id and the foreign key nci h23.sid are indicated and imply their use in an On clause when these two tables are joined. 

There are several approaches to creating the relation between compound and substance. One is to create an integer column, say, pubchem. substance.cid that would contain only the primary compound id from the column cid associations. This column becomes a foreign key related to the pubchem. compound. c id column. This would form a proper relation between the tables, but would neglect the secondary cid associations. If those are of no interest, this approach is an excellent choice. 

Another approach is to create multiple columns one for the primary compound id and others for the secondary, tertiary, etc. compound ids. Each of these integer columns could serve as a foreign key and form a proper relation to the pubchem.compound.cid column. This approach is not recommended because the maximum number of compound ids in the cid associations column is not known and could increase as more data is added. In addition, the type of association, primary, secondary, etc. would have to be neglected, stored in another column, or somehow encoded in the new column names. This approach has too many drawbacks to be acceptable. 

Another commonly used constraint is the uniqueness constraint. In previous examples, the column compound id was defined to be a unique integer. When the uniqueness constraint is used in a table holding a collection of compounds, it ensures that there can never be more than one compound with a particular compound id. This is essential if other data about a compound are stored in other tables that use compound id as a foreign key. Notice that this does not prevent two identical compounds... 

Figure 6.4 Entity-relationship diagram for all three sets of PubChem data, showing primary and foreign keys relating compounds, substances, and biological assay data.

---