Overview of Relational Databases and Their Design

Database management system (DBMS)

the program that manages and controls the data and the interfaces between the data and the application programs that use the data stored in the database

Database system

the database, the DBMS, and the application programs that access the database through the DBMS

Database administrator (DBA)

the person responsible for coordinating, controlling, and managing the database

Online transaction processing database (OLTP)

database containing detailed current transaction data, usually in the third normal form. Focuses on throughput, speed, availability, concurrency by hundreds of users

Data warehouse

very large databases containing detailed and summarized data for a number of years used for analysis rather than transaction processing

Business intelligence

analyzing large amounts of data for strategic decision making. There are two main business intelligence techniques: online analytical processing (OLAP) and data mining

Online analytical processing (OLAP)

using queries to investigate hypothesized relationships among data; one of two main techniques used in business intelligence

Data mining

using sophisticated statistical analysis to "discover" unhypothesized relationships in the data

Database

Efficiently and centrally coordinates information for a related group of files

File

a related group of records

Record

a related group of fields

Field

a specific attribute of interest for the entity

Record layout

document that shows the items stored in a file, including the order and length of the data fields and the type of data stored

Advantages of Databases

Data is integrated, Data sharing, Minimize data redundancy and inconsistencies, Data is independent of the programs that use the data, Data is easily accessed for reporting and cross functional analysis

Logical view

how users conceptually organize the relationships among data items. This is the view at an external level

Physical view

how data are physically arranged and stored. This is the view at an internal level

Schema

a description of the data elements in a database, the relationships among them, and the logical model used to organize and describe the data

Conceptual level

organization wide view of entire database. Lists all elements (entities) and relationships among them

External level

individual users view (subschema)

Subschema

a subset of the schema; the way the user defines the data and the data relationship

Internal level

low level view describing record layouts, definitions, addresses, and indexes

Database design

To design a database, you need to have a conceptual view of the entire database.

Data dictionary

A blueprint of the structure of the database and includes data elements, field types, programs that use the data element, outputs, and so on.

Data Definitions Language (DDL)

Primarily used by Data Base Administrator (DBA) to build the data dictionary, create the database, describe logical views for each user, and specify record or field security constraints.

Structured Query Language (SQL)

A standardized commercial programming language designed for managing data in relational database systems, with variations existing among different database systems.

Data Manipulation Language (DML)

Primarily used by Programmers to change the content in the database, including creating, updating, insertions, and deletions.

Data Query Language (DQL)

Used by report writers and power users to retrieve, sort, and display specific data from the database.

Relational Data model

Represents conceptual and external level schemas as if data are stored in two-dimensional tables.

Relational database

Implements the relational data model and is comprised of tables and relationships between them.

Update anomaly

Occurs when data in one location is updated, but not in other locations.

Insert anomaly

The inability to add records to a database.

Delete anomaly

The loss of all information about an entity when a row is deleted.

Primary key

A database attribute, or combination of attributes, that uniquely identifies each row in a table; used to distinguish, order, and reference records in a database.

Foreign key

An attribute in a table that is also a primary key in another table; used to link the two tables.

Relational Database Design Rules

Rules that include every column in a row must be single valued, primary key cannot be null, and if a foreign key is not null, it must correspond to a primary key in another table.

Entity integrity

The rule that a primary key cannot be null (empty).

Referential integrity

The rule that if a foreign key is not null, it must have a value that corresponds to the value of a primary key in another table.

Database normalization

The process of structuring a relational database in accordance with a series of so-called 'normal forms' to reduce data redundancy and improve data integrity.

Queries

Specific requests made by users to find information in a relational database without sorting through all the files.