Relational DBMS is a core exam area because it combines theory, design and query practice. PSC questions often test ER diagrams, keys, relational schema, SQL joins, normalization up to 3NF/BCNF and file organization.

Engineering Definitions

Relation

Standard definition: A table-like structure consisting of tuples and attributes.

Exam meaning: Rows र columns भएको relational table।

Primary key

Standard definition: A minimal attribute set that uniquely identifies each tuple in a relation.

Exam meaning: हरेक row unique चिनाउने key।

Foreign key

Standard definition: An attribute set in one relation referencing candidate/primary key of another relation.

Exam meaning: अर्को table को key लाई reference गर्ने attribute।

Normalization

Standard definition: A process of organizing relations to reduce redundancy and update anomalies.

Exam meaning: Redundancy र insert/update/delete anomalies घटाउने design process।

Concept Teaching

Database design starts with real-world entities and relationships, then maps them into relational tables. Normalization is not decoration; it prevents inconsistent duplicated facts. SQL then retrieves and changes data using relational operations.

ER Model to Relational Model

ER model captures conceptual data requirements.

  • Entity becomes relation/table.
  • Attribute becomes column.
  • Relationship becomes foreign key or separate table depending cardinality.
  • 1:N relationship usually stores FK on N side.
  • M:N relationship becomes separate associative relation.
  • Weak entity depends on owner entity and partial key.

Keys and Constraints

Keys enforce identity and relationship correctness.

Key/constraint Meaning Exam point
Super key Uniquely identifies row May contain extra attributes
Candidate key Minimal super key No unnecessary attributes
Primary key Chosen candidate key Unique and not null
Foreign key References another table Referential integrity
Domain constraint Allowed values/type Data validity

SQL Essentials

SQL questions often test SELECT logic and joins.

  • DDL defines schema: CREATE, ALTER, DROP.
  • DML manipulates data: SELECT, INSERT, UPDATE, DELETE.
  • WHERE filters rows before grouping.
  • GROUP BY groups rows; HAVING filters groups.
  • INNER JOIN returns matching rows.
  • LEFT JOIN preserves left table rows even without match.
  • Aggregate functions include COUNT, SUM, AVG, MIN, MAX.

Normalization

Normal forms are based on dependency and redundancy.

Normal form Requirement Purpose
1NF Atomic values, no repeating groups Tabular clarity
2NF 1NF + no partial dependency on part of composite key Remove partial dependency
3NF 2NF + no transitive dependency of non-key on key Remove transitive dependency
BCNF Every determinant is candidate key Stronger dependency control

File Organization and Index Basics

Physical storage affects performance.

  • Heap file stores records unordered.
  • Sequential file stores records sorted by key.
  • Hash file uses hash function for direct access.
  • Index maps key values to record locations.
  • B+ tree index supports range queries well.
  • Dense index has entry for every search key; sparse index has fewer entries.

Engineering Mechanism

  • Identify entities, attributes and relationships.
  • Choose keys and map ER to relations.
  • Apply constraints for integrity.
  • Normalize schema by analyzing functional dependencies.
  • Write SQL queries using selection, projection, join and aggregation.
  • Choose file/index organization based on access pattern.

Diagrams / Models To Draw

  • Draw ER diagram with cardinalities.
  • Draw relation schema with PK/FK.
  • Draw normalization dependency diagram.
  • Draw SQL join Venn-style result idea.
  • Draw B+ tree index concept.

Formulas, Algorithms and Rules

  • Candidate key = minimal super key.
  • M:N relationship -> new relation with foreign keys.
  • 3NF condition: for FD X -> A, X is superkey or A is prime attribute.
  • BCNF condition: for every nontrivial FD X -> Y, X is superkey.
Concept Role Exam trap
ER model Conceptual modeling Not physical table only
Primary key Tuple identity Cannot be null
Foreign key Referential integrity References key in another relation
Join Combine related rows Outer join preserves unmatched side
Normalization Reduce redundancy Can increase joins
Index Fast lookup Costs storage/update overhead

Exam Point

  • State key type precisely.
  • For normalization, identify functional dependency first.
  • WHERE and HAVING are different.
  • M:N relationship needs separate table.
  • Index improves reads but can slow writes.

Worked Example

If Student(StudentID, Name, DeptID, DeptName) has StudentID -> DeptID and DeptID -> DeptName, then DeptName depends transitively on StudentID. Split into Student(StudentID, Name, DeptID) and Department(DeptID, DeptName) to reach 3NF.

Subjective Answer Pattern

  • Define relational and ER model.
  • Explain keys and constraints.
  • Map ER to relations.
  • Explain SQL clauses and joins.
  • Discuss normalization with examples.
  • Add file/index structure.

Common Engineering Mistakes

  • Confusing candidate key and primary key.
  • Putting M:N relationship as one foreign key only.
  • Using HAVING instead of WHERE for row filter.
  • Normalizing without functional dependencies.
  • Assuming index is always beneficial.

MCQ Revision

  • What is candidate key?
  • Which SQL clause filters groups?
  • M:N relationship maps to what?
  • Which normal form removes transitive dependency?
  • What is BCNF condition?
  • Which index supports range queries well?

Final Summary

  • Relational DBMS organizes data into relations with keys and constraints.
  • ER modeling captures conceptual structure.
  • SQL queries and updates data.
  • Normalization reduces redundancy and anomalies.
  • File and index structures affect performance.