Internal Assignment

Database Management Systems (DCA2102)

Set-I

Q1. What do you mean by cardinality? What are the different types of Cardinalities in
RDBMS? Explain by giving suitable example.

Cardinality in the context of a Relational Database Management System (RDBMS) refers

to the uniqueness of data values in a particular column or the relationship between two
tables. It describes the number of occurrences of one entity relative to another in a
relationship. Cardinality plays a crucial role in determining how tables are related and
helps in maintaining the integrity of the data.

Types of Cardinalities in RDBMS

There are mainly four types of cardinalities that define how tables relate to each other:

1. One-to-One (1:1) Cardinality

In a one-to-one relationship, each record in the first table corresponds to exactly
one record in the second table and vice versa. This type of relationship is rare and
is typically used for splitting data into two tables for organizational purposes or to
handle sensitive information separately.

Example:
Consider two tables, Employees and EmployeeDetails. Each employee in the
Employees table has exactly one entry in the EmployeeDetails table. Here, the
cardinality is one-to-one.

Employees:
| emp_id | emp_name |
|--------|----------|
| 1 | John |
| 2 | Alice |

EmployeeDetails:
| emp_id | emp_address |
|--------|---------------|
| 1 | 123 Street |
| 2 | 456 Avenue |

2. One-to-Many (1:M) Cardinality

A one-to-many relationship occurs when a record in one table is related to
multiple records in another table. This is the most common type of relationship in
databases.
 Example:
In a relationship between Departments and Employees, one department can have
many employees, but each employee can belong to only one department.

Departments:
| dept_id | dept_name |
|---------|-----------|
| 1 | HR |
| 2 | IT |

Employees:
| emp_id | emp_name | dept_id |
|--------|----------|---------|
| 1 | John | 1 |
| 2 | Alice | 2 |
| 3 | Bob | 1 |

3. Many-to-One (M:1) Cardinality

A many-to-one relationship is simply the reverse of a one-to-many relationship.
In this case, many records in one table can be related to a single record in another
table.

Example:
If many employees work in one department, then the relationship from
Employees to Departments is many-to-one.

4. Many-to-Many (M:M) Cardinality

In a many-to-many relationship, multiple records in one table can relate to
multiple records in another table. This type of relationship is usually modeled
using a junction table to manage the connections between the two tables.

Example:
Consider a relationship between Students and Courses. A student can enroll in
many courses, and each course can have many students.

Students:
| student_id | student_name |
|------------|--------------|
| 1 | John |
| 2 | Alice |

Courses:
| course_id | course_name |
|-----------|-------------|
| 101 | Math |
| 102 | Science |

StudentCourses (Junction Table):

| student_id | course_id |
|------------|-----------|
| 1 | 101 |
 | 1 | 102 |
| 2 | 102 |

In conclusion, cardinality defines how tables are interrelated in a database. Understanding

and using the correct cardinality helps structure the database efficiently, ensuring proper
data integrity and reducing redundancy.

Q2. What do you mean by Entity Integrity Constraint and Referential Integrity Constraint?
Explain by giving suitable example.

In a Relational Database Management System (RDBMS), integrity constraints are rules

that ensure the accuracy, consistency, and reliability of data. Two important types of
integrity constraints are Entity Integrity Constraint and Referential Integrity
Constraint. These constraints help maintain the validity of the data by ensuring certain
conditions are met when inserting, updating, or deleting records.

Entity Integrity Constraint

The Entity Integrity Constraint ensures that each record in a table is unique and
identifiable. It primarily applies to the primary key of a table. A primary key is a column
(or a combination of columns) whose value uniquely identifies each row in the table. The
constraint ensures that:

1. The primary key field cannot have NULL values.

2. Each value in the primary key must be unique.

The reason behind this constraint is that the primary key is used to uniquely identify a
record, and without a unique or non-null value, the integrity of the database would be
compromised.

Example:
Consider a Students table where each student has a unique student_id as the primary
key.

Students:
| student_id | student_name |
|------------|--------------|
| 1 | John |
| 2 | Alice |
| 3 | Bob |

Here, student_id is the primary key. The Entity Integrity Constraint ensures that:

• No two students can have the same student_id.

• The student_id cannot be NULL for any student.
If you try to insert a record with a NULL student_id or a duplicate value, it would
violate the entity integrity constraint.

Referential Integrity Constraint

The Referential Integrity Constraint ensures that relationships between tables remain
consistent. This constraint applies to foreign keys—a field (or combination of fields) in
one table that refers to the primary key in another table. It ensures that a foreign key
value must either be NULL or must match an existing primary key value in the
referenced table.

The core idea behind this constraint is that a foreign key must point to a valid record in
the referenced table, preventing orphaned or invalid records.

Example:
Consider a Departments table and an Employees table. Each employee belongs to a
department, and the Employees table has a dept_id foreign key that references the
dept_id in the Departments table.

Departments:
| dept_id | dept_name |
|---------|-----------|
| 1 | HR |
| 2 | IT |

Employees:
| emp_id | emp_name | dept_id |
|--------|----------|---------|
| 1 | John | 1 |
| 2 | Alice | 2 |
| 3 | Bob | 3 | (Invalid)

In this example, the Employees table has a foreign key dept_id that refers to the
Departments table. According to the Referential Integrity Constraint, if an employee
has a dept_id of 3, but there is no department with dept_id = 3 in the Departments
table, it would violate the referential integrity. The foreign key value must correspond to
a valid dept_id in the Departments table.

Additionally, referential integrity also specifies actions like CASCADE (where related
records are automatically updated or deleted) or SET NULL (where foreign key values
are set to NULL if the referenced record is deleted).

Conclusion

• Entity Integrity Constraint ensures that each row in a table is uniquely identifiable
by a non-null, unique primary key.
• Referential Integrity Constraint ensures that foreign keys in a table always refer
to valid records in another table, maintaining consistency in relationships.
Both constraints are essential for ensuring the integrity, accuracy, and consistency of data
within a relational database.

Q3. Explain the important properties of transactions that a DBMS must ensure to maintain
data in the face of concurrent access and system failures.

In a Database Management System (DBMS), a transaction is a sequence of one or more

operations (like insert, update, or delete) that are executed as a single unit. For a DBMS
to maintain the consistency and reliability of data in the face of concurrent access
(where multiple users are accessing the database simultaneously) and system failures
(like power loss or crashes), it must ensure certain properties of transactions. These
properties are collectively known as the ACID properties, which stand for Atomicity,
Consistency, Isolation, and Durability.

1. Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit. This means
that either all operations within the transaction are successfully completed, or none of
them are. If a transaction encounters an issue (e.g., a system crash), it is completely rolled
back, and the database remains in its previous state.

Example:
Consider a bank transfer transaction. If a transaction involves transferring money from
Account A to Account B, the transfer must either fully complete (i.e., the money is
deducted from Account A and added to Account B) or it must not occur at all (if any
part fails, such as during money deduction but before addition to the second account).

2. Consistency

Consistency ensures that a transaction takes the database from one valid state to another.
The database must always meet the integrity constraints (e.g., data types, foreign key
relationships) before and after the transaction. If a transaction violates any constraints, it
is rolled back to maintain consistency.

Example:
In a banking system, a transaction that tries to withdraw more money than available in an
account should not be allowed, ensuring that the balance never goes below zero.

3. Isolation

Isolation ensures that transactions are executed independently of each other, even if they
run concurrently. The effects of one transaction should not be visible to other transactions
until it is complete. This prevents issues like dirty reads (reading uncommitted data),
non-repeatable reads (getting different results for the same query within the same
transaction), and phantom reads (getting a different number of records in successive
reads).
Example:
If two customers are trying to book the last available seat on a flight at the same time,
isolation ensures that only one customer will be able to complete the booking, and the
other will be notified that the seat is no longer available.

4. Durability

Durability guarantees that once a transaction has been committed, its changes are
permanent and will not be lost, even in the case of a system crash. The changes made by
a successful transaction are written to the database, and the system ensures that they are
preserved.

Example:
If a user updates their contact information in a database and the system crashes after the
update is committed, the new contact information will still be present in the database
after recovery.

Conclusion

These ACID properties are fundamental to ensuring that a DBMS can handle
concurrent access (multiple transactions happening simultaneously) and system failures
without compromising data integrity. By ensuring atomicity, consistency, isolation, and
durability, a DBMS ensures that transactions are processed reliably, even under
challenging conditions.

Set-II

Q4. Discuss different Operations in Relational Algebra? Explain each operation by giving
suitable example.

Relational Algebra is a formal query language used to perform operations on relational

databases. It is the foundation of SQL and is used to retrieve and manipulate data from
relational tables. The operations in relational algebra are defined on relations (tables) and
provide a way to combine or modify these relations to extract useful data.

Here are the basic operations in relational algebra:

1. Select (σ)

The Select operation (denoted as σ) is used to filter rows from a relation that satisfy a
certain condition. It extracts a subset of the rows based on a given predicate.

Syntax:
σ(condition)(Relation)
Example:
Consider a table Employees:

Employees:
| emp_id | emp_name | salary |
|--------|----------|--------|
| 1 | John | 5000 |
| 2 | Alice | 6000 |
| 3 | Bob | 4500 |

To find all employees with a salary greater than 5000:

σ(salary > 5000)(Employees)

Result:

| emp_id | emp_name | salary |

|--------|----------|--------|
| 2 | Alice | 6000 |

2. Project (π)

The Project operation (denoted as π) is used to select specific columns from a relation,
essentially projecting the data into a new set with only the specified attributes.

Syntax:
π(attribute1, attribute2,...)(Relation)

Example:
To get only the emp_name and salary from the Employees table:

π(emp_name, salary)(Employees)

Result:

| emp_name | salary |
|----------|--------|
| John | 5000 |
| Alice | 6000 |
| Bob | 4500 |

3. Union (∪)

The Union operation (denoted as ∪) combines the rows of two relations, provided they
have the same number of attributes and corresponding domains. It returns a relation
containing all rows from both tables, excluding duplicates.

Syntax:
Relation1 ∪ Relation2
Example:
Consider two tables, Employees1 and Employees2:

Employees1:
| emp_id | emp_name |
|--------|----------|
| 1 | John |
| 2 | Alice |

Employees2:
| emp_id | emp_name |
|--------|----------|
| 3 | Bob |
| 4 | Eve |

The union of Employees1 and Employees2 would be:

Employees1 ∪ Employees2

Result:

| emp_id | emp_name |
|--------|----------|
| 1 | John |
| 2 | Alice |
| 3 | Bob |
| 4 | Eve |

4. Set Difference (−)

The Set Difference operation (denoted as −) returns the rows that are present in one
relation but not in the other. It’s essentially the opposite of the union.

Syntax:
Relation1 − Relation2

Example:
If we want to find employees in Employees1 who are not in Employees2:

Employees1 − Employees2

Result:

| emp_id | emp_name |
|--------|----------|
| 1 | John |
| 2 | Alice |
5. Cartesian Product (×)

The Cartesian Product operation (denoted as ×) combines each row of one relation with
every row of another relation, producing a new relation. The number of rows in the
resulting relation is the product of the number of rows in the two relations.

Syntax:
Relation1 × Relation2

Example:
Given two relations Employees and Departments:

Employees:
| emp_id | emp_name |
|--------|----------|
| 1 | John |
| 2 | Alice |

Departments:
| dept_id | dept_name |
|---------|-----------|
| 101 | HR |
| 102 | IT |

The Cartesian product of Employees and Departments would be:

Employees × Departments

Result:

| emp_id | emp_name | dept_id | dept_name |

|--------|----------|---------|-----------|
| 1 | John | 101 | HR |
| 1 | John | 102 | IT |
| 2 | Alice | 101 | HR |
| 2 | Alice | 102 | IT |

6. Rename (ρ)

The Rename operation (denoted as ρ) is used to rename a relation or its attributes. This
helps avoid ambiguity, especially when performing operations involving the same
relation multiple times.

Syntax:
ρ(new_relation_name, Relation)

Example:
To rename the Employees table to Staff:

ρ(Staff, Employees)
Result:

Staff:
| emp_id | emp_name | salary |
|--------|----------|--------|
| 1 | John | 5000 |
| 2 | Alice | 6000 |
| 3 | Bob | 4500 |

Conclusion

These operations are fundamental to relational algebra, allowing users to retrieve and
manipulate data in a structured way. By combining different operations, you can perform
complex queries and gain insights from relational databases.

Q5. What do you mean by Normalization? What are the different Normal Forms? Explain by
giving suitable example.

Normalization is a process in database design used to organize data in a way that

reduces redundancy and dependency. It involves breaking down a database into smaller,
more manageable tables and ensuring that data is stored efficiently. The goal of
normalization is to avoid issues such as update anomalies, insertion anomalies, and
deletion anomalies, which can occur when data is stored improperly.

Normalization is achieved by applying a series of normal forms. Each normal form has a
set of rules and conditions that a database must satisfy. There are several normal forms,
with the most common ones being First Normal Form (1NF), Second Normal Form
(2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF).

1. First Normal Form (1NF)

A relation (table) is in First Normal Form (1NF) if it meets the following criteria:

• All attributes (columns) contain atomic (indivisible) values, meaning there are no
repeating groups or arrays.
• Each record (row) is unique, and each column contains only one value per row.

2. Second Normal Form (2NF)

A relation is in Second Normal Form (2NF) if:

• It is already in 1NF.
• It has no partial dependency, meaning that all non-key attributes are fully
dependent on the entire primary key, not just part of it.

This applies mainly to tables with composite primary keys (a primary key made up of
more than one attribute).
3. Third Normal Form (3NF)

A relation is in Third Normal Form (3NF) if:

• It is in 2NF.
• It has no transitive dependency, meaning non-key attributes are not dependent on
other non-key attributes.

4. Boyce-Codd Normal Form (BCNF)

A relation is in Boyce-Codd Normal Form (BCNF) if:

• It is in 3NF.
• Every determinant (an attribute or set of attributes that determines another
attribute) is a candidate key.

Conclusion

Normalization helps ensure that a database is free from redundancy and update anomalies
by breaking down tables into smaller, logically structured ones. By progressively
applying normal forms (1NF, 2NF, 3NF, BCNF), we make the database more efficient,
maintainable, and consistent.

Q6. What do you mean by Fragmentation? What are the different types of Fragmentation?
Explain by giving suitable example.

Fragmentation in the context of databases refers to the process of breaking a large

database into smaller, more manageable pieces, called fragments. This is typically done
to improve performance, scalability, and manageability, especially when a database is
distributed across multiple locations or servers. The idea is that data can be stored more
efficiently, and the system can handle queries faster by retrieving only the relevant
fragments instead of scanning the entire database.

Fragmentation can be applied to both data and schema. There are different strategies for
fragmenting data, each suited to different use cases and requirements. The two primary
types of fragmentation are Horizontal Fragmentation, Vertical Fragmentation, and
Mixed Fragmentation (a combination of horizontal and vertical).

1. Horizontal Fragmentation

Horizontal fragmentation divides a relation (table) into subsets of rows based on some
condition, such as geographical location, range of values, or any other criteria. Each
fragment contains a subset of the rows but all the columns from the original table. This
type of fragmentation is often used when you want to distribute data across different
sites, for example, across different geographic locations.
2. Vertical Fragmentation

Vertical fragmentation involves dividing a table into smaller fragments by splitting

columns, rather than rows. Each fragment contains a subset of the columns but all the
rows. This is useful when certain columns are frequently accessed together or when
certain columns are rarely used, thus saving storage space and improving query
performance.

3. Mixed Fragmentation

Mixed fragmentation is a combination of both horizontal and vertical fragmentation. This

approach is useful when the database has complex usage patterns, and data can benefit
from being partitioned both by rows and columns.

Conclusion

Fragmentation is a crucial technique for optimizing large, distributed databases.

Horizontal fragmentation helps in distributing data across different sites, vertical
fragmentation reduces storage and increases access efficiency by splitting columns, and
mixed fragmentation combines both methods for more tailored optimization. By
choosing the right fragmentation strategy, a database system can significantly improve
performance, scalability, and ease of management.