DATABASE MANAGEMENT

SYSTEMS
Unit Notes & Important Questions

Topics Covered
✦ Relational Model: Basic Concepts, Attributes & Domains
✦ Codd's 12 Rules
✦ Relational Integrity & Referential Integrity
✦ Features of Good Relational Design
✦ Normalization: 1NF · 2NF · 3NF · BCNF
✦ Functional Dependencies & Decomposition
✦ 8-Mark Important Questions with Full Answers

B.E. / [Link] Computer Science & Engineering

Academic Reference Material
 UNIT NOTES — RELATIONAL MODEL & DATABASE
DESIGN

1. Relational Model — Basic Concepts

The Relational Model, proposed by E.F. Codd in 1970, represents data as a collection of relations (tables).
Each relation has a unique name and consists of rows (tuples) and columns (attributes). It is the theoretical
foundation of relational database systems such as MySQL, Oracle, and PostgreSQL.

Key Terminology
Term Definition Example

Relation A 2D table with rows and columns STUDENT table

Tuple A single row in a relation One student record

Attribute A column with a specific name StudentName, Age

Domain Set of all valid values for an attribute Age: integers 0–150

STUDENT has 4 attributes →

Degree Number of attributes (columns)
degree 4

Cardinality Number of tuples (rows) 100 students → cardinality 100

STUDENT(ID, Name, Age,

Schema Structure definition of a relation
Dept)

Instance Current data stored in the relation Actual 100 student rows

Properties of a Relation
• Each cell contains exactly one atomic (indivisible) value.
• All values in a column belong to the same domain.
• Each attribute has a unique name within the relation.
• The order of tuples does not matter (set semantics).
• The order of attributes does not matter.
• No two tuples can be identical (no duplicate rows).

2. Attributes and Domains

An attribute represents a property of an entity. Each attribute is associated with a domain — the set of
permissible values it can hold.

Types of Attributes
Type Description Example

Simple Cannot be divided further Age, Salary

Composite Made up of sub-attributes Name = {FirstName, LastName}

Single-valued Holds only one value Date_of_Birth

 Type Description Example

Multi-valued Holds multiple values Phone_Numbers

Derived Computed from other attributes Age derived from DOB

Key Uniquely identifies a tuple StudentID, RollNo

Null Value is unknown or not applicable MiddleName may be NULL

Domain Definition Example

Consider the relation: EMPLOYEE(EmpID, Name, Age, Salary, Department)

Attribute Domain Constraint

EmpID Positive Integers NOT NULL, UNIQUE

Name String (max 50 chars) NOT NULL

Age Integer 18–65 CHECK(Age BETWEEN 18 AND 65)

Salary Decimal (10,2) Salary > 0

Department {'HR','IT','Finance','Sales'} Must be one of listed values

3. Codd's Rules (12 Rules for a Relational DBMS)

E.F. Codd proposed 13 rules (Rule 0 to Rule 12) in 1985 to define what constitutes a truly relational
database management system.

Rule Name Description

A RDBMS must manage its data using its relational

Rule 0 Foundation Rule
capabilities exclusively.

All data must be stored in tables (relations). No other forms

Rule 1 Information Rule
are allowed.

Every datum is accessible by: table name + primary key +

Rule 2 Guaranteed Access
column name.

Systematic NULL NULLs must be supported to represent missing/inapplicable

Rule 3
Handling information.

Database structure (metadata) is stored in tables and

Rule 4 Active Online Catalog
accessible via SQL.

Comprehensive Must support at least one language (SQL) with DDL, DML,
Rule 5
Sub-language DCL.

All theoretically updatable views must be updatable by the

Rule 6 View Updating
system.

Insert, Update, Delete must operate on sets of rows, not

Rule 7 High-Level DML
single rows.

Physical Application programs unaffected by physical storage

Rule 8
Independence changes.
 Rule Name Description

Logical
Rule 9 Application programs unaffected by logical schema changes.
Independence

Integrity Integrity constraints stored in catalog, not in application

Rule 10
Independence programs.

Distribution Must work the same whether data is centralized or

Rule 11
Independence distributed.

Rule 12 Non-subversion Rule Low-level access cannot bypass integrity constraints.

4. Relational Integrity Constraints

Integrity constraints ensure the correctness and consistency of data. There are four main types:

4.1 Domain Integrity

Values stored in a column must belong to its defined domain. Example: The 'Grade' column may only hold
values {A, B, C, D, F}.

4.2 Entity Integrity

The primary key of a relation must be unique and NOT NULL. No attribute forming part of the primary key
can have a null value.

• Example: STUDENT(RollNo, Name, Age) — RollNo cannot be NULL or duplicate.

4.3 Referential Integrity

A foreign key value must either match an existing primary key value in the referenced (parent) table, or be
NULL. This maintains consistency between related tables.

Example — Referential Integrity:

ENROLL(RollNo, CourseID, Marks) — RollNo is FK referencing STUDENT, CourseID is FK referencing
COURSE. Inserting ENROLL(999, 'CS101', 85) violates referential integrity because RollNo 999 does not
exist in STUDENT.

4.4 Key Integrity

Every relation must have at least one candidate key. The primary key uniquely identifies each tuple and
enforces uniqueness.

Referential Integrity Violation Actions

Action Description

RESTRICT / NO
Reject the operation that causes violation (default)
ACTION

CASCADE Propagate the change to all referencing rows automatically

SET NULL Set the foreign key attribute to NULL in referencing rows

SET DEFAULT Set the foreign key attribute to its default value

5. Features of a Good Relational Design

A well-designed relational database should exhibit the following properties:
 • Minimal Redundancy: Data should not be unnecessarily duplicated across tables to save storage and
avoid update anomalies.
• No Update Anomalies: Updating a fact should require changing it in only one place. Types: Insertion,
Deletion, and Modification anomalies.
• Preservation of Information: After decomposition, no original information should be lost (lossless join
decomposition).
• Dependency Preservation: All functional dependencies should be verifiable without joining multiple
tables.
• Clear Semantics: Each tuple in a relation should represent exactly one entity or relationship instance.
• Minimal NULL Values: Avoid relations with many NULL-valued attributes by separating optional
attributes.
• No Spurious Tuples: Joining decomposed relations should produce only valid tuples from the original
relation.

Anomalies — Example
Consider the table: EMP_DEPT(EmpID, EmpName, DeptID, DeptName, DeptLocation)

EmpID EmpName DeptID DeptName DeptLocation

E01 Rahul D01 IT Pune

E02 Priya D01 IT Pune

E03 Amit D02 HR Mumbai

• Insertion Anomaly: Cannot add a new department without assigning an employee.

• Deletion Anomaly: Deleting E03 loses all information about Dept D02.
• Update Anomaly: Changing DeptLocation for D01 requires updating both E01 and E02 rows.
Solution: Decompose into EMPLOYEE(EmpID, EmpName, DeptID) and DEPARTMENT(DeptID, DeptName,
DeptLocation).

6. Normalization
Normalization is the process of organizing a relational database to reduce redundancy and improve data
integrity. It uses Functional Dependencies (FDs) and progressively applies Normal Forms (NF).

6.1 Functional Dependencies (FD)

An FD X → Y means: for every pair of tuples t1, t2 in relation R, if t1[X] = t2[X], then t1[Y] = t2[Y]. We say 'X
functionally determines Y'.

• Example: In STUDENT(RollNo, Name, Age, DeptID) — RollNo → Name, RollNo → Age

• Types: Full FD, Partial FD, Transitive FD

6.2 First Normal Form (1NF)

A relation is in 1NF if and only if every attribute contains only atomic (indivisible) values — no multi-valued
or composite attributes, no repeating groups.

Violation Example (NOT in 1NF):

 StudentID Name Subjects

101 Rahul DBMS, OS, CN

102 Priya DBMS, DS

After 1NF Conversion:

StudentID Name Subject

101 Rahul DBMS

101 Rahul OS

101 Rahul CN

102 Priya DBMS

102 Priya DS

Primary Key becomes composite: (StudentID, Subject)

6.3 Second Normal Form (2NF)

A relation is in 2NF if it is in 1NF AND every non-key attribute is fully functionally dependent on the entire
primary key (no partial dependencies).

Violation Example — Table ORDER_DETAIL:

OrderID
ProductID (PK) Quantity ProductName CustomerName
(PK)

O01 P01 5 Laptop Rahul

O01 P02 2 Mouse Rahul

O02 P01 1 Laptop Priya

Partial Dependencies: ProductName depends only on ProductID (not the full key). CustomerName depends
only on OrderID.

Decomposition to 2NF:

• ORDER(OrderID, CustomerName) — CustomerName depends only on OrderID

• PRODUCT(ProductID, ProductName) — ProductName depends only on ProductID
• ORDER_DETAIL(OrderID, ProductID, Quantity) — Quantity depends on full key

6.4 Third Normal Form (3NF)

A relation is in 3NF if it is in 2NF AND there are no transitive dependencies — no non-key attribute
depends on another non-key attribute.

Violation Example — STUDENT_DEPT:

StudentID
StudentName DeptID DeptName HOD
(PK)

101 Rahul D01 Computer Sci. Dr. Sharma

102 Priya D01 Computer Sci. Dr. Sharma

 StudentID
StudentName DeptID DeptName HOD
(PK)

103 Amit D02 Electronics Dr. Kumar

Transitive Dependency: StudentID → DeptID → DeptName, HOD. DeptName and HOD depend on DeptID
(a non-key attribute), not directly on StudentID.

Decomposition to 3NF:

• STUDENT(StudentID, StudentName, DeptID)

• DEPARTMENT(DeptID, DeptName, HOD)

6.5 Boyce-Codd Normal Form (BCNF)

A relation is in BCNF if for every non-trivial FD X → Y, X must be a superkey (i.e., X determines the entire
relation). BCNF is stricter than 3NF.

A relation in 3NF may still have anomalies if there are multiple overlapping candidate keys. BCNF eliminates
these.

Violation Example — COURSE_TEACHER:

Schema: COURSE_TEACHER(StudentID, Course, Teacher)

Assumptions: Each teacher teaches only one course. Each student takes a course with one teacher.

StudentID Course Teacher

S01 DBMS Prof. Mehta

S01 OS Prof. Singh

S02 DBMS Prof. Mehta

S03 OS Prof. Patel

FDs: {StudentID, Course} → Teacher (PK) AND Teacher → Course (Teacher is a non-superkey determining
Course!)

BCNF Violation: Teacher → Course but Teacher alone is NOT a superkey.

Decomposition to BCNF:

• TEACHER_COURSE(Teacher, Course) ← Teacher → Course

• STUDENT_TEACHER(StudentID, Teacher) ← captures which teacher a student has

Summary: Normal Forms Comparison

Normal Form Condition Eliminated Requires

1NF Multi-valued / composite attributes Atomic values only

2NF Partial dependencies 1NF + Full FD on entire PK

3NF Transitive dependencies 2NF + No non-key → non-key FDs

Anomalies from overlapping

BCNF Every determinant is a superkey
candidate keys

Decomposition using Functional Dependencies

When a relation R violates a Normal Form, we decompose it into smaller relations. A good decomposition
must satisfy:

• Lossless Join: Joining the decomposed relations must reproduce the original relation exactly (no
spurious tuples).
• Dependency Preservation: All original FDs must be verifiable in the decomposed relations without
performing joins.
Lossless Join Test (for R → R1, R2): The decomposition is lossless if R1 ∩ R2 → R1 or R1 ∩ R2 → R2
(i.e., the common attributes form a superkey in at least one part).
 8-MARK IMPORTANT QUESTIONS & ANSWERS

Q1. Explain the basic concepts of the Relational Model. Discuss Attributes, Domains, and
Properties of a Relation with examples.

Answer:
Relational Model — Overview
The Relational Model, introduced by E.F. Codd (1970), represents data as a collection of relations (tables). It
is the basis for all modern SQL databases.

Key Components:
• Relation: A two-dimensional table with rows (tuples) and columns (attributes). Example: EMPLOYEE
table.
• Attribute: A named column representing a property. Example: EmpID, EmpName, Salary.
• Domain: Set of valid values for an attribute. Example: Domain of 'Age' = {integers 18 to 65}.
• Tuple: A single row. Example: (E01, Rahul, 55000, IT).
• Degree: Number of attributes. EMPLOYEE(EmpID, Name, Age, Dept) → Degree = 4.
• Cardinality: Number of tuples (rows) in the relation.
Properties of a Relation:
• All attribute values are atomic (1NF).
• Each column has a unique attribute name.
• All values in a column belong to the same domain.
• No two tuples are identical.
• The order of tuples and attributes is immaterial.
Example Relation — STUDENT:
RollNo (PK) Name Age Department

101 Rahul Sharma 20 CSE

102 Priya Patel 21 ECE

103 Amit Kumar 19 CSE

Domain(RollNo) = Positive Integers; Domain(Name) = Strings; Domain(Age) = 15 to 30;

Domain(Department) = {'CSE','ECE','ME','CE'}

Q2. State and explain Codd's 12 Rules for a Relational Database Management System.

Answer:
E.F. Codd proposed 13 rules (Rule 0 to Rule 12) in 1985 to evaluate how relational a DBMS truly is. No
commercial system fully satisfies all rules, but they provide a benchmark.

• Rule 0 — Foundation Rule: The system must manage databases using only its relational capabilities.
• Rule 1 — Information Rule: All data is stored as values in table cells. No hidden data structures.
• Rule 2 — Guaranteed Access: Each data item is accessible using: table name + primary key value +
attribute name.
 • Rule 3 — Systematic NULL Handling: NULL values are supported to represent missing or
inapplicable information, distinct from 0 or blank string.
• Rule 4 — Active Online Catalog: Database description (schema) is stored in tables and can be
queried with the same SQL language.
• Rule 5 — Comprehensive Data Sublanguage: The system must support at least one language with
DDL, DML, DCL, and transaction management (e.g., SQL).
• Rule 6 — View Updating Rule: All theoretically updatable views must be updatable by the system.
• Rule 7 — High-Level Insert, Update, Delete: DML operations must work on sets of rows, not one row
at a time.
• Rule 8 — Physical Data Independence: Changes to physical storage (disk, indexing) must not affect
application programs.
• Rule 9 — Logical Data Independence: Changes to logical schema (adding columns) must not require
rewriting application programs.
• Rule 10 — Integrity Independence: Integrity constraints are defined in the catalog, not in application
code.
• Rule 11 — Distribution Independence: Applications work correctly whether data is centralized or
distributed across network.
• Rule 12 — Non-subversion Rule: Low-level row-at-a-time access cannot bypass integrity constraints
defined at the higher level.

Q3. What is Referential Integrity? Explain with an example, and discuss the actions taken
when a referential integrity constraint is violated.

Answer:
Referential Integrity is a constraint that ensures a foreign key value in a child (referencing) table must match
an existing primary key value in the parent (referenced) table, or it must be NULL. It maintains consistency
between related tables.

Example:
Consider two tables:
• DEPARTMENT(DeptID [PK], DeptName, Location)
• EMPLOYEE(EmpID [PK], EmpName, Salary, DeptID [FK → DEPARTMENT])

EmpID EmpName Salary DeptID (FK)

E01 Rahul 55000 D01

E02 Priya 62000 D02

E03 Amit 48000 D99 ← VIOLATION

DeptID = 'D99' for Amit does not exist in DEPARTMENT → Referential Integrity Violation.

Actions on Violation:
• RESTRICT / NO ACTION: Reject the INSERT or UPDATE operation. Most common default behavior.
• CASCADE DELETE: Deleting a parent row automatically deletes all child rows referencing it.
• CASCADE UPDATE: Updating PK in parent automatically updates the FK in all child rows.
• SET NULL: Sets FK value to NULL in child rows when parent row is deleted/updated.
• SET DEFAULT: Sets FK to its default value when the parent row is deleted/updated.
SQL Example: CREATE TABLE EMPLOYEE ( ... DeptID INT, FOREIGN KEY (DeptID) REFERENCES
DEPARTMENT(DeptID) ON DELETE CASCADE ON UPDATE SET NULL );

Q4. What is Normalization? Explain 1NF, 2NF, and 3NF with a complete step-by-step
example.

Answer:
Normalization is the process of structuring a relational database to reduce data redundancy and eliminate
data anomalies (insertion, deletion, update anomalies) using a series of rules called Normal Forms, based on
Functional Dependencies.

Starting Relation — STUDENT_COURSE (UNF):

SID SName CourseID CourseName Instructor InstrPhone

S1 Rahul C1 DBMS Dr. Mehta 9876543210

S1 Rahul C2 OS Dr. Singh 9988776655

S2 Priya C1 DBMS Dr. Mehta 9876543210

S2 Priya C3 CN Dr. Patel 9911223344

Primary Key: (SID, CourseID). Functional Dependencies:

• SID → SName (SID alone determines SName — Partial FD)

• CourseID → CourseName, Instructor (Partial FD)
• Instructor → InstrPhone (Transitive FD)
Step 1: Convert to 1NF
The above table already has atomic values (no multi-valued attributes). It IS in 1NF. PK = (SID, CourseID).

Step 2: Convert to 2NF — Remove Partial Dependencies

SName depends only on SID (not full PK). CourseName, Instructor depend only on CourseID. Remove them.

• STUDENT(SID, SName)
• COURSE(CourseID, CourseName, Instructor, InstrPhone)
• ENROLLMENT(SID, CourseID)
ENROLLMENT Table (2NF):
SID CourseID

S1 C1

S1 C2

S2 C1

S2 C3

Step 3: Convert to 3NF — Remove Transitive Dependencies

In COURSE: CourseID → Instructor → InstrPhone. InstrPhone depends on Instructor (non-key), not on
CourseID directly. This is a transitive dependency. Remove it.

• COURSE(CourseID, CourseName, Instructor)

• INSTRUCTOR(Instructor, InstrPhone)
Final 3NF Tables:
 Relation Attributes PK

STUDENT SID, SName SID

COURSE CourseID, CourseName, Instructor CourseID

INSTRUCTO
Instructor, InstrPhone Instructor
R

ENROLLMEN
SID, CourseID (SID, CourseID)
T

Q5. What is BCNF? How does it differ from 3NF? Explain with an example and show the
decomposition.

Answer:
Boyce-Codd Normal Form (BCNF), proposed by Raymond Boyce and E.F. Codd, is a stricter version of
3NF. A relation R is in BCNF if for every non-trivial functional dependency X → Y, X must be a superkey of
R.

Difference: 3NF vs BCNF

Aspect 3NF BCNF

Condition No transitive dependencies Every determinant is a superkey

Strictness Less strict More strict (subset of 3NF)

Dependency
Always possible May not always be preserved
Preservation

Complete for FD-based

Anomaly Elimination Partial
anomalies

BCNF Violation Example — EXAM_SCHEDULE:

Schema: EXAM_SCHEDULE(Student, Subject, Examiner)
Rules: Each examiner teaches exactly one subject. Each student is examined by one examiner per subject.

Student Subject Examiner

Rahul DBMS Dr. Mehta

Rahul OS Dr. Singh

Priya DBMS Dr. Mehta

Priya OS Dr. Patel

Functional Dependencies:

• {Student, Subject} → Examiner (Candidate Key — PK)

• Examiner → Subject (Dr. Mehta always examines DBMS)
BCNF Violation: Examiner → Subject holds, but 'Examiner' alone is NOT a superkey (it doesn't uniquely
identify a tuple). This violates BCNF.

Note: This relation IS in 3NF (no transitive deps on non-key), yet violates BCNF.

Decomposition to BCNF:
 • EXAMINER_SUBJECT(Examiner, Subject) ← Examiner → Subject
• STUDENT_EXAMINER(Student, Examiner) ← Maps student to their examiner
Decomposed Tables:
Examiner Subject

Dr. Mehta DBMS

Dr. Singh OS

Dr. Patel OS

Student Examiner

Rahul Dr. Mehta

Rahul Dr. Singh

Priya Dr. Mehta

Priya Dr. Patel

Both decomposed relations satisfy BCNF: every determinant is a superkey in its relation.

Q6. What is a Functional Dependency? Explain types of FDs and discuss Lossless Join
and Dependency Preserving Decomposition with examples.

Answer:
A Functional Dependency (FD) X → Y on a relation R means: for any two tuples t1, t2 in R, if t1[X] = t2[X]
then t1[Y] = t2[Y]. In other words, knowing the value of X allows you to uniquely determine Y.

Types of Functional Dependencies:

• Full FD: Example: {StudentID, CourseID} → Grade — Grade depends on the complete PK.
• Partial FD: Example: {StudentID, CourseID} → StudentName — StudentName depends only on
StudentID.
• Transitive FD: Example: StudentID → DeptID → DeptName — DeptName depends on DeptID
(non-key), not directly on StudentID.
• Trivial FD: Example: {A, B} → A — The RHS is a subset of LHS — always holds.
• Non-trivial FD: Example: A → B where B ∉ {A} — B is not a subset of A — meaningful dependency.

Lossless Join Decomposition:

A decomposition of R into R1 and R2 is lossless if joining R1 and R2 on common attributes gives back the
original relation R with no spurious tuples.

Test: The decomposition R → {R1, R2} is lossless if:

• R1 ∩ R2 → R1 (common attributes form a key in R1), OR

• R1 ∩ R2 → R2 (common attributes form a key in R2)
Example: R(A, B, C) with FD A → B

• Decompose into R1(A, B) and R2(A, C)

• R1 ∩ R2 = {A}; A → B (A is PK of R1) → Lossless ✓
Dependency Preserving Decomposition:
A decomposition preserves dependencies if every FD in the original relation F can be verified using only the
attributes in one of the decomposed relations, without performing joins.
Example: R(A, B, C) with FDs: A → B and B → C

• Decompose into R1(A, B) and R2(B, C)

• A → B can be checked in R1 ✓; B → C can be checked in R2 ✓
• Both FDs preserved → Dependency Preserving Decomposition ✓
Note: BCNF decomposition may not always preserve all dependencies, while 3NF decomposition always
can.

Q7. Explain the features of a good relational database design. What are anomalies and
how does normalization help?

Answer:
Features of Good Relational Design:
• Minimal Redundancy: Store each fact once. Redundancy wastes space and causes inconsistency.
• No Update Anomalies: Changing one fact should not require multiple row updates.
• Lossless Decomposition: Decomposing and rejoining tables should give back the original data.
• Dependency Preservation: All integrity rules remain enforceable without joining tables.
• Clear Semantics: Each relation represents one entity or relationship — no mixing.
• Minimal NULLs: Avoid attributes that are NULL for most tuples — move them to separate tables.
• No Spurious Tuples: Natural joins should not produce incorrect extra rows.

Types of Anomalies (with example):

Unnormalized Table: EMP_PROJECT(EmpID, EmpName, ProjID, ProjName, HoursWorked)

EmpID EmpName ProjID ProjName Hours

E01 Rahul P01 Alpha 40

E01 Rahul P02 Beta 20

E02 Priya P01 Alpha 30

• Insertion Anomaly: Cannot add a new project without assigning an employee to it.
• Deletion Anomaly: Deleting E02 loses all information about Project P01 if E01 rows are also removed.
• Update Anomaly: Changing ProjName 'Alpha' requires updating multiple rows. Inconsistency if one is
missed.
Solution via Normalization:
• EMPLOYEE(EmpID, EmpName)
• PROJECT(ProjID, ProjName)
• WORKS_ON(EmpID, ProjID, HoursWorked) ← Only fact that truly needs both keys
Now all three anomalies are eliminated. Each table has clear, single-entity semantics.

Q8. Compare 2NF, 3NF, and BCNF with definitions and complete examples. Discuss when
BCNF may not be achievable with dependency preservation.

Answer:
The three advanced Normal Forms progressively eliminate different types of redundancy caused by
Functional Dependencies.

2NF — Remove Partial Dependencies:

Condition: In 1NF + Every non-prime attribute is fully functionally dependent on the entire candidate key.

Example: RESULT(StudentID, SubjectID, StudentName, SubjectName, Marks)

• StudentID → StudentName (Partial — only depends on StudentID part of PK)

• SubjectID → SubjectName (Partial — only depends on SubjectID part of PK)
• 2NF Tables: STUDENT(StudentID, StudentName), SUBJECT(SubjectID, SubjectName),
RESULT(StudentID, SubjectID, Marks)
3NF — Remove Transitive Dependencies:
Condition: In 2NF + No non-prime attribute depends on another non-prime attribute (no X→Y→Z where Y is
non-prime).

Example: EMPLOYEE(EmpID, DeptID, DeptLocation)

• EmpID → DeptID, and DeptID → DeptLocation (transitive)

• 3NF Tables: EMPLOYEE(EmpID, DeptID), DEPARTMENT(DeptID, DeptLocation)
BCNF — Every Determinant is a Superkey:
Condition: For every non-trivial FD X → Y, X must be a superkey.

BCNF Example: ADVISING(StudentID, DeptID, AdvisorID)

• {StudentID, DeptID} → AdvisorID (Candidate Key 1)

• AdvisorID → DeptID (Advisor belongs to one Dept — NOT a superkey → BCNF violation)
• BCNF decomposition: ADVISOR_DEPT(AdvisorID, DeptID) and STUDENT_ADVISOR(StudentID,
AdvisorID)
When BCNF may lose Dependency Preservation:
Sometimes achieving BCNF forces us to split a table in a way that some FDs can no longer be checked
within a single table. In such cases, we prefer 3NF (which always allows dependency preserving
decomposition) over BCNF.

Example: R(A, B, C) with FDs: {A,B} → C and C → A. Both {A,B} and {B,C} are candidate keys. C → A
violates BCNF (C is not a superkey). Decomposing to BCNF loses the FD {A,B} → C. Therefore, 3NF is
preferred here.

Preserves
Normal Form What It Eliminates Key Requirement
Deps?

2NF Partial Dependencies Full FD on all PK attributes Yes

3NF Transitive Dependencies No non-prime → non-prime FDs Yes (always)

BCNF All FD-based redundancy Every determinant is a superkey Not always

All 8-mark answers are designed to fit approximately one page in an exam. Memorize definitions, use tabular examples,
and always conclude with a summary table for full marks.