Databases – Key Vocabulary

Vocabulary flashcards covering fundamental database concepts, structures, modeling terms, and SQL operations discussed in the lecture notes.

Database

A structured collection of persistent, shared, and integrated data, typically stored in tables.

Ubiquitous (in databases)

Describes databases that are accessible globally from any location, system, or device.

Pervasive (in databases)

Indicates that a database is always present, stable, and recoverable even after failures.

Data

Raw facts or values (text, numbers) that have not yet been processed.

Information

Meaningful results produced after data is processed, calculated, or formatted.

Database Management System (DBMS)

Software that manages, secures, and controls access to a database.

Single-User Database

A database installed locally for use by one person, e.g., MS Access on a PC.

Multi-User Database

A database accessed concurrently by multiple people or departments.

Centralized Database

A database hosted in one physical location, often for high security and control.

Distributed Database

A database whose data is spread across multiple physical sites, providing redundancy.

Cloud Database

A database hosted on cloud platforms like AWS, Azure, or GCP, offering elastic scaling.

General-Purpose Database

A database used across many business functions, e.g., company-wide employee data.

Discipline-Specific Database

A database tailored to a particular function, such as a product or medical database.

Operational Database

A database that supports day-to-day transactions and business operations.

Analytical Database

A database (often a data warehouse) storing historical data for analysis and reporting.

Structured Data

Data organized in rows and columns, stored in relational tables.

Unstructured Data

Data without a fixed schema, e.g., PDFs, images, audio files.

Semi-Structured Data

Data with partial structure, e.g., JSON or XML, where each record may differ.

Primary Key

A unique, non-NULL column (or set of columns) that identifies each row in a table.

Foreign Key

A column in one table that references a primary (or unique) key in another table.

Entity Integrity

Rule ensuring primary keys are unique and never NULL.

Referential Integrity

Rule ensuring foreign-key values are either NULL or match existing primary-key values.

Relational Model

Dominant data model storing data in tables connected by keys.

Hierarchical Model

Tree-like data model with one parent to many children relationships.

Network Model

Older data model allowing many-to-many record links via pointers.

Object-Oriented Model

Data model treating entities as reusable objects with attributes and methods.

NoSQL Model

Schema-flexible model for unstructured or semi-structured data; example: MongoDB.

Data Model

An abstract representation of a real-world system’s data requirements and rules.

Conceptual Model

High-level, platform-independent diagram showing entities and relationships.

Logical Model

Model adding attributes, data types, and detailed relationships to the conceptual design.

Physical Model

Platform-specific model detailing how tables and indexes are stored on disk.

Entity

A real-world object or concept represented as a table in a database.

Attribute

A property of an entity; implemented as a column in a table.

Relationship

An association (verb) linking two entities, such as "enrolls" or "teaches."

Constraint

A rule that limits permissible data values, e.g., GPA 0–4.

Cardinality

The numeric range (min, max) describing how many entity instances participate in a relationship.

One-to-One (1:1)

Relationship where a row in Table A relates to exactly one row in Table B and vice versa.

One-to-Many (1:M)

Relationship where one row in Table A relates to many rows in Table B.

Many-to-Many (M:N)

Relationship where many rows in Table A relate to many rows in Table B; resolved with a bridge table.

Entity Relationship Diagram (ERD)

Visual diagram showing entities, attributes, relationships, and cardinalities.

Projection (Relational Algebra)

Operation that selects specific columns from a relation.

Union (Relational Algebra)

Operation that combines two result sets, removing duplicates.

Intersect (Relational Algebra)

Operation returning only rows appearing in both result sets; equals SQL INNER JOIN on shared columns.

INNER JOIN

SQL join returning only rows where both tables have matching values.

LEFT OUTER JOIN

SQL join returning all rows from the left table and matching rows from the right; unmatched rights are NULL.

RIGHT OUTER JOIN

SQL join returning all rows from the right table and matching rows from the left; unmatched lefts are NULL.

FULL OUTER JOIN

SQL join returning all rows from both tables, with NULLs where matches are absent.

Bridge Table (Junction Table)

Intermediate table that breaks an M:N relationship into two 1:M relationships via composite keys.

Composite Key

Primary key made of two or more columns, often used in bridge tables.

Strong Entity

Entity that can exist independently of other entities; has its own primary key.

Weak Entity

Entity that depends on another entity for existence and often has a composite key.

Identifying Relationship

Strong relationship where child’s primary key contains part of the parent’s key.

Non-Identifying Relationship

Relationship where child’s primary key is independent of the parent’s key.

Participation (Mandatory vs Optional)

Whether an entity instance must (mandatory) or may (optional) participate in a relationship.

Recursive Relationship

Relationship where a table references itself (e.g., employee manages employee).

Derived Attribute

Attribute whose value is calculated from other stored values, e.g., Age from DateOfBirth.

Domain (Attribute Domain)

Set of valid values an attribute is allowed to take.

Simple Attribute

Attribute that cannot be subdivided further (e.g., ZipCode).

Composite Attribute

Attribute that can be split into subparts (e.g., FullName into First and Last).

Single-Valued Attribute

Attribute holding exactly one value per entity instance.

Multivalued Attribute

Attribute capable of holding multiple values per entity instance.

Subtype

A specialized entity that inherits attributes and the primary key from a supertype.

Supertype

A generalized entity whose primary key is inherited by its subtypes.

Discriminator

Attribute in the supertype that indicates which subtype a record belongs to.

Disjoint Category (D)

Subtype rule where a supertype instance can belong to only one subtype.

Overlapping Category (O)

Subtype rule where a supertype instance can belong to multiple subtypes.

Partial Completeness

Constraint where some supertype instances may have no subtype occurrence.

Total Completeness

Constraint where every supertype instance must appear in at least one subtype.

Select Statement

Basic SQL command starting with SELECT to retrieve data.

SELECT Keyword

SQL reserved word that begins a query and specifies columns to return.

FROM Keyword

SQL reserved word that identifies the source table of a query.

SQL (Structured Query Language)

Standard language for defining, manipulating, and querying relational databases.

Ubiquitous (in databases)

Describes data that can be accessed globally from any device, system, or location.

Pervasive (in databases)

Indicates data that is always present, persistent, and recoverable even after failures.

Data

Raw, unprocessed facts—text, numbers, or symbols—that have not yet been analyzed.

Information

Meaningful output created after data is processed, calculated, or aggregated.

Database

A structured collection of related data, often stored in tables and made shareable.

Database Management System (DBMS)

Software that manages a database by controlling access, enforcing security, and validating data.

Single-User Database

A locally installed database intended for use by one person at a time (e.g., MS Access).

Multi-User Database

A database accessed concurrently by multiple users, teams, or departments.

Centralized Database

Database stored in one physical location, offering strong control but limited scalability.

Distributed Database

Database whose data is spread across multiple sites for high availability and redundancy.

Cloud Database

Database hosted on cloud services (AWS, Azure, GCP) that scales on demand and is vendor-managed.

General-Purpose Database

Shared across departments to support varied business functions (e.g., employee database).

Discipline-Specific Database

Built for one functional area, such as a product BOM or medical records.

Operational Database

Supports day-to-day transactions like sales orders and shipping records.

Analytical Database

Stores historical data (data warehouse) for reporting, BI, and trend analysis.

Structured Data

Tabular data organized into rows and columns within RDBMS tables.

Unstructured Data

Data lacking a fixed schema (e.g., PDFs, images, audio, free-form text).

Semi-Structured Data

Data with partial organization, often tagged (XML, JSON, CSV).

Primary Key

A field or set of fields that uniquely identifies each row; cannot be NULL.

Foreign Key

A field in one table that references a primary (or unique) key in another table to enforce relationships.

Entity Integrity

Rule that primary keys must be unique and non-NULL.

Referential Integrity

Rule that a foreign-key value must match a valid row (or be NULL) in the referenced table.

Projection (Relational Algebra)

Operation that selects specific columns from a table.

Union (Relational Algebra)

Combines result sets of two queries, removing duplicates.

Intersect (Relational Algebra)

Returns only rows present in both result sets; equivalent to SQL INNER JOIN on shared fields.

One-to-Many (1:M) Relationship

One record in Table A relates to many in Table B (e.g., professor → classes).

Many-to-Many (M:N) Relationship

Many records in Table A relate to many in Table B; resolved with a bridge table.

Bridge / Junction Table

Table containing two foreign keys that breaks an M:N relationship into two 1:M relationships.