1. Introduction
Database Management System (DBMS) is one of the most important subjects in Computer Science and Information Technology courses. Questions based on database design and normalization are very common in university exams.
In this blog post, we will solve Slip No. 1 – Question A from DBMS. The question focuses on designing a Relational Database (RDB) in Third Normal Form (3NF) using given entities, relationships, and constraints.
This explanation is written in simple English, making it easy for students, beginners, and exam aspirants to understand. By the end of this post, you will clearly know how to convert entities into tables, apply constraints, and write SQL code correctly.
2. Problem Statement
Slip No. 1 – Question A
Slip no1:Consider the following entities and their relationships.
Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Emp(eno ,ename ,designation ,salary, Date_Of_Joining)
Dept(dno,dname ,loc)
The relationship between Dept & Emp is one-to-many. Constraints: – Primary Key, ename should not be NULL, salary must be greater than 0.
3. Source Code
Before writing SQL code, let us understand the design:
Step 1: Normalization (3NF)
- Each table represents a single entity
- No repeating groups
- No partial or transitive dependency
- Relationship handled using Foreign Key
Step 2: Table Structure
Department Table
- dno → Primary Key
Employee Table
- eno → Primary Key
- dno → Foreign Key (references Dept)
SQL Source Code
CREATE TABLE Dept (dno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);
-- Create Employee Table
CREATE TABLE Emp (
eno INT PRIMARY KEY,
ename VARCHAR(50) NOT NULL,
designation VARCHAR(50),
salary DECIMAL(10,2) CHECK (salary > 0),
Date_Of_Joining DATE,
dno INT,
CONSTRAINT fk_dept
FOREIGN KEY (dno)
REFERENCES Dept(dno)
);Explanation:
- PRIMARY KEY uniquely identifies each record
- NOT NULL ensures employee name is mandatory
- CHECK (salary > 0) enforces valid salary values
- FOREIGN KEY maintains one-to-many relationship
- Tables are now in Third Normal Form (3NF)
5. FAQ Section
Q1. What is Third Normal Form (3NF)?
Answer:
A table is in 3NF if:
- It is already in 2NF
- There is no transitive dependency
- Every non-key attribute depends only on the primary key
Q2. Why is dno a foreign key in Emp table?
Answer:
Because one department can have many employees, dno in the Emp table connects employees to their department and maintains the one-to-many relationship.
Q3. Why is salary constraint important?
Answer:
The constraint salary > 0 ensures data validity and prevents invalid or negative salary values in the database.
Q4. Is this database design suitable for exams?
Answer:
Yes. This design follows:
- Proper normalization
- Correct constraints
- Clear entity relationship
It is ideal for university exams and practical assessments.
Q5. Can this question be asked in DBMS practical exams?
Answer:
Absolutely. Similar questions frequently appear in DBMS theory, practicals, and viva exams.