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

3 multiple choice options

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

Determinant

3 multiple choice options

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

Candidate Key

3 multiple choice options

Define & manage database structures

DDL

2 multiple choice options

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

DML

2 multiple choice options

Used to create user accounts, manage permissions and access.

DCL

2 multiple choice options

Includes CREATE, DROP, ALTER, TRUNCATE, RENAME

DDL

2 multiple choice options

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

Materialized View

2 multiple choice options

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

Virtual View

2 multiple choice options

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

Metadata

3 multiple choice options

Ordered collection of elements in parentheses.

Tuple

3 multiple choice options

% of table rows selected by a query

Hit Ration

3 multiple choice options

Reads blocks directly, without accessing an index.

Table Scan

3 multiple choice options

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

Index Scan

2 multiple choice options

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

Binary Search

3 multiple choice options

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

Tablespace

3 multiple choice options

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

Logical Design

3 multiple choice options

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

Physical Design

3 multiple choice options

Components of a computer system and the relationships between components.

Architecture

3 multiple choice options

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

Tools

3 multiple choice options

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

3 multiple choice options

Link between tools and query processor

Connection

3 multiple choice options

Creates connections and manages communication between tools and query parser.

Connection Manager

3 multiple choice options

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

Storage Engine

3 multiple choice options

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

2 multiple choice options

Stores reusable information in main memory.

Cache Manager

3 multiple choice options

Retains data blocks from the file system for possible reuse.

Buffer Manager

3 multiple choice options

Arranged in hierarchy

Multi-tier architecture

3 multiple choice options

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

Web architecture

3 multiple choice options

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

Virtual Machine

3 multiple choice options

A database offered as a PaaS cloud service.

Cloud Database

3 multiple choice options

A statement or proposition from which another statement is inferred.

Premise

3 multiple choice options

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

3 multiple choice options

Group of computers connected by LAN or WAN

Node

3 multiple choice options

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

Cluster

3 multiple choice options

Runs on a parallel computer or cluster

Parallel Database

3 multiple choice options

Runs on multiple computers connected by a WAN

Distributed Database

3 multiple choice options

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

3 multiple choice options

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

Local Transaction

3 multiple choice options

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

3 multiple choice options

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

Replicated database

3 multiple choice options

Entire catalog resides on a single node

Central Catalog

1 multiple choice option

Copy of catalog resides on each node

Replicated Catalog

1 multiple choice option

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

Operational Data

3 multiple choice options

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

3 multiple choice options

Data warehouse designed for specific business area

Data Mart

2 multiple choice options

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

Data Lake

2 multiple choice options

Optimized for analytics

Data Warehouse

2 multiple choice options

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

3 multiple choice options

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

3 multiple choice options

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

Dimension Hierarchy

3 multiple choice options

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

3 multiple choice options

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

Embedded Databases

3 multiple choice options

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

Federated Database

3 multiple choice options

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

2 multiple choice options

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

2 multiple choice options

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

Weak Entity

2 multiple choice options

Can exist in the database independently.

Strong Entity

2 multiple choice options

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

Subtype Entities

2 multiple choice options

Depicted with square corners

Tables

3 multiple choice options

Indicate primary key columns

Bullets

3 multiple choice options

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

2 multiple choice options

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

Referential Integrity

3 multiple choice options

Represents an intersection of the data sets

AND

3 multiple choice options

Represents a union of the data sets

OR

3 multiple choice options

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

DROP

3 multiple choice options

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

Entity Class

3 multiple choice options

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

3 multiple choice options

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

3 multiple choice options

Multiple values removed. Only one value in each cell.

First Normal Form

3 multiple choice options

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

Second Normal Form

3 multiple choice options

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

3 multiple choice options

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

3 multiple choice options

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

Left Outer Join

3 multiple choice options

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

Right Outer Join

3 multiple choice options

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

Full Outer Join

3 multiple choice options

Returns only matching values.

Inner Join

2 multiple choice options

Combines two tables without comparing data.

Cross Join

2 multiple choice options

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

Primary Index

3 multiple choice options

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

Secondary Index

3 multiple choice options

Entry for every table row

Dense Index

2 multiple choice options

Entry for every table block

Sparse Index

3 multiple choice options

Stores column values and row pointers in hierarchy

Multi-Level Index

3 multiple choice options

All branches are the same length

Balanced Indexes

3 multiple choice options

Index entries assigned to buckets

Hash Index

3 multiple choice options

Block or group of blocks containing rows/index entries.

Bucket

3 multiple choice options

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

Hash Function

3 multiple choice options

Grid of bits

Bitmap Index

3 multiple choice options

Stores table rows in each bucket

Hash Table

3 multiple choice options

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

Physical Index

3 multiple choice options

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

Logical Index

3 multiple choice options

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

3 multiple choice options

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

3 multiple choice options

Subset of table rows.

Horizontal Partition

3 multiple choice options

Subset of table columns.

Vertical Partition

3 multiple choice options

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

Range Partition

3 multiple choice options

List of partition expression values using VALUES IN keywords.

List Partition

3 multiple choice options

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

Hash Partition

3 multiple choice options

Similar to hash but partition expression determined automatically by database.

Key Partition

2 multiple choice options

Candidate Key - Composite Key - Primary Key - Surrogate Key

Unique

1 multiple choice option