DATABASE MANAGEMENT

SYSTEM

Complete Study Notes

Units 2 & 3: Relational Model & Database Design

📚 Theory • 📊 Examples • 💡 Practice • ❓ Questions

Introduction to Database Management Systems

What is a Database?
A database is an organized collection of structured data stored electronically. It allows
for efficient storage, retrieval, and management of information.

 School database: students, teachers, courses

 Bank database: accounts, transactions, customers
 E-commerce: products, orders, inventory

What is DBMS?
Database Management System (DBMS) is software that provides an interface between
databases and users/applications. It handles storage, retrieval, security, and integrity.

Understanding Keys - The Foundation

Keys are attributes that uniquely identify records and establish relationships. This is THE
MOST IMPORTANT concept in databases!

Primary Key
 Uniquely identifies each record
 Cannot be NULL
 Must be unique
 Each table has exactly ONE primary key

Example: Student Table

Student_ID (PK) Name Age Dept

101 Alice 20 CS
102 Bob 22 Math
103 Carol 21 Physics

Foreign Key
 References primary key of another table
 Creates relationships between tables
 Can be NULL
 Can have duplicate values

Example: Two Related Tables

Department Table:

Dept_ID (PK) Dept_Name

CS Computer Science
MATH Mathematics

Student Table (with foreign key):

Student_ID (PK) Name Dept_ID (FK)

101 Alice CS
102 Bob MATH
103 Carol CS

Other Key Types

 Candidate Key: Can be chosen as primary key (multiple per table)
 Super Key: Any combination that uniquely identifies rows
 Composite Key: Primary key made of 2+ columns

Composite Key Example: Enrollment Table

Student_ID Course_ID Grade

101 CS101 A
101 CS102 B
102 CS101 A
Primary Key = {Student_ID, Course_ID} together
UNIT 2: RELATIONAL MODEL

2.1 Core Concepts

Domains
A domain is the set of allowed values for an attribute. It defines data type, range, and
format.

Attribute Domain Examples

Student_ID Integer (1-99999) 101, 5678
Name String (max 50) Alice, Bob
Age Integer (15-100) 18, 20, 25
Grade Char {A,B,C,D,F} A, B
GPA Decimal (0.0-4.0) 3.5, 3.85

Attributes
 Simple: Cannot be divided (Age, Gender)
 Composite: Can be divided (Name = First + Last)
 Single-valued: One value (Student_ID)
 Multi-valued: Multiple values (Phone Numbers)
 Stored: Explicitly stored (Birth_Date)
 Derived: Calculated (Age from Birth_Date)

Tuples and Relations

 Tuple: A single row representing one record
 Relation: A table with rows and columns
 Degree: Number of attributes (columns)
 Cardinality: Number of tuples (rows)

Characteristics of Relations
 No duplicate tuples
 Atomic values (1NF)
 Unordered rows
 Unique column names
2.2 Integrity Constraints

Entity Integrity
Primary key cannot be NULL

Referential Integrity
Foreign key must reference existing primary key value or be NULL

Domain Constraint
Values must be from valid domain

SQL Example:
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 15 AND Age <= 100),
Dept_ID VARCHAR(10),
FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);
2.3 Relational Algebra Operations
Relational Algebra is a procedural query language using operators to retrieve data.

1. SELECT (σ) - Selects rows

Syntax: σ_condition(Relation)

Example: Find students with age > 20

σ_Age>20(Student)

2. PROJECT (π) - Selects columns

Syntax: π_attributes(Relation)

Example: Show only names

π_Name(Student)

3. UNION (∪) - Combines rows from two relations

Must be union-compatible (same schema)

Example: All students from CS or Math

CS_Students ∪ Math_Students

4. INTERSECTION (∩) - Common rows

Example: Students in BOTH CS AND Math
CS_Students ∩ Math_Students

5. SET DIFFERENCE (−) - Rows in first but not second

Example: CS students NOT in Math
CS_Students − Math_Students

6. CARTESIAN PRODUCT (×) - All combinations

Combines every row from R with every row from S

7. JOIN Operations - THE MOST IMPORTANT!

a) NATURAL JOIN (⋈)

Joins tables based on common attributes

Student_ID Name Dept_ID

101 Alice CS
102 Bob MATH
⋈

Dept_ID Dept_Name
CS Computer Science
MATH Mathematics
Result:

Student_ID Name Dept_ID Dept_Name

101 Alice CS Computer Science
102 Bob MATH Mathematics

b) EQUI JOIN (θ-join)

Join based on equality condition

Student ⋈_(Student.Dept_ID = Department.Dept_ID) Department

c) INNER JOIN

Returns only matching rows from both tables

d) LEFT OUTER JOIN

All rows from left table + matching from right

NULL for non-matching right rows

e) RIGHT OUTER JOIN

All rows from right table + matching from left

NULL for non-matching left rows

f) FULL OUTER JOIN

All rows from both tables

NULL for non-matching rows

Practical JOIN Example:

Students:

S_ID Name Dept_ID

1 Alice CS
2 Bob MATH
3 Carol PHY

Departments:

Dept_ID Dept_Name
CS Computer Science
MATH Mathematics

INNER JOIN (only matching):

S_ID Name Dept_ID Dept_Name

1 Alice CS Computer Science
2 Bob MATH Mathematics
Carol excluded (no matching dept)

LEFT OUTER JOIN (all students):

S_ID Name Dept_ID Dept_Name

1 Alice CS Computer Science
2 Bob MATH Mathematics
3 Carol PHY NULL
Carol included with NULL for Dept_Name

8. DIVISION (÷)
Finds tuples in R that are associated with ALL tuples in S

Example: Find students enrolled in ALL courses

2.4 Relational Calculus

Tuple Relational Calculus (TRC)

Non-procedural query language

Specifies WHAT to retrieve, not HOW

Syntax: {t | P(t)}

Reads as: "Set of all tuples t such that predicate P(t) is true"

Example: Find students with age > 20

{t | t ∈ Student ∧ [Link] > 20}

Domain Relational Calculus (DRC)

Uses domain variables instead of tuple variables

Syntax: {< x1, x2, ..., xn > | P(x1, x2, ..., xn)}

Example: Find names of students in CS dept

{< name > | ∃ sid, age, dept (< sid, name, age, dept > ∈ Student ∧ dept =
'CS')}
UNIT 3: DATABASE DESIGN

3.1 Functional Dependencies

A functional dependency X → Y means: If two tuples have the same value for X, they
must have the same value for Y.

Example Table:

Student_ID Name Dept Dept_Head

101 Alice CS Dr. Smith
102 Bob CS Dr. Smith
103 Carol Math Dr. Jones

Functional Dependencies:

 Student_ID → Name (each ID determines one name)

 Student_ID → Dept (each ID determines one dept)
 Dept → Dept_Head (each dept has one head)
 Student_ID → Dept_Head (transitive dependency)

Armstrong's Axioms
 1. Reflexivity: If Y ⊆ X, then X → Y
 2. Augmentation: If X → Y, then XZ → YZ
 3. Transitivity: If X → Y and Y → Z, then X → Z

Secondary Rules (Derived)

 4. Union: If X → Y and X → Z, then X → YZ
 5. Decomposition: If X → YZ, then X → Y and X → Z
 6. Composition: If X → Y and A → B, then XA → YB
 7. Pseudo-transitivity: If X → Y and YZ → W, then XZ → W
3.2 Normalization
Normalization is the process of organizing data to reduce redundancy and improve data
integrity. We decompose tables into smaller, well-structured tables.

1st Normal Form (1NF)

Rules:
 All attributes must contain atomic (indivisible) values
 No repeating groups
 Each column must contain values of single type

❌ NOT in 1NF:

Student_ID Name Courses

101 Alice CS101, CS102, CS103
102 Bob MATH201, CS101
Problem: Courses column has multiple values!

✓ IN 1NF:

Student_ID Name Course_ID

101 Alice CS101
101 Alice CS102
101 Alice CS103
102 Bob MATH201
102 Bob CS101
2nd Normal Form (2NF)

Rules:
 Must be in 1NF
 No partial dependencies (non-key attributes must depend on entire primary key)
 Only applies to tables with composite primary keys

❌ NOT in 2NF:

Primary Key = {Student_ID, Course_ID}

Student_ID Course_IDStudent_Nam Course_Name Grade

e
101 CS101 Alice Database A
101 CS102 Alice Networks B
102 CS101 Bob Database A
Problem: Student_Name depends only on Student_ID, not full key!

Problem: Course_Name depends only on Course_ID, not full key!

✓ IN 2NF (Split into 3 tables):

Table 1: Student

Student_ID (PK) Student_Name

101 Alice
102 Bob

Table 2: Course

Course_ID (PK) Course_Name

CS101 Database
CS102 Networks

Table 3: Enrollment

Student_ID (PK) Course_ID (PK) Grade

101 CS101 A
101 CS102 B
102 CS101 A
3rd Normal Form (3NF)

Rules:
 Must be in 2NF
 No transitive dependencies
 Non-key attributes must depend ONLY on primary key

❌ NOT in 3NF:

Student_ID Name Dept_ID Dept_Name Dept_Building

(PK)
101 Alice CS Computer Building A
Science
102 Bob CS Computer Building A
Science
103 Carol MATH Mathematics Building B
Dependencies:

 Student_ID → Dept_ID (direct)

 Dept_ID → Dept_Name (direct)
 Student_ID → Dept_Name (transitive!) ✗

✓ IN 3NF (Split into 2 tables):

Table 1: Student

Student_ID (PK) Name Dept_ID (FK)

101 Alice CS
102 Bob CS
103 Carol MATH

Table 2: Department

Dept_ID (PK) Dept_Name Dept_Building

CS Computer Science Building A
MATH Mathematics Building B
Boyce-Codd Normal Form (BCNF / 3.5NF)

Rules:
 Must be in 3NF
 For every dependency X → Y, X must be a superkey
 Stronger than 3NF

❌ NOT in BCNF (but in 3NF):

Assumptions: Each professor teaches one course, students can take course from any
prof

Student_ID Course_ID Professor

101 CS101 Dr. Smith
102 CS101 Dr. Jones
101 CS102 Dr. Brown
Candidate Keys: {Student_ID, Course_ID}

Dependencies:

 {Student_ID, Course_ID} → Professor ✓

 Professor → Course_ID (violates BCNF!)

Problem: Professor is not a superkey but determines Course_ID

✓ IN BCNF:

Table 1: Professor_Course

Professor (PK) Course_ID

Dr. Smith CS101
Dr. Jones CS101
Dr. Brown CS102

Table 2: Student_Professor

Student_ID (PK) Professor (PK)

101 Dr. Smith
102 Dr. Jones
101 Dr. Brown
4th Normal Form (4NF)

Rules:
 Must be in BCNF
 No multi-valued dependencies

Multi-valued Dependency: X ↠ Y

For a given X value, there is a set of Y values independent of other attributes

❌ NOT in 4NF:

Student has multiple phones AND multiple skills (independent)

Student_ID Phone Skill

101 9876543210 Python
101 9876543210 Java
101 9876543211 Python
101 9876543211 Java
Multi-valued Dependencies:

 Student_ID ↠ Phone (independent of Skill)

 Student_ID ↠ Skill (independent of Phone)

Problem: 4 rows needed for 1 student with 2 phones and 2 skills!

✓ IN 4NF (Split into 2 tables):

Table 1: Student_Phone

Student_ID Phone
101 9876543210
101 9876543211

Table 2: Student_Skill

Student_ID Skill
101 Python
101 Java
Now only 4 rows total instead of redundant combinations!
Normalization Summary Table
Normal Form Eliminates Rule
1NF Repeating groups Atomic values only
2NF Partial dependencies No partial dependency on
composite key
3NF Transitive dependencies Non-key depends only on
key
BCNF Dependency on non- All determinants are
superkey superkeys
4NF Multi-valued dependencies No independent multi-
valued attributes
3.3 Decomposition

Lossless Join Decomposition

When we decompose R into R1 and R2:

 R = R1 ⋈ R2 (can reconstruct original without loss)

 Common attribute must be a key in at least one relation

Dependency Preservation
All functional dependencies can be checked without joining tables

Dealing with NULL Values

 NULL represents unknown or missing information
 Can cause problems in joins and aggregations
 Use CHECK constraints and default values when possible

Dangling Tuples
Tuples that reference non-existent records (violate referential integrity)

Prevented by foreign key constraints

3.4 Query Optimization
Query optimization is the process of selecting the most efficient way to execute a query.

Steps of Query Optimization

1. 1. Parse query - Check syntax and semantics
2. 2. Transform query - Convert to relational algebra
3. 3. Generate execution plans - Different ways to execute
4. 4. Cost estimation - Estimate cost of each plan
5. 5. Select best plan - Choose plan with lowest cost
6. 6. Execute - Run the selected plan

Optimization Techniques
 Selection early - Apply WHERE conditions as early as possible
 Projection early - Select only needed columns
 Join ordering - Start with tables that reduce size most
 Index usage - Use indexes for fast lookup
 Avoid Cartesian products - Use proper join conditions

Example: Optimizing a query

-- Original (inefficient):
SELECT * FROM Student, Department
WHERE Student.Dept_ID = Department.Dept_ID AND [Link] > 20;

-- Optimized:
SELECT Student.*, Department.Dept_Name
FROM Student
INNER JOIN Department ON Student.Dept_ID = Department.Dept_ID
WHERE [Link] > 20;
PRACTICE QUESTION PAPER 1
Time: 2 Hours Max Marks: 60

______________________________________________________________________
__________

Part A: Multiple Choice Questions (10 × 1 = 10 marks)

7. 1. Which of the following is NOT a characteristic of a relation?

 a) No duplicate tuples
 b) Ordered rows
 c) Atomic values
 d) Unique column names

8. 2. In relational algebra, which operation selects specific columns?

 a) SELECT (σ)
 b) PROJECT (π)
 c) JOIN (⋈)
 d) UNION (∪)

9. 3. Which normal form eliminates transitive dependencies?

 a) 1NF
 b) 2NF
 c) 3NF
 d) 4NF

10. 4. Foreign key can contain:

 a) NULL values
 b) Duplicate values
 c) Both a and b
 d) Neither a nor b

11. 5. Armstrong's axiom of augmentation states:

 a) If X→Y then XZ→YZ

 b) If X→Y and Y→Z then X→Z
  c) If Y⊆X then X→Y
 d) If X→Y and X→Z then X→YZ

12. 6. Which join returns all rows from left table?

 a) INNER JOIN
 b) LEFT OUTER JOIN
 c) RIGHT OUTER JOIN
 d) CROSS JOIN

13. 7. A composite key is made up of:

 a) One column
 b) Two or more columns
 c) Only foreign keys
 d) Only candidate keys

14. 8. Which normal form handles multi-valued dependencies?

 a) 1NF
 b) 2NF
 c) 3NF
 d) 4NF

15. 9. The operation R × S in relational algebra is called:

 a) Join
 b) Union
 c) Cartesian Product
 d) Intersection

16. 10. Which constraint ensures primary key is not NULL?

 a) Referential Integrity
 b) Entity Integrity
 c) Domain Constraint
 d) Key Constraint
Part B: Short Answer Questions (5 × 3 = 15 marks)
1. Given the Student table below, write relational algebra expressions for:

a) Find students from CS department

b) Find names of students with age > 21

c) Find all departments of students

2. Explain the difference between candidate key and super key with an example.

3. Consider: Student(Student_ID, Name, Course_ID, Course_Name, Grade)

Identify functional dependencies and explain why it's not in 2NF.

4. Differentiate between INNER JOIN and LEFT OUTER JOIN with examples.

5. Write SQL statements to:

a) Create a Department table with primary key

b) Create a Student table with foreign key referencing Department

Part C: Long Answer Questions (5 × 7 = 35 marks)
1. (7 marks) Consider the following Employee relation:

Emp_ID Name Project_ID Project_Na Hours Manager

me
E1 Alice P1 Database 40 John
E1 Alice P2 Networks 20 Mary
E2 Bob P1 Database 30 John
a) Identify all functional dependencies (3 marks)

b) Normalize to 3NF showing all intermediate steps (4 marks)

2. (7 marks)

a) Explain all types of JOIN operations with examples (4 marks)

b) Write SQL queries for INNER, LEFT, and FULL OUTER joins (3 marks)

3. (7 marks)

a) Explain Armstrong's axioms with examples (3 marks)

b) Derive Union and Decomposition rules from basic axioms (4 marks)

4. (7 marks) Given tables:

Student(Student_ID, Name, Dept_ID)

Course(Course_ID, Course_Name, Credits)

Enrollment(Student_ID, Course_ID, Grade)

a) Draw ER diagram showing relationships (3 marks)

b) Write SQL queries for: (4 marks)

- Find students enrolled in more than 3 courses

- Find average grade for each course

5. (7 marks)
a) What is BCNF? How is it different from 3NF? (3 marks)

b) Convert the following to BCNF: (4 marks)

CourseOffer(Course_ID, Instructor, Student_ID, Grade)

Given: Each instructor teaches only one course

PRACTICE QUESTION PAPER 2
Time: 2 Hours Max Marks: 60

______________________________________________________________________
__________

Part A: Multiple Choice Questions (10 × 1 = 10 marks)

17. 1. Which is NOT a property of primary key?

 a) Unique
 b) Not NULL
 c) Can be changed
 d) Identifies each row

18. 2. Relational calculus is:

 a) Procedural
 b) Non-procedural
 c) Both
 d) Neither

19. 3. To eliminate partial dependencies, we use:

 a) 1NF
 b) 2NF
 c) 3NF
 d) BCNF

20. 4. Which operation in relational algebra finds common tuples?

 a) UNION
 b) INTERSECTION
 c) DIFFERENCE
 d) PRODUCT

21. 5. A derived attribute is:

 a) Stored in database
 b) Calculated from other attributes
  c) Always NULL
 d) Primary key

22. 6. BCNF is:

 a) Same as 3NF
 b) Weaker than 3NF
 c) Stronger than 3NF
 d) Same as 4NF

23. 7. Which join gives all possible combinations?

 a) INNER JOIN
 b) NATURAL JOIN
 c) CROSS JOIN
 d) SELF JOIN

24. 8. Degree of a relation is:

 a) Number of rows
 b) Number of columns
 c) Number of keys
 d) Number of tables

25. 9. Referential integrity constraint is enforced by:

 a) Primary key
 b) Foreign key
 c) Candidate key
 d) Super key

26. 10. In 4NF, we eliminate:

 a) Partial dependency
 b) Transitive dependency
 c) Multi-valued dependency
 d) All of above
Part B: Short Answer Questions (5 × 3 = 15 marks)
1. Explain the concept of functional dependency with examples.

2. Write relational algebra expressions for:

a) Students in either CS or Math department (UNION)

b) Students in both CS and Math (INTERSECTION)

3. What are the differences between 3NF and BCNF? Provide an example.

4. Explain lossless join decomposition. Why is it important?

5. Given: Employee(Emp_ID, Name, Salary, Dept_ID)

Department(Dept_ID, Dept_Name, Location)

Write SQL for LEFT OUTER JOIN showing all employees.

Part C: Long Answer Questions (5 × 7 = 35 marks)
1. (7 marks) Consider:

Book(ISBN, Title, Author, Publisher, Publisher_Address, Price)

a) Identify functional dependencies (2 marks)

b) Which normal form does it violate? (1 mark)

c) Normalize to BCNF with all steps (4 marks)

2. (7 marks)

a) Explain ALL types of JOIN operations (5 marks)

b) When would you use FULL OUTER JOIN? (2 marks)

3. (7 marks) For tables:

Product(Product_ID, Name, Price)

Order(Order_ID, Customer_ID, Order_Date)

Order_Items(Order_ID, Product_ID, Quantity)

a) Write SQL to find total sales per product (3 marks)

b) Find products never ordered (2 marks)

c) Find customers with orders > $1000 (2 marks)

4. (7 marks)

a) What is query optimization? (2 marks)

b) Explain 5 query optimization techniques (5 marks)

5. (7 marks) Given: Student_Course_Hobby table

Student_ID Course Hobby

S1 Math Chess
S1 Math Reading
S1 CS Chess
S1 CS Reading
a) What normal form is violated and why? (3 marks)

b) Normalize to 4NF (4 marks)

ANSWER KEYS

Question Paper 1 - Answer Key

Part A - MCQ Answers

27. 1. b
28. 2. b
29. 3. c
30. 4. c
31. 5. a
32. 6. b
33. 7. b
34. 8. d
35. 9. c
36. 10. b

Question Paper 2 - Answer Key

Part A - MCQ Answers

37. 1. c
38. 2. b
39. 3. b
40. 4. b
41. 5. b
42. 6. c
43. 7. c
44. 8. b
45. 9. b
46. 10. c

______________________________________________________________________
__________

END OF DOCUMENT

All the Best for Your Exams! 📚✨