DBMS Fundamentals and Relational Database Design

Posted on Sep 16, 2026 in Commercialization and Logistics

DDL vs DML: Definitions and Examples

Q1. Differentiate between DDL and DML Languages with example. [5 Marks]

Answer:

DDL (Data Definition Language) is used to define, create, and modify the structure of database objects such as tables, databases, and schemas. It mainly deals with the structure of the database.

  • Commands: CREATE, ALTER, DROP, TRUNCATE.
  • Example: CREATE TABLE Student(Roll_No INT, Name VARCHAR(20), Age INT);

DML (Data Manipulation Language) is used to insert, modify, delete, and retrieve data stored in database tables. It mainly deals with the data present inside the database.

  • Commands: INSERT, UPDATE, DELETE, SELECT.
  • Example: INSERT INTO Student VALUES(1, 'Amit', 20);

Differences:

  • DDL defines database structure, while DML manipulates data.
  • DDL works mainly on schema, while DML works on records.
  • DDL commands include CREATE, ALTER, DROP, and TRUNCATE; DML commands include INSERT, UPDATE, DELETE, and SELECT.

Thus, DDL defines the database structure whereas DML manipulates the data stored in it.

Categories of Data Models in DBMS

Q2. What are the different categories of Data Models? Explain each of them in detail. [5 Marks]

Answer:

A Data Model is a collection of concepts used to describe the structure of a database, relationships among data, and constraints on the data.

  1. High-Level / Conceptual Data Model: Describes the database from the user’s point of view. It represents entities, attributes, and relationships without considering physical storage details. Example: ER and EER models.
  2. Representational / Logical Data Model: Represents data in a form that can be implemented by a DBMS while hiding physical storage details. Examples: Relational, Hierarchical, and Network models.
  3. Physical Data Model: Describes how data is physically stored in the computer system, including files, indexes, storage structures, and access paths.

Flow: Conceptual Model → Logical Model → Physical Model.

Thus, data models provide different levels of abstraction for database design and storage.

Enhanced Entity-Relationship (EER) Model

Q3. Describe the key components of an Enhanced Entity-Relationship (EER) diagram and how it extends the traditional E-R model. [5 Marks]

Answer:

The Enhanced Entity-Relationship (EER) model is an extension of the traditional ER model used to represent complex real-world situations.

  • 1. Entity: Represents a real-world object, e.g., Student or Employee.
  • 2. Attribute: Represents a property of an entity, e.g., Name, Roll_No, and Age.
  • 3. Relationship: Represents an association between entities, e.g., Student enrolls in Course.
  • 4. Specialization: Divides a superclass into subclasses. Example: Employee → Manager, Engineer.
  • 5. Generalization: Combines similar subclasses into a higher-level superclass.
  • 6. Inheritance: A subclass inherits attributes and relationships from its superclass.
  • 7. Aggregation: Treats a relationship as a higher-level entity.

Thus, EER extends the traditional ER model by adding specialization, generalization, inheritance, and aggregation.

Converting ER Diagrams to Relational Tables

Q4. Identify and create the tables required to represent the given ER diagram in relational model. [5 Marks]

Answer:

The given ER diagram can be converted into the following relational tables. Entities are converted into tables and relationships are represented using foreign keys or separate relationship tables.

  • BRANCH(B_name, B_city, Assets)
  • ACCOUNT(Ac_no, Balance, B_name)
    Primary Key: Ac_no; Foreign Key: B_name
  • CUSTOMER(C_name, C_street, C_city)
  • LOAN(L_no, Amt, B_name)
    Primary Key: L_no; Foreign Key: B_name
  • DEPOSITOR(C_name, Ac_no)
  • BORROWER(C_name, L_no)

Therefore, the ER diagram is represented by six relations: BRANCH, ACCOUNT, CUSTOMER, LOAN, DEPOSITOR, and BORROWER.

Database Normalization: 1NF to BCNF

Q5. What is normalization? What are the conditions required for a relation to be in 1NF, 2NF, 3NF and BCNF? Explain with examples. [5 Marks]

Answer:

Normalization is the process of organizing data in a database to reduce data redundancy and eliminate insertion, deletion, and update anomalies.

  • 1NF: A relation is in First Normal Form if every attribute contains atomic (single) values and there are no repeating groups. Example: Instead of Phone = 9876, 8765 in one cell, store the phone numbers as separate atomic values.
  • 2NF: A relation is in 2NF if it is in 1NF and there is no partial dependency. Every non-key attribute must depend on the whole primary key.
  • 3NF: A relation is in 3NF if it is in 2NF and there is no transitive dependency. For example, Student_ID → Dept_ID and Dept_ID → Dept_Name creates a transitive dependency.
  • BCNF: A relation is in BCNF if for every functional dependency X → Y, X is a super key.

Memory: 1NF = Atomic values; 2NF = No partial dependency; 3NF = No transitive dependency; BCNF = Every determinant is a super key.

Lossless and Lossy Decomposition Explained

Q6. Define lossless and lossy decomposition in DBMS with suitable example. What are the conditions required for Lossless Decomposition? [5 Marks]

Answer:

Decomposition is the process of dividing a relation into two or more smaller relations.

  • Lossless Decomposition: A decomposition is lossless if the original relation can be obtained exactly by joining the decomposed relations. It does not lose information or produce spurious tuples. Example: Let R(A,B,C) with functional dependency A → B. Decompose it into R1(A,B) and R2(A,C). If R1 ♮ R2 gives the original R, the decomposition is lossless.
  • Lossy Decomposition: A decomposition is lossy if joining the decomposed relations produces extra/spurious tuples or results in loss of information.

Condition for Lossless Decomposition: If R is decomposed into R1 and R2, it is lossless if:
(R1 ∩ R2) → R1 OR (R1 ∩ R2) → R2

In simple words, the common attributes must determine all attributes of at least one decomposed relation.

Steps in Database Query Processing

Q7. How would you apply the steps of query processing to optimize a database query? [5 Marks]

Answer:

Query processing is the process of converting an SQL query into an efficient execution plan and executing it to obtain the required result.

  1. Parsing and Translation: The DBMS checks syntax and semantics and converts the query into an internal representation such as relational algebra.
  2. Query Decomposition: The query is divided into smaller operations such as selection, projection, and join.
  3. Query Optimization: Different execution plans are considered and the most efficient plan is selected.
  4. Cost Estimation: The cost of plans is estimated using CPU time, memory, and disk I/O.
  5. Query Execution: The selected execution plan is executed and the final result is returned.

Optimization techniques: Use indexes, perform selection and projection early, choose efficient joins, and reduce intermediate results.

Flow: SQL Query → Parsing → Decomposition → Optimization → Cost Estimation → Execution → Result.

Key Concepts in the Relational Model

Q8. Explain the differences between the following pairs of concepts in the relational model. [5 Marks]

Answer:

  • a) Primary Key vs Candidate Key:
    Primary Key: Selected key that uniquely identifies each record; a relation has one primary key.
    Candidate Key: Minimal key that can uniquely identify each record; a relation may have multiple candidate keys.
  • b) Super Key vs Candidate Key:
    Super Key: Set of attributes that uniquely identifies a record and may contain extra attributes.
    Candidate Key: Minimal super key with no unnecessary attributes.
  • c) Foreign Key vs Primary Key:
    Primary Key: Uniquely identifies records in its own table and is unique.
    Foreign Key: Refers to a key in another table and establishes a relationship; it may contain duplicate values.
  • d) Domain vs Attribute:
    Domain: Set of allowed values for an attribute.
    Attribute: Property or characteristic of an entity. Example: Age is an attribute and 1–100 can be its domain.
  • e) Relation vs Relational Schema:
    Relation: Actual table containing rows and columns/data.
    Relational Schema: Structure/design of a relation, including its attributes and constraints.

Example: STUDENT(Roll_No, Name, Age) is a schema, while the rows containing actual student data form the relation.