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, 30 new students, 4 new courses, and at least 15 in each course in the enrollments 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. Analysis and Reporting:
    • Analyze the importance of transactions in database operations.
    • Reflect on how transactions can prevent data inconsistencies and errors.

Deliverables

  • The SQL script, named “your_last_name_assign3.sql” used for the INSERT, UPDATE, and DELETE operations AND the university.db file.
  • 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.
  • 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.
 
  •