WGU D426 V2

The relationship (within the relation) that describes how the value of one attribute may be used to find the value of another attribute.

Functional Dependency

An attribute that can be used to find the value of another attribute in the relation.

Determinant

Value can be used to find the value of every other non-key attribute in the table. Unique and minimal.

Candidate Key

Define & manage database structures

DDL

Adding, deleting, and modifying data - Manipulates data stored in a database.

DML

Used to create user accounts, manage permissions and access.

DCL

Includes CREATE, DROP, ALTER, TRUNCATE, RENAME

DDL

A view created and stored on the database. Underlying data is changed and also updated in this view. Uses memory space.

Materialized View

Not stored in memory. Only computed when used or accessed.

Virtual View

The data about the data. Describes elements such as columns, names, and data type.

Metadata

Ordered collection of elements in parentheses.

Tuple

% of table rows selected by a query

Hit Ration

Reads blocks directly, without accessing an index.

Table Scan

Reads index blocks sequentially in order to locate the needed table blocks.

Index Scan

Database repeatedly splits the index in two until it finds a match.

Binary Search

Database object that maps one or more tables to a single file.

Tablespace

Specifies tables, columns, and keys. Implements database requirements in a specific database system. Also called a schema.

Logical Design

Specifies indexes, table structures, and partitions. Specifies how tables are organized on storage media.

Physical Design

Components of a computer system and the relationships between components.

Architecture

Interact directly with database users and administrators and send queries to the query processor. Includes Connectors & APIs, Workbench, and utility programs.

Tools

Interprets queries. Compiles queries into low-level instructions for the storage engine. Creates a plan to modify the database or retrieve data, and returns query results to the application.

Query Processor

Link between tools and query processor

Connection

Creates connections and manages communication between tools and query parser.

Connection Manager

Translates the query processor instructions into low-level file system commands that modify or retrieve data.

Storage Engine

Accesses data on storage media. Data stored on storage media and organized in files. Contains 3 types of data: - User data (tables & indexes) - Log files (record of any changes) - Data dictionary (aka: catalog directory of tables, columns, keys, indexes in a relational database). One row for each database object.

File System

Stores reusable information in main memory.

Cache Manager

Retains data blocks from the file system for possible reuse.

Buffer Manager

Arranged in hierarchy

Multi-tier architecture

Multi-tier consisting of web browsers and web servers communicating over the internet.

Web architecture

A software layer that emulates a complete, independent computing environment.

Virtual Machine

A database offered as a PaaS cloud service.

Cloud Database

A statement or proposition from which another statement is inferred.

Premise

Multiple processors managed by a single operating system instance.

Shared memory - share the same memory and storage media.

Shared storage - share storage media only.

Shared nothing

Parallel Computer

Group of computers connected by LAN or WAN

Node

Group of nodes connected by a LAN, managed by separate operating system instances. Similar to a parallel computer.

Cluster

Runs on a parallel computer or cluster

Parallel Database

Runs on multiple computers connected by a WAN

Distributed Database

Updates data on multiple nodes of a distributed database. Two-phase commit (nodes receive notice of update, store update in log, send confirmation, then receive commit). Either all nodes or no nodes must be successfully updated.

Distributed Transactions

Updates data on a single node of a distributed database. Databases that use this are called "eventually consistent".

Local Transaction

States that a distributed database cannot be Consistent, Available, and Petition-tolerant simutaneously. Availability refers to individual nodes. Consistent database - confirms to all rules at all times. Network partition - forms when a network error prevents nodes from communicating. Petition-tolerant - database that continues to function when a network partition occurs.

CAP Theorem

Maintains two or more replicas on separate storage devices. Advantages: High availability, fast concurrent reads, local reads. Disadvantages: Slow or inconsistent updates.

Replicated database

Entire catalog resides on a single node

Central Catalog

Copy of catalog resides on each node

Replicated Catalog

Volatile- Used to conduct daily business -Changes in real time -Detailed Designed for specific business function -Concerned primarily with current data

Operational Data

AKA: Reporting Data or Decision Support Data -Enterprise-wide- Used to understand, manage, and plan the business- Summary Updated at fixed intervals and summarized by time period -Combines data from many business functions -Usually contains current and historic data

Analytic Data

Data warehouse designed for specific business area

Data Mart

Analytic database of raw, unprocessed data copied from multiple data sources.

Data Lake

Optimized for analytics

Data Warehouse

Five-step process - ETL: Extract-Transform Load 1) Extract data 2) Cleanse data 3) Integrate data 4) Restructure data 5) Load data - Open-source ETL tools: -PowerCenter -SQL Server Integration Services -Oracle Data Integrator

Data Warehouse Refresh

AKA: Star Schema - Consists of fact and dimension tables. Fact Table - Contains numeric data used to measure business performance.. Primary key is the composite for all foreign keys referencing dimension tables. Dimension Table - Contains textual data that describes the fact data.

Dimensional Design

Sequence of columns in which each column has a one-many relationship to the next column.

Dimension Hierarchy

A database that stores data in main memory, instead of or in addition to storage media. Ex: SQL Server In-Memory OLTP - Oracle Database In-Memory - MySQL

In-Memory Databases

A database that is packaged with a programming language. AKA: In-Process Database (Database Architecture) - SQLite - SQL Server Compact

Embedded Databases

Collection of two or more participating databases under a coordinating software layer called middleware.

Federated Database

Relationships are named and classified by their cardinalities, which means count (as in the number of items in a set). HAS-A relationship. Each of the three types of binary relationships has a different cardinality. Is the maximum number of entity instances that can participate in a relationship instance.

Maximum Cardinality

Minimum number of entity instances that must participate in a relationship instance. These values typically assume a value of zero (optional) or one (mandatory).

Minimum Cardinality

An entity whose instances cannot exist in the database without the existence of an instance of another entity.

Weak Entity

Can exist in the database independently.

Strong Entity

Can be exclusive or inclusive. Can be a part of an IS-A relationship.

Subtype Entities

Depicted with square corners

Tables

Indicate primary key columns

Bullets

Depict foreign keys. Starts at the foreign key and points to the referenced table. RESTRICT, CASCADE, or SET NULL appears on the arrow, on top read left-to-right, on bottom read right-to-left.

Arrows

States that every value of a foreign key must match a value of an existing primary key or be fully NULL.

Referential Integrity

Represents an intersection of the data sets

AND

Represents a union of the data sets

OR

Used to remove unwanted database objects from the database. Will permanently remove the object and all associated data!

DROP

Description of the structure and format of the occurrences of the entity, similar to a recipe or architectural blueprints.

Entity Class

Eliminates redundancy by decomposing into two or more tables. To be a well-formed relation, every determinant must be a candidate key. Any relation that is not well-formed should be broken into two or more well-formed relations. As a general rule, a well-formed relation will not encompass more than one business concept.

Normalization

Putting tables back together into a single table for business needs. Used in place of a join to speed up a query, but it uses more space, data redundant, modification anomalies.

Denormalization

Multiple values removed. Only one value in each cell.

First Normal Form

No partial dependencies. All non-key columns depend on the whole primary key.

Second Normal Form

No transitive dependencies (Functional dependencies on non-primary-key attributes. Called transitive because the primary key is a determinant for another attribute, which in turn is a determinant for a third. Solution: Non-key determinant with transitive dependencies goes into a new table; non-key determinant becomes primary key in the new table and remains as a foreign key in the old table).

Third Normal Form

Every determinate is also a candidate key during normalization. Optimal normal form for frequent inserts, updates, and deletes. One key is a super key. If Column A depends on Column B, then B must be unique.

Boyce-Codd Normal Form

Gets data from the left table that's not in the right table.

Left Outer Join

Gets data from the right table that's not in the left table.

Right Outer Join

Unmatched data from either table is included. Will result in empty fields.

Full Outer Join

Returns only matching values.

Inner Join

Combines two tables without comparing data.

Cross Join

AKA: Clustering Index. Usually sparse. Index on a sort column.

Primary Index

AKA: Nonclustering Index. Index that is not on the sort column. Always dense.

Secondary Index

Entry for every table row

Dense Index

Entry for every table block

Sparse Index

Stores column values and row pointers in hierarchy

Multi-Level Index

All branches are the same length

Balanced Indexes

Index entries assigned to buckets

Hash Index

Block or group of blocks containing rows/index entries.

Bucket

Computes the bucket containing the row from the value of the indexed column.

Hash Function

Grid of bits

Bitmap Index

Stores table rows in each bucket

Hash Table

Single or multi-level index that normally contains pointers to table blocks

Physical Index

Single or multi-level index where pointers to table blocks are replaced with primary key values

Logical Index

Database designer specifies a function on the column value consistent with a WHERE clause. Can be applied to any index type. Considered when the WHERE clause format is different than the storage format.

Function Index

A subset of table data. One table has many that do not overlap and, together, contain all table data. Must appear in all unique columns. May not contain foreign keys and foreign keys may not refer to table.

Partitions

Subset of table rows.

Horizontal Partition

Subset of table columns.

Vertical Partition

Range of partition expression values (VALUES LESS THAN, MAXVALUE)

Range Partition

List of partition expression values using VALUES IN keywords.

List Partition

A partition expression with positive integer values. The partition number for each row is computed as (partition expression value)modulo N.

Hash Partition

Similar to hash but partition expression determined automatically by database.

Key Partition

Candidate Key - Composite Key - Primary Key - Surrogate Key

Unique