D426 Study Guide

What is a database application?

Software that helps business users interact with database systems.

Who is a database administrator (DBA)?

A person responsible for securing, maintaining, and controlling user access to a database system.

What is authorization in databases?

Limiting user access to specific tables, columns, or rows based on permissions.

What does the query processor do?

Interprets and optimizes SQL queries and returns results to applications.

What does the storage manager do?

Translates database instructions into file-system commands to retrieve or modify data.

What is the transaction manager's role?

Ensures transactions are properly executed and restores consistency after failures.

What type of database is MongoDB?

A NoSQL, open-source database.

What does the SQL INSERT command do?

Adds new rows to a table. Example: INSERT INTO Students VALUES (1, 'John');

What does the SQL SELECT command do?

Retrieves data from a table. Example: SELECT * FROM Students;

What does the SQL UPDATE command do?

Modifies existing data in a table. Example: UPDATE Students SET Name='Jane' WHERE ID=1;

What does the SQL DELETE command do?

Deletes rows from a table. Example: DELETE FROM Students WHERE ID=1;

What does CREATE TABLE do?

Creates a new table and defines columns and data types.

What does DROP TABLE do?

Deletes a table and all of its rows from a database.

What does ALTER TABLE do?

Adds, modifies, or deletes columns in an existing table.

What is data independence?

The principle that physical design changes don't affect query results.

What are the SQL sublanguages?

DDL (structure), DQL (retrieve), DML (manipulate), DCL (access), DTL (transactions).

What is a primary key?

A column or group of columns that uniquely identifies a row.

What is a composite primary key?

A primary key made up of multiple columns.

What is a foreign key?

A column that references a primary key in another table to enforce relationships.

What is referential integrity?

Rules ensuring foreign keys match existing primary keys.

Name the referential integrity actions.

RESTRICT, SET NULL, SET DEFAULT, CASCADE.

What is a constraint?

A rule that defines valid data (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK).

What does BETWEEN do in SQL?

Checks if a value is between two other values. Example: WHERE Age BETWEEN 18 AND 30.

What does LIKE do in SQL?

Searches for patterns in text using % (many chars) and _ (single char).

What does ORDER BY do?

Sorts query results by one or more columns (ASC or DESC).

What does GROUP BY do?

Groups rows with the same values in specified columns.

What is the HAVING clause?

Filters groups created by GROUP BY (used with aggregate functions).

What are aggregate functions?

Functions like COUNT, SUM, AVG, MIN, MAX that summarize data.

What is an INNER JOIN?

Combines only matching rows from two tables.

What is a LEFT JOIN?

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

What is a RIGHT JOIN?

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

What is a FULL JOIN?

Combines all rows from both tables, with NULLs for unmatched rows.

What is a cross-join?

Combines every row of one table with every row of another (Cartesian product).

What is an equijoin?

A join where columns are compared using '='.

What is a self-join?

A table joined to itself to compare rows within the same table.

What is a subquery?

A query inside another SQL query.

What is an alias in SQL?

A temporary name for a table or column using the AS keyword.

What is a view?

A virtual table created by a query. Doesn't store data itself.

What is a materialized view?

A view that stores data physically and must be refreshed when base data changes.

What is an ER diagram?

A visual model showing entities, relationships, and attributes.

What are entities in ER modeling?

Objects like people, places, or things represented as tables.

What are relationships in ER modeling?

Connections between entities (e.g., Student-EnrollsIn-Course).

What are attributes in ER modeling?

Properties or characteristics of an entity (e.g., Name, Age).

What is cardinality?

Defines how many instances of one entity relate to another (1:1, 1:N, M:N).

What is a subtype entity?

A subset of another entity (e.g., Manager is a subtype of Employee).

What is normalization?

The process of reducing redundancy and ensuring data integrity by organizing data into tables.

What is denormalization?

Intentionally adding redundancy for performance improvement.

What is a candidate key?

A unique, minimal column or set of columns that can be a primary key.

What is a non-key column?

A column not part of any candidate key.

What is a functional dependency?

When one column's value determines another's value.

What is a trivial dependency?

When one set of columns is a subset of another, always true.

What is first normal form (1NF)?

A table with atomic (indivisible) values and unique rows.

What is second normal form (2NF)?

1NF plus all non-key attributes depend on the whole primary key.

What is third normal form (3NF)?

2NF plus no transitive dependencies (non-key columns depend only on keys).

What is Boyce-Codd normal form (BCNF)?

Stricter than 3NF; every determinant must be a candidate key.

What is a heap table?

Rows stored with no particular order; optimized for inserts.

What is a sorted table?

Rows are physically stored in order based on a column.

What is a hash table in databases?

Rows assigned to buckets using a hash function for quick lookups.

What is an index?

A data structure that speeds up data retrieval by providing quick lookup paths.

What is a dense index?

Contains an entry for every row in a table.

What is a sparse index?

Contains entries for groups or blocks of rows, not every row.

What is a bitmap index?

Uses bits (0 or 1) to represent row matches for each distinct value.

What is a tablespace?

A storage location mapping tables to physical files.

What is data type INT used for?

Storing integer values.

What is data type VARCHAR used for?

Storing text strings up to a defined length.

What is data type DATE used for?

Storing date values (year, month, day).

What is the purpose of DECIMAL(M, D)?

Stores numbers with fixed precision (M total digits, D after decimal).

What does the % (modulo) operator do?

Returns the remainder of a division. Example: 5 % 2 = 1.

What does the ^ operator do?

Raises one number to the power of another (e.g., 5^2 = 25).

What does the TRUNCATE statement do?

Removes all rows from a table quickly, without deleting the table itself.

What is SQL syntax for creating an index?

CREATE INDEX idx_name ON table_name (column_name);

What is the main benefit of indexes?

Improves query performance by reducing search time.

What is a tuple?

An ordered collection of values (a row in a table).

What are synonyms for table elements?

Table=Relation, Row=Tuple, Column=Attribute.

What is the main difference between logical and physical design?

Logical design defines structure (tables, keys); physical design defines storage and indexes.