Скачать книгу

technology to use, you may then need to design a schema that will support optimal storage and retrieval.

      

The distinction between relational and NoSQL databases is becoming less pronounced as each type adopts features of the other. Some relational databases support storing and querying JavaScript Object Notation (JSON) structures, similar to the way that document databases do. Similarly, some NoSQL databases now support ACID (atomicity, consistency, isolation, durability) transactions, which are a staple feature of relational databases.

      Relational Database Design

      OLTP

      Online transaction processing (OLTP) databases are designed for transaction processing and typically follow data normalization rules. There are currently 10 recognized forms of normalization, but most transaction processing systems follow no more than three of those forms:

       The first form of normalization requires that each column in the table have an atomic value, no repeating groups, and a primary key, which is one or more ordered columns that uniquely identify a row.

       The second form of normalization includes the first form and creates separate tables for values that apply to multiple rows and links them using foreign keys. A foreign key is one or more ordered columns that correspond to a primary key in another table.

       The third form of normalization, which includes the second form, eliminates any columns from a table that does not depend on the key.

      These rules of normalization are designed to reduce the risk of data anomalies and to avoid the storage of redundant data. Although they serve those purposes well, they can lead to high levels of I/O operations when joining tables or updating a large number of indexes. Using an OLTP data model requires a balance between following the rules of normalization to avoid anomalies and designing for performance.

      Denormalization—that is, intentionally violating one of the rules of normalization—is often used to improve query performance. For example, repeating customer names in both the customer table and an order table could avoid having to join the two tables when printing invoices. By denormalizing, you can reduce the need to join tables since the data that would have been in another table is stored along with other data in the row of one table.

      OLAP

      Online analytical processing (OLAP) data models are often used for data warehouse and data mart applications. OLAP models are also called dimensional models because data is organized around several dimensions. OLAP models are designed to facilitate the following:

       Rolling up and aggregating data

       Drilling down from summary data to detailed data

       Pivoting and looking at data from different dimensions—sometimes called slicing and dicing

      OLAP can be implemented in relational database or in specialized multidimensional data stores.

      SQL Crash Course

      SQL has three types of statements that developers use:

       Data definition language (DDL) statements, which are used to create and modify database schemas

       Data manipulation language (DML) statements, which are used to insert, update, delete, and query data

       Data query language (DQL) statements, which is a single statement: SELECT

DDL statement Example Explanation
CREATE TABLE CREATE TABLE address (address_id INT PRIMARY KEY, street_name VARCHAR(50), city VARCHAR(50), state VARCHAR(2) ); Creates a table with four columns. The first is an integer and the primary key; the other three are variable-length character strings.
CREATE INDEX CREATE INDEX addr_idx ON address(state); Creates an index on the state column of the address table.
ALTER TABLE ALTER TABLE address ADD (zip VARCHAR(9)); Adds a column called zip to the address table. ALTER is also used to modify and drop entities.
DROP INDEX DROP INDEX addr_idx; Deletes the index addr_idx.
DML Statement Example Explanation
INSERT INSERT INTO address VALUES (1234, ’56 Main St’, ’Seattle’, ’WA’); Adds rows to the table with the specified values, which are in column order
UPDATE UPDATE address SET state = ’OR’ Sets the value of the state column to ’OR’ for all rows
DELETE DELETE FROM address WHERE state = ’OR’ Removes all rows that have the value ’OR’ in the state column
Data Query Language
DDL statement Example Explanation
SELECT … FROM SELECT address_id, state FROM address Returns the address_id and state values for all rows in the address table
SELECT … FROM … WHERE SELECT address_id, state FROM address WHERE state = ’OR’ Returns the address_id and state values for all rows in the address table that have

Скачать книгу