SQL Data Definition and Manipulation Concepts

Vocabulary flashcards defining fundamental SQL concepts, database objects, data types, constraints, and referential actions based on the lecture notes.

SQL

Structured Query Language: the standard query language for relational databases.

Fourth-generation language (4GL)

A language where you describe what you want, not how to get it.

Transform-oriented language

A language that uses relations to transform inputs into required outputs.

DDL (Data Definition Language)

Creates database objects (tables, indexes, views) and defines access rights to them; acts on structure.

DML (Data Manipulation Language)

Inserts, updates, deletes and retrieves data in tables; acts on rows.

DCL (Data Control Language)

Controls access to database objects (includes commands like GRANT and REVOKE).

TCL (Transaction Control Language)

Commands run within a transaction (includes commands like COMMIT and ROLLBACK).

Transaction

A logical unit of work made of one or more SQL statements, defined by business rules.

Reserved words

SQL command words, which you should avoid using as names (such as order).

Schema

A logical container for database objects such as tables, views and triggers, created for a user or set of users.

dbo

The default schema for database objects in SQL Server (for example, the schema prefix in dbo.DH_Branch).

SSMS

SQL Server Management Studio, the program used to work with SQL Server.

Server roles

Privileges such as creating or deleting databases and tables, and disk administration.

Backup devices

Configured database backup destinations listed under Server Objects in SSMS.

Linked servers

Other database servers connected to the current database server.

Table

The primary object in a relational database, and the implementation of a relation from the relational model.

CHAR(n)

Fixed-length text, used when every value has the same length (such as a two-letter state code CHAR(2)).

VARCHAR(n)

Variable-length text up to n characters, such as last_name VARCHAR(15).

VARCHAR(max)

Text up to $2^{31}$ characters, used for long comments.

DECIMAL(L, D)

Exact decimal numbers, also called NUMERIC, where L is precision and D is scale (e.g., DECIMAL(7,2)).

Precision (L)

In DECIMAL(L, D), the total number of digits on both sides of the decimal point.

Scale (D)

In DECIMAL(L, D), the number of digits to the right of the decimal point.

INT

Data type for whole numbers, such as person_id INT.

DATE

Data type that stores a date without time, formatted as YYYY-MM-DD.

DATETIME

Data type that stores both a date and a time, such as a timestamp.

BIT

Boolean data type where 0 = false and 1 = true, such as an active flag.

Null

A special value for data that is unknown or does not exist (not the same as blank).

NOT NULL

A constraint that excludes nulls from a column; SQL Server rejects any attempt to store one.

Entity integrity

The rule that a primary key must hold a unique, non-null value in every record.

Required data constraint

An integrity constraint ensuring a column must have a value; enforced using NOT NULL.

Domain constraint

An integrity constraint ensuring column values fall within a valid set; enforced using CHECK.

Entity integrity constraint

An integrity constraint ensuring each row has a unique, non-null identifier; enforced using PRIMARY KEY.

Referential integrity constraint

An integrity constraint ensuring a foreign key matches a primary key in another table; enforced using FOREIGN KEY … REFERENCES.

General (other) constraints

Integrity constraints enforcing additional business rules such as uniqueness; uses UNIQUE and DEFAULT.

Column constraint

A constraint written with the column's definition that covers just one column.

Table constraint

A constraint written after all columns are defined that covers one or more columns (required for composite keys).

NO ACTION

The default referential action where deleting or updating a parent key is rejected if child rows still match it.

CASCADE

A referential action that deletes or updates the parent and automatically applies that operation to matching child rows down the chain.

SET NULL

A referential action that deletes or updates the parent and sets the child table's foreign key values to NULL (requires the foreign key to allow nulls).

SET DEFAULT

A referential action that deletes or updates the parent and sets the child table's foreign key to its defined default value.

UNIQUE

A constraint that ensures uniqueness across a column or set of columns, accommodating values that must not repeat outside the primary key.

DEFAULT

A constraint that automatically sets a column's value when none is specified in an INSERT statement.

Bridge table

An intermediary table whose composite primary key combines the primary keys of two parent tables to resolve a many-to-many ($\text{M:N}$) relationship.