Introduction to Database Design: Normalization and SQL Basics

Vocabulary flashcards covering normalization concepts, normal forms, dependencies, keys, and core SQL commands from the notes.

Normalization

The process of organizing data in a relational database to reduce redundancy and dependency, ensuring each data item is stored in one place and relationships are represented.

Relational database

A database that stores data in tables with defined relationships between them to minimize redundancy and enable efficient queries.

Data redundancy

Duplication of data across multiple places, leading to wasted space and potential inconsistencies.

Data anomalies

Inconsistencies or errors that arise from data redundancy when inserting, updating, or deleting data.

Atomic values

Indivisible values stored in a column; each cell contains a single value.

First Normal Form (1NF)

A form where each column contains atomic values and each row is unique.

Second Normal Form (2NF)

Eliminates partial dependencies by ensuring non-key attributes depend on the entire primary key.

Third Normal Form (3NF)

Removes transitive dependencies by ensuring non-key attributes depend only on the primary key.

Boyce-Codd Normal Form (BCNF)

A stricter version of 3NF that eliminates all non-trivial functional dependencies.

Fourth Normal Form (4NF)

Addresses multi-valued dependencies among attributes.

Fifth Normal Form (5NF)

Addresses join dependencies; handles complex data relationships beyond 4NF.

Partial dependency

A non-key attribute depends on part of a composite primary key.

Transitive dependency

A non-key attribute depends on another non-key attribute rather than directly on the primary key.

Primary key

A unique identifier for each row in a table.

Foreign key

A field that links to the primary key in another table, creating a relationship between tables.

Functional dependency

A relationship where one attribute (or set) determines another attribute.

Data integrity

The accuracy, consistency, and reliability of data over time.

SQL

Structured Query Language used to communicate with and manipulate relational databases.

DDL

Data Definition Language; defines or modifies database structure (e.g., CREATE, ALTER, DROP).

DML

Data Manipulation Language; manipulates data within the database (e.g., SELECT, INSERT, UPDATE, DELETE).

DCL

Data Control Language; controls access to data (e.g., GRANT, REVOKE).

TCL

Transaction Control Language; manages transactions (e.g., COMMIT, ROLLBACK, SAVEPOINT).

CREATE TABLE

DDL command to create a new table with specified columns and constraints.

ALTER TABLE

DDL command to modify an existing table (e.g., add or alter columns).

DROP TABLE

DDL command to delete a table and its data.

SELECT

DML command to query data from one or more tables.

INSERT

DML command to add new rows to a table.

UPDATE

DML command to modify existing data in a table.

DELETE

DML command to remove data from a table.

GRANT

DCL command to give specific permissions to a user or role.

REVOKE

DCL command to remove previously granted permissions.

COMMIT

TCL command to permanently apply all changes in the current transaction.

ROLLBACK

TCL command to undo all changes in the current transaction.

SAVEPOINT

TCL command to set a point within a transaction to which you can roll back.