Database Management Systems: Concepts, Architecture, and Languages

A set of question-and-answer flashcards reviewing core database concepts, Three-Schema Architecture, Data Independence, SQL language categories (DDL, DCL, DML, TCL), types of database users, and NoSQL databases.

In DBMS, what is the definition of "Data"?

Data refers to facts that can be recorded and have meaning, such as a student's name, roll number, and marks.

What is a database?

An organised collection of related data stored for a specific purpose.

What is a Database Management System (DBMS), and what are examples of it?

A DBMS is software used to create, store, retrieve, update, and manage data in a database. Examples include MySQL, Oracle, and PostgreSQL.

What is meant by the term "mini-world" in database systems?

The part of the real world that is represented in a particular database (for example, a hospital database storing only data relevant to its own operations rather than every hospital in the country).

What are the six main purposes of a database system compared to traditional file-processing systems?

1. Reduce data redundancy, 2. Maintain data consistency, 3. Easy data access, 4. Data security, 5. Data sharing, and 6. Data backup and recovery.

If a student's phone number is updated in one file but the old number remains in three other files, what problem does this represent?

This represents the problem of data inconsistency caused by storing duplicate information (redundancy).

What is a database schema?

The overall description, design, or blueprint of a database that defines its structure, data types, and constraints (rules).

What is a database instance (or database state)?

The actual data stored in the database at a particular moment; it is also called a database state, occurrence, or snapshot.

What is the difference between an initial database state and a valid state?

The initial database state is the state of the database when it is initially loaded, whereas a valid state is a state that satisfies the structure and constraints of the database.

How do a database schema and a database instance differ in terms of content and frequency of change?

A database schema defines the structure, acts like a blueprint, and changes less frequently. A database instance contains actual stored data, acts like the contents, and changes frequently.

What are the three levels of the Three-Schema Architecture?

1. External Level (describes different user views), 2. Conceptual Level (describes overall logical structure), and 3. Internal Level (describes physical storage structure).

What is the primary function of the External Level in the Three-Schema Architecture?

It describes different views of the database for individual users, allowing different users to access different parts of the database.

What is described at the Conceptual Level of the Three-Schema Architecture?

The overall logical structure of the entire database, defining entities, relationships, and constraints.

What is described at the Internal Level of the Three-Schema Architecture?

The physical storage structure of the database, including storage files, physical organisation, and access paths such as indexes.

What are the three main components of a data model in DBMS?

1. Data Model Constructs (elements, data types, records, tables, and relationships), 2. Constraints (restrictions ensuring valid data), and 3. Operations (e.g., Insert, Delete, Update, and Retrieve).

What is a conceptual data model, and what is a popular example of it?

A high-level model that focuses on what data is required and how items are related during requirement gathering, without technical details. A popular example is the Entity-Relationship (ER) Model.

What are the three main components of the Entity-Relationship (ER) Model?

1. Entity (a real-world object, such as Student), 2. Attribute (a property of an entity, such as Student_ID or Name), and 3. Relationship (an association between entities, such as Student enrolls in Course).

What is a representational data model?

A data model that describes the logical structure of how data is organized without focusing on physical storage, with the Relational Model (tables) being the most popular example.

What technical details are included in a physical data model?

Column data types and sizes, primary keys and foreign keys, indexes, storage structures, and default values and constraints.

What is Data Independence in DBMS?

The ability to modify the schema at one level of the database without affecting the schema at the next higher level.

What is Logical Data Independence?

The ability to change the logical structure (tables, columns, relationships) without affecting external views or application programs.

What is Physical Data Independence?

The ability to change how data is physically stored without affecting the logical schema or user-facing applications.

What are the four main categories of DBMS languages?

1. DDL (Data Definition Language), 2. DCL (Data Control Language), 3. DML (Data Manipulation Language), and 4. TCL (Transaction Control Language).

What is the purpose of DDL (Data Definition Language), and which commands belong to it?

DDL defines the database's internal structure and pattern. Its commands include CREATE, ALTER, DROP, TRUNCATE, and RENAME.

What is the difference between the DROP and TRUNCATE commands in DDL?

DROP deletes entire database objects, whereas TRUNCATE removes all rows from a table without affecting the table's structure.

What is DCL (Data Control Language), and what do its commands do?

DCL controls user access permissions to the database. The GRANT command provides specific permissions/privileges, while the REVOKE command removes previously granted permissions/privileges.

What is DML (Data Manipulation Language), and what commands are included in it?

DML manages and manipulates data within a database. It includes SELECT, INSERT, UPDATE, DELETE, and MERGE.

In DML, what does the MERGE command do?

It performs an "upsert" operation, which updates existing records or inserts new ones if they do not exist.

What is TCL (Transaction Control Language), and what do COMMIT and ROLLBACK do?

TCL manages and controls transactions by grouping them into logical units. COMMIT saves all changes made during the current transaction, while ROLLBACK restores the database to its state at the last commit.

What are the key responsibilities of a Database Administrator (DBA)?

Defining schema, managing all three architecture levels, controlling access, ensuring security, monitoring backup/recovery, providing technical support, repairing hardware/software damages, and executing DCL commands using a system/superuser account.

Who are Naive / Parametric End Users in DBMS?

Unsophisticated users who lack DBMS knowledge but frequently use database applications in daily life to get desired results (for example, railway ticket booking users).

What is the role of a System Analyst in DBMS?

A System Analyst analyzes the requirements of parametric end users.

How do Sophisticated Users interact with a database?

They interact directly using SQL queries through the query processor to access and manipulate data without writing full application code (e.g., engineers or analysts).

What is the role of Database Designers?

They create the structure of a database (including tables, views, indexes, triggers, and constraints) and decide what data to store and how it relates based on user requirements.

Who are Casual Users (Temporary Users) in DBMS?

Users who access the database occasionally but require new information each time they access it (for example, middle- or higher-level managers).

What is a NoSQL database, and what are its key features?

A database that stores data using non-relational models instead of tables. It manages massive volumes of structured, semi-structured, and unstructured data, works with distributed tools like Docker, Kubernetes, and Apache Cassandra, and optimizes performance by reducing complex JOIN operations.