Section A — 1 Mark Questions
1. Define an atomic domain. [1 Mark]
Answer: A domain is atomic if elements of the domain are considered to be indivisible units.
2. What is the primary requirement for a table to be in the first normal form (1NF)? [1 Mark]
Answer: A relation is in 1NF if the domains of all attributes are atomic.
3. Define a functional dependency. [1 Mark]
Answer: A functional dependency is a constraint between two sets of attributes in a relation where the value of one set uniquely determines the value of another.
4. In the functional dependency X → Y, what is X called? [1 Mark]
Answer: X is called the determinant.
5. What does the process of decomposition involve? [1 Mark]
Answer: Decomposition involves breaking a relation schema into two or more smaller relation schemas.
6. What is a trivial functional dependency? [1 Mark]
Answer: A functional dependency X → Y is trivial if Y is a subset of X.
7. What is the main goal of database normalization? [1 Mark]
Answer: The main goal is to reduce data redundancy and eliminate update, insertion, and deletion anomalies.
8. What is a multivalued dependency? [1 Mark]
Answer: It is a constraint that specifies that the presence of certain rows in a table implies the presence of certain other rows.
9. If a relation is in Boyce-Codd Normal Form (BCNF), what must be true about its determinants? [1 Mark]
Answer: Every determinant must be a candidate key.
10. What is an update anomaly? [1 Mark]
Answer: An update anomaly occurs when data redundancy leads to the need to update multiple rows to reflect a single change, potentially causing inconsistency.
11. Define the term closure of an attribute set. [1 Mark]
Answer: The closure of a set of attributes is the set of all attributes functionally determined by that set.
12. What is 4NF concerned with? [1 Mark]
Answer: 4NF is concerned with eliminating anomalies arising from multivalued dependencies.
13. When is a database design considered lossless? [1 Mark]
Answer: A design is lossless if the original relation can be recovered by joining the decomposed relations.
14. State the dependency preservation property. [1 Mark]
15. What is the transitive dependency in a relation? [1 Mark]
Answer: A transitive dependency exists when an attribute A determines B, and B determines C, such that A determines C indirectly.
16. Can a relation be in 2NF but not in 3NF? [1 Mark]
Answer: Yes, if it contains transitive dependencies on the primary key.
17. What are insertion anomalies? [1 Mark]
Answer: These occur when certain data cannot be inserted into the database without the presence of other unrelated data.
18. What is the role of a candidate key? [1 Mark]
Answer: A candidate key is a minimal set of attributes that uniquely identifies a tuple in a relation.
19. Define the process of 'normalization'. [1 Mark]
Answer: It is the systematic process of organizing data in a database to reduce redundancy and improve data integrity.
20. What is a 'superkey'? [1 Mark]
Answer: A superkey is a set of one or more attributes that taken collectively allows us to identify uniquely a tuple in the relation.
Section B — 2/3 Mark Questions
1. Explain the difference between 1NF and 2NF. [2 Marks]
Answer: 1NF requires all attributes to have atomic domains. 2NF requires the relation to be in 1NF and ensures that all non-prime attributes are fully functionally dependent on the entire primary key, eliminating partial dependencies.
2. Why is decomposition necessary in database design? [2 Marks]
Answer: Decomposition is necessary to eliminate data redundancy and prevent update, insertion, and deletion anomalies. By breaking relations into smaller, logically sound pieces, we maintain data consistency.
3. Describe the concept of a lossy-join decomposition with an example. [3 Marks]
Answer: A lossy-join decomposition occurs when joining the decomposed relations results in a relation with more tuples than the original, creating 'spurious tuples'. For example, if we decompose R(A, B, C) into (A, B) and (B, C) without a proper dependency, re-joining them might link unrelated rows, losing the original meaning.
4. Explain the concept of functional dependency with a simple example. [3 Marks]
Answer: A functional dependency X → Y means for any two rows in a table, if the value of attribute X is the same, then the value of attribute Y must also be the same. Example: In an Employee table, 'Employee_ID' → 'Name', meaning one ID always determines one specific name.
5. List two primary goals of the database design process. [2 Marks]
Answer: The two primary goals are: 1. Minimizing data redundancy to save storage and prevent inconsistencies. 2. Ensuring data integrity by structuring tables to avoid anomalies.
6. Differentiate between partial dependency and transitive dependency. [3 Marks]
Answer: Partial dependency occurs when a non-prime attribute depends on only part of a composite primary key. Transitive dependency occurs when a non-prime attribute depends on another non-prime attribute, which in turn depends on the primary key.
7. Explain the concept of multivalued dependency (MVD). [2 Marks]
Answer: An MVD exists when one attribute (or set of attributes) determines a set of values in another attribute, independent of any other attributes in the relation.
8. What is meant by 'dependency preservation' in decomposition? [2 Marks]
Answer: A decomposition is dependency preserving if the union of all functional dependencies enforced on the decomposed tables is equivalent to the set of functional dependencies on the original table.
9. How do anomalies affect database performance? [3 Marks]
Answer: Anomalies lead to data inconsistency and corruption. Insertion anomalies prevent adding data due to missing keys; update anomalies require redundant changes across many rows; deletion anomalies can result in the accidental loss of unrelated information, all of which degrade data reliability.
10. Define Third Normal Form (3NF). [2 Marks]
Answer: A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on the primary key.
Section C — 5 Mark Questions
1. Explain the process of database normalization and its importance. [5 Marks]
Introduction
Normalization is a technique used in relational database design to organize tables in a way that minimizes redundancy and ensures data dependency.
Importance
- Reduces Data Redundancy: It ensures that each piece of data is stored only once, saving disk space.
- Eliminates Anomalies: Prevents insertion, update, and deletion anomalies that arise from redundant data.
- Improves Integrity: Keeps data consistent across the entire database.
- Logical Structure: Provides a systematic approach to decomposing complex tables into smaller, well-structured relations.
Conclusion
By moving through different normal forms (1NF, 2NF, 3NF, BCNF), designers create more robust and maintainable database systems.
2. Describe the difference between 2NF and 3NF with examples. [5 Marks]
2NF Explanation
A table is in 2NF if it is in 1NF and all non-prime attributes are fully functionally dependent on the entire primary key. This removes partial dependencies.
3NF Explanation
A table is in 3NF if it is in 2NF and has no transitive dependencies. That is, no non-key attribute should determine another non-key attribute.
Comparison
- 2NF focuses on removing partial dependency on the primary key.
- 3NF focuses on removing transitive dependency of non-prime attributes.
- If a table has a single-attribute primary key, it is automatically in 2NF.
Example
In a table (Student_ID, Course_ID, Course_Name), if Course_ID → Course_Name, we have a transitive dependency if Student_ID is the key. To reach 3NF, we must split this into (Student_ID, Course_ID) and (Course_ID, Course_Name).
3. Discuss the role of Functional Dependency Theory in database design. [5 Marks]
Introduction
Functional dependency (FD) theory serves as the mathematical foundation for relational database normalization and schema design.
Key Roles
- Defining Constraints: FDs formalize the relationships between data items, ensuring accuracy.
- Decomposition Guidance: They provide the criteria for splitting large, problematic tables into smaller, more efficient schemas.
- Identifying Keys: By computing attribute closures, designers can identify superkeys and candidate keys accurately.
- Normalization Standard: FD theory is used to test whether a relation satisfies the requirements of 1NF, 2NF, 3NF, and BCNF.
- Lossless Join Validation: It helps ensure that when tables are split, they can be recombined without losing original data information.
4. Explain the concept of Boyce-Codd Normal Form (BCNF). [5 Marks]
Definition
BCNF is a higher level of normalization than 3NF. A relation is in BCNF if and only if for every non-trivial functional dependency X → Y, X is a superkey.
Why BCNF is required
3NF allows dependencies where a non-prime attribute determines a part of the primary key. BCNF eliminates this possibility by strictly enforcing that every determinant must be a candidate key.
Comparison with 3NF
- Every BCNF table is 3NF, but not every 3NF table is BCNF.
- BCNF provides a stricter condition to prevent anomalies that 3NF might still permit in specific complex scenarios.
Application
BCNF is preferred in systems where data integrity and the avoidance of update anomalies are more critical than maintaining every original dependency.
5. Elaborate on the problems associated with non-normalized databases. [5 Marks]
Introduction
A non-normalized database contains redundant data and suffers from design flaws that lead to severe operational issues known as anomalies.
Main Problems
- Insertion Anomaly: Occurs when you cannot add specific data because some other required data is missing.
- Update Anomaly: If redundant data is stored in multiple rows, updating it requires changing every instance. Failure to update all leads to data inconsistency.
- Deletion Anomaly: When deleting a record results in the unintended loss of other critical information stored within the same row.
Example
If an 'Employee_Department' table stores the department location repeatedly for every employee, changing the location of a department would require updating thousands of rows, leading to high processing costs and potential errors.
Conclusion
Normalization is the standard solution to these problems, ensuring data is stored efficiently and reliably.