Design and Use of Relational Databases in Chemistry

Table of Contents

Introduction

Relational Database Fundamentals

Introduction

Tables, Rows, and Columns

External and Internal Representations of Data

Advantages over Spreadsheets

Relationships among Tables

Entity Relationship Diagrams

Uniqueness

Sequences

Keys

Constraints

Indexes

Joining Tables

Normal Forms

Structured Query Language (SQL)

Introduction

Databases, Schemas, Tables, Rows and Columns

Create

Insert

Select

Update and Delete

SQL Functions

Domains, Triggers, and Views

Unions, Intersections, and Differences

Relational Database Management Systems (RDBMS)

Introduction

Standard SQL

A Sampling of Differences

Server and Client

Compatibility

Client and Web Applications

Introduction

Command Line Programs

Web-Based Applications

Client Applications

SQL Interfaces in Various Languages

Data Storage, Searching, and Manipulation

Introduction

General Schema Design Decisions

Sample Schema for Tracking Chemical Samples

Schemas for PubChem Data

Data Constraints and Data Integrity

Developing Complex SQL

Sub-Select Statements

Views

Computer Representations of Molecular Structures

Introduction

SMILES Representation of Molecular Structure

Extensions to SQL for Chemical Structures

SMARTS Representation of Molecular Searches

SMILES and SMARTS Quirks

SMILES and Inorganic Structures

Other SMILES Extensions

Input and Output of Molecular Structures

Useful SQL Extensions

SMILES as a SQL Data Type

Summary

Molecular Fragments and Fingerprints

Introduction

Fragments

Fingerprints

Similarity Measures

Computing Fragment-Based Properties

Reactions and Transformations

Introduction

Reaction SMILES

Transformations

Canonical Reaction SMILES

PostgreSQL Extensions

Introduction

Composite Data Types

Composite Data Type for Experimental Values

Array Data Types for Two- and Three-Dimensional Coordinates

Functions in Other Languages

Object RDBMS

Three-Dimensional Molecular Structure Tables

Introduction

Using Tables Instead of Files

Molfile and Other Common File Formats

Processing SDF Files

Using Tables Instead of Files in Client Programs

File Import, Export, and Conversions

Functions Using Three-Dimensional Atomic Coordinates

Conformations

Other Representations of Three-Dimensional Molecular Structure

More on Client and Web Interfaces to RDBMS

Introduction

Store All Possible Data in the RDBMS

Advanced SQL Techniques

R Programs

Applications

Introduction

Compound Registration

Experimental Chemical and Biological Data Integration

Data from External Sources

Utilities

Appendix

Introduction

Symbols and Bonds from SMILES

Normalizing Data

SQL Functions

Tables Used in Functions

Core Function Implementation for PostgreSQL

C Language PostgreSQL Functions

Database Utilities Dbutils

Loading Files into Simple Tables

Index

References appear at the end of each chapter.

Read More