Physical Database Design, Analytics, & Infrastructure

Catalog

A set of schemas that constitute the description of a database

Schema

The structure that contains descriptions of objects created by a user (base tables, views, constraints)

Data definition language (DDL)

Commands that define a database, including creating, altering, and dropping tables and establishing constraints

Data manipulation language (DML)

Commands that maintain and query a database

Data control language (DCL)

Commands that control a database, including administering privileges and committing data

SELECT statement

used for queries on single/multiple tables

SELECT

List the columns (and expressions) to be returned from the query

FROM

Indicate the table(s)/view(s) from which data will be obtained

WHERE

Indicate the conditions under which a row will be included in the result

GROUP BY

Indicate categorization of results

HAVING

Indicate the conditions under which a row will be included

ORDER BY

Sort the results according to a specified criteriasuch as one or more columns in ascending or descending order.

___,___, and ____ operators for customizing conditions in WHERE clause

AND, OR, NOT
(add () to override normal conditions)

Scalor aggregate

Single value returned from SQL query with aggregate function

Vector aggregate

Multiple values returned from SQL query with aggregate function (via Groupby)

Data administration

A high level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards

Database administration

A technical function that is responsible for physical database design and for dealing with technical issues

File organization

A technique for physically arranging the records of a file on secondary storage devices

Heap

No particular order (fast write, not optimized for read)

Sequential

Stored sorted sequentially by primary key
-compromise ‘write’ efficiency for faster ‘read’

Write

Data is inserted at a position to keep the data sorted

Read

Binary search algorithm - compare list to midpoints

Indexed

Stored sorted by primary/secondary data
Storage of records sequentially/nonsequentially with an index that allows software to locate individual records

Index

A table/other data structure used to determine in a file the location of records that satisfy some conditions 

-primary keys are automatically indexed and called unique index

-other fields/combinations of fields can also be indexed; these are called secondary keys (nonunique)

Unique (primary) index

Typically done for primary keys, but could also apply to other unique fields

Nonunique (secondary) index

Done for fields that are often on other non-unique fieldsto optimize search and retrieval of records with duplicate values.

When to use indexes

-On larger tables

-When there are >100 values but not <30 values

-Index search fields (fields frequently in WHERE clause)

-Fields in SQL ORDER BY and GROUP BY commands, Avoid use of indexes for fields with long values

-DBMS may have limit on number of indexes per table and number of bytes per indexed field(s)

-Be careful of indexing attributes with null values; many DBMS will not recognize null values in an index search

-Understand that indexes slow down the WRITE operation while speeding up the READ operation

Database Security

Protection of the data against accidental/intentional loss, destruction, or misuse

Sarbanes-Oxley (SOX)

Requires companies to audit the access to sensitive data, designed to ensure integrity of public companies financial statements

Three areas of SOX audits

IT change management, logical access to data, IT operations

IT change management

Process by which changes to operational systems and databases are authorized

Logical access to data

Security procedures to prevent unauthorized access, personnel controls, and physical access cards

IT operations

Policies and procedures for day-to-day management of infrastructure, applications, and databases

Physical access controls

Swipe cards, equipment locking, check-out procedures, screen placement, laptop protection

Personnel controls

Hiring policies, employee monitoring, security training, segregation of duties (SOD)

Internet security

Firewall (IP whitelisting), intrusion detention systems (IDS), encryption-decryption

Authentication

Ensuring that a user is who they claim to be

Authorization

Rules to specify who has what access rights to what data elements

Authentication schemas

Single factor, two factor, three factor

Single factor

Something you know (password)

Two factor

Something you know plus something you have

Three factor

something you know plus something you have plus something you are (biometric identifier)

Authorization matrix for

Subjects, objects, actions, constraints permissions within a system.

Basic facilities for backup and recovery

Backup, journalizing, checkpoint, and recovery managerfeatures that ensure data is protected and can be restored in case of loss or corruption.

Back-up facilities

DBMS cop utility that produces periodic backup copy of the entire database/subset

Cold backup

Database is shutdown during backup, usually full

Hot backup

Selected portion is shut down and backed up at a given timewhile the rest of the database remains operational, allowing continuous access to the data.

Journalizing facilities

Audit trail of transactions and database updates

Transaction log

Record of essential data for each transaction process against the database

Database change loss

Images of updated data

Before-image-copy before modification

After-image-copy after modificationto prevent data loss during changes. It ensures that updates can be reverted if necessary.

Checkpoint facilities

DBMS periodically refuses to accept new transaction and goes momentarily into a quiet state

Database and transaction logs are synchronized

A DBMS may perform checkpoints automatically (preferred) or in response to commands in user application programs

If the checkpoint fails, it allows recovery manager to resume processing from short period, instead of repeating entire day

Recovery manager

DBMS module that restores the database to a correct condition when a failure occurs and then resumes processing user request

Disk mirroring

Switch between identical copies of database needs, two copies maintained, expensive fault tolerant system

Restore/rerun

Restore and reprocess transactions against the backup manually (only done as last resort)

Backward recovery

Roll back

Forward recovery

Roll forward

Disaster recovery plan

Develop a detailed written disaster recovery plan and test this regularly

Choose and train a multidisciplinary team to carry out the plan

Establish a backup data center at an off-site location, located a sufficient distance from the primary site

Send backup copies of databases to the backup data center on a scheduled basisand ensure that all team members are familiar with their responsibilities during an emergency.

Cloud computing

Provisioning/acquiring computing services on demand using a centralized resource accessed through public internet/private networks