DBMS Fundamentals MCQs: 12 Solved Questions with Explanations

DBMS questions often look like recall, but close terms such as schema, instance, subschema, data dictionary and internal level create distractors. Attempt these 12 practice MCQs before reading each explanation, then connect the correct option to the CollegeDB example. For focused practice on data models, ANSI-SPARC levels, catalogues and metadata, use DBMS Data Models, Architecture and Components MCQs; this set instead spans DBMS functions, DDL, file-system limits, schema versus instance, and cardinality.

Build one mini database before solving the MCQs

A database is organised data; a DBMS stores, changes, retrieves, protects and constrains it. The mini database uses this schema:

STUDENT(roll_no INT PRIMARY KEY, name VARCHAR(30), dept VARCHAR(10))

Its current instance contains (101, 'Asha', 'CSE') and (102, 'Ravi', 'ECE'). The schema is the design; the instance is the rows at one moment. Each row is a tuple. This instance has cardinality 2 (rows) and degree 3 (columns). The data dictionary stores metadata such as roll_no: INT, primary key and name: VARCHAR(30).

Add ENROLMENT(roll_no INT, course_id VARCHAR(8)), containing (101, 'DBMS01') and (102, 'CN01'). Its roll_no is a foreign key to STUDENT, letting the DBMS reject an enrolment for an unknown student. Entities, attributes, relationships and constraints belong to the conceptual level; page numbers, file placement and index blocks belong to the internal level. An application may see an external view with only needed columns and rows.

Questions 1-3: DBMS purpose, DDL and schema

Question 1: TPSC 2025, Assistant Programmer

Which of the following is the main function of a DBMS ?

• A. Data storage

• B. Data manipulation

• C. Data retrieval

• D. All of these

Answer: D. All of these.

Store (101, 'Asha', 'CSE'), manipulate it with UPDATE STUDENT SET dept = 'AI' WHERE roll_no = 101, and retrieve it with SELECT name FROM STUDENT WHERE roll_no = 101, which returns Asha. One DBMS performs all three, so A, B or C alone is incomplete.

Question 2: BEL 2023, Probationary Engineer

Database designers use _____ to define the structure of a database (database scheme) which typically includes definition of all data elements of the database and organization of data elements into records, tables etc.

• A. Query by example

• B. Data Manipulation Language

• C. Find Command Language

• D. Data Definition Language

Answer: D. Data Definition Language.

CREATE TABLE STUDENT (roll_no INT PRIMARY KEY, name VARCHAR(30), dept VARCHAR(10)); defines the table, columns, types and key, so it is DDL. INSERT INTO STUDENT VALUES (101, 'Asha', 'CSE'); changes the instance, so it is DML.

Question 3: TPSC 2025, Programmer

In a relational database, which of the following describes the structure of data ?

• A. Schema

• B. Tuple

• C. Index

• D. Query

Answer: A. Schema.

STUDENT(roll_no, name, dept) is the schema. (101, 'Asha', 'CSE') is a tuple, an index is an access structure, and a query acts on data. Schema alone means structure.

Questions 4-5: why file systems are not a DBMS

Question 4: Bihar STET 2025

Which of the following is not a limitation of file system?

• A. Data Redundancy

• B. Data Inconsistency

• C. Data dependence

• D. Storing Space

Answer: D. Storing Space.

students.csv stores 101,Asha,9876500000, while fees.csv has 101,Asha,9123400000. Repetition is redundancy, mismatch is inconsistency, and fixed-column code shows data dependence. Storage is required, so Storing Space is not the named limitation.

Question 5: UGC NET 2022, December

What are the drawbacks of using file systems to store data?

A. Data inconsistency

B. Difficulty in accessing data

C. Data isolation

D. Lack of atomicity of updates

Choose the correct answer from the options given below.

• A. A, B, only

• B. B, C, D only

• C. A, B, C only

• D. A, B, C, D

Answer: D. A, B, C, D.

A is the phone mismatch. B means custom code replaces SELECT. C means student and enrolment facts use unrelated formats. For D, transfer 100 from A1 = 1000 to A2 = 500. A crash after writing A1 = 900, before A2 = 600, leaves a partial update. All four are drawbacks.

Questions 6-8: three levels, data independence and application views

Question 6: NVS 2019

Which of the following represents the capacity to change the conceptual schema without having to change external schemas or application programs?

• A. Virtual data independence

• B. Hierarchical data independence

• C. Physical data independence

• D. Logical data independence

Answer: D. Logical data independence.

Add email VARCHAR(50) to STUDENT while keeping CSE_STUDENT_VIEW(roll_no, name) unchanged. That conceptual-to-external insulation is logical data independence. Moving rows from heap page P7 to indexed pages P12 and P13 is physical independence.

Question 7: KVS 2017

Conceptual level, Internal level and External level are three components of the three-level RDBMS architecture.
 Which of the following is not part of the conceptual level?

• A. Storage dependent details

• B. Entities, attributes, relationships

• C. Constraints

• D. Semantic information

Answer: A. Storage dependent details.

STUDENT, ENROLMENT, their attributes, relationship and foreign-key constraint are conceptual. Heap page P7, record layout and the B+ tree are internal, so A is outside the conceptual level.

Question 8: ISRO 2007

A view of database that appears to an application program is known as

• A. Schema

• B. Subschema

• C. Virtual table

• D. Index Table

Answer: B. Subschema.

The schema has roll_no, name and dept, but the application sees CSE_STUDENT_VIEW(roll_no, name) and (101, 'Asha'). That external description is its subschema; a virtual table can implement it, but is not the architecture term.

Questions 9-12: metadata, design order, instance and cardinality

Question 9: UP Police 2013, Paper 2 - Subject Oriented (Shift I)

Information about the structure of Database is stored in

• A. Data File

• B. Data Dictionary

• C. Data Components

• D. Data Model

Answer: B. Data Dictionary.

A data file holds (101, 'Asha', 'CSE'). The dictionary holds metadata: table STUDENT; column roll_no; type INT; constraint PRIMARY KEY; and column name, type VARCHAR(30). A data model gives general rules; the dictionary records the implemented structure.

Question 10: UGC NET 2024, August

Arrange the following phases of database design in the correct order:

(A) Physical Design

(B) Conceptual Design

(C) Logical Design

(D) Requirement Analysis

Choose the correct answer from the options given below:

• A. (B), (D), (A), (C)

• B. (C), (A), (B), (D)

• C. (D), (B), (C), (A)

• D. (A), (D), (C), (B)

Answer: C. (D), (B), (C), (A).

Requirements identify students, enrolments and the unknown-roll-number constraint. Conceptual design models the entities and relationship. Logical design produces STUDENT(roll_no PK, name, dept) and ENROLMENT(roll_no FK, course_id). Physical design selects a B+ tree and pages P7, P8. Thus D → B → C → A.

Question 11: BPSC 2024, NB

What is an Instance of a Database?

• A. The state of the database system at any given point of time

• B. The entire set of attributes of the Database put together in a single relation

• C. The initial values inserted into the Database immediately after its creation

• D. More than one of the above

• E. None of the above

Answer: A. The state of the database system at any given point of time.

At 10:00, the instance has (101, 'Asha', 'CSE') and (102, 'Ravi', 'ECE'). At 10:05, insert (103, 'Meera', 'CSE'). The schema stays STUDENT(roll_no, name, dept), but the instance changes from two rows to three: it is the state at one moment.

Question 12: UP Police 2023, Paper 2 - Subject Oriented

What does the term “Tuple Cardinality” refer to in a database ?

• A. The number of keys in a table

• B. The number of rows in a table

• C. The number of tables in a database

• D. The number of columns in a table

Answer: B. The number of rows in a table.

Before the Question 11 insert, STUDENT has two tuples, (101, 'Asha', 'CSE') and (102, 'Ravi', 'ECE'). Thus cardinality is 2; three columns make degree 3. Keep cardinality = rows = 2 beside degree = columns = 3.

A worked CollegeDB snapshot links the answers to database concepts

Each answer is justified with CollegeDB evidence.

Check atomicity fully. Initially, ACCOUNT(A1)=1000 and ACCOUNT(A2)=500, total 1500. Subtract 100 and add 100: 900 + 600 = 1500. A crash after only the first write gives 900 + 500 = 1400, losing 100. This partial state explains Question 5.

If CollegeDB cannot justify an answer, revise that term before later topics.

Diagnose the traps, score the set and choose the next step

Use these study thresholds, not official cutoffs:

• 10-12 correct: move to mixed questions in the DBMS MCQs set.

• 7-9 correct: redraw the three-level diagram and redo Questions 6-9.

• 0-6 correct: read the DBMS subject explainer, rebuild the CollegeDB glossary, and reattempt all 12 after one day.

Use the GATE Test Series for timed practice and browse related material through GATE CS Exam Preparation.

The short version

A DBMS manages data. A schema defines structure, an instance is the current state, subschemas serve applications, the data dictionary stores metadata, and cardinality counts rows. For structured coverage, use GATE Guidance by Sanchit Sir. Hide the answers, reattempt the set, and write the decisive term before checking.