Ruankao Database Systems Engineer (Intermediate) flashcards

30 cards. Read the term and the definition here. The deck at the top of this page is optional.

Relational database

A database that stores data in tables made of rows and columns and uses relationships between tables. It is queried with languages such as SQL and follows the relational model.

Primary key

A column or set of columns whose values uniquely identify each row in a database table. It cannot contain duplicate or null values.

Foreign key

A column or set of columns in one table that refers to the primary key of another table. It enforces referential integrity between related records.

SQL

Structured Query Language, a standard language for defining, querying, updating, and managing data in relational databases. It includes statements for creating tables and retrieving rows.

Normalization

The process of organizing relational database tables to reduce duplicated data and avoid update anomalies. It typically involves dividing data into related tables and defining keys.

Database index

A data structure that speeds up searches on a database table by providing quick lookup paths to rows. It uses extra storage and must be updated when data changes.

Metadata

Data that describes other data, such as a file's author, creation date, format, or size. In databases, it describes table structures, constraints, and other schema information.

Table

The basic storage structure in a relational database: a named set of rows, each row having the same set of named columns with a defined data type per column. Also called a relation or entity.

Row

A single record in a table, holding one value for each column. Also called a tuple or record. Rows are unordered in the relational model; only an ORDER BY clause gives a result a defined sequence.

Column

A named attribute of a table that holds one value per row, with a declared data type and optional constraints. Also called a field or attribute. All values in a column share the same type.

Candidate key

Any minimal set of columns that could serve as the primary key because it uniquely identifies every row. One candidate key is chosen as the primary key; the others are usually enforced as unique constraints.

Referential integrity

The rule that every non-null foreign key value must correspond to an existing key value in the referenced table. The database rejects inserts, updates and deletes that would break this rule.

Unique constraint

A rule that no two rows may hold the same value in the specified column or column combination. Unlike a primary key, a unique constraint usually permits null values and a table may have several of them.

NOT NULL constraint

A column rule that forbids the absence of a value, so every row must supply a value for that column. It is a column-level check and is implied automatically by a primary key.

CHECK constraint

A declared predicate that rejects rows for which the condition evaluates FALSE. UNKNOWN, including many NULL-related results, can pass, so required presence needs a separate NOT NULL constraint.

DEFAULT constraint

A value the database supplies automatically when an INSERT statement omits the column. It applies only when no value is given; explicitly inserting NULL stores NULL rather than the default.

NULL

A marker meaning that a value is unknown or not applicable, distinct from zero or an empty string. Comparisons with NULL yield unknown, so tests must use IS NULL or IS NOT NULL.

Three-valued logic

The rule that SQL conditions evaluate to true, false or unknown, with unknown arising whenever NULL takes part in a comparison or arithmetic expression. A WHERE clause keeps only rows whose condition is true.

COALESCE

A function returning the first of its arguments that is not null, or null if all are null. Commonly used to substitute a default for missing values in query output.

Data type

The declared category of values a column may hold, such as integer, decimal, character string, date or Boolean. The type determines allowed operations, storage size and which implicit conversions the database permits.

CHAR versus VARCHAR

CHAR stores fixed-length strings padded with spaces to the declared length, while VARCHAR stores variable-length strings up to a maximum. CHAR suits uniform codes; VARCHAR suits names and addresses of varying length.

CAST

An operator or function that converts a value from one data type to another, for example turning a string into a number or a date. It may be explicit in the statement or applied implicitly by the database.

Implicit conversion

Automatic type conversion the database performs when operands of different types meet in an expression or comparison. It can silently change results or prevent an index from being used, so explicit conversion is safer.

CREATE TABLE

The DDL statement that defines a new table by naming it and listing its columns with data types and constraints. Variants can also copy structure from an existing table or create the table only if absent.

CPU

The central processing unit is the main processor in a computer. It fetches instructions, decodes them, and carries out arithmetic, logic, and control operations while coordinating other components.

RAM

Temporary, volatile computer memory that holds programs and data currently in use. Its contents disappear when power is lost, but it allows fast reading and writing.

Cache memory

A small, fast memory area close to the processor that stores frequently used instructions and data. It reduces average access time by keeping likely-needed information nearby.

Addressable unit

The quantity identified by one memory address, such as a byte or a word. A bus-width calculation requires this convention before converting address counts into byte capacity.

TLB miss

A failed lookup in the translation cache, requiring the processor’s defined translation procedure. It does not alone establish that the target virtual page is absent from physical memory.

Page fault

An architectural exception caused by an absent or disallowed page translation or access under the platform model. Handling depends on mapping, protection and residency rather than always reading a disk.