INT302: Database System Concepts - Flashcards

Vocabulary practice flashcards covering basic database terminology, architectures, data models, ER diagram notation, and fundamental SQL syntax.

Data

Known facts that can be recorded and have an implicit meaning.

Database

A collection of related data.

DBMS (Database Management System)

A software package or system to facilitate the creation and maintenance of a computerized database.

Database System

The DBMS software together with the data itself (and sometimes the applications).

Data Model

A set of concepts that describes three main components: Structure (entities, attributes, and relationships), Operations (mechanisms for retrieving and updating data), and Constraints (restrictions on valid data).

High-level Conceptual Data Model

A category of data model that establishes entities, attributes, and relationships (like an ER Diagram), focusing on what is represented close to how users perceive data.

Physical Data Model

A low-level data model that describes details of how data is stored and searched, such as indexes, data types, and partitions.

Relational Data Model

An implementation model that uses collections of tables where each table has columns (attributes).

Hierarchical Data Model

An implementation model where data is organized into a tree-like structure (parent/child) where a parent has many children, but a child has only one parent.

Network Data Model

An implementation model represented by collections of records and relationships represented by links or pointers.

Database Schema (Intension)

The description of the database structure, types, and constraints, which changes very infrequently.

Database State (Instance / Extension)

The actual data stored in a database at a particular moment in time, which changes every time the database is updated.

External Level (Three-Schema Architecture)

The architecture level where external schemas describe various user views.

Conceptual Level (Three-Schema Architecture)

The architecture level where conceptual schemas describe the structure and constraints for the whole database.

Internal Level (Three-Schema Architecture)

The architecture level where internal schemas describe physical storage structures and access paths.

Logical Data Independence

The capacity to change the conceptual schema without having to change external schemas or application programs.

Physical Data Independence

The capacity to change the internal schema (like adding an index) without having to change the conceptual schema.

Database Constraints

Mandatory rules and restrictions placed on valid data within a database that must be enforced by the DBMS at all times to ensure data integrity, accuracy, and validity.

CRUD Operations

Mechanisms used to manipulate, update, and retrieve data: Create (Insert), Read (Retrieve), Update, and Delete.

Weak Entity

An entity that does not have its own primary key and depends entirely on an owner entity to exist, represented by double rectangles in Chen's notation (e.g., PROMOTION TIER).

Chen's Notation Symbols

In Chen's Notation for ER diagrams: Rectangles represent Strong Entities, Double Rectangles represent Weak Entities, Diamonds represent Relationships, Double Diamonds represent Identifying Relationships, Ovals represent Attributes, Solid Underlines represent Primary Keys, Dashed Underlines represent Partial Keys, and Double Lines represent Total participation.

Retail Chain Product & Promotion ER Diagram

An ER diagram modeling strong entities (CUSTOMER, PRODUCT, PROMOTION), weak entity (PROMOTION TIER), and relationships including Buys, Applies To, Has Tiers, and recursive Bundle/Component.

Cartesian Product

The resulting query combination when the WHERE clause is omitted, joining every tuple from table A with every tuple from table B.

LIKE Operator

An SQL comparison operator used to match substring patterns, where % replaces an arbitrary number of characters and _ replaces exactly one character.

UNION Operator

An SQL set operation that combines results from two union-compatible queries (representing A OR B) and automatically eliminates duplicates.

INTERSECT Operator

An SQL set operation that returns only tuples present in both union-compatible query results (representing A AND B) and eliminates duplicates.

MINUS / EXCEPT Operator

An SQL set operation that returns tuples in the first query result that are NOT in the second query result (representing A BUT NOT B).

Union Compatibility

The condition where two SELECT statements select the exact same attributes in the exact same order so set operations can be applied.

IS NULL / IS NOT NULL

SQL comparison operators required to test for missing or undefined values, as = NULL cannot be used.