Database key terms

Class diagram

A graph of classes connected through relationships. It is designed to show the static structure of the model. Similar to the entity-relationship diagram.

Composite key

A primary key that consists of more than one column. Indicates a many-to-many relationship between the columns.

Concurrent access

Performing two (or more) operations on the same data at the same time. The DBMS must sequence the operations so that some of the changes are not lost.

Deletion anomaly

Problems that arise when you delete data from a table that is not in third normal form. For example, if all customer data is stored with each order, when you delete an order, you could lose all associated customer data.

canonical

(adj) following or in agreement with accepted, traditional standards

Two types of optimization

1. logical optimization—which generates a sequence of relational algebra to solve the query 2. physical optimization—which is used to determine the means of carrying out each operation.

Database

A collection of data stored in a standardized format, designed to be shared by multiple users. A collection of tables for a particular business situation.

Certificate authority

A company that ensures the validity of public keys and the applicant's identity for public-key encryption systems.

Data repository

A complete listing of all terms used in a database design, including column names and tables

Class

A descriptor for a set of objects with similar structure, behavior, and relationships. That is, a class is the model description of the business entity. A business model might have an Employee class, where one specific employee is an object in that class.

Attribute

A feature or characteristic of an entity. An attribute will become a column in a data table. Employee attributes might include name, address, date hired, and phone.

hashed file

A file organization in which the address for each row is determined using an algorithm. It is most useful you frequently access individual rows in a random order and infrequently update value of a hash key.

what is a query?

A query is a request for information from a database. It can be as simple as "finding the address of a person with SS# 123-45-6789," or more complex like "finding the average salary of all the employed married men in California between the ages 30 to 39, that earn less than their wives."

A query plan

A query plan (or query execution plan) is an ordered set of steps used to access data in a SQL relational database management system

natural join

A relational operation that links tables by selecting only the rows with common values in their common attribute(s).

Composition association

A relationship in which an object is composed of a collection of other objects. For example, a bicycle is built from components. In UML, it is indicated with a small filled diamond on the association end.

Data definition language (DDL)

A set of commands that are used to define data, such as CREATE TABLE. Graphical interfaces are often easier to use, but the data definition commands are useful for creating new tables with a program.

Data manipulation language (DML)

A set of commands used to alter the data.

Data warehouse

A specialized database that is optimized for management queries. Data is extracted from online transaction processing systems. The data is cleaned and optimized for searching and analysis. Generally supported by parallel processing and RAID storage.

Base table

A table that contains data about a single basic entity. It generally contains no foreign keys, so data can be entered into this table without reference to other tables. For example, Customer would be a base table; Order would not.

referential integrity

A value for a foreign key in a child table must be either null, or MATCH EXACTLY a value for a primary key in the parent table.

B+tree

An indexed data storage method that is efficient for a wide range of data access tasks. Tree searches provide a consistent level of performance that is not affected by the size of the database.

Computer-aided software engineering (CASE)

Computer programs that are designed to support the analysis and development of computer systems. They make it easier to create, store, and share diagrams and data definitions. Some versions can analyze existing code and generate new code.

entity integrity

Ensures that a row or tuple is uniquely identified by a primary key.

Hash, Nested loops and Merge

Hash, Nested loops and Merge are all different types of joins.

Data dictionary

Holds the definitions of all of the data tables and describes the type of data that is being stored.

Back end

In a client-server system, the back end usually consists of a central database. In general, hardware and data placed at the back end is designed to be centralized and shared.

Data replication

In a distributed system, placing duplicate copies of data on several servers to reduce overall transmission time and costs.

SQL query hints

In database query operations, various SQL implementations use hints as additions to the SQL standard that instruct the database engine on how to execute the query. For example, a hint may tell the engine to use as little memory as possible (even if the query will run slowly), or to use or not to use an index (even if the query optimizer would decide otherwise).

database index

Is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and less storage space. Indices can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

Data integrity

Keeping accurate data, which means few errors and means that the data reflects the true state of the business. A DBMS enables you to specify constraints or rules that help maintain integrity, such as prices must always be greater than 0.

Cascading triggers

Multiple events that arise when a change that fires a trigger on one table causes a change in a second table, that triggers a change in a third table and so on.

heap file

Rows appended to end of file as they are inserted, deleted rows create gaps in file. Organization efficient if query returns all rows and order of access is not important.

Data independence

Separates the data from the programs, which often enables the data definition to be changed without altering the program.

Database management system (DBMS)

Software that defines a database, stores the data, supports a query language, produces reports, and creates data entry screens.

query optimization

Take queries from users and restructure them to minimize response times. The needed data for a query can be collected from a database by accessing it in different ways, through different data-structures, and in different orders. The purpose of query optimization, which is an automated process, is to find the way to process a given query in minimum time.

Data volume

The estimated size of the database. Computed for each table by multiplying the estimated number of rows times the average data length of each row.

Database engine

The heart of the DBMS. It is responsible for storing, retrieving, and updating the data.

Data normalization

The process of creating a well-behaved set of tables to efficiently store data, minimize redundancy, and ensure data integrity.

Database administration

The technical aspects of creating and running the database. The basic tasks are performance monitoring, backup and recovery, and assigning and controlling security.

Domain-key normal form (DKNF)

The ultimate goal in designing a database. Each table represents one topic, and all of the business rules are expressed in terms of domain constraints and key relationships. That is, all of the business rules are explicitly described by the table rules.

Data hierarchy

structured ordering of data where higher levels contain aggregates of the lower levels. Some common hierarchies include dates (year, quarter, month, day), and geography (country, state, city).

Cascading delete

deleting a record in the first table will cause all matching records in the second table to also be deleted. For example, if you delete Customer 1173, all orders placed by that customer are also deleted.

Cost-based query optimizers

evaluate the resource footprint of various query plans and use this as the basis for plan selection. These assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the run-time cost of evaluating the query, in terms of the number of I/O operations required, CPU path length, amount of disk buffer space, disk storage service time, and interconnect usage between units of parallelism, and other factors determined from the data dictionary.

Index range scan

means that index will be used, however the filters provided in the WHERE clause may not be 'complete'.

Table access full

means that the complete table needs to be searched in order to find the information

Index full scan

means the entire index will be searched.

Table access by index rowid

means the rows are being located using an index.

index scan

search is directed from an index structure

sequential scan

serial scans