DBMS Review Flashcards

Flashcards for DBMS (Database Management Systems) review, focusing on key vocabulary and concepts from the lecture notes.

Data

A collection of raw, unprocessed facts and figures, such as text, measurements, observations, and symbols, that holds no meaning until processed and analyzed.

Quantitative Data

Numerical data that can be measured and expressed in numbers, such as weight, volume, or cost.

Qualitative Data

Descriptive data that cannot be measured numerically, such as color, gender, or name.

Information

Data that has been processed, organized, and structured to provide meaning and aid in decision-making by giving context to raw data.

Database

An organized system or electronic platform where data is stored, making it easy to retrieve, manage, and update.

DBMS

A Database Management System is a software system used to manage databases, allowing users to store, retrieve, modify, and delete data efficiently.

File System

A traditional method of storing data in separate files manually, contrasting with the organized approach of a DBMS.

Data Redundancy

The drawback of file processing systems where the same data is stored in multiple files, leading to duplication and conflicting values.

Hierarchical Data Model

A data model where data is organized in a tree-like structure with parent-child relationships, suitable for simple, predefined paths.

Network Data Model

A more flexible data model than the hierarchical model, organizing data using graphs and allowing many-to-many relationships.

Relational Data Model

A data model where data is organized in tables with rows and columns, accessed using SQL, and widely used in systems like MySQL and Oracle.

Physical Level (Internal Level)

The lowest level of DBMS architecture, describing how data is stored physically in memory using low-level data structures like indexes and B+ Trees.

Logical Level (Conceptual Level)

The middle level of DBMS architecture, describing what data is stored and the relationships, handled by a DBA to ensure simplicity and consistency.

View Level (External Level)

The highest level of DBMS architecture, providing customized views to different users for security, simplicity, and personalization.

Instance

The data in the database at a specific moment, representing a snapshot of the database.

Schema

The overall design or structure of the database, which does not change frequently and includes physical, logical, and view schemas.

DDL (Data Definition Language)

A database language used to define the schema, including commands like CREATE, ALTER, and DROP, and defining constraints such as primary and foreign keys.

DML (Data Manipulation Language)

A database language used to handle data, including commands like SELECT, INSERT, UPDATE, and DELETE, with SQL being a part of DML.

Database Administrator (DBA)

An individual responsible for schema definition, storage and access method setup, authorization, access control, and routine maintenance of a database.

Tier-1 (T1) Architecture

A DBMS application architecture where all components (client, database, application) are on the same machine.

Tier-2 (T2) Architecture

A DBMS application architecture using a client-server structure where the client sends SQL queries to the database server.

Tier-3 (T3) Architecture

A three-tier architecture (Client -> App Server -> DB Server) for DBMS applications, best for web applications, providing scalability and security.

ER Model

A high-level data model based on a perception of a real world that consists of a collection of basic objects, called entities and of relationships among these objects.

Entity

A thing or object in the real world that is distinguishable from all other objects and can be uniquely identified by a primary key.

Weak Entity

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

Entity Set

A set of entities of the same type that share the same properties or attributes.

Attribute

A property or characteristic of an entity, represented by a value from its domain.

Simple Attribute

An attribute that cannot be divided further into subparts, such as a customer's account number.

Composite Attribute

An attribute that can be divided into subparts or other attributes, such as a person's name.

Single-Valued Attribute

An attribute that can only have one value, such as a student ID or loan-number.

Multi-Valued Attribute

An attribute that can have more than one value, such as a phone-number or nominee-name.

Derived Attribute

An attribute whose value can be derived from the value of other related attributes, such as age.

NULL Value

A value that an attribute takes when an entity does not have a value for it, indicating 'not applicable' or 'unknown'.

Relationship

An association among two or more entities, such as a person having a vehicle or a customer borrowing a loan.

Unary Relationship

A relationship in which only one entity participates, such as an employee manages employee.

Binary Relationship

A relationship in which two entities participate, such as a student taking a Course.

Relationship Constraints

Rules that specify the number of entities to which another entity can be associated via a relationship (mapping cardinality) and whether all entities must participate (participation constraints).

Specialization (in EER)

The process of dividing a single entity set into multiple sub-entity sets based on specific attributes or roles.

Generalization (in EER)

The process of combining two or more entity sets that share common features into a higher-level entity set.

Attribute Inheritance (in EER)

Lower-level entities (subclasses) inherit attributes of the higher-level entities (superclass).

Aggregation (in EER)

Abstraction concept used when a relationship acts as an entity itself.

Relation (Table)

A table with rows and columns, representing a specific type of entity

Tuple

A row in a table, representing a single data point or record.

Attribute

A column in a table, representing a property or characteristic of the entity.

Relation Schema

Describes the structure of a table: its name and its attributes.

Degree

Number of attributes in a relation (i.e., number of columns).

Cardinality

Number of tuples (i.e., number of rows) in a relation.

Super Key

Any combination of attributes that can uniquely identify a tuple.

Candidate Key

Minimal super key. Contains no redundant attributes.

Primary Key (PK)

A chosen candidate key. Must be unique and not NULL.

Foreign Key (FK)

Used to establish a relationship between two tables. References primary key of another table.

Domain Constraint

Defines the data type and range of values for an attribute.

Entity Integrity Constraint

Every relation must have a primary key. No attribute of the primary key can be NULL.

Referential Integrity Constraint

Maintains valid references between tables. A FK must refer to an existing PK in another table or be NULL. Ensures no orphan records in child table.

Multivalued Attributes

Attributes that can hold multiple values (e.g., phone numbers). Create a new table for each multivalued attribute.

Relational Algebra

A procedural query language used to query relational database

Tuple Relational Calculus

Queries are expressed using variables that range over tuples.

Domain Relational Calculus

Queries are expressed using variables that range over attributes (domains).

Embedded SQL

SQL code embedded within a host programming language (like C, Java, etc.).

PL/SQL (Procedural Language/SQL)

Oracle’s procedural extension to SQL, allowing developers to write complex queries and control structures (loops, conditions) in SQL.

Query Processing

The process of transforming an SQL query into an efficient execution plan.

Query Optimization

Involves choosing the most efficient way to execute a query.

Normalizaiton

Normalization is the process of structuring a relational database to reduce data redundancy and improve data integrity.

Functional Dependency

A functional dependency expresses a relationship where one set of attributes uniquely determines another set.

Transaction

A transaction is a logical unit of work that consists of one or more SQL operations.

ACID Properities

ACID properties are essential to guarantee reliable processing in a database system. They include: Atomicity, Consistency, Isolation, Durability

Serializability

Serializability ensures the consistency of a database when multiple transactions are executed concurrently.

Concurrency Control

Goal: To allow multiple transactions to execute safely and efficiently without interfering with eachother, ensuring isolation and consistency.

Database Recovery

Recovery ensures that the database is in a consistent state after a failure. It brings the DB back to the last consistent state.

Data Storage

Data storage refers to how and where the data is physically stored — in files, blocks, or pages — usually on disks (like HDD, SSD).

Index

An index is like a book index — it helps you find data faster by pointing to its location.

Hashing

Hashing is a technique used in DBMS to quickly locate data in a table using a hash function.

CAP Theorem

The CAP Theorem (also called Brewer's Theorem) is a principle that applies to distributed databases that states that: A distributed system can guarantee only two out of the following three properties at the same time: 1. Consistency (C) 2. Availability (A) 3. Partition Tolerance (P)

NoSQL

NoSQL Database is a non-relational Data Management System, that does not require a fixed schema.

MongoDB

MongoDB is a document-oriented NoSQL database used for high volume data storage.

Apache Cassandra

Apache Cassandra is a distributed, wide-column NoSQL database designed for handling large volumes of data across many servers with high availability and no single point of failure.

Apache HBase

Apache HBase is a distributed, column-oriented NoSQL database built on top of Hadoop HDFS. It's modeled after Google’s Bigtable as well and is optimized for random, real-time read/write access to big data.

Redis

Redis (REmote DIctionary Server) is an in-memory key-value data store known for ultra-fast performance.