Module 3 :
Question 1 : Discuss the informal design guidelines for relation schema design.
Informal Design Guidelines for Relation Schema Design
These guidelines help avoid common problems such as redundancy, anomalies, and
poor structure in relational database design.
1. Clear Semantics of Attributes
● Every attribute should describe a property of the entire relation, not just part of it
● Prevents storing unrelated data in the same relation
● Example: Don’t mix employee and department data in one relation
2. Avoid Redundant Information
● Repeating values waste space and increase the risk of inconsistencies
● Redundancy leads to update anomalies
● Example: Storing department name repeatedly for every employee
3. Avoid Null Values
● Schema should be designed to minimize nulls, as they can be confusing and
affect query results
● Nulls may indicate missing, unknown, or not applicable data
● Better to split relation or use default values where applicable
4. Avoid Spurious Tuples (Join Anomalies)
● Improper decomposition and rejoining of tables may result in incorrect or
meaningless data
● Ensure decomposition is lossless (original relation can be reconstructed without
error)
● Use foreign keys and proper normalization techniques
5. Use of Functional Dependencies
● Understand and use functional dependencies to guide normalization
● Helps eliminate redundancy and ensure correct data dependencies
● Leads to better normal forms like 1NF, 2NF, 3NF, BCNF
Question 2 : Define 1NF, 2NF, and 3NF with example
1. First Normal Form (1NF)
● Each attribute must have atomic (indivisible) values
● No repeating groups or arrays allowed
● Ensures every field holds a single value
Example (Not in 1NF):
| StudentID | Name | Courses |
|-----------|-------|-------------|
| 101 | Rahul | DBMS, OS |
Converted to 1NF:
| StudentID | Name | Course |
|-----------|-------|--------|
| 101 | Rahul | DBMS |
| 101 | Rahul | OS |
2. Second Normal Form (2NF)
● Must be in 1NF
● No partial dependency (non-key attributes must depend on the whole composite
key)
Example (Not in 2NF):
| StudentID | CourseID | StudentName | CourseName |
Problem: StudentName depends only on StudentID, CourseName on CourseID
Converted to 2NF:
Student Table
| StudentID | StudentName |
|-----------|-------------|
| 101 | Rahul |
Course Table
| CourseID | CourseName |
|----------|------------|
| C01 | DBMS |
Enrollment Table
| StudentID | CourseID |
|-----------|----------|
| 101 | C01 |
3. Third Normal Form (3NF)
● Must be in 2NF
● No transitive dependency (non-prime attribute depending on another non-prime)
Example (Not in 3NF):
| StudentID | StudentName | DeptID | DeptName |
Problem: DeptName depends on DeptID, which is not a key
Converted to 3NF:
Student Table
| StudentID | StudentName | DeptID |
|-----------|-------------|--------|
| 101 | Rahul | D01 |
Department Table
| DeptID | DeptName |
|--------|----------|
| D01 | CSE |
Question 3 : Write the syntax for INSERT, UPDATE, and DELETE statements in SQL and
explain with suitable examples.
1. INSERT Statement :
Used to add new records into a table.
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
INSERT INTO Students (StudentID, Name, Age) VALUES (101, 'Rahul', 20);
2. UPDATE Statement :
Used to modify existing records in a table.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Example:
UPDATE Students SET Age = 21 WHERE StudentID = 101;
3. DELETE Statement :
Used to remove one or more rows from a table.
Syntax:
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Students WHERE StudentID = 101;
Question 4 : Discuss insertion, deletion and modification anomalies. Why are they
considered bad? Illustrate with examples.
Anomalies in DBMS
Anomalies are problems that occur in poorly designed (un-normalized) relational
databases. They lead to redundancy, inconsistency, and loss of data integrity.
1. Insertion Anomaly :
● Occurs when we cannot insert data into a table without the presence of other
unrelated data.
● Happens when two entities are stored in one table.
Example:
| StudentID | Name | Course | Instructor |
|-----------|-------|----------|------------|
| 101 | Rahul | DBMS | Prof. A |
● If we want to insert a new course that has no student yet, we cannot insert it
unless we also create a fake student row.
2. Deletion Anomaly :
● Occurs when deleting one piece of information causes unintended loss of
related data.
● Leads to loss of important data.
Example:
| StudentID | Name | Course | Instructor |
|-----------|-------|----------|------------|
| 102 | Tina | DBMS | Prof. A |
● If Tina is the only student in DBMS course, deleting her record also deletes all
information about Prof. A and the course DBMS.
3. Modification Anomaly :
● Occurs when the same data appears in multiple rows and needs to be updated
in all places.
● Failing to update everywhere leads to inconsistencies.
Example:
| StudentID | Name | Course | Instructor |
|-----------|-------|----------|------------|
| 103 | Amit | DBMS | Prof. A |
| 104 | Riya | DBMS | Prof. A |
● If Prof. A changes to Prof. B, we must update all rows. If we miss one, we get
inconsistent data.
Why Are Anomalies Bad?
● Cause redundancy and waste storage
● Lead to inconsistent data
● Create difficulties in inserting, deleting, or updating data
● Reduce data integrity and reliability
● Are avoided by normalizing the database schema (1NF, 2NF, 3NF)
Question 5 : Illustrate the following woth suitable example. 1.Datatypes in SQL, 2.
Substring Pattern Matching in SQL.
1. Data Types in SQL
SQL provides various data types to define the type of data a column can hold.
● INT – Stores integer values. Example: Age INT
● VARCHAR(n) – Variable-length string. Example: Name VARCHAR(50)
● CHAR(n) – Fixed-length string. Example: Gender CHAR(1)
● DATE – Stores calendar date. Example: DOB DATE
● DECIMAL(p,s) – Fixed-point number with precision and scale. Example: Salary
DECIMAL(10,2)
● FLOAT – Floating-point numbers
● BOOLEAN – Stores TRUE or FALSE values
Example Table:
CREATE TABLE Employee (
EmpID INT,
Name VARCHAR(50),
Salary DECIMAL(10,2),
JoinDate DATE
);
2. Substring Pattern Matching in SQL
Pattern matching is done using LIKE, %, and _ for flexible string search.
● % – Matches any sequence of characters
● _– Matches any single character
Examples:
-- Names starting with 'A'
SELECT * FROM Students WHERE Name LIKE 'A%';
-- Names ending with 'n'
SELECT * FROM Students WHERE Name LIKE '%n';
-- Names with 'an' in the middle
SELECT * FROM Students WHERE Name LIKE '%an%';
-- Names where second letter is 'a'
SELECT * FROM Students WHERE Name LIKE '_a%';
MODULE 2 :
Question 1 : Discuss the update operations and dealing with constraint voilations with
suitabke examples.
Update Operations in SQL :
1. INSERT – Adds new records
INSERT INTO Students (ID, Name, Age) VALUES (101, 'Rahul', 20);
2. UPDATE – Modifies existing records
UPDATE Students SET Age = 21 WHERE ID = 101;
3. DELETE – Removes records
DELETE FROM Students WHERE ID = 101;
4. SELECT – Reads records (used to verify)
SELECT * FROM Students WHERE ID = 101;
Constraints in SQL :
● PRIMARY KEY – Uniquely identifies each record
● FOREIGN KEY – Ensures referential integrity
● NOT NULL – Prevents missing values
● UNIQUE – Prevents duplicate values
● CHECK – Enforces condition-based validation
Constraint Violation Examples
1. Primary Key Violation
-- Error: Duplicate ID
INSERT INTO Students (ID, Name) VALUES (101, 'Raj');
Solution: Ensure ID is unique
2. Foreign Key Violation
-- Error: Dept D10 not found
INSERT INTO Employee (EmpID, DeptID) VALUES (1, 'D10');
Solution: Insert D10 in Department table first
3. NOT NULL Violation
-- Error: Name cannot be NULL
INSERT INTO Students (ID, Name) VALUES (102, NULL);
Solution: Provide a valid value
4. CHECK Constraint Violation
-- Age must be >= 18
INSERT INTO Students (ID, Name, Age) VALUES (103, 'Anil', 15);
Solution: Use age ≥ 18
Question 2 : Illustrate the relation algebra operators with examples for select and project
operation.
1. SELECT (σ) Operation :
Used to filter rows based on a condition
Symbol: σ (sigma)
Selects tuples (rows) that satisfy a predicate
Syntax: σ<condition>(Relation)
Example relation Students:
RollNo | Name | Age | Dept
101 | Ravi | 20 | CSE
102 | Riya | 19 | ECE
103 | Amit | 21 | CSE
Query: Select students from CSE department
Σ Dept = ‘CSE’ (Students)
Result:
RollNo | Name | Age | Dept
101 | Ravi | 20 | CSE
103 | Amit | 21 | CSE
2. PROJECT (π) Operation :
Used to select specific columns (attributes)
Symbol: π (pi)
Removes duplicate rows by default
Syntax: π<column1, column2,…>(Relation)
Example 1: Project only names of students
Π Name (Students)
Result:
Name
Ravi
Riya
Amit
Example 2: Project names and departments
Π Name, Dept (Students)
Result:
Name | Dept
Ravi | CSE
Riya | ECE
Amit | CSE
Question 3 : Discuss the characteristics of relations that make them different from
ordinary table and files.
Characteristics of Relations in DBMS :
1. Tuples are Unordered – Row order does not matter; relation is a set, not a list
2. Attributes are Unordered – Column order is irrelevant; accessed by name
3. Values are Atomic – Each cell holds a single, indivisible value (1NF)
4. All Tuples are Unique – No duplicate rows allowed
5. Each Attribute has a Unique Name – No duplicate column names
6. Same Data Type per Attribute – All values in a column must be of same type
7. Null Values Allowed – Represents unknown/missing/inapplicable values
8. Mathematically Defined – Based on set theory and predicate logic
Differences from Ordinary Tables/Files :
● Ordinary tables may allow duplicate rows; relations do not
● Order of rows/columns matters in tables/files; not in relations
● Tables/files may store multi-valued fields; relations follow atomicity
● Files may lack structure; relations follow strict schema rules
