MODULE 3

Q1: Discuss the informal design guidelines for relation

schema design.
ANS:
The informal design guidelines for relation schema design aim to improve the clarity,
efficiency, and correctness of a database schema. These guidelines help database
designers create schemas that are understandable, maintainable, and free from
common data anomalies. The key principles involve ensuring clear semantics,
minimizing redundancy, reducing null values, and preventing the generation of spurious
tuples.

1. Impart Clear Semantics to Attributes

Guideline: Ensure that each relation clearly represents a single real-world entity or
relationship. Attributes should have meaningful names that reflect their purpose.

Example:

Bad Design:

STUDENT_COURSE(EmpID, Ename, Cname, DeptName, DeptHead)

• Ambiguous because it mixes student, course, and department data without

clear separation.

Proper Design:

STUDENT(EmpID, Ename, Dno)

COURSE(Cno, Cname, Dno)

DEPARTMENT(Dno, Dname, DHead)

ENROLLMENT(EmpID, Cno)

• Each relation clearly represents a single entity or relationship.

• For example, STUDENT pertains only to students, COURSE for courses, etc.

2. Reduce Redundant Information in Tuples

Guideline: Avoid storing the same data repeatedly, which can lead to inconsistencies
and wasted space.

Example :
Bad Design:

STUDENT(EmpID, Ename, Dname, DHead)

• If multiple students are in the same department, Dname and DHead are
repeated.

Proper Design:

STUDENT(EmpID, Ename, Dno)

DEPARTMENT(Dno, Dname, DHead)

• Department info is stored once in DEPARTMENT.

• Changes to a department's info only need to be updated once.

3. Reduce NULL Values

Guideline: Design schemas to avoid NULLs unless necessary, because NULLs can
cause confusion and complicate queries.

Example :

Bad Design:

EMPLOYEE(EmpID, Ename, OfficeNumber)

• Only some employees have offices, leading to NULLs in OfficeNumber.

Proper Design:

EMPLOYEE(EmpID, Ename)

OFFICE(EmpID, OfficeNumber)

• OFFICE stores office info only for employees who have offices.

• No NULLs in OfficeNumber for employees without offices.

4. Disallow Generating Spurious Tuples

Guideline: Design relations so that join operations don’t produce meaningless or

invalid data.

Example :

Problematic Approach:

Suppose you combine employees and courses into one relation:

EMPLOYEE_COURSE(EmpID, Ename, Cname, Cnumber)

Without constraints, a join could produce invalid combinations (e.g., Employee A with
Course X and Employee B with Course Y).

Proper Design:

EMPLOYEE(EmpID, Ename)

COURSE(Cno, Cname)

ENROLLMENT(EmpID, Cno)

• ENROLLMENT links employees and courses explicitly.

• Proper foreign key constraints prevent spurious tuples (e.g., an employee cannot
be linked to a non-existent course).

Q2 : What is Normalization. Define INF, 2NF, and 3NF with

examples.
ANS:
Normalization is a systematic process used in database design to organize data in a way
that reduces redundancy and minimizes anomalies such as insertion, update, and
deletion errors. It involves decomposing complex relations into simpler, well-structured
relations that satisfy specific normal forms or conditions.

Key Objectives of Normalization:

• Eliminate Redundancy: Avoid storing the same data multiple times.

• Ensure Data Integrity: Maintain consistency across the database.

• Simplify Data Maintenance: Make updates, deletions, and insertions easier and
less error-prone.

• Facilitate Efficient Querying: Organize data for optimal retrieval.

Process of Normalization:

Normalization proceeds through multiple steps called normal forms, each with stricter
requirements:

1. First Normal Form (1NF): Ensures that all attributes are atomic (indivisible
values) and that there are no repeating groups.
 2. Second Normal Form (2NF): Achieved when the relation is in 1NF and all non-
prime attributes are fully functionally dependent on the primary key.

3. Third Normal Form (3NF): Achieved when the relation is in 2NF and there are no
transitive dependencies (non-prime attributes depend on other non-prime
attributes).

4. Higher Normal Forms (BCNF, 4NF, 5NF): Address more complex dependencies
like multivalued dependencies and join dependencies to further refine the
schema.

1. INF (Informal Normal Form)

Definition: INF is an informal, conceptual guideline rather than a formal rule. It
emphasizes the importance of designing relation schemas so that their attributes
clearly represent a single real-world entity or relationship. The goal is to have attributes
with meaningful and unambiguous semantics, which makes the schema easier to
understand, maintain, and less prone to anomalies.

Key Points:

• Attributes should have clear, understandable meanings.

• A relation should ideally model a single entity or a specific relationship.

• Combining multiple unrelated entities or relationships into a single relation leads

to semantic confusion and redundancy.

Example:

• Poor schema: STUDENT_COURSE(StudentID, StudentName, Major, CourseID,

CourseName, Instructor)

Issue: It combines student information with course information into one relation, which
muddles the semantics.

• Better schema (following INF):

• STUDENT(StudentID, StudentName, Major)

• COURSE(CourseID, CourseName, Instructor)

• ENROLLMENT(StudentID, CourseID)

2. 2NF (Second Normal Form)

Definition: A relation is in 2NF if it satisfies two conditions:

• It is already in 1NF (all attributes are atomic—indivisible).

• There are no partial dependencies: Non-prime attributes (attributes not part of

any candidate key) should not depend on only part of a composite primary
key.

In essence: Every non-prime attribute must depend fully on the entire primary key, not
just a part of it.

Understanding Partial Dependency:

• Partial dependency: When a non-prime attribute depends on only a subset

(part) of a composite key.

• Example:

Suppose we have a relation:

ENROLLMENT(StudentID, CourseID, InstructorName)

• Candidate key: (StudentID, CourseID) (together they uniquely identify each

record).

• Problem: InstructorName depends only on CourseID (since each course has a

fixed instructor), not on both (StudentID, CourseID).

Decomposition into 2NF:

• ENROLLMENT(StudentID, CourseID)

• COURSE(CourseID, InstructorName)

This decomposition removes partial dependency, ensuring the relation is in 2NF.

3. 3NF (Third Normal Form)

Definition: A relation schema is in 3NF if:

• It is in 2NF.

• It has no transitive dependencies: Non-prime attributes should

depend directly on the primary key, not on other non-prime attributes.

Understanding Transitive Dependency:

• Transitive dependency: When a non-prime attribute determines another non-

prime attribute, which then determines a third attribute, creating an indirect
dependency on the primary key.
Example:

EMPLOYEE(EmpID, EmpName, DeptName, DeptLocation)

• EmpID is the primary key.

• DeptName depends on EmpID (which department the employee works in).

• DeptLocation depends on DeptName (the location of that department).

• Issue: DeptLocation depends transitively on EmpID via DeptName. This violates

3NF.

Solution:

Decompose into:

• EMPLOYEE(EmpID, EmpName, Dno)

• DEPARTMENT(Dno, Dname, DLocation)

Now, each relation depends directly on its primary key, and transitive dependencies are
eliminated.

Summary Table for Thoroughness:

Normal Key
Example of Violation Decomposition / Correct Approach
Form Requirements

Clear
semantics, Mixing multiple entities Separate schemas per entity or
INF
meaningful in one relation relationship
attributes

No partial
dependency InstructorName depends Separate
2NF
on a only on CourseID into ENROLLMENT and COURSE relations
composite key

DeptLocation depends
No transitive Decompose
3NF on DeptName, which
dependency into EMPLOYEE and DEPARTMENT
depends on EmpID

Significance in Database Design:

• INF helps in designing schemas that are easy to interpret and maintain.
 • 2NF reduces redundancy caused by partial dependencies—that is, attributes
stored multiple times.

• 3NF minimizes update anomalies caused by transitive dependencies, ensuring

each non-prime attribute is directly dependent on keys only.

Q3: Write the syntax for INSERT, UPDATE and DELETE

statements in SQL and explain with suitable examples.
Ans:
1. INSERT Statement
Syntax

-- To insert a single tuple with values in order

INSERT INTO relation_name

VALUES (value1, value2, ..., valueN);

-- To insert a tuple by specifying specific attributes

INSERT INTO relation_name (attribute1, attribute2, ..., attributeN)

VALUES (value1, value2, ..., valueN);

Explanation & Examples

• Insert a tuple with values in the order of attributes defined in the table:

INSERT INTO EMPLOYEE VALUES ('John', 'Doe', 1234, '1980-05-15');

• Insert by specifying attribute names (attributes with NOT NULL or no default

values):

INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Bdate)

VALUES ('Jane', 'Smith', 5678, '1990-08-25');

• Insert multiple tuples (via multiple VALUES clauses):

INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Bdate)

VALUES
('Alice', 'Brown', 1111, '1985-03-10'),

('Bob', 'Johnson', 2222, '1979-12-05');

2. UPDATE Statement
Syntax

UPDATE relation_name

SET attribute1 = value1, attribute2 = value2, ...

WHERE condition;

Explanation & Examples

• Update specific attribute(s) for tuples satisfying the condition:

UPDATE EMPLOYEE

SET Salary = Salary * 1.1

WHERE Dno = 5;

(This gives a 10% raise to all employees in department 5.)

• Update multiple attributes:

UPDATE PROJECT

SET Pname = 'New Project Name', Plocation = 'New Location'

WHERE Pnumber = 10;

• Update all tuples if no WHERE clause is specified (be cautious!):

UPDATE EMPLOYEE

SET Salary = 50000;

(Sets salary to 50,000 for all employees.)

3. DELETE Statement
Syntax

-- Delete specific tuples

DELETE FROM relation_name

WHERE condition;
-- Delete all tuples (empty table)

DELETE FROM relation_name;

Explanation & Examples

• Delete tuples satisfying a condition:

DELETE FROM EMPLOYEE

WHERE Ssn = 1234;

(Deletes the employee with Ssn 1234.)

• Delete multiple tuples based on a condition:

DELETE FROM PROJECT

WHERE Plocation = 'Old Location';

• Delete all tuples in a table:

DELETE FROM EMPLOYEE;

(Removes all records, but the table remains in the database as an empty relation.)

Summary Table

SQL
Syntax Description Example
Command

INSERT INTO relation INSERT INTO EMPLOYEE

Adds new tuples
INSERT [(attributes)] VALUES (Fname, Lname) VALUES
to a table
(values); ('Tom', 'Brown');

UPDATE relation SET UPDATE EMPLOYEE SET

Modifies existing
UPDATE attribute = value WHERE Salary = Salary * 1.05
tuples
condition; WHERE Dno = 3;

Removes tuples
DELETE FROM relation DELETE FROM EMPLOYEE
DELETE matching
WHERE condition; WHERE Ssn = 9876;
condition
Q4: Discuss insertion, deletion and Update anomalies. Why
are they considered bad? Illustrate with examples.
ANS:
1. Insertion Anomalies
Definition

Insertion anomalies happen when there are difficulties or restrictions in adding new
data to the database due to schema design. This often occurs because certain
attributes depend on other data that must already exist, or because of the way data is
structured.

Why Are They Bad?

They prevent inserting data in a straightforward way and may lead to incomplete or
inconsistent data if forced or improperly handled.

• Example:

EmployeeID EmployeeName Department DepartmentBudget

101 Alice HR 50,000

102 Bob HR 50,000

• Scenario: The HR department's budget increases from 50,000 to 55,000.

• Incorrect Update:

• Update only Alice's row:

EmployeeID EmployeeName Department DepartmentBudget

101 Alice HR 55,000

102 Bob HR 50,000

• Result:

• Inconsistency: HR's budget is shown as 55,000 for Alice but still 50,000 for
Bob. This inconsistency illustrates an update anomaly.
2. Deletion Anomalies
Definition

Deletion anomalies occur when deleting certain data accidentally causes the loss of
other valuable data, especially when related data is stored redundantly.

Why Are They Bad?

Data that is relevant and should remain stored gets lost unintentionally, leading to loss
of critical information.

• Example:

EmployeeID EmployeeName Department DepartmentBudget

101 Alice HR 50,000

102 Bob HR 50,000

103 Charlie IT 100,000

• Scenario: Deleting the last employee in the "IT" department.

• Action:

• Delete Charlie's record:

EmployeeID EmployeeName Department DepartmentBudget

101 Alice HR 50,000

102 Bob HR 50,000

• Problem:

• If department information is stored only with employee records, deleting

last employee of "IT" causes loss of department data, unless separated
properly.

• Result: When the last employee of a department is removed, the department

info might be mistakenly lost if not stored separately.
3. Update Anomalies
Definition

Update anomalies happen when an update in one place causes inconsistent data
because the same data is stored redundantly in multiple places.

Why Are They Bad?

They cause data inconsistency if all copies are not updated consistently, making the
database unreliable.

• Example:

EmployeeID EmployeeName Department DepartmentBudget

101 Alice HR 50,000

102 Bob HR 50,000

• Scenario: The HR department's budget increases from 50,000 to 55,000.

• Incorrect Update:

• Update only Alice's row:

EmployeeID EmployeeName Department DepartmentBudget

101 Alice HR 55,000

102 Bob HR 50,000

• Result:

• Inconsistency: HR's budget is shown as 55,000 for Alice but still 50,000 for
Bob. This inconsistency illustrates an update anomaly.

Summary Table of Update Anomalies:

Type Description Example Issue

Update Budget updated only for

Inconsistent data due to partial updates
Anomaly some employees, not all

Difficulties inserting new data, especially

Insertion Adding a department
when schema constraints enforce
Anomaly with no employees
redundancy or absence
Type Description Example Issue

Deleting the last

Deletion Loss of valuable data due to deletion of
employee deletes
Anomaly related records
department info

How to Avoid These Anomalies?

• Normalization: Organize data into well-structured tables to eliminate

redundancy.

• Design Decomposition: Split large tables into smaller, related tables.

• Use of foreign keys and constraints: Maintain data integrity and consistency.

Q5: Illustrate the following with suitable examples:

(i) Datatypes in SQL
(ii) Substring Pattern Matching in SQL.
ANS:
1. Datatypes in SQL
SQL supports various data types to define the kind of data stored in each column of a
table. Proper selection of data types ensures efficient storage and appropriate data
validation.

Basic Data Types in SQL

Numeric Data Types

Data Type Description Example Usage

Whole numbers (e.g., -2,147,483,648 to

INTEGER / INT Age, Quantity
2,147,483,647)

SMALLINT Smaller range of integers Number of children

Floating-point numbers with approximate

FLOAT / REAL Temperature, Price
precision
Data Type Description Example Usage

DOUBLE
Double-precision floating-point numbers Scientific calculations
PRECISION

Exact fixed-point numbers; p total Salary, Price, Monetary

DECIMAL(p, s)
digits, s digits after decimal values

Character String Data Types

Data Type Description Example Usage

Fixed-length character string; Gender

CHAR(n) / CHARACTER(n)
padded with spaces if shorter (e.g., CHAR(1))

VARCHAR(n) / CHAR Variable-length string up

Name, Email
VARYING(n) to n characters

Large text data, no specific length Article content,

TEXT / CLOB
in many systems Comments

Bit-string Data Types

Data Type Description Example Usage

BIT(n) Fixed-length bit string of n bits Flags, Boolean arrays

BIT VARYING(n) Varying length bit string Complex binary data

Date/Time Data Types

Data Type Description Example Usage

DATE Date values (year, month, day) Birthdate, Hire date

TIME Time of day Login time

TIMESTAMP Date and time Transaction time

2. Substring Pattern Matching in SQL

SQL provides pattern matching capabilities using the LIKE operator, which allows
searching for strings based on specific patterns.

Using LIKE for Pattern Matching

Pattern Character Description Example

% Matches zero or more characters 'A% matches 'Apple', 'A'

_ (underscore) Matches exactly one character 'B_t' matches 'Bat', 'Bet'

Examples

Example 1: Find employees with addresses in Houston, Texas

SELECT Fname, Lname

FROM EMPLOYEE

WHERE Address LIKE '%Houston, Texas';

• % before the string means addresses ending with 'Houston, Texas'.

Example 2: Find employees born in the 1950s

SELECT Fname, Lname

FROM EMPLOYEE

WHERE Bdate LIKE '195_';

• '195_' matches any date starting with '195' and followed by any single character,
which could be used if the date field strings are formatted as '1950', '1951',
etc. (Note: Usually, LIKE on date types may require casting to strings depending
on RDBMS.)

Example 3: Use escape characters to match literal % or _

Suppose you want to find addresses containing the literal % symbol:

SELECT Address

FROM EMPLOYEE

WHERE Address LIKE '%\%%' ESCAPE '\';

• Here, \% escape sequence matches a literal %.

Summary

Concept Syntax Example

Match any string (zero or more chars) LIKE '%pattern%'

Concept Syntax Example

Match a single character LIKE '_attern'

Match literal % or _ Use ESCAPE clause: LIKE '%\%%' ESCAPE '\'

Q6: What is Functional dependency? Explain the inference

rules for functional dependency with proof.
Ans:
Functional Dependency (FD)
Definition:

A functional dependency is a constraint between two sets of attributes in a relation

schema. Formally, for a relation schema R, a set of attributes X functionally
determines attribute Y (denoted as X→Y) if and only if whenever two tuples agree on
the attributes X, they must also agree on the attribute Y.

In other words:

• For all tuples r1,r2 in the relation R, if r1[X]=r2[X], then r1[Y]=r2[Y].

Note:

• X is called the determinant.

• Y is called the dependent.

Example:

In a relation Employee(EmpID, EName, DeptID, DName):

• EmpID→EName because each employee has a unique ID.

• DeptID→DName because each department ID uniquely determines the

department name.
Inference Rules for FDs and Their Proofs
The inference rules allow us to derive new FDs from existing ones. These rules are
known as Armstrong’s Axioms:

1. Reflexivity

Statement: If Y⊆X, then X→Y.

Intuition: Any set of attributes determines its subset.

Proof:

• Since Y⊆X, any tuple's Y components are part of its X components.

• Therefore, knowing X, you automatically know Y.

Formal proof:

Given any tuple r, r[X]⟹r[Y] because Y⊆X.

Hence, If Y⊆X, then X→Y

2. Augmentation

Statement: If X→Y, then XZ→YZ for any set of attributes Z.

Proof:

• Assume X→Y.

• For any two tuples r1,r2, if r1[XZ]=r2[XZ], then r1[X]=r2[X] and r1[Z]=r2[Z].

• Since r1[X]=r2[X], r1[Y]=r2[Y] (by the FD X→Y).

• And, because r1[Z]=r2[Z], it follows that:

r1[XZ]=r2[XZ]⟹r1[YZ]=r2[YZ]

• Thus, XZ→YZ.

Formal proof:

1. From the assumption X→Y,

2. For any tuples r1,r2, r1[X]=r2[X]⟹r1[Y]=r2[Y]

3. Consider:

r1[XZ]=r2[XZ]⟹r1[X]=r2[X],r1[Z]=r2[Z]
 4. As r1[X]=r2[X], r1[Y]=r2[Y],

5. And, since r1[Z]=r2[Z], then:

r1[YZ]=r2[YZ]

which implies:

XZ→YZ

Hence, If X→Y, then XZ→YZ

3. Transitivity

Statement: If X→Y and Y→Z, then X→Z.

Proof:

• Starting with X→Y,

• and Y→Z,

• For any tuples r1,r2, if r1[X]=r2[X],

• Then from X→Y, r1[Y]=r2[Y].

• And from Y→Z, since r1[Y]=r2[Y], it follows r1[Z]=r2[Z].

• Therefore, r1[X]=r2[X] implies r1[Z]=r2[Z], which is exactly X→Z.

Formal proof:

1. X→Y,

2. Y→Z,

3. For any tuples r1,r2 in R, r1[X]=r2[X]⇒r1[Y]=r2[Y](by X→Y)

4. And since r1[Y]=r2[Y], and Y→Z, r1[Z]=r2[Z]

5. Therefore, r1[X]=r2[X]⇒r1[Z]=r2[Z] which proves X→Z.

Additional Derived Rules (from Armstrong's Axioms)

Union Rule

Statement: If X→Y and X→Z, then X→YZ.

Proof:

• From X→Y and X→Z,

 • For any tuples r1,r2, r1[X]=r2[X]⇒r1[Y]=r2[Y],r1[Z]=r2[Z]

• Combining these:

r1[X]=r2[X]⇒r1[YZ]=r2[YZ]

• Hence, X→YZ.

Decomposition Rule

Statement: If X→YZ, then X→Y and X→Z.

Proof:

• From X→YZ,

• For any tuples r1,r2, r1[X]=r2[X]⇒r1[YZ]=r2[YZ]

• This implies: r1[Y]=r2[Y],r1[Z]=r2[Z]

• So,

X→YandX→Z

Summary of Inference Rules and Proofs

Rule Statement Boundary / Proof

By the definition of tuples; the values in Y are

Reflexivity Y⊆X⇒X→Y
part of X.

Adding extra attributes Z to both sides preserves

Augmentation X→Y⇒XZ→YZ
the FD.

Chain the implications using the definition of

Transitivity X→Y and Y→Z⇒X→Z
FDs.

Combine dependencies that share the same

Union X→Y and X→Z⇒X→YZ
determinant.

Split composite dependencies into simpler

Decomposition X→YZ⇒X→Y and X→Z
ones.
Q7: Consider two sets of functional dependency. Consider
X={A→B, B→C, E} and Y={A→B, B→C, A→C} are they equivalent?
Ans: