ITSS 4300 QUIZ 3

Normalization

A process for evaluating and correcting table structures to minimize data redundancies and reduce anomalies. "Properly executed, the normalization process eliminates uncontrolled data redundancies."

1NF

Table format with no repeating groups and a primary key identified.

2NF

A table in 1NF with no partial dependencies.

3NF

A table in 2NF with no transitive dependencies.

BCNF

A table in 3NF where every determinant is a candidate key.

4NF

A table in BCNF with no multivalued dependencies.

5NF

A table in 4NF that cannot be decomposed further without losing information (lossless decomposition).

Partial Dependency

A functional dependency where the determinant is only part of a composite primary key.

Transitive Dependency

A dependency where a non-key attribute depends on another non-key attribute.

Atomic Attribute

An attribute that cannot be further subdivided.

Surrogate Key

A system-defined, usually numeric key created by the DBMS.

Denormalization

The process of producing lower normal forms to improve performance at the cost of redundancy.

Functional Dependency

A relationship where one attribute determines another.

Repeating Group

Multiple entries of the same type existing for a single key attribute occurrence.

Granularity

The level of detail represented by the values stored in a table's row.

Well-Formed Relation

A table with a single subject, atomic values, no redundancy, and no anomalies.

SQL

A nonprocedural language used for data manipulation, definition, control, and transactions.

DML

Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).

DDL

Data Definition Language (CREATE, ALTER, DROP).

TCL

Transaction Control Language (COMMIT, ROLLBACK).

DCL

Data Control Language (GRANT, REVOKE).

SELECT

Retrieves data from tables; specifies columns to return.

FROM

Specifies the table(s) from which data is retrieved.

WHERE

Filters rows based on conditions.

GROUP BY

Groups rows into collections for aggregate functions.

HAVING

Filters groups created by GROUP BY.

ORDER BY

Sorts query results in ascending or descending order.

Wildcard (*)

Returns all columns in a table.

Column Alias

An alternative name for a column in a SQL query.

Computed Column

A derived attribute created using arithmetic in SELECT.

DISTINCT

Returns only unique values from a column.

JOIN

Combines rows from multiple tables based on related columns.

INNER JOIN

Returns only matching rows between tables.

LEFT OUTER JOIN

Returns all rows from the left table and matching rows from the right.

RIGHT OUTER JOIN

Returns all rows from the right table and matching rows from the left.

FULL OUTER JOIN

Returns all rows from both tables, matching where possible.

NATURAL JOIN

Joins tables using columns with identical names and compatible data types.

JOIN USING

Joins tables based on a shared column name specified in USING().

JOIN ON

Joins tables using a specified join condition.

Cross Join

Produces the Cartesian product of two tables.

Aggregate Function

A function that reduces a collection of rows to a single value (COUNT, SUM, AVG, MIN, MAX).

COUNT

Returns number of non-null values in a column.

SUM

Returns the total of all numeric values in a column.

AVG

Returns the arithmetic mean of a numeric column.

MIN

Returns the smallest value in a column.

MAX

Returns the largest value in a column.

BETWEEN

Tests whether a value is within a range.

IN

Tests whether a value matches any value in a list.

LIKE

Tests whether a value matches a string pattern.

IS NULL

Tests whether a value is null.

Subquery

A query inside another query; inner query executes first.

Correlated Subquery

A subquery that executes once for each row of the outer query.

EXISTS

Tests whether a subquery returns any rows.

UNION

Combines rows from two queries, removing duplicates.

UNION ALL

Combines rows from two queries, keeping duplicates.

INTERSECT

Returns rows appearing in both queries.

EXCEPT (MINUS)

Returns rows in the first query but not the second.

SELECT Query Order

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

ORDER BY vs GROUP BY

ORDER BY sorts rows; GROUP BY groups rows for aggregation. "ORDER BY has no impact on which rows are returned… GROUP BY does impact the rows returned."

Outer Join Types

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.