G.L.

BAJAJ INSTITUTE OF TECHNOLOGY & MANAGEMENT

GREATER NOIDA

B. TECH 5th Sem- (IT)

SESSIONAL TEST Odd SEM 2024-25)

Database Management System (BCS501) Solution

SECTION A

a) List any four disadvantages of file system approach over database approach.

Solution:

Here are four disadvantages of the file system approach compared to the database approach:

Data Redundancy and Inconsistency:

1.
In file systems, the same data may be duplicated in different files, leading to redundancy. This redundancy increases the risk of data
o
inconsistencies because if one copy of the data is updated, but others are not, there will be mismatched data in different files.
Limited Data Sharing and Isolation:
2.
File systems do not provide mechanisms to efficiently share data among multiple users or applications. Different files may be isolated from
o
one another, making it difficult to integrate and access data across multiple programs.
Lack of Data Integrity:
3.
File systems do not enforce rules for maintaining data integrity, such as constraints to ensure that data is correct and follows certain formats
o
(e.g., ensuring that all IDs are unique). In contrast, databases offer integrity constraints, like primary keys and foreign keys, to maintain data
correctness.
Poor Data Security:
4.
File systems typically offer minimal security features. Managing user access control, preventing unauthorized access, and ensuring data
o
privacy are challenging in file systems. Database systems provide robust security mechanisms, allowing controlled access to data at a fine-
grained level, such as role-based permissions.

These limitations make database management systems (DBMS) more efficient and effective for managing large volumes of data compared to traditional file systems.

b) List the four functions of DBA.

The Database Administrator (DBA) plays a critical role in managing and maintaining a database system. Here are four key functions of a DBA:

Database Design and Implementation:

1.
The DBA is responsible for designing the logical and physical structure of the database. This includes defining schemas, relationships
o
between tables, indexing strategies, and data storage requirements to ensure optimal performance and scalability.
Data Security and Access Control:
2.
Ensuring data security is one of the most important functions of a DBA. They implement security policies, manage user roles and privileges,
o
and control access to sensitive data. This ensures that only authorized users can access or modify specific parts of the database.
Backup and Recovery Management:
3.
The DBA ensures that regular backups of the database are taken to prevent data loss in case of system failures, natural disasters, or other
o
incidents. They are also responsible for designing and implementing recovery strategies to restore data when necessary.
Performance Monitoring and Tuning:
4.
The DBA monitors database performance and takes steps to optimize it. This includes tuning database queries, managing indexes, optimizing
o
storage, and identifying bottlenecks that might slow down the system. They ensure that the database operates efficiently, especially as the data
grows.
These functions are critical to the smooth operation, security, and efficiency of a database system.

c) What is the difference between DROP and DELETE command?

The DROP and DELETE commands in SQL are used for different purposes, and they operate at different levels of the database. Here's the difference between them:

1. Purpose:

DROP: The DROP command is used to remove an entire database object (e.g., a table, database, index, or view). It permanently deletes the object and its

structure from the database.
DELETE: The DELETE command is used to remove rows (records) from an existing table without deleting the table structure. It allows for selective

removal of data based on conditions specified in the WHERE clause.

2. Scope of Operation:

DROP: Affects the entire database object (e.g., table, view) and removes everything related to it.

DELETE: Affects specific rows within a table. The table itself remains intact after the operation.


The DROP and DELETE commands in SQL are used for different purposes, and they operate at different levels of the database. Here's the difference between them:

1. Purpose:

DROP: The DROP command is used to remove an entire database object (e.g., a table, database, index, or view). It permanently deletes the object and its

structure from the database.
DELETE: The DELETE command is used to remove rows (records) from an existing table without deleting the table structure. It allows for selective

removal of data based on conditions specified in the WHERE clause.

2. Scope of Operation:

DROP: Affects the entire database object (e.g., table, view) and removes everything related to it.

DELETE: Affects specific rows within a table. The table itself remains intact after the operation.


3. Rollback:

DROP: Once a DROP command is executed, the operation cannot be undone (unless using advanced features like flashback in some databases). The entire

object and its data are lost permanently.
DELETE: The DELETE operation can be rolled back if used within a transaction, meaning you can undo the deletion and restore the data if necessary

(before committing the transaction).

4. TRUNCATE vs DELETE:

While not directly part of your question, it's worth noting:


TRUNCATE: Similar to DELETE, but it removes all rows from a table more efficiently without logging individual row deletions. However,
o
like DROP, it cannot be rolled back in many systems.
DELETE: Allows selective deletion of specific rows and generates log entries for each row being deleted.
o
Example:

DROP:


DROP TABLE employees;

DELETE:


DELETE FROM employees WHERE employee_id = 101;

In summary, DROP removes the entire structure of a table or other database object, while DELETE removes specific rows of data within a table but leaves the table
itself intact.

d) What is Relational Calculus?

Relational Calculus is a non-procedural query language in relational databases that allows users to describe what they want from the database, rather than specifying
how to retrieve it. It focuses on defining the properties of the desired result without describing the step-by-step procedure to achieve it, unlike relational algebra which
is more procedural.

There are two main types of relational calculus:

1. Tuple Relational Calculus (TRC):

In Tuple Relational Calculus, queries are expressed as formulas that specify properties of the desired tuples (rows).

The result of a TRC query is a set of tuples that satisfy a given condition.

TRC uses tuple variables that represent individual rows in a relation (table).


Syntax Example:

{t | P(t)}

Here, t is a tuple variable, and P(t) is a predicate (condition) that must be satisfied by the tuple.

For example, to find all employees with a salary greater than 50,000:


{t | t ∈ Employees ∧ t.salary > 50000}

2. Domain Relational Calculus (DRC):

In Domain Relational Calculus, queries are expressed by specifying the domain variables that represent the values of individual attributes (columns) of the

tuples.
The result of a DRC query is a set of attribute values that satisfy the given conditions.

Syntax Example:

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

Here, x1, x2, ..., xn are domain variables, and P(x1, x2, ..., xn) is a condition on these variables.

For example, to find the names and salaries of employees with a salary greater than 50,000:


{<name, salary> | ∃e (e ∈ Employees ∧ e.salary > 50000 ∧ e.name = name ∧ e.salary = salary)}

Key Characteristics of Relational Calculus:

Declarative Nature: It specifies what the desired result should be without defining the steps to obtain it.

Based on Predicate Logic: Relational calculus uses logical conditions (predicates) to filter and define the result set.

Safe Queries: In both TRC and DRC, queries should be "safe," meaning they should return a finite set of results, avoiding ambiguous or infinite results.


In summary, Relational Calculus is a foundational concept in relational database theory, allowing users to focus on the conditions that data must meet rather than the
operations to retrieve it. It is more declarative compared to the procedural nature of Relational Algebra.

e) List all prime and non-prime attributes In Relation R(A,B,C,D,E) with FD set F = {AB→C, B→E, C→D}.

A+=A

B+=B,E

AB+=ABCDE

Candidate key is:AB

So prime attributes are those Participited in andidate key :A,B

So non prime attributes are:C,D,E

Section: B

a) Explain the following: Generalization, Specialization and Aggregation.

1. Generalization (Bottom-Up Approach)

Definition: Generalization in database modeling is when we combine multiple entities with shared attributes into a single, more general entity (superclass).

It simplifies data by reducing redundancy.
Example: Suppose we have two entities, Student and Professor. Both share common attributes like Name, Address, and ID. We can generalize them into a

single entity called Person.

ER Diagram for Generalization:

Person
(ID, Name, Address)
 / \
Student Professor
(Course) (Department)

Here, Student and Professor are generalized into the Person entity because they share common attributes (ID, Name, Address).

2. Specialization (Top-Down Approach)

Definition: Specialization is the reverse of generalization. A generalized entity is divided into specialized entities that have unique attributes. This allows

more specific representation of entities.
Example: If we have a Person entity in the database with attributes such as Name and ID, we might specialize it into Student and Professor based on

specific attributes like Course for students and Department for professors.

ER Diagram for Specialization:

Person
(ID, Name, Address)
/ \
Student Professor
(Course) (Department)

Here, Person is specialized into Student and Professor based on their specific roles.

3. Aggregation (Whole-Part Relationship)

Definition: Aggregation is a special form of association in which a relationship between entities is treated as a higher-level entity. It represents a "whole-

part" relationship where the component entities can exist independently.
Example: Suppose we have two entities, Project and Employee. A Project may consist of multiple employees, but an employee can exist even if the project

ends. In this case, Project aggregates Employee.

ER Diagram for Aggregation:

Project ---<> Employee
(ProjectID) (EmpID, Name)

In this diagram:

Project aggregates Employee through an aggregation relationship.


Even if the Project is removed, Employee remains independent.


Complete ER Diagram Combining All Concepts:

Imagine we want to model a university system where Person can be generalized into Student and Professor; a Project aggregates Employees who may also be
professors.

Person
(ID, Name, Address)
/ \
Student Professor
(Course) (Department)
|
Employee
(EmpID, Salary)
|
Project ---<> Employee
(ProjID, Name)

Generalization: Person generalizes Student and Professor.


Specialization: Person is specialized into Student and Professor.

Aggregation: Project aggregates Employee, and employees (professors) can work on projects.


This diagram illustrates how generalization, specialization, and aggregation can be used together to model complex relationships in a database system.

b) State the procedural DML and nonprocedural DML with their differences.

rocedural DML (Data Manipulation Language) and Non-Procedural DML are two types of languages used to interact with databases for data retrieval and
manipulation. The key difference between them lies in how the user specifies queries and what control they have over the execution process.

1. Procedural DML:

Definition: Procedural DML requires the user to specify both the what (what data to retrieve) and how (how to retrieve the data). The user must explicitly

mention the sequence of operations (procedures) that the system needs to follow to achieve the desired result.
Example: Relational Algebra is a procedural query language, where the user specifies the sequence of operations like select, project, join, etc., to retrieve

the data.
Advantages: Provides fine control over how the query is executed, allowing for optimization or customization of the query process.

Disadvantages: More complex to write and maintain since the user needs to understand the internal structure of the database and specify the steps to get

the result.
Example:


sql
Copy code
SELECT A.name
FROM Employees A
JOIN Departments B
ON A.department_id = B.id
 WHERE B.name = 'HR';

Here, the user specifies the join between the two tables and the filtering condition.

2. Non-Procedural DML:

Definition: Non-Procedural DML requires the user to specify only what data to retrieve, without needing to specify the steps or the procedure to retrieve it.

The system (DBMS) decides the best method to execute the query.
Example: SQL (Structured Query Language) is a non-procedural query language where the user simply declares the desired result, and the system handles

the procedure for fetching the data.
Advantages: Easier to use because the user only specifies what data is needed, and the system figures out how to get it. It abstracts the complexity of the

internal operations from the user.
Disadvantages: The user has less control over how the query is executed, leaving the optimization entirely up to the DBMS.

Example:


SELECT name
FROM Employees
WHERE department = 'HR';

Here, the user only specifies what data they need (employees in HR), and the system determines how to retrieve that data.

Key Differences:

Aspect Procedural DML Non-Procedural DML

Control User specifies both what and how to get the data. User specifies only what data is needed.

Complexity More complex as it involves defining steps. Simpler as the system figures out the steps.

Optimization Requires manual optimization by the user. The system optimizes the query execution.

Examples Relational Algebra, Low-level programming languages (e.g., Cursors in SQL). SQL, Domain Relational Calculus, Tuple Relational Calculus.

Flexibility High flexibility in specifying the execution steps. Less flexibility, system manages execution.

Ease of Use More difficult and technical for users. Easier to use for general users.

In summary, Procedural DML gives the user more control over how data is retrieved but requires more effort and expertise, while Non-Procedural DML abstracts the
procedural complexity, making it easier to use but with less control over query optimization.
 c) What is Aggregate function in SQL? Explain and write SQL query for aggregate functions.

An aggregate function in SQL performs a calculation on a set of values and returns a single value. They are commonly used in combination with the GROUP BY
clause to summarize data from a table. Aggregate functions are crucial for analyzing large sets of data by performing tasks such as counting, summing, averaging, or
finding minimum/maximum values.

Common Aggregate Functions:

COUNT(): Returns the number of rows.

1.
SUM(): Returns the sum of a numeric column.
2.
AVG(): Returns the average of a numeric column.
3.
MAX(): Returns the maximum value in a column.
4.
MIN(): Returns the minimum value in a column.
5.

1. COUNT() Function

Description: Counts the number of rows in a result set or non-NULL values of a column.


Example Query:
sql
Copy code
SELECT COUNT(*) AS Total_Employees
FROM Employees;

This query will return the total number of rows (employees) in the Employees table.


2. SUM() Function

Description: Sums up all the values in a numeric column.



Example Query:
sql
Copy code
SELECT SUM(Salary) AS Total_Salary
FROM Employees;

This query will return the total sum of salaries of all employees in the Employees table.

3. AVG() Function

Description: Calculates the average value of a numeric column.



Example Query:
sql
Copy code
SELECT AVG(Salary) AS Average_Salary
FROM Employees;

This query will return the average salary of all employees.



4. MAX() Function

Description: Returns the highest value from a numeric or date column.



Example Query:
sql
Copy code
SELECT MAX(Salary) AS Highest_Salary
FROM Employees;

This query will return the maximum salary in the Employees table.


5. MIN() Function

Description: Returns the lowest value from a numeric or date column.



Example Query:
sql
Copy code
SELECT MIN(Salary) AS Lowest_Salary
FROM Employees;

This query will return the minimum salary in the Employees table.


Using Aggregate Functions with GROUP BY

To summarize data based on categories, aggregate functions are often used with the GROUP BY clause.
Example:

If you want to find the total salary for each department, you can use SUM() with GROUP BY:

SELECT Department, SUM(Salary) AS Total_Salary

FROM Employees
GROUP BY Department;

This query will return the total salary for each department by grouping rows based on the Department column.


Combining Aggregate Functions

You can combine multiple aggregate functions in one query to perform multiple calculations.

Example:
SELECT Department, COUNT(*) AS Total_Employees, AVG(Salary) AS Average_Salary
FROM Employees
GROUP BY Department;

This query returns the total number of employees and the average salary for each department.


Summary of Aggregate Functions:

COUNT(): Counts rows or non-null values.


SUM(): Sums up numeric values.

AVG(): Computes the average of numeric values.

MAX(): Finds the maximum value.

MIN(): Finds the minimum value.


These aggregate functions help in generating summary reports and answering analytical questions in SQL.

d) Explain in detail different types of joins with examples.

n SQL, joins are used to combine rows from two or more tables based on a related column between them. Joins help retrieve meaningful data by connecting tables that
share a logical relationship. SQL supports different types of joins, each designed for specific use cases.

1. INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables. Rows that do not meet the condition are excluded from the result set.

Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example:

Suppose we have two tables:

Employees:

EmpID Name DeptID

1 John 101

2 Sarah 102

3 Mike 101

Departments:

DeptID DeptName

101 HR

102 Finance

103 IT

SELECT Employees.Name, Departments.DeptName

FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Result:

Name DeptName

John HR

Sarah Finance

Mike HR

Only employees that have a matching DeptID in both tables are returned.


2. LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
LEFT JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Result:

Name DeptName

John HR

Sarah Finance

Mike HR

David NULL

If there is no matching DeptID in the right table (Departments), a NULL is returned for that column. All employees from the left table (Employees) are

returned.

3. RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.

Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
RIGHT JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Result:

Name DeptName

John HR

Sarah Finance

Mike HR

NULL IT

All rows from the right table (Departments) are returned. If there is no matching DeptID in the left table (Employees), a NULL is returned for the left table

columns.
4. FULL JOIN (FULL OUTER JOIN)

A FULL JOIN returns all rows when there is a match in either left or right table. It combines the result of both LEFT JOIN and RIGHT JOIN. Rows with no match in
one table will have NULL values in that table's columns.

Syntax:
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Result:

Name DeptName

John HR

Sarah Finance

Mike HR

NULL IT

David NULL

All rows from both tables are returned. Where no match exists, NULL is shown.


5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables. This means every row from the first table is combined with every row from the second table. It results
in a large number of rows, equal to the product of the number of rows in both tables.

Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;

Example:
SELECT Employees.Name, Departments.DeptName
FROM Employees
CROSS JOIN Departments;

Result:

Name DeptName

John HR
 Name DeptName

John Finance

John IT

Sarah HR

Sarah Finance

Sarah IT

Mike HR

Mike Finance

Mike IT

Each employee is paired with every department, resulting in all possible combinations.


6. SELF JOIN

A SELF JOIN is a join where a table is joined with itself. It is useful when there is a hierarchical or recursive relationship within the same table.

Syntax:
SELECT a.columns, b.columns
FROM table1 a
JOIN table1 b
ON a.column = b.column;

Example:

Assume the Employees table has a column ManagerID that refers to another employee.

Employees:

EmpID Name ManagerID

1 John NULL

2 Sarah 1

3 Mike 1

4 David 2

SELECT e1.Name AS Employee, e2.Name AS Manager

FROM Employees e1
LEFT JOIN Employees e2
ON e1.ManagerID = e2.EmpID;

Result:

Employee Manager

John NULL

Sarah John

Mike John
 Employee Manager

David Sarah

This query joins the Employees table with itself to find the manager of each employee.


Summary of Joins in SQL:

INNER JOIN: Returns only matching rows from both tables.


LEFT JOIN: Returns all rows from the left table and matching rows from the right table (NULL if no match).

RIGHT JOIN: Returns all rows from the right table and matching rows from the left table (NULL if no match).

FULL JOIN: Returns all rows from both tables (NULL where no match).

CROSS JOIN: Returns the Cartesian product of both tables.

SELF JOIN: Joins a table with itself to compare rows within the same table.


These joins are critical in SQL when working with relational data across multiple tables.

Write different inference rule for functional dependency.

e)
In Relational Database Theory, Functional Dependencies (FDs) represent relationships between attributes in a relation. There are several inference rules (also known as
Armstrong's Axioms) that help deduce all possible FDs from a given set of functional dependencies. These rules form the foundation for normalization and help ensure
that a database design is efficient and free from anomalies.

Inference Rules for Functional Dependencies (Armstrong's Axioms)

1. Reflexivity Rule (Trivial Dependency)

Definition: If an attribute set Y is a subset of attribute set X, then X functionally determines Y.


Notation: If Y⊆XY \subseteq XY⊆X, then X→YX \to YX→Y.


Example:

If X={A,B}X = \{A, B\}X={A,B} and Y={A}Y = \{A\}Y={A}, since Y⊆XY \subseteq XY⊆X, it follows that {A,B}→{A}\{A, B\} \to \{A\}{A,B}→{A}.

2. Augmentation Rule

Definition: If a functional dependency X→YX \to YX→Y holds, then by adding the same set of attributes Z to both sides, the dependency still holds.

Notation: If X→YX \to YX→Y, then XZ→YZXZ \to YZXZ→YZ (where XZXZXZ and YZYZYZ represent the concatenation of sets).


Example:

If {A}→{B}\{A\} \to \{B\}{A}→{B}, then {A,C}→{B,C}\{A, C\} \to \{B, C\}{A,C}→{B,C}. Here, the attribute C was added to both sides.
3. Transitivity Rule

Definition: If X→YX \to YX→Y and Y→ZY \to ZY→Z, then X→ZX \to ZX→Z (this is analogous to transitivity in mathematical relations).

Notation: If X→YX \to YX→Y and Y→ZY \to ZY→Z, then X→ZX \to ZX→Z.


Example:

If {A}→{B}\{A\} \to \{B\}{A}→{B} and {B}→{C}\{B\} \to \{C\}{B}→{C}, then {A}→{C}\{A\} \to \{C\}{A}→{C}.

4. Union Rule (Additivity)

Definition: If X→YX \to YX→Y and X→ZX \to ZX→Z, then X→YZX \to YZX→YZ. This means that if X determines Y and X determines Z, then X

determines both Y and Z together.
Notation: If X→YX \to YX→Y and X→ZX \to ZX→Z, then X→YZX \to YZX→YZ.


Example:

If {A}→{B}\{A\} \to \{B\}{A}→{B} and {A}→{C}\{A\} \to \{C\}{A}→{C}, then {A}→{B,C}\{A\} \to \{B, C\}{A}→{B,C}.

5. Decomposition Rule (Projectivity)

Definition: If X→YZX \to YZX→YZ, then X→YX \to YX→Y and X→ZX \to ZX→Z. This is the reverse of the Union Rule and states that if X

determines a set of attributes YZ, then X determines both Y and Z individually.
Notation: If X→YZX \to YZX→YZ, then X→YX \to YX→Y and X→ZX \to ZX→Z.


Example:

If {A}→{B,C}\{A\} \to \{B, C\}{A}→{B,C}, then {A}→{B}\{A\} \to \{B\}{A}→{B} and {A}→{C}\{A\} \to \{C\}{A}→{C}.

6. Pseudotransitivity Rule

Definition: If X→YX \to YX→Y and WY→ZWY \to ZWY→Z, then WX→ZWX \to ZWX→Z. This rule allows functional dependencies to propagate

through intermediate sets of attributes.
Notation: If X→YX \to YX→Y and WY→ZWY \to ZWY→Z, then WX→ZWX \to ZWX→Z.


Example:

If {A}→{B}\{A\} \to \{B\}{A}→{B} and {B,C}→{D}\{B, C\} \to \{D\}{B,C}→{D}, then {A,C}→{D}\{A, C\} \to \{D\}{A,C}→{D}.
7. Complementary Rule

Definition: If X→YX \to YX→Y and X→ZX \to ZX→Z, then X→Y−ZX \to Y - ZX→Y−Z. This rule states that if X determines Y and X determines Z,

then X also determines the difference between Y and Z.

Example of Applying Inference Rules

Given a set of functional dependencies:

A→BA \to BA→B


B→CB \to CB→C

A→DA \to DA→D


Using Transitivity and Union, we can deduce additional dependencies:

From A→BA \to BA→B and B→CB \to CB→C, by Transitivity: A→CA \to CA→C.
1.
From A→BA \to BA→B, A→DA \to DA→D, and A→CA \to CA→C, by Union: A→BCDA \to BCDA→BCD.
2.

Use of Inference Rules in Database Normalization

The inference rules are essential in normalizing databases by allowing us to:

Identify all functional dependencies in a relation.


Determine candidate keys and normalize the schema to remove redundancy and anomalies (e.g., into 1NF, 2NF, 3NF, and BCNF).


For example, by using the Decomposition Rule and Transitivity, we can split a table with functional dependencies into smaller tables, each in a higher normal form.

f) Explain different type of anomalies in DBMS.

In a Database Management System (DBMS), anomalies are problems that can arise when data is stored in an unnormalized or poorly designed database, particularly
when operations like insertions, deletions, or updates are performed. These anomalies can lead to data inconsistencies, redundancy, and inefficiencies. The primary
types of anomalies in DBMS are:

Insertion Anomaly
1.
Update Anomaly
2.
Deletion Anomaly
3.
These anomalies are usually addressed by normalizing the database into higher normal forms (1NF, 2NF, 3NF, BCNF, etc.).

1. Insertion Anomaly

An insertion anomaly occurs when you are unable to insert data into a table due to the absence of other data or when redundant data must be added.

Example:

Consider the following table storing Student and Course information:

StudentID StudentName CourseID CourseName Instructor

1 John C101 Math Dr. Smith

2 Sarah C102 Physics Dr. Brown

Problem: If a new course is introduced but no students have yet enrolled, you cannot insert the course information without adding a StudentID and

StudentName as well. This results in an insertion anomaly because you are forced to insert incomplete or redundant data.
Solution: This issue can be resolved by normalizing the data into two tables: one for Students and one for Courses.


Normalized Tables:

1. Students:

StudentID StudentName

1 John

2 Sarah

2. Courses:

CourseID CourseName Instructor

C101 Math Dr. Smith

C102 Physics Dr. Brown

2. Update Anomaly

An update anomaly occurs when data must be updated in multiple places, leading to data inconsistency or redundancy if not all instances are updated properly.

Example:

Consider the same unnormalized table storing Student and Course information:

StudentID StudentName CourseID CourseName Instructor

1 John C101 Math Dr. Smith

 StudentID StudentName CourseID CourseName Instructor

2 Sarah C102 Physics Dr. Brown

3 Mike C101 Math Dr. Smith

Problem: If Dr. Smith’s name changes to Dr. Johnson, the update needs to be made in multiple rows. If one row is updated but another is not, this will lead

to inconsistent data (some rows might still have Dr. Smith while others have Dr. Johnson).
Solution: By normalizing the database, you can store the instructor’s information separately, ensuring the instructor’s name is stored once, and any update

is propagated automatically.

Normalized Tables:

1. Courses:

CourseID CourseName Instructor

C101 Math Dr. Smith

C102 Physics Dr. Brown

2. Enrollments:

StudentID CourseID

1 C101

2 C102

3 C101

Now, if Dr. Smith's name changes, it only needs to be updated once in the Courses table.

3. Deletion Anomaly

A deletion anomaly occurs when deleting data causes unintended loss of other valuable information.

Example:

Again, using the same unnormalized table:

StudentID StudentName CourseID CourseName Instructor

1 John C101 Math Dr. Smith

2 Sarah C102 Physics Dr. Brown

3 Mike C101 Math Dr. Smith

Problem: If John drops out (i.e., you delete the row where StudentID = 1), you will also lose information about the Math course and its instructor, Dr.

Smith. This is an unintended loss of data.
Solution: By normalizing the data into separate tables, such as Students, Courses, and Enrollments, you can delete a student’s information without

affecting the course information.
Normalized Tables:

1. Students:

StudentID StudentName

1 John

2 Sarah

2. Courses:

CourseID CourseName Instructor

C101 Math Dr. Smith

C102 Physics Dr. Brown

3. Enrollments:

StudentID CourseID

1 C101

2 C102

Now, if John drops out (i.e., a deletion from the Enrollments table), the information about Math and Dr. Smith is still preserved in the Courses table.

Summary of Anomalies

Insertion Anomaly: Occurs when you can't insert a record without adding redundant or unrelated data.
1.
Update Anomaly: Occurs when you need to update the same data in multiple places, which can lead to inconsistencies.
2.
Deletion Anomaly: Occurs when deleting a record causes the unintended loss of other data.
3.
How to Prevent Anomalies:

These anomalies can be prevented by normalizing the database, which involves dividing the data into multiple related tables. Normalization ensures that:

Data is stored without redundancy.


Data integrity is maintained.

The database is flexible and scalable for updates, insertions, and deletions.


Normalization techniques include transforming the database into different normal forms like 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third
Normal Form), and BCNF (Boyce-Codd Normal Form). Each of these steps reduces redundancy and minimizes the risk of anomalies.

Section: C
An ER Diagram (Entity-Relationship Diagram) is a graphical representation of entities and the relationships between them in a database system. It helps in designing
and structuring databases by modeling the real-world entities and their interconnections. ER diagrams are widely used in database design to visually represent the
system and ensure the relationships and data structure align with the needs of the system.

Key Components of an ER Diagram:

Entity:
1.
An entity represents a real-world object or concept. In an ER diagram, entities are typically shown as rectangles. For example, in an Employee Project
Management System, the entities could include:
Employee
o
Project
o
Department
o
Attributes:
2.
Attributes provide information about the entities. They are typically shown as ovals and connected to their respective entities. Each entity can have
multiple attributes.
For example, attributes for an Employee entity could include:
Employee_ID
o
Name
o
Job_Title
o
Salary
o
Address
o

Attributes for a Project entity could include:

Project_ID
o
Project_Name
o
Start_Date
o
End_Date
o
Relationships:
3.
Relationships represent associations between two or more entities. They are represented by diamonds in ER diagrams, and lines connect entities to
relationships.
Example relationships in the employee project management system:
Works_On: Relationship between Employee and Project. An employee works on one or more projects.
o
Manages: Relationship between Employee and Department. An employee manages a department.
o
Belongs_To: Relationship between Employee and Department. An employee belongs to a department.
o
Primary Key:
4.
A primary key is a unique identifier for an entity. It is an attribute (or a combination of attributes) that uniquely identifies each record. For example:
Employee_ID in the Employee entity.
o
Project_ID in the Project entity.
o
Foreign Key:
5.
A foreign key is an attribute in one entity that links it to the primary key of another entity. It helps to establish the relationship between the entities. For
example:
The Department_ID in the Employee entity may be a foreign key linking to the Department entity.
o
Cardinality:
6.
Cardinality defines the number of instances of one entity that can be associated with the instances of another entity. It helps define the relationships
between entities. Cardinality can be:
 One-to-One (1:1)
o
One-to-Many (1
o

Many-to-Many (M
o

Examples in the Employee Project Management System:

7.
One Employee can work on multiple Projects (One-to-Many).
o
A Project can have multiple Employees working on it (Many-to-Many).
o
One Department can be managed by one Employee (One-to-One).
o

Example of an ER Diagram for an Employee Project Management System

Entities:
1.
Employee (Employee_ID, Name, Job_Title, Salary)
o
Project (Project_ID, Project_Name, Start_Date, End_Date)
o
Department (Department_ID, Department_Name)
o
Relationships:
2.
Works_On (Employee ↔ Project): An employee works on multiple projects, and a project can have multiple employees.
o
Manages (Employee ↔ Department): An employee manages one department, and each department has one manager.
o
Belongs_To (Employee ↔ Department): An employee belongs to one department, and a department can have many employees.
o
Cardinalities:
3.
One Employee works on many Projects, and many Projects can have many Employees (Many-to-Many).
o
One Employee manages one Department (One-to-One).
o
One Department can have many Employees (One-to-Many).
o
b) Describe the three-schema architecture. Why do we need mappings between schema levels? How do different schema definition languages support this architecture?

he three-schema architecture is a framework used to describe databases and their data management systems in a structured way, involving three levels of abstraction.
These levels help in managing complexity, supporting data independence, and ensuring a clear separation between the physical storage and user interaction with data.
The three levels are:

Internal Schema (Physical Level):

1.
This schema defines how the data is stored physically in the system. It describes the storage structures, file formats, indexes, and data access
o
paths. The internal schema is closest to the hardware and deals with low-level details like record placement and compression techniques.
Conceptual Schema (Logical Level):
2.
This schema represents the logical view of the entire database. It abstracts away the complexities of how data is physically stored and focuses
o
on defining what data is stored and the relationships between them. This level is concerned with the structure of the database, including
tables, attributes, constraints, and relationships. It ensures data integrity and consistency without being concerned with the physical data
representation.
External Schema (View Level):
3.
The external schema defines how end-users and applications view the data. Different users may have different external views of the same
o
database, depending on their needs. For example, one user might see only a subset of the data, or data might be presented in a simplified
 form. This level ensures that different users can interact with the data in ways that meet their specific requirements, without needing to worry
about the details of the underlying database structure.

Importance of Mappings between Schema Levels

Mappings between these levels are essential for achieving data abstraction and data independence, which are key goals in database management. Here's why they are
needed:

Internal/Conceptual Mapping: This defines how the data structures in the conceptual schema are translated to the internal schema. It allows the physical

storage to change (e.g., moving from a traditional disk to an SSD, or changing the index structures) without affecting the logical representation of the data
or the applications using it. This is referred to as physical data independence.
Conceptual/External Mapping: This defines how the logical data structures in the conceptual schema are presented to users through their external schemas.

It ensures that different users can have different views of the same data without affecting the actual database design or other users' views. Changes to the
conceptual schema, such as adding new attributes, should not impact existing user views. This is called logical data independence.

Schema Definition Languages and Their Support for the Three-Schema Architecture

Different schema definition languages are used to define and support the schemas at these levels, and they have features to ensure mappings between them:

SQL (Structured Query Language): SQL supports both the conceptual and external schema levels. At the conceptual level, SQL defines the logical

structure of the database (e.g., tables, constraints, views) through CREATE TABLE, ALTER TABLE, and CONSTRAINT commands. At the external
level, SQL allows the creation of views (CREATE VIEW), which provide different user perspectives of the data without modifying the underlying
structure. SQL also provides mechanisms to define triggers, roles, and privileges, supporting multiple external views.
DDL (Data Definition Language): The internal schema can be managed using a DDL that is specific to the underlying DBMS. DDL commands define

storage structures, indexing, and physical attributes of data at the internal level, while maintaining abstraction from higher-level views.
Mapping Mechanisms: Database management systems (DBMS) use internal mechanisms to maintain mappings between these levels. These may be

automatic, or they may require manual setup using specific DBMS tools or scripts. For example, when a view (external schema) is created in SQL, the
DBMS automatically handles the necessary mapping to the underlying conceptual schema.

In summary, the three-schema architecture separates concerns about how data is stored, structured, and presented to users, enabling both logical and physical data
independence. Schema definition languages, especially SQL, support this architecture by providing tools to define, query, and manipulate data at different abstraction
levels.
4) Consider the following schema for institute library:
Student (RollNo, Name, Father_ Name, Branch)
Book (ISBN, Title, Author, Publisher)
Issue (RollNo, ISBN, Date-of –Issue)
Write the following queries in SQL and relational algebra:

(i)List roll number and name of all students of the branch ‘CSE’.
(ii)Find the name of student who has issued a book published by ‘ABC’ publisher.
(iii) List title of all books and their authors issued to a student ‘RAM’.
(iv) List title of all books issued on or before December 1, 2020.
Solution: QL Queries

1) SELECT RollNo, Name

FROM Student
WHERE Branch = 'CSE';

Relational Algebra:

πRollNo,Name(σBranch=′CSE′(Student))

ii) SELECT S.Name FROM Student S, Issue I, Book B WHERE S.RollNo = I.RollNo AND I.ISBN = B.ISBN AND B.Publisher = 'ABC';

Relational Algebra:

πName(σPublisher=′ABC′(Student⋈RollNoIssue⋈ISBNBook)
iii) SELECT B.Title, B.Author
FROM Book B, Issue I, Student S
WHERE S.RollNo = I.RollNo
AND I.ISBN = B.ISBN
AND S.Name = 'RAM';

Relational Algebra:

πTitle,Author(σName=′RAM′(Student⋈RollNoIssue⋈ISBNBook)

iv)  SELECT B.Title

FROM Book B, Issue I
WHERE I.ISBN = B.ISBN
AND I.Date_of_Issue <= '2020-12-01';

Relational Algebra:

πB.Title(σI.Date_of_Issue≤′2020−12−01′(Book ⋈B.ISBN=I.ISBN Issue))

 List all books published by publisher ‘ABC’.

SQL:

SELECT Title
FROM Book
WHERE Publisher = 'ABC';

Relational Algebra:

πTitle(σPublisher=′ABC′(Book)

Consider the following relation.

Student(RollNo, Name,Branch)
Book(Isbn, Title, Author, Publisher)
Issue(Rollno, Isbn, date_of_issue).
Write the query in Relational calculus and SQL .
List the Roll Number and Name of All IT Branch Students.
i)
Find the name of students who have issued a book of publication ‘T.M.H’.
ii)
List the title of all books issued on or before 28/10/2020.
iii)
List the name of student who will read the book of author named ‘Kunal’.
iv)
List the Roll Number and Name of All IT Branch Students.

Relational Calculus:
{S.RollNo,S.Name ∣ Student(S) ∧ S.Branch=′IT′}

SQL:
SELECT RollNo, Name
FROM Student
WHERE Branch = 'IT';

ii) Find the name of students who have issued a book of publication ‘T.M.H’.

Relational Calculus:
{S.Name ∣ Student(S) ∧ ∃I(Issue(I) ∧ I.RollNo=S.RollNo ∧ ∃B(Book(B) ∧ B.Isbn=I.Isbn ∧ B.Publisher=′T.M.H′))}

SQL:
SELECT DISTINCT S.Name
FROM Student S, Issue I, Book B
WHERE S.RollNo = I.RollNo
AND I.Isbn = B.Isbn
AND B.Publisher = 'T.M.H';

iii) List the title of all books issued on or before 28/10/2020.

Relational Calculus:
{B.Title ∣ Book(B) ∧ ∃I(Issue(I) ∧ I.Isbn=B.Isbn ∧ I.date_of_issue≤′28/10/2020′)}

SQL:
SELECT DISTINCT B.Title
FROM Book B, Issue I
WHERE B.Isbn = I.Isbn
AND I.date_of_issue <= '2020-10-28';

iv) List the name of student who will read the book of author named ‘Kunal’.

Relational Calculus:
{S.Name ∣ Student(S) ∧ ∃I(Issue(I) ∧ I.RollNo=S.RollNo ∧ ∃B(Book(B) ∧ B.Isbn=I.Isbn ∧ B.Author=′Kunal

SQL:
SELECT DISTINCT S.Name
FROM Student S, Issue I, Book B
WHERE S.RollNo = I.RollNo
AND I.Isbn = B.Isbn
AND B.Author = 'Kunal';
These are the equivalent relational calculus and SQL expressions for the given queries.