CS312 Lecture-wise Questions and MSQs by Maha

Module 12: Anomalies

1. What are anomalies in database design? Provide examples.

Anomalies in database design refer to issues that arise when data is not properly structured or normalized. These problems can lead to inconsistencies, redundancy, and errors in the database. The most common types of anomalies include insert, delete, and update anomalies.

Examples:

•  Redundancy: Storing the same data multiple times, e.g., storing an employee’s address in multiple tables, leading to data inconsistency.
•  Inconsistency: When data across different parts of the database contradicts, e.g., a different record for the same employee showing different addresses.

Q2. explains the Insert, Delete, and Update anomalies with scenarios?

Insert Anomaly: This occurs when data cannot be inserted into a database without having unnecessary or incomplete information.

Scenario: In a database that stores employee and department details, if an employee hasn’t been assigned to a department yet, it might be impossible to insert a new employee record because the department field is mandatory.

Delete Anomaly: This happens when deleting data leads to the unintended loss of other related data.

Scenario: In the same employee-department database, if an employee is deleted from a department, but that department has no other employees, the deletion could also result in losing the entire department record.

Update Anomaly: This occurs when a single change in data requires multiple updates, which might not happen consistently, leading to data inconsistency.

Scenario: If an employee’s salary is updated but not all records reflecting that employee’s salary are updated in different tables, some records may show outdated data.

Q3. Why is it important to eliminate anomalies in a large database?

Eliminating anomalies is crucial for maintaining data integrity, reducing redundancy, and ensuring that the database is scalable and easy to maintain. Without addressing anomalies:

•  Data Integrity: Inconsistent data could lead to incorrect business decisions.
•  Efficiency: Anomalies increase the likelihood of errors and complexity in data management tasks like backups, updates, and querying.
•  Scalability: A database with anomalies is harder to expand or modify as it grows.

In large databases, addressing these issues early through techniques like normalization helps avoid future complications and ensures smoother operations.

MSQs:

Q1. Which anomaly occurs when certain data cannot be entered without entering unwanted data?

1. Insert
2. Update
3. Delete
4. None of these

Answer: a) Insert

Q2. What is the primary cause of anomalies in database design?

1. Redundancy
2. Normalization
3. Lack of primary keys
4. Indexing

Answer: a) Redundancy

Module 13: Normalization

Q1. Define normalization and its objectives?

Normalization is a database design technique used to reduce redundancy and dependency by organizing data into multiple related tables. It aims to improve data integrity and eliminate anomalies.

Objectives:

Reduce Redundancy: Avoid duplicate data storage.

Eliminate Anomalies: Prevent insert, delete, and update anomalies.

Improve Data Integrity: Ensure consistency and accuracy of data.

Enhance Scalability: Simplify database maintenance as the system grows.

Q2. What are the rules for First Normal Form (1NF)?

Rules for First Normal Form (1NF):

A table is in 1NF if:

Atomic Values: All columns contain atomic (indivisible) values. No repeating groups or arrays.

Uniqueness: Each row in the table must be unique and identified by a primary key.

Consistent Domain: Values in a column must be of the same type (e.g., no mixing integers and strings).

Example:

StudentID Subjects

Math, Science

This is not in 1NF because " Math, Science" is not atomic.