Final Exam Study Guide: Database Processing Fundamentals

Importance of Databases

Essential for managing user data in applications.

Nature and Characteristics

Helps track items of interest through data storage.

Data vs. Information

Data is raw facts; information is processed data.

Database Definition

Self-describing collection of integrated tables.

Metadata

Data about data, describing structure and relationships.

Database Management System (DBMS)

Software for creating and managing databases.

Database Categories

Classified as relational or non-relational.

Relational Databases

Organize data in tables, emphasizing structured relationships.

Non-Relational Databases

Flexible data structures for unstructured data management.

Database Design Types

Includes existing data analysis, new development, and redesign.

Database History

Milestones include early file systems and relational products.

Tables

Structures for storing data in rows and columns.

Rows

Records representing instances of data in tables.

Columns

Fields that define attributes of data in tables.

ACID Properties

Ensure reliability in relational database transactions.

SQL

Structured Query Language for managing relational databases.

Big Data

Massive unstructured data requiring non-relational databases.

Data Normalization

Process of organizing data to reduce redundancy.

Concurrency Control

Management of simultaneous database access to maintain integrity.

Backup and Recovery

Processes to restore data after loss or corruption.

Data Integrity

Accuracy and consistency of data within a database.

Self-Describing Data

Data that includes its own metadata for context.

Database

A structured collection of data organized in tables.

Relationships

Connections among rows of data in a database.

Keys

Identifiers used to establish relationships between rows.

Key

Combination of columns identifying specific rows.

Primary Key (PK)

Unique identifier for each row in a table.

Candidate Key

Key determining all other columns in a relation.

Composite Key

Primary key using more than one column.

Surrogate Key

Automatically assigned key used as primary key.

Foreign Key (FK)

Column linking two tables via primary key reference.

Referential Integrity

Ensures foreign key values match primary key values.

Data Integrity Constraints

Rules ensuring data accuracy and consistency.

Domain Integrity Constraint

Limits attribute values to a defined set.

Entity Integrity Constraint

Requires primary key to be unique and non-NULL.

Modification Anomalies

Issues arising from unnormalized tables.

Insertion Anomaly

Difficulty adding data without related data present.

Deletion Anomaly

Loss of data about one entity during deletion.

Update Anomaly

Inconsistency from changing data in multiple places.

Normalization

Technique to reduce redundancy and data dependency.

Functional Dependencies (FD)

Relationship where one attribute determines another.

Determinant

Attribute whose value determines other attributes' values.

Multivalued Dependencies (MVD)

Determinant matched with a set of values.

Anomalies

Problems arising from poor database design.

Multivalued Dependency (MVD)

A situation where one attribute determines multiple values.

Modification Anomalies

Problems arising from database updates or deletions.

Normal Forms

Categories of relations based on anomaly susceptibility.

First Normal Form (1NF)

Requires atomic values and no repeating groups.

Second Normal Form (2NF)

All non-key attributes depend on entire primary key.

Third Normal Form (3NF)

No determinants except the primary key allowed.

Boyce-Codd Normal Form (BCNF)

Every determinant must be a candidate key.

Fourth Normal Form (4NF)

Resolves multivalued dependencies by creating separate tables.

Domain Key Normal Form (DK/NF)

Constraints are logical consequences of keys and domains.

Functional Dependency (FD)

Relationship where one attribute uniquely determines another.

Candidate Key

An attribute that can uniquely identify a record.

Primary Key (PK)

A unique identifier for a database record.

Foreign Key (FK)

An attribute that creates a link between tables.

Referential Integrity Constraint

Ensures relationships between tables remain valid.

SQL COUNT(*)

Counts the number of rows in a table.

SQL SELECT TOP

Retrieves a specified number of records.

Denormalization

Combining normalized tables to improve performance.

Read-Only Databases

Nonoperational databases used for querying and reporting.

Data Warehouse

A system for reporting and data analysis.

Multivalue, Multicolumn Problem

Storing multiple values in multiple columns instead of rows.

Inconsistent Values Problem

Same data represented differently across records.

Database Design Assessment

Evaluating table structure and data relationships.

NULL

Values that have never been provided.

Remarks Column Problem

Inconsistent data in remarks or notes columns.

SQL Significance

Language for communicating with and manipulating databases.

SQL Standard

ANSI endorsed SQL as a national standard in 1992.

SQL Categories

Five types of SQL statements for various tasks.

Data Definition Language (DDL)

Creates, modifies, and deletes database structures.

Data Manipulation Language (DML)

Queries, inserts, modifies, and deletes database data.

SQL/Persistent Stored Modules (SQL/PSM)

Adds procedural programming capabilities to SQL.

Transaction Control Language (TCL)

Controls transaction boundaries and behavior.

Data Control Language (DCL)

Grants or revokes database permissions.

CREATE TABLE

Command to create new database tables.

ALTER TABLE

Modifies an existing table's structure.

DROP TABLE

Removes a table from the database.

TRUNCATE TABLE

Removes all data while keeping table structure.

SELECT

Retrieves data from a database.

DISTINCT

Eliminates duplicate rows in SELECT results.

TOP

Limits number of rows returned in SELECT.

FROM Clause

Specifies tables to select data from.

WHERE Clause

Filters rows based on specified conditions.

ORDER BY Clause

Sorts query results in ascending or descending order.

GROUP BY Clause

Groups rows with same values in specified columns.

HAVING Clause

Filters groups based on conditions after GROUP BY.

Aggregate Functions

Perform calculations on column values like SUM or AVG.

AVG

Calculates the average of numeric values.

MIN

Finds the minimum value in a dataset.

MAX

Finds the maximum value in a dataset.

COUNT

Counts rows or non-NULL values in a column.

HAVING

Filters results after aggregation in SQL.

RTRIM()

Removes trailing spaces from a string.

SQL Views

Virtual tables constructed from SELECT statements.

CREATE VIEW

Command to create a new SQL view.

SQL/PSM

Standard for procedural programming in SQL.

User-Defined Functions

Stored SQL statements that return output values.

CREATE FUNCTION

Command to create a user-defined function.