Skip to content

Week 8 - SQLite3 III

Objective
The aim of this assignment is to provide hands-on experience with the fundamental Data Manipulation Language (DML) commands in SQL: INSERT, UPDATE, and DELETE. Additionally, it introduces the concept of transactions, emphasizing their importance in maintaining data integrity, especially during multi-step operations.

Background
Understanding how to manipulate data within a database is a core skill for any database professional. This assignment focuses on the execution of basic DML commands and the use of transactions to ensure data integrity and consistency, especially in complex operations involving multiple steps.

Tasks
  1. Data Manipulation Practice:
    • Insert new records into each table (Students, Courses, Enrollments).
    • Update existing records, such as changing a student’s major or a course’s instructor.
    • Delete records from a table, for example, removing a student or a course.
  2. Transaction Management:
    • Perform a multi-step operation that involves several INSERT, UPDATE, or DELETE commands. For example, enrolling a student in multiple courses and then updating their major.
    • Implement this operation as a transaction, ensuring that either all steps are completed successfully or none are applied (to maintain database integrity).
  3. Error Handling and Rollback:
    • Introduce an intentional error in one of the transaction steps to trigger a rollback.
    • Demonstrate how the database state reverts to its original form before the transaction started.
  4. Analysis and Reporting:
    • Analyze the importance of transactions in database operations.
    • Reflect on how transactions can prevent data inconsistencies and errors.

Deliverables
  • A detailed report on the data manipulation tasks performed, including the purpose and outcome of each operation.
  • The SQL script used for the INSERT, UPDATE, and DELETE operations.
  • A demonstration of the transaction, including the implementation of rollback in case of an error.
  • A reflection on the learning experience, focusing on the importance of transactions in maintaining data integrity.

Evaluation Criteria
  • Accuracy and efficiency in the use of INSERT, UPDATE, and DELETE commands.
  • Correct implementation of transaction management, including successful rollback in case of errors.
  • Depth of understanding as reflected in the analysis and report, particularly regarding the role of transactions in database operations.
By the end of this assignment, you will have a practical understanding of how to manipulate data within a database using DML commands and the significance of transactions in ensuring data integrity. These skills are crucial for any database professional, particularly in scenarios involving complex data manipulation and maintenance.
 
  •