Database Management Systems and Relational Modeling Flashcards

Vocabulary flashcards covering core concepts in database management systems, relational modeling, SQL definition, ER diagrams, and key constraints.

Database

A collection of persistent and interrelated data that are shared by many users and applications, organized so data can be easily stored, managed, updated, and retrieved.

Data

Symbolically represented raw facts (i.e., numeric, textual, graphical, and often pictorial encoding) about things, people, and events observed in the real world.

Information

Data transformed (e.g., aggregated, sorted, clustered, sampled, & depicted) for a specific purpose.

Persistent Data

Data that resides on stable storage like a magnetic disk, lasts a long time, and retains relevance for intended operational usage.

Interrelated Data

Separate data units connected to provide a broader view or whole picture, containing both entities and relationships among entities.

Shared Data

Data accessible by multiple applications and users, potentially at the same time, serving as a common information repository.

File-Processing Approach

An information processing approach where an individual application program maintains its own separate data file.

Database Management System (DBMS)

A collection of components that support the creation, use, and maintenance of databases.

Enterprise DBMS

A DBMS supporting mission-critical information systems, running on powerful servers for large databases with many simultaneous users.

Desktop DBMS

A DBMS designed for small and end-user databases running on personal computers with limited transaction processing features.

Embedded DBMS

A DBMS residing in a separate system or device, like a PDA or phone, with low memory, processing, and storage requirements.

Table

A two-dimensional arrangement of data used to store collections of entities in a database.

Structured Query Language (SQL)

An industry standard database language used for defining tables, relationships among tables, integrity constraints, data manipulation, and authorization rights.

Query

A request for data submitted to a database to answer a specific question.

Nonprocedural Access

Database access where the user specifies what parts of a database to retrieve rather than how retrieval occurs.

Form

A graphical database tool and interface that supports convenient data entry and display.

Report

A database display component that enhances the appearance of data being displayed for printing or presentation.

View (Standard SQL)

A saved SQL definition in standard SQL used to represent a virtual table or customized data requirement.

Stored Query (Access)

A saved nonprocedural query definition within Microsoft Access used for form and report data requirements.

Procedural Language Interface

A feature combining procedural programming language code with nonprocedural access (SQL) for batch processing, customization, and performance.

Direct API Calls

A mode of connection directly linking a programming language to database software without middleware.

Middleware

Software layer such as ODBC or JDBC that connects a programming language (like Java) to database software.

Transaction

A unit of work that should be processed reliably without interference from other users and without loss of data due to failures.

Database Tuning

The process of monitoring, analyzing, and improving database system performance through physical structure selection or reorganization.

1st Generation Database Technology

Database orientation from the 1960s characterized by file orientation, proprietary file structures, and proprietary program interfaces.

2nd Generation Database Technology

Database orientation from the 1970s characterized by network navigation, hierarchies of related records, and standard program interfaces.

3rd Generation Database Technology

Database orientation from the 1980s characterized by relational orientation, non-procedural languages, optimization, and transaction processing.

4th Generation Database Technology

Database orientation from the 1990s–2000s characterized by object orientation, multi-media, active/distributed processing, XML, data warehousing, and cloud computing.

Data Warehouse (DWH)

A database that supports mid/long-range decision-making in an organization by storing and retrieving summarized data.

Cloud Computing (Database)

Web-based computing supporting on-demand and pay-per-use access for data and software with no fixed costs of ownership.

Big Data

High volumes of data generated from social networks, smartphones, auto technology, RFID tags, and digitized media requiring high-performance processing.

NoSQL

Non-relational database systems using simplified data models to achieve high performance with big data and cloud applications.

Data Independence

The separation of database identity from application programs, allowing the database definition to be changed without affecting related applications.

Three Schema Architecture

An architectural framework comprising three levels of database description (external, conceptual, internal) to achieve data independence.

External Level (User View)

The schema level where each group of users has a tailored view of the database, hiding irrelevant data and restricting access to sensitive information.

Conceptual Level (Logical View)

The schema level defining entity types and their relationships, capturing the overall logical meaning of the database structure.

Internal Level (Storage View)

The schema level representing physical storage aspects, defining files, data collections on hardware, and mappings from logical entities to physical storage files.

Database Administrator (DBA)

A super-technical role encompassing both technical and non-technical responsibilities to support IS/IT professionals and functional business users.

Data Administrator

A less technical, managerial position focused on high-level planning and information resource management across an organization.

Heading (Table)

The portion of a database table consisting of the table name and column names.

Body (Table)

The portion of a database table consisting of rows containing actual data.

Relation

The formal relational database term for a table or record type file.

Tuple

The formal relational database term for a row or record in a table.

Attribute

The formal relational database term for a column, field, or property of a table or entity.

Data Type

A specification that defines a set of values and permissible operations on those values for a column or attribute.

DECIMAL(n,m)

A numeric data type where n represents the total number of digits and m represents the number of digits below the decimal point.

INTEGER

A numeric data type represented by 32 bits.

SMALLINT

A numeric data type represented by 16 bits.

CHAR(n)

A fixed-length text data type occupying exactly n characters.

VARCHAR(n)

A variable-length text data type holding up to n characters and occupying up to n character spaces.

Superkey

A column or combination of columns containing unique values for each row in a table.

Candidate Key

A minimal superkey from which no column can be removed without destroying its uniqueness property.

Uniqueness Constraint (Candidate Key)

The requirement that no two rows of a table can have the same candidate key value.

Minimality (Irreducibility)

The property of a composite key meaning no proper subset of its components can uniquely identify a row.

Simple Key

A key that consists of a single attribute.

Composite Key

A key that consists of two or more attributes combined to form a unique identifier.

Primary Key

A designated candidate key chosen to uniquely identify rows in a table, which cannot contain null values in any component.

Foreign Key

A column or combination of columns in a child table whose values must match the primary key values of a parent (source) table or be null.

Alternate Key

A candidate key that was not selected as the primary key for a table.

Null

A special marker in a database field indicating that a value is unknown or inapplicable.

Parent Table

The source table in a relationship that contains the primary key referenced by a foreign key in another table.

Child Table

The referencing table in a relationship that contains the foreign key pointing to a parent table's primary key.

One-to-Many (1-M) Relationship

A connection between two tables where one row of a parent table can be referenced by many rows of a child table.

Many-to-Many (M-N) Relationship

A connection where rows in each table are related to multiple rows in the other table, implemented in relational models via an associative table.

Self-Referencing Relationship

A relationship where a foreign key in a table references the primary key of the very same table to represent hierarchical or recursive relationships.

Entity Integrity

An integrity rule stating that primary key values must be unique and cannot contain null values in any part.

Referential Integrity

An integrity rule stating that foreign key values must either match a primary key value in the referenced source table or be null.

Restrict (No Action)

A referential integrity action that blocks the deletion or update of a referenced row if related child rows exist.

Cascade

A referential integrity action that automatically performs the same deletion or update action on related child rows when a referenced parent row is modified or deleted.

Nullify (Set Null)

A referential integrity action that sets foreign key values in related child rows to null when the referenced parent row is deleted.

Default (Set Default)

A referential integrity action that sets foreign key values in related child rows to a predefined default placeholder value when the referenced row is deleted.

Data Model

A graphic representation of the data structures and relationships of complex real-world objects.

Entity Relationship Model (ERM)

A graphical data model capturing data as entity types, relationships, and attributes, usually displayed as an Entity Relationship Diagram (ERD).

Entity Instance

A single occurrence or distinguishable object resulting from assigning values to properties of an entity type.

Entity Type

A generic description or set representing a collection of distinguishable real-world objects of interest, depicted as a rectangle in ERDs.

Cardinality

Notation markers at the ends of relationship lines indicating the minimum and maximum participation/connectivity between entity occurrences.

Simple Attribute

An attribute that consists of a single atomic value, such as a last name.

Composite Attribute

An attribute composed of multiple related properties, such as a full name consisting of first, middle, and last names.

Single-Valued Attribute

An attribute that can store only a single value for any entity occurrence.

Multi-Valued Attribute

An attribute that can store multiple values for a single entity instance, such as multiple educational degrees.

Base Attribute

An attribute whose value is explicitly stored directly in the database rather than calculated.

Derived Attribute

An attribute whose value is computed or derived from other attributes in the database (e.g., total quantity).

Generalization Hierarchy

An ERD structure showing subset relationships between a supertype entity and one or more specialized subtype entities.

Supertype Entity

A generic entity type in a generalization hierarchy that contains shared attributes for all its specialized subtypes.

Subtype Entity

A specialized entity type in a generalization hierarchy containing attributes specific to a subset of the supertype entity.

Mandatory-1-to-Optional-1 Relationship

A 1-to-1 relationship configuration used to represent generalization hierarchies when specialized hierarchy symbols are unavailable in an ERD tool.

Crow's Foot ERD Type A

A Crow's Foot ERD variation where foreign keys are shown in the diagram and many-to-many relationships must be converted to associative entities.

Crow's Foot ERD Type B

A Crow's Foot ERD variation where foreign keys are hidden from the diagram view.

Chen's Notation for Entity

An Entity Relationship diagram convention representing entity types as rectangles.

Chen's Notation for Relationship

An Entity Relationship diagram convention representing relationships as diamonds.

Chen's Notation for Attribute

An Entity Relationship diagram convention representing attributes as ovals connected by lines.

Individuation of Entity Instances

The practice of uniquely tracking each individual physical unit (such as a motor vehicle VIN) versus tracking non-individuated bulk items.

Stable Primary Key

A primary key property stating that its assigned value should never change over time.

Single Purpose Primary Key

A primary key property stating that the key serves no functional purpose other than unique entity identification.

Minimum Cardinality

The lower bound of entity occurrences that must participate in a relationship (typically 0 for optional, 1 for mandatory).

Maximum Cardinality

The upper bound of entity occurrences that can participate in a relationship (typically 1 or M/many).

Associative Entity

An entity created to convert a many-to-many relationship into two one-to-many relationships in a relational schema.

Foreign Key Placement Rule (1-M)

The rule stating that in a 1-to-many relationship, the foreign key must be placed in the entity on the 'many' side of the cardinality.

Subtype Primary Key Rule

The rule dictating that every subtype entity's primary key must also be a foreign key referencing its supertype entity's primary key.

Relational Database Definition Notation

A shorthand text format for database schemas written as TableName(PKAttr, Attr1, Attr2, …) with primary keys designated.