Relationship Diagramming

The Entity-Relationship diagram presented in Figure 1 exhibits the respective roles of these tables. The relationship between Source and Environment is called LargeJSession. The relationship between one Source and one Host is called a Tiny JSession. 

A detailed design is required. A Functional Specification should be written covering interfaces, system boarders, algorithms, macros, and data structures. The definition of macros should include a description of all the variables used, template design, VBA-Architecture, and VBA-Codes. The definition of data structures should include Entity Relationship Diagrams and data field descriptions. 

FIGURE 33.2 Entity Relationship Diagram for a Simple Training Database (Crow s Foot Technique). 

Relational database design is certainly unlike other forms of programming, but there are tools available to help with the task. Since the relationship between data elements is a key consideration, the Entity Relationship Diagram (ERD) is an important one of these tools. There are many conventions for documenting ERDs the one depicted below is one of the simpler examples to understand. 

I would like to thank the following people for their contributions to the preparation of this chapter my wife Jeanne for proofreading and suggestions for improving readability Philip Flip Rutledge for help with the finer points of entity relationship diagramming and Guy Wingate for final review and comments. 

Spaccapietra and Parent [1994] work in the context of ERC+ [Spaccapietra et al. 1992], which extends an early version of entity relationship diagrams [Chen 1976]. In ERC+, there are three basic types of objects attributes, entities, and relationships. While there are differences between ERC+ and Chen s original ER diagram, for the purposes of this paper, the primary difference is that ERC+ allows complex attributes (i.e., attributes can have subattributes) (Fig. 8.1). 

Many-to-many relationships are much easier to understand when visualization tools are used. Entity relationship diagrams (ERD) help visualize one-to-one, one-to-many, and many-to-many relationships. 

Figure 2.1 Entity relationship diagram showing the one-to-many relationship between compounds and logP.

Figure 2.2 Entity relationship diagram showing the many-to-many relationship among compounds and vendors.

Figure 3.1 Simple entity relationship diagram for structure table.

Figure 3.2 Entity relationship diagram for structure and hiv prot tables.

Figure 6.1 Entity relationship diagram for a chemical sample tracking schema. 

Figure 6.3 Entity-relationship diagram for pubchem.substance and pubhchem. nci h23 tables.

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. 

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.

When a project involves three-dimensional structures, it often includes multiple conformations for any particular structure. In previous sections of this chapter, an array of three-dimensional coordinates was stored for each structure. When a project needs multiple conformations, another table is needed to accommodate this. Instead of a coords column in a structure table, a conformations table will be used. The unique cid column in the structure table will function as a primary key related to a cid foreign key column in the conformation table. The entity relationship diagram in Figure 11.3 illustrates this. This effectively allows many conformations for any structure. Each one has an energy and comment... 

Figure 13.2 Entity relationship diagram for schema containing tables for a compound registration system.

Figure 13.3 shows an entity relationship diagram for the tables created by the sdf loader script. Once files have been loaded into the database, the dbutils shell script is run to define several utility functions that operate on these tables. The dbutils script is listed in the Appendix. This... 

During the analysis phase of the SDLC, data are represented in the DFDs in the form of data flows and data stores. The data flows and data stores are depicted as DFD components in a processing context that will enable the system to do what is required. To produce a system, however, it is necessary to focus on the data, independent of the processes. Conceptual data modeling is used to examine the data at rest in the DFD to provide more precise knowledge about the data, such as definitions, structure, and relationships within the data. Withoirt this knowledge, httle is actually known about how the system will have to manipulate the data in the processes. Two conceptual data modeling tools are commonly used entity relationship diagrams (ERDs), and data dictionaries (DDs). Each of these tools provides critical information to the development effort. ERDs and DDs also provide important documentation of the system. 

---