Database Fundamentals and Entity-Relationship Modeling

Comprehensive vocabulary flashcards covering Database Fundamentals, DBMS Architecture, Modern Database Types, and Entity-Relationship Modeling notations and constraints.

Database

A collection of related data.

Database Management System (DBMS)

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

Database System

The DBMS software together with the data itself (Software + Database), sometimes also including the application programs.

File-Based System

A collection of programs that perform services for the end user, where each separate program defines and manages its own private data.

Limitations of File-Based Systems

Key drawbacks including separation and isolation of data, duplication of data, program-data dependence, and incompatible file formats.

Advantages of DBMS

Benefits including controlling data redundancy, restricting unauthorized access, data sharing, enforcing integrity constraints, avoiding inconsistency, and providing backup and recovery.

Disadvantages of DBMS

High financial cost (expensive) and potential incompatibility with other available DBMS software.

External Schema

The level of DBMS abstraction focused on what data different types of users see and how that data is presented to them.

Conceptual Schema

The logical model in DBMS architecture that specifies what data is represented (defining structures such as tables and integrity constraints) rather than how it is physically stored.

Internal Schema

The physical model in DBMS architecture that emphasizes how data are physically represented, structured, and implemented on storage disks.

Mappings

The processes of transforming requests and results between the different levels of abstraction in a three-schema database architecture.

Data Independence

The capacity to change the schema at one level of the database architecture without requiring modifications to the schema at the next higher level.

Conceptual Data Model

A high-level data model (such as an ERD) providing concepts close to how users perceive real-world information, including entities, attributes, and relationships.

Physical Data Model

A data model that describes how data is stored in the computer system and specifies the access paths required to search for and retrieve data.

Non-Relational Database (NoSQL)

A database system ('Not Only SQL') that eliminates traditional tables, rows, primary keys, and foreign keys in favor of storage models optimized for specific data types.

Four Types of NoSQL Databases

Document (or Document Oriented) Data Stores, Columnar (or Column Oriented) Data Stores, Key-Value Stores, and Graph Stores.

Vector Database

A database that stores information as numerical vectors (embeddings) representing the semantic meaning of text, images, or audio, querying them via similarity search (e.g., cosine similarity) to power AI applications like semantic search and RAG.

Data Warehouse

A consolidated central repository that integrates data extracted, transformed, and loaded (ETL) from multiple operational sources (such as CRM, Billing, ERP, and flat files) for reporting, analytics, and data mining.

Big Data

Data whose scale, distribution, diversity, and/or timeliness require new technical architectures and analytics, characterized by the three dimensions of Volume, Velocity, and Variety.

Centralized Database

A database environment where all enterprise data is stored at a single site and accessed from remote sites via communication links; it is easy to administer but suffers from uncertain data availability.

Distributed Database

A database environment for spread-out organizations where enterprise data is distributed across multiple computer systems, categorized into homogeneous and heterogeneous databases.

Entity-Relationship Diagram (ERD)

A conceptual modeling tool that identifies information required by a business by displaying relevant entity types and the relationships among them.

Entity

A real-world thing with an independent existence, which may have physical existence (e.g., a person or car) or conceptual existence (e.g., a job or university course).

Entity Instance

A single, particular occurrence of an entity type (such as an individual person or a specific car).

Weak Entity Type

An entity that does not possess a key attribute of its own and must participate in an identifying relationship type with an owner/identifying entity type.

Partial Key

A discriminator attribute within a weak entity type that, when combined with the key attribute of the owner entity, uniquely identifies a weak entity instance.

Attribute

A particular property or characteristic that describes an entity (e.g., name, age, address, and salary for an Employee).

Key Attribute

An attribute whose values are distinct (unique) for each entity instance and can be used to uniquely identify records.

Candidate Key

Each attribute or minimal set of attributes that can uniquely identify an entity when an entity type has multiple possible keys.

Composite Key

A key formed by the combination of two or more attribute values that together are distinct for each entity instance (e.g., ID and Application_no).

Multi-valued Attribute

An attribute that has a set of multiple values for the same single entity instance (e.g., multiple college degrees or phone numbers).

Composite Attribute

An attribute that can be divided into smaller subparts with independent meanings (e.g., Name divided into Fname, Minit, and Lname).

Derived Attribute

An attribute that is not stored directly but can be calculated or derived from another attribute or related entity (e.g., Age computed from BirthDate).

Simple (Single) Attribute

An attribute that is not divisible into smaller subparts and contains a single atomic value for a particular entity instance.

Relationship

A meaningful connection or association between entity classes.

Degree of a Relationship

The number of participating entity types involved in a relationship type.

Unary (Recursive) Relationship

A relationship of degree one, where the same entity type participates more than once in distinct roles (e.g., an Employee supervising other Employees).

Binary Relationship

A relationship of degree two that connects exactly two participating entity types (e.g., Employee working for Department).

Ternary Relationship

A relationship of degree three that connects three participating entity types simultaneously (e.g., Supplier supplying Part for Project).

Cardinality Ratio

A constraint specifying the maximum number of relationship instances that an entity can participate in (e.g., $1:1$, $1:N$, or $M:N$).

One-to-One ($1:1$) Cardinality

A relationship constraint where at most one entity instance on one side is associated with at most one entity instance on the other side (e.g., Employee manages Department).

One-to-Many ($1:N$) Cardinality

A relationship constraint where one entity instance on one side can associate with multiple instances on the other, but each instance on the second side associates with at most one instance on the first side.

Many-to-Many ($M:N$) Cardinality

A relationship constraint where multiple entity instances on one side can associate with multiple entity instances on the other side (e.g., Employee works on Project).

Participation Constraint

A constraint specifying the minimum number of relationship instances each entity must participate in (divided into total or partial participation).

Total Participation (Full Dependency / Mandatory)

A condition where every entity instance in the entity set must participate in at least one relationship instance, indicated graphically by a double line.

Partial Participation (Optional Dependency)

A condition where only some entity instances participate in the relationship while others do not, indicated graphically by a single line.

Double Rectangle Symbol

The standard ERD graphic notation representing a Weak Entity Type.

Double Diamond Symbol

The standard ERD graphic notation representing an Identifying Relationship Type that links a weak entity type to its owner entity.

Underlined Oval Symbol

The standard ERD graphic notation representing a Key Attribute.

Double Oval Symbol

The standard ERD graphic notation representing a Multivalued Attribute.

Dashed Oval Symbol

The standard ERD graphic notation representing a Derived Attribute.

Double Line Notation

The standard ERD graphic notation representing Total Participation (mandatory participation) of an entity in a relationship.

(min, max) Structural Constraint Notation

The ERD notation placed on a connecting line specifying the exact minimum and maximum number of relationship instances an entity participates in.

Branching Oval Hierarchy

The standard ERD graphic notation representing a Composite Attribute divided into smaller component attributes.