Database Management Systems (DBMS) Review Flashcards

40 practice flashcards covering core Database Management System concepts, SQL commands, ER diagrams, relational keys, Codd's rules, and database integrity constraints.

What is the formal definition of a Database Management System (DBMS)?

A DBMS is a collection of programs that enables users to create and maintain a database, providing controlled access to data.

What is the difference between raw data and a database?

Data consists of raw facts and figures (e.g., 'Rahul', '22'), whereas a database is an organised, shared collection of related data.

What core problem in file systems leads to inconsistent data when a student record is updated in students.txt but forgotten in fees.txt?

High Data Redundancy across multiple independent files.

How does a DBMS handle multi-step operations like bank transfers to ensure money is not lost during a crash?

It wraps operations in a transaction to guarantee Atomicity (from ACID properties), ensuring either all steps complete or none do.

What is the difference between database Schema and Instance?

Schema is the fixed overall design or blueprint of the database, whereas Instance is the actual data present at a specific point in time.

What are the three levels of Data Abstraction defined in the ANSI-SPARC architecture?

View / External Level, Logical / Conceptual Level, and Physical / Internal Level.

What is Physical Data Independence?

The ability to change physical storage structures or devices (e.g., moving to cloud storage) without altering the logical schema or user applications.

What is Logical Data Independence?

The ability to modify the logical schema (e.g., adding a new field) without breaking existing applications that rely on relevant parts of the database.

What does DDL stand for, and what is its primary role?

Data Definition Language; it defines or alters the structure and blueprint of database objects like tables.

What does DML stand for, and what is its primary role?

Data Manipulation Language; it inserts, retrieves, updates, or deletes actual records stored inside tables.

What is the purpose of Data Control Language (DCL)?

To manage access permissions and privileges, controlling who can view or modify database data.

Which DBMS component determines the most efficient path to fetch query results?

Query Processor / Query Optimizer.

What is the function of the Storage / Buffer Manager in a DBMS?

It controls physical reads and writes to disk and keeps frequently used data in fast memory for quick access.

What is stored inside the DBMS Data Dictionary / Catalog?

Metadata ('data about the data'), including table names, field types, and integrity constraints.

Why is 3-Tier Database Architecture widely used for modern applications like Amazon and Flipkart?

It adds an Application Server layer between the user client and database server, improving security, scalability, and maintainability.

How does a Distributed Database differ from a Centralized Database?

A Centralized Database stores all data on a single server, whereas a Distributed Database spreads data across multiple physical locations while appearing as one logical database.

In the DBMS query execution flow shown, what component creates the most efficient execution plan after syntax validation?

Query Optimizer

According to the DDL commands diagram, what is the result of executing TRUNCATE TABLE Student?

All records are removed from the Student table, but the table structure remains.

According to the DML queries guide, why must a WHERE clause be used carefully in UPDATE and DELETE statements?

To avoid accidentally updating or deleting unwanted rows across the table.

In the TCL reference diagram, what is the function of the SAVEPOINT command?

It sets a checkpoint within a transaction allowing partial rollback to that specific point instead of rolling back the entire transaction.

What is the main purpose of an Entity-Relationship (ER) Diagram?

To visually map and plan the database design conceptually before building it, requiring no coding.

What symbol represents an Entity in standard ER diagram notation?

Rectangle.

What does a Double Oval represent in an ER diagram?

A Multi-valued attribute (an attribute that can hold more than one value for a single entity).

What does a Dashed Oval signify in an ER diagram?

A Derived attribute, which is calculated from another attribute (e.g., Age derived from Date of Birth).

How is a Primary Key represented within an ER diagram?

By underlined text inside an oval.

What is a Weak Entity?

An entity that cannot be uniquely identified by its own attributes alone and depends on a strong entity (represented by a double rectangle).

Based on the ERD for B.B.A. and B.Sc. student records, what is the cardinality of the APPEARS IN EXAM relationship between STUDENT and EXAMINATION?

One Student to Many Examinations (1 to M), and One Examination to Many Students (1 to M).

In the academic administration ERD shown, what direct impact does an 'Attendance Fail' status have on a student?

The student becomes Not Eligible for Exam.

What is a Super Key in relational database theory?

Any set of one or more attributes that uniquely identifies a row in a table, which may contain extra, unnecessary attributes.

What is a Candidate Key?

A minimal super key, meaning no attribute can be removed from it without losing uniqueness.

What is a Primary Key?

The single candidate key chosen by the database designer as the main unique identifier; it cannot be NULL or repeated.

What is a Foreign Key?

An attribute in one table that references the primary key of another table to link related data.

Based on the keys illustration in the reference sheet, why are both {RollNo} and {Email} listed as Candidate Keys?

Because both are minimal attribute sets that can independently and uniquely identify a tuple in the relation.

What is Generalization in Enhanced ER (EER) modeling?

A bottom-up process that combines multiple lower-level entities sharing common traits into a single higher-level supertype entity.

What is Specialization in Enhanced ER (EER) modeling?

A top-down process that divides a general entity into more specific lower-level subtype entities based on distinguishing features.

How is a Many-to-Many (M:N) relationship converted into a relational table schema?

It is converted into a brand-new junction (linking) table containing foreign keys referencing the primary keys of both entities.

According to Codd's Rule 1 (Information Rule), how must all information in a relational database be represented?

Exclusively as values stored inside table rows and columns.

What does the Entity Integrity Constraint mandate?

The Primary Key of a table must be unique and can never contain a NULL value.

What does the Referential Integrity Constraint mandate?

A Foreign Key value must either match an existing Primary Key value in the referenced table, or be NULL.

According to the Extended ER Model reduction guide shown in the diagram, how is Option 2 (Single Table) implemented for Specialization/Generalization?

By creating one single table containing all attributes for all subtypes plus a Type column (e.g., Manager, Engineer, Salesperson).