WGU D426/427

What is a subquery

A query within another SQL query.

What is an alias in SQL

A temporary name assigned to a column or table, created with the AS keyword.

What is a materialized view

A view for which data is stored at all times and must be refreshed when the base table changes.

What does the WITH CHECK OPTION clause do

Ensures that inserts and updates in a view satisfy the view's WHERE clause.

What is an entity in entity-relationship modeling

A person, place, product, concept, or activity.

What is a relationship in entity-relationship modeling

A statement about two entities.

What is an attribute in entity-relationship modeling

A descriptive property of an entity.

What is a reflexive relationship

A relationship that relates an entity to itself.

What is an entity-relationship diagram (ER diagram)

A schematic picture of entities, relationships, and attributes, where entities are drawn as rectangles.

What is an entity type

A set of things, such as all employees in a company.

What is a relationship type

A set of related things, such as employee-manages-department pairs.

What is an attribute type

A set of values, such as all employee salaries.

What is an entity instance

An individual thing, such as the employee Sam Snead.

What is a relationship instance

A specific statement about entity instances, such as "Maria Rodriguez manages Sales."

What is an attribute instance

An individual value, such as a salary of $35,000.

What is cardinality in entity-relationship modeling

The maxima and minima of relationships and attributes.

What is a subtype entity

A subset of another entity type, called the supertype entity.

What is a supertype entity

An entity that has one or more subtype entities, such as "vehicle" with subtypes like "car" and "truck."

What is an IsA relationship

The identifying relationship between a supertype and its subtypes.

What is a partition in entity-relationship modeling

A group of mutually exclusive subtype entities within a supertype entity.

What is crow's foot notation

A convention for depicting cardinality in ER diagrams, using symbols like circles, short lines, and three short lines (a crow's foot).

What is an intangible entity

An entity documented in the data model but not tracked with data in the database.

What are the characteristics of a primary key

A primary key should be stable, simple, and meaningless.

What is an artificial key

A single-column primary key created by the database designer when no suitable primary key exists.

What is functional dependence

The dependence of one column on another.

What is redundancy in databases

The repetition of related values in a table.

What is a candidate key

A simple or composite column that is unique and minimal, with all columns necessary for uniqueness.

What is a non-key column

A column that is not contained in a candidate key.

What is third normal form

A table is in third normal form if, whenever a non-key column A depends on column B, then B is unique.

What is Boyce-Codd normal form

A stricter form of third normal form where column B must be unique regardless of whether it is a candidate key.

What is a trivial dependency

When the columns of A are a subset of the columns of B, A always depends on B.

What is normalization

The process of eliminating redundancy by decomposing a table into two or more tables in higher normal form.

What is denormalization

The intentional introduction of redundancy by merging tables.

What is a heap table

A table where no order is imposed on rows, optimizing insert operations.

What is a sorted table

A table where rows are physically ordered by a specified sort column.

What is a hash table

A table where rows are assigned to buckets, optimizing search and retrieval.

What is a table cluster

A storage structure that interleaves rows of two or more tables in the same storage area.

What is a table scan

A database operation that reads table blocks directly without using an index.

What is an index scan

A database operation that reads index blocks sequentially to locate the needed table blocks.

What is a hit ratio (filter factor

selectivity), The percentage of table rows selected by a query.

What is a binary search

A search method where the database repeatedly splits the index in two until the search value is found.

What is a dense index

An index that contains an entry for every table row.

What is a sparse index

An index that contains an entry for every table block.

What is a hash index

An index where entries are assigned to buckets.

What is a bitmap index

A grid of bits representing a set of values, with ones and zeros used to indicate the presence of values.

What is a tablespace

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

What does the CREATE INDEX statement do

Creates an index on specified columns in a table.

What is the role of a storage engine (or storage manager)

Translates query processor instructions into low-level commands that access data on storage media.

What is a table in a database

A set of rows with a name, a fixed tuple of columns, and a varying set of rows.

What is a column in a table

A name and a data type for each piece of data stored in a row.

What is a row in a table

An unnamed tuple of values, where each value corresponds to a column and belongs to the column's data type.

What is the synonym for a table

file, or relation, Table, file, and relation are synonyms for the same concept in a database.

What are the synonyms for a row

record, or tuple, Row, record, and tuple are synonyms for the same concept in a database.

What are the synonyms for a column

field, or attribute, Column, field, and attribute are synonyms for the same concept in a database.

What is the purpose of the SQL CREATE TABLE statement

It creates a new table by specifying the table name, column names, and column data types.

What is the purpose of the SQL DROP TABLE statement

It deletes a table along with all of its rows from a database.

What does the SQL ALTER TABLE statement do

It adds, deletes, or modifies columns in an existing table.

What is an example of an integer data type

INT represents positive and negative integers, implemented as 4 bytes of storage.

What is the range for a TINYINT data type

A signed range from -128 to 127 and an unsigned range from 0 to 255.

What is the range for a SMALLINT data type

A signed range from -32,768 to 32,767 and an unsigned range from 0 to 65,535.

What does the arithmetic operator + do

Adds two numeric values.

What does the arithmetic operator * do

Multiplies two numeric values.

What does the comparison operator = do

Compares two values for equality.

What does the comparison operator != do

Compares two values for inequality.

What does the SQL UPDATE statement do

Modifies existing rows in a table using the SET clause.

What does the SQL DELETE statement do

Deletes existing rows in a table, with an optional WHERE clause to specify which rows.

What does the SQL TRUNCATE statement do

Deletes all rows from a table but preserves the table structure.

What is a primary key

A column or group of columns used to uniquely identify a row in a table.

What is a foreign key

A column or group of columns that refer to the primary key of another table.

What does the FOREIGN KEY constraint do

It ensures that relationships between tables are maintained by rejecting insert, update, or delete operations that violate referential integrity.

What does the CASCADE action do in foreign key constraints

Propagates changes in the primary key to foreign keys.

What is the BETWEEN operator used for

Determines if a value is between two other values.

What does the LIKE operator do

Matches text against a pattern using wildcard characters % and _.

What does the SQL HAVING clause do

Filters group results in combination with the GROUP BY clause.

What is an INNER JOIN

A join that selects only matching rows from both the left and right tables.

What is a LEFT JOIN

A join that selects all rows from the left table and matching rows from the right table.

What is a FULL JOIN

A join that selects all rows from both the left and right tables, regardless of matching.

What is a self-join

A join that connects a table to itself.

What is a cross-join

A join that combines all possible combinations of rows from two tables without comparing columns.

What is a database application

Software that helps business users interact with database systems.

What is the role of a database administrator

Responsible for securing the database system against unauthorized users, enforcing user access procedures, and ensuring database availability.

What is authorization in a database context

The process that allows individual users limited access to specific tables, columns, or rows of a database.

What is the role of the query processor

It interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

What does the storage manager do

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

What is the responsibility of the transaction manager

Ensures transactions are properly executed and restores the database to a consistent state in case of failure.

What does the SQL INSERT statement do

Inserts rows into a table.

What does the SQL UPDATE statement do

Modifies data in a table.

What does the SQL DELETE statement do

Deletes rows from a table.

What is the purpose of the SQL CREATE TABLE statement

It creates a new table by specifying the table and column names.

What are some examples of SQL data types

INT (integer values), DECIMAL (fractional numeric values), VARCHAR (textual values), and DATE (year, month, day).

What are the three phases of database design

Analysis, logical design, and physical design.

What happens in the analysis phase

Specifies database requirements without regard to a specific database system, represented by entities, relationships, and attributes.

What happens in the logical design phase

Implements database requirements in a specific database system, converting entities, relationships, and attributes into tables, keys, and columns.

What is the focus of the physical design phase

Adds indexes and specifies how tables are organized on storage media, affecting query speed but not results.

What is data independence

The principle that physical design never affects query results.

What is an API in the context of databases

An application programming interface that simplifies the use of SQL with a general-purpose language.

What is a tuple

An ordered collection of elements enclosed in parentheses.