A.2.6 Database Terms Notes

Key Terms used in Databases

1. Table (relation / file):
   1. Two‑dimensional structure, columns define attributes, rows define tuples.
   2. Represents one entity set, e.g. a Students table stores every student in the school.
   3. Tables are unordered, a DBMS can physically store rows anywhere as long as the logical structure is preserved.
2. Record (tuple / row):
   1. One horizontal line of data inside a table.
   2. Integrity rules (such as NOT NULL or CHECK) are validated at the record level when it is inserted or updated.

1. Field (attribute / column)
   1. Vertical slice specifying a single data item (e.g., Email VARCHAR (100)).
   2. Every record supplies one atomic value for the field, drawn from the column’s defined data type and domain.
   3. Columns can carry constraints (default values, uniqueness) and metadata (comments, collation, masks).
2. Primary key
   1. Column(s) chosen to uniquely label each record, cannot be NULL.
   2. Guarantees entity integrity and is automatically indexed, speeding joins and searches.
   3. Other tables reference it through foreign keys (e.g., StudentID in Students).
3. Secondary key (alternate / secondary index)
   1. Any non‑primary column (or set) indexed to speed look‑ups.
   2. May contain duplicates (e.g., LastName in Students) or be unique (NationalID), but uniqueness is not required.
   3. Improves query performance without enforcing identity.
4. Foreign key
   1. Column(s) holding values that match a primary key in another (or the same) table.
   2. Enforces referential integrity: prevents orphaned rows, and can cascade updates/deletes if configured.

1. Candidate key
   1. Any minimal field set whose values are unique and non‑null, qualifies to become the primary key.
   2. A table can have multiple candidate keys (e.g., StudentID, NationalID, Email) but only one is selected as the primary key.
2. Composite primary key
   1. Primary key formed by two or more columns whose combined value is unique.
   2. Common in junction tables, Enrollments(StudentID, CourseID), where neither column alone is unique but together they prevent duplicate enrolments.
3. Join (inner join)
   1. Relational operation that merges rows from two tables where specified columns match (usually a foreign‑key ↔ primary‑key pair).
   2. Returns only the intersecting rows, non‑matching rows are excluded.