Database Management System - SE III

Vocabulary flashcards covering core DBMS concepts from ER models to SQL and DBMS architecture.

Schema

The overall design of a database, describing its structure, organization, and constraints (tables, columns, data types, relationships).

Database

A logically coherent collection of data and its meaning, managed and accessed by a DBMS.

Entity

An object in the real world that exists distinctly and is represented in a database.

Entity Set

A collection of entities of the same type that share the same properties.

Instance

The actual content (data) of a database at a particular point in time.

Physical Data Independence

The ability to change the physical storage of data without changing the logical schema.

Data Dictionary

Metadata about data, including definitions, schemas, and constraints; often stored as system catalogues.

Database Administrator (DBA)

A person who has central control over the DBMS, including configuration, security, and performance tuning.

Data Abstraction

Hiding the complexity of data from users by presenting different levels: physical, logical, and view.

Physical Level

The lowest level of data abstraction describing how data are actually stored on disk.

Logical Level

The level describing what data are stored in the database and how they are related.

View Level

The highest level providing user-defined views of the database, often a subset of data.

Data Independence

The ability to change the database schema at one level without affecting other levels or programs.

DDL (Data Definition Language)

SQL commands that define or modify the database schema (e.g., CREATE, ALTER, DROP, RENAME, TRUNCATE).

DML (Data Manipulation Language)

SQL commands that retrieve and modify data (e.g., SELECT, INSERT, UPDATE, DELETE).

DCL (Data Control Language)

SQL commands that control access to data (e.g., GRANT, REVOKE).

TCL (Transaction Control Language)

SQL commands that manage transactions (e.g., COMMIT, ROLLBACK).

Query Language

Subset of a DML used for retrieving data; often used interchangeably with DML in context.

ER Model

Entity-Relationship model used for database design, showing entities, attributes, and relationships.

Entity

A real-world object of interest to the database, represented in the ER model.

Attribute

Property of an entity; can be simple or composite, and can be single-valued or multi-valued.

Simple Attribute

An attribute that is not divided into smaller components.

Composite Attribute

An attribute that can be decomposed into smaller sub-attributes.

Single-Valued Attribute

An attribute that holds exactly one value for an entity.

Multivalued Attribute

An attribute that can hold multiple values for a single entity.

Relationship

An association among two or more entities in the ER model.

Relationship Set

A set of relationships corresponding to a relationship type among entity sets.

Role

Labels describing how entities participate in a relationship (optional in ER diagrams).

Cardinality

The count of entities in one side related to another: one-to-one, one-to-many, many-to-one, many-to-many.

One-to-One

Each entity in one set relates to at most one in the other set.

One-to-Many

One entity in one set relates to many in the other set.

Many-to-One

Many entities in one set relate to a single entity in the other set.

Many-to-Many

Multiple entities in both sets relate to each other.

Ternary Relationship

A relationship involving three or more entity sets.

Notation (ER Diagram) Diamond

The diamond shape is used in ER diagrams to denote a relationship.

Extended ER (EER) Model

An ER model that includes advanced constructs like specialization, generalization, and aggregation.

Specialization

Top-down partitioning of an entity set into subtypes with distinct attributes/relationships.

Generalization

Bottom-up combination of similar entity sets into a higher-level supertype.

Aggregation

Treating a relationship as a higher-level entity to model relationships between relationships.

ISA (Super Class-Sub Class)

A relationship indicating that a subclass inherits from a superclass (is-a relationship).

Superclass

The higher-level entity type in a generalization/specialization hierarchy.

Subclass

A specialized, lower-level entity type within a generalization hierarchy.

Subclass Discriminator

An attribute used to indicate membership of an instance in a particular subclass.

Participation Constraint

Whether instances of a superclass must participate in a subclass (mandatory vs optional).

Total Participation

Every instance of the superclass must be a member of some subclass.

Partial Participation

Some instances of the superclass may not belong to any subclass.

Disjoint Constraint

Subclasses cannot overlap in membership for a superclass (Disjoint vs Overlap).

Overlap Constraint

An instance can belong to more than one subclass of a superclass.

Relational Model

Data model that represents data as relations (tables) of tuples and attributes.

Relation/Table

A table in a relational database consisting of rows (tuples) and columns (attributes).

Tuple/Row

A single row in a relation representing one record.

Column/Attribute

A field in a relation representing a data property.

Primary Key

A chosen candidate key that uniquely identifies each row in a table.

Candidate Key

A minimal set of attributes that uniquely identify each tuple in a relation.

Super Key

Any set of attributes that uniquely identifies a tuple; may contain extra attributes.

Foreign Key

An attribute (or set) in one table that references the primary key of another table.

Referential Integrity

Consistency of references between tables; foreign keys must reference existing rows.

Normalization

Process of organizing data to reduce redundancy and improve data integrity; includes 1NF, 2NF, 3NF, BCNF.

1NF (First Normal Form)

Atomic attribute values; no repeating groups.

2NF (Second Normal Form)

1NF plus no partial dependency of non-key attributes on part of a candidate key.

3NF (Third Normal Form)

No transitive dependencies; non-key attributes depend only on keys.

BCNF (Boyce-Codd Normal Form)

A stricter version of 3NF with every determinant being a candidate key.

Weak Entity

An entity that cannot be identified uniquely by its own attributes alone and depends on a strong entity.

Strong Entity

An entity that has a primary key and can be identified independently.

Mapping ER to Relational

Converting ER diagrams into relational tables: each entity set and relationship set maps to a table; foreign keys implement relationships.

Composite Attribute Flattening

Breaking a composite attribute into its component attributes in a table.

Multivalued Attribute Table

Representing a multi-valued attribute with a separate table linking the entity to its values.

DML Commands (Examples)

SELECT, INSERT, UPDATE, DELETE; used to retrieve and modify data.

DDL Commands (Examples)

CREATE, ALTER, DROP, RENAME, TRUNCATE; used to define/modify schema.

DCL Commands (Examples)

GRANT, REVOKE; used to manage access privileges.

TCL Commands (Examples)

COMMIT, ROLLBACK; used to control transaction boundaries.

SET OPS (Set Operators)

UNION, INTERSECT, MINUS; combine results from two queries.

Group By

Clause to group rows sharing a value to apply aggregate functions.

Having

Filter conditions on groups formed by Group By.

Aggregate Functions

Functions that compute a single value from a set of rows (AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE).

String Functions

Operations on strings: LOWER, UPPER, INITCAP, CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, REPLACE.

Distinct

Clause to remove duplicates from query results.

Join

Operation to combine rows from two or more tables based on a related column.

Index

Data structure to speed up retrieval of rows by a search key.

Metadata

Data about data; stored in the data dictionary or system catalogs.

Data Files

Physical storage files where the database data reside.

Storage Manager

DBMS component responsible for managing data on storage devices.

Query Processor

Component that translates a user query into an efficient sequence of operations.

Data Manager

DBMS component that enforces constraints, security, concurrency, and integrity.

Notational Symbols in ER

Diamond denotes relationships; rectangles denote entity sets; ovals denote attributes.