Relational Database Fundamentals - Design and Use of Relational Databases in Chemistry - page 13

epa.compound

epa.logP

name TEXT

TEXT
NUMERIC

temp

NUMERIC

formula
MW

FK cid * INTEGER

NUMERIC

logP

PK cid * INTEGER

method TEXT

MP NUMERIC

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

vendor
name

vendor_compound

TEXT

PF vid * INTEGER

PK vid *

INTEGER

PF cid * INTEGER

location
phone

TEXT
TEXT

compound

name TEXT

PK cid * INTEGER

cas TEXT

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

symbol after the cid data type means that these values cannot be null,
since they act as primary and foreign keys. Other ERD diagram software
may indicate keys and not-null values using other symbols, although
the crow's feet symbol is a standard way of representing relationships
between tables in most ERD software.
Many-to-many relationships are regularly encountered in chemical
databases. In the case of many compound vendors and many compounds,
the ERD in Figure 2.2 shows how three tables can be used to define a
many-to-many relationship. The many-to-many type of relationship will
be seen in other examples in later chapters. In Figure 2.2, the vendor table
contains only as many rows as there are vendors. The compound table
contains only as many rows as there are compounds. Each row in these
tables contains the information available for each compound or vendor.
No information is repeated in other rows or tables. The vendor_compound
table contains many more rows, one row for each compound offered by
each vendor. This table is the largest in this schema, yet it contains only
integers, which are easily indexed and efficiently stored.