Database Management Systems Questions For Practice

9/24/20 Answer this question below. 1-Normalization in SQL: Consider a database with two tables: Students and Courses . The current design allows for duplicate entries and may lead to data inconsistencies. Provide an SQL script to normalize the database, ensuring that it complies with at least the first three normal forms (1NF, 2NF, 3NF). You can use hypothetical column names, and make sure to explain the changes you are making at each normalization step. 2-Transactions and ACID Properties in SQL: Imagine a scenario where a banking application is handling transactions that involve transferring money between two accounts. Write an SQL transaction script that transfers a specified amount from one account to another. Ensure that the transaction adheres to the ACID properties. Explain how your script maintains Atomicity, Consistency, Isolation, and Durability. Additionally, discuss any potential issues that could arise in a multi-user environment and how the script addresses or mitigates these issues. 9/24/20 Answers : 1. Normalization in SQL: Assuming we have a denormalized structure: -- Original tables CREATE TABLE Students ( student_id INT PRIMARY KEY, student_name VARCHAR(50), course_name VARCHAR(50) ); CREATE TABLE Courses ( course_name VARCHAR(50) PRIMARY KEY, instructor_name VARCHAR(50), enrollment_date DATE ); To normalize, we can do the following: -- Normalized tables CREATE TABLE Students ( student_id INT PRIMARY KEY, student_name VARCHAR(50) ); CREATE TABLE Courses ( course_name VARCHAR(50) PRIMARY KEY, instructor_name VARCHAR(50) ); CREATE TABLE Enrollments ( enrollment_id INT PRIMARY KEY, student_id INT, course_name VARCHAR(50), enrollment_date DATE, FOREIGN KEY (student_id) REFERENCES Students(student_id), FOREIGN KEY (course_name) REFERENCES Courses(course_name)); 9/24/20 2. Transactions and ACID Properties in SQL: -- Transaction script BEGIN TRANSACTION; -- Assuming we have tables: Accounts(account_id, balance) DECLARE @from_account INT = 123; DECLARE @to_account INT = 456; DECLARE @transfer_amount DECIMAL(10, 2) = 100.00; -- Step 1: Ensure Sufficient Balance IF (SELECT balance FROM Accounts WHERE account_id = @from_account) >= @transfer_amount BEGIN -- Step 2: Deduct from Sender's Account UPDATE Accounts SET balance = balance - @transfer_amount WHERE account_id = @from_account; -- Step 3: Add to Receiver's Account UPDATE Accounts SET balance = balance + @transfer_amount WHERE account_id = @to_account; -- Transaction Successful COMMIT; END ELSE BEGIN -- Insufficient Balance, Rollback Transaction ROLLBACK; END; 9/24/20 Explanation:     Atomicity: The entire transaction is treated as a single unit, and it either succeeds entirely or fails entirely. The use of BEGIN TRANSACTION , COMMIT , and ROLLBACK ensures atomicity. Consistency: The script maintains consistency by ensuring that the transfer only occurs if there is sufficient balance in the sender's account. Isolation: The transaction is isolated from other transactions. The use of BEGIN TRANSACTION and COMMIT ensures that the changes made by the transaction are not visible to other transactions until the transaction is committed. Durability: Once the transaction is committed, the changes made to the database are permanent and will survive any subsequent failures. Please note that these are simplified examples, and real-world scenarios may involve additional considerations and error handling.