DBMS · Question paper

CET-1 · 23 Aug 2025

DBMS (C2311C4) · Continuous Evaluation Test (CET)-I · 23 August 2025 · 30 marks · 10:30 am – 12:00 pm ITM (SLS) Baroda University · B.Tech CSE/CSN/IT/AI&DS · Semester III, Odd Semester 2025. Questions transcribed from the original paper. Solutions are written by CampusLoop; check them against your class notes.

Q1. Multiple choice questions [8]

1. What does DBMS stand for? a) Data Backup Management System · b) Database Management System · c) Digital Base Management System · d) Data Binary Management System

Answer: (b) Database Management System.

2. Which database model represents data in tables with rows and columns? a) Hierarchical model · b) Network model · c) Relational model · d) Object-oriented model

Answer: (c) Relational model.

3. What is a primary key in a database? a) A key that can have duplicate values · b) A key that uniquely identifies each record in a table · c) A key used only for indexing · d) A key that must be numeric

Answer: (b). A primary key is unique and NOT NULL for every row.

4. What does a double diamond represent in an ER diagram? a) Identifying relationship · b) Weak entity · c) Multi-valued attribute · d) Derived attribute

Answer: (a) Identifying relationship (the relationship that connects a weak entity to its owner). A weak entity itself is a double rectangle.

5. In an ER diagram, what does a rectangle represent? a) Attribute · b) Relationship · c) Entity · d) Weak entity

Answer: (c) Entity.

6. Which of the following represents a multi-valued attribute in an ER diagram? a) Single oval · b) Double oval · c) Dashed oval · d) Rectangular box

Answer: (b) Double oval. (Dashed oval = derived attribute.)

7. Which of the following is NOT a type of relationship in an ER diagram? a) One-to-One (1:1) · b) One-to-Many (1:N) · c) Many-to-Many (M:N) · d) None-to-One (0:1)

Answer: (d) None-to-One.

8. Which language is used to define the structure of a database? a) DML · b) DDL · c) DCL · d) TCL

Answer: (b) DDL (CREATE, ALTER, DROP, TRUNCATE).

Q2. Answer any THREE out of FOUR [12]

Q2.1 Explain the architecture of DBMS.

The standard answer is the three-level (ANSI/SPARC) architecture:

  • External level (view level): what each user or application sees. Different users can have different views of the same data (e.g. a student sees only their marks).
  • Conceptual level (logical level): the complete logical structure of the whole database: all entities, attributes, relationships and constraints, independent of storage.
  • Internal level (physical level): how data is actually stored on disk: files, indexes, record layouts, compression.

Two mappings connect the levels: external/conceptual and conceptual/internal. They give data independence:

  • Logical data independence: change the conceptual schema without rewriting external views.
  • Physical data independence: change storage (e.g. add an index) without changing the conceptual schema.

Draw the three stacked boxes (External views on top, Conceptual in the middle, Internal at the bottom, database on disk below) with the two mappings between them. See DBMS Study Guide › Unit 1.

Q2.2 Difference between Generalization and Specialization.

GeneralizationSpecialization
Bottom-up approachTop-down approach
Combines two or more lower-level entities with common attributes into one higher-level entitySplits one higher-level entity into lower-level sub-entities based on distinguishing attributes
Reduces the size of the schema (common attributes stored once)Increases the size of the schema (new sub-entities added)
Example: Car and Bike → VehicleExample: Employee → Engineer, Manager
Higher-level entity must have lower-level entitiesLower-level entity inherits all attributes of the higher-level entity

Both are drawn with the ISA triangle in the ER diagram.

Q2.3 Explain types of integrity constraints.

Integrity constraints are rules that keep data correct and consistent.

  1. Domain constraint: every value of an attribute must come from its domain (data type, range, format). Example: age INT CHECK (age >= 17).
  2. Entity integrity: the primary key cannot be NULL (and must be unique).
  3. Referential integrity: a foreign key value must either match a primary key value in the referenced table or be NULL. Example: Enrol.roll_no must exist in Student.roll_no.
  4. Key constraint: candidate keys must be unique (UNIQUE).
  5. NOT NULL / CHECK constraints: a value is mandatory, or must satisfy a condition.

Q2.4 Write advantages of DBMS.

  1. Controls data redundancy (data stored once, shared).
  2. Removes inconsistency (one copy, so no conflicting values).
  3. Data sharing among many users and applications.
  4. Enforces integrity constraints.
  5. Security: users get only the access they are authorised for.
  6. Backup and recovery after failures.
  7. Concurrency control: many users at once without corrupting data.
  8. Data independence (logical and physical).
  9. Easy querying with SQL; faster application development.

Q3. Answer any TWO out of THREE [10]

Q3.1 Explain types of keys with example.

Use the table Student(RollNo, EnrolNo, Email, Name, DeptID).

  • Super key: any set of attributes that uniquely identifies a row. {RollNo}, {RollNo, Name}, {EnrolNo, Email}.
  • Candidate key: a minimal super key (no extra attributes). {RollNo}, {EnrolNo}, {Email}.
  • Primary key: the candidate key chosen to identify rows. RollNo.
  • Alternate key: candidate keys not chosen as primary key. EnrolNo, Email.
  • Foreign key: an attribute that refers to the primary key of another table. DeptID → Department(DeptID).
  • Composite key: a key made of two or more attributes. Enrolment(RollNo, CourseID).

Q3.2 Design a database for a hospital with patients, doctors, and a log of tests/examinations per patient.

Entities and attributes

  • Patient(patient_id, name, dob, gender, phone, address)
  • Doctor(doctor_id, name, specialization, phone)
  • Test(test_id, patient_id, doctor_id, test_name, test_date, result) (weak entity in some designs; here given its own key)

Relationships

  • A patient is treated by one or more doctors; a doctor treats many patients → M:N Treats(patient_id, doctor_id, since).
  • A patient has many tests; each test belongs to one patient → 1:N.
  • Each test is ordered/conducted by one doctor → N:1.

ER diagram (describe/draw): rectangles Patient, Doctor, Test; diamonds Treats (M:N between Patient and Doctor), Undergoes (1:N Patient → Test), Conducts (1:N Doctor → Test).

Relational schemas

Patient(patient_id PK, name, dob, gender, phone, address)
Doctor(doctor_id PK, name, specialization, phone)
Treats(patient_id FK→Patient, doctor_id FK→Doctor, since, PK(patient_id, doctor_id))
Test(test_id PK, patient_id FK→Patient NOT NULL, doctor_id FK→Doctor, test_name, test_date, result)

Constraints

  • Primary keys: patient_id, doctor_id, test_id, and (patient_id, doctor_id) for Treats.
  • Referential integrity on every FK; ON DELETE CASCADE from Patient to Test (a test log has no meaning without the patient).
  • test_date NOT NULL; gender IN ('M','F','O'); phone UNIQUE.

Q3.3 Write SQL queries for the table TRANSACT

TRANSACT(TRNO, ANO, AMOUNT, TYPE, DOT) with 5 rows (T001–T005).

  1. Display details of all transactions of type Withdraw.
SELECT * FROM TRANSACT WHERE TYPE = 'Withdraw';
  1. Count total number of ANO.
SELECT COUNT(ANO) FROM TRANSACT;               -- 5 rows
SELECT COUNT(DISTINCT ANO) FROM TRANSACT;      -- 3 distinct accounts (101, 102, 103)
  1. Create table Customer from TRANSACT with all the columns.
CREATE TABLE Customer AS SELECT * FROM TRANSACT;
  1. List total AMOUNT from TRANSACT (the rest of this question is cut off in the scan).
SELECT SUM(AMOUNT) AS total_amount FROM TRANSACT;   -- 20500