DBMS Assignment 2 PDF

MTE-2 Assignment-2 DBMS Q.1) What is Normalization? Explain in detail. Ans: Normalization in DBMS Introduction Normalization is a process in Database Management Systems (DBMS) that organizes data to reduce redundancy (duplicate da...

MTE-2 Assignment-2 DBMS Q.1) What is Normalization? Explain in detail. Ans: Normalization in DBMS Introduction Normalization is a process in Database Management Systems (DBMS) that organizes data to reduce redundancy (duplicate data) and dependency by dividing a large table into smaller ones. The goal is to ensure that the database is efficient, easy to maintain, and free from anomalies (problems) when inserting, updating, or deleting data. Why is Normalization Important? 1. Reduces Data Redundancy: It eliminates duplicate data, ensuring that each piece of information is stored only once. 2. Avoids Update, Insert, and Delete Anomalies: Without normalization, modifying data can lead to inconsistent results or errors. 3. Ensures Data Integrity: The process maintains the accuracy and consistency of data throughout the database. Stages of Normalization (Normal Forms) Normalization is done in stages called Normal Forms (NFs). There are several normal forms, each with stricter rules. 1. First Normal Form (1NF): A table is in 1NF if:  It has a primary key (a unique identifier for each record).  Every column contains atomic values, meaning each column should store only one value (no lists or sets in a single column). 2. Second Normal Form (2NF): A table is in 2NF if:  It is already in 1NF.  All non-key columns (other columns) are fully dependent on the primary key. 3. Third Normal Form (3NF): A table is in 3NF if:  It is already in 2NF.  There are no transitive dependencies. This means non-key columns should not depend on other non-key columns. Example Unnormalized Table: COURSE_MANAGEMENT StudentID StudentName CourseID CourseName InstructorID InstructorName Department Credits Database Computer S1 Harsh C101 I1 Suraj Sir 3 Systems Science Web Computer S1 Harsh C102 I2 Prashant sir 4 Development Science Database Computer S2 Aryan C101 I1 Suraj Sir 3 Systems Science Data Computer S3 Harshal C103 I3 Amit sir 4 Structures Science After conversion: Table 1 (Student-Course Table): StudentID StudentName CourseID S1 Harsh C101 S1 Harsh C102 S2 Aryan C101 S3 Harshal C103 Table 2 (Course-Details Table): CourseID CourseName InstructorID Credits C101 Database Systems I1 3 C102 Web Development I2 4 C103 Data Structures I3 4 Table 3 (Instructor-Details Table): InstructorID InstructorName Department I1 Suraj Sir Computer Science I2 Prashant Sir Computer Science I3 Amit Sir Computer Science Q.2) Explain Decomposition with the help of example. Ans: Decomposition in DBMS Introduction Decomposition in DBMS refers to breaking down a larger table into smaller, related tables to improve the design and eliminate issues like data redundancy and anomalies (insert, update, and delete anomalies). It is a critical part of normalization, where we split tables while ensuring that the original information can be reconstructed using the smaller tables. The goal of decomposition is to ensure that the database maintains data integrity and avoids redundancy while still being able to reconstruct the original data when needed. Why Decomposition is Necessary  Avoid Redundancy: Storing the same data multiple times increases storage space and may cause inconsistency in the data.  Eliminate Anomalies: Without decomposition, we can face issues such as: o Update Anomaly: Changing data in one place but not in another. o Insert Anomaly: Difficulty in inserting new data without other dependent data. o Delete Anomaly: Deleting data might cause the loss of other valuable information. Example of Decomposition Let’s use the following example: StudentID StudentName CourseID CourseName InstructorID InstructorName Department S1 Harsh C101 Database Systems I1 Suraj Sir Computer Science S1 Harsh C102 Web Development I2 Prashant Sir Computer Science S2 Aryan C101 Database Systems I1 Suraj Sir Computer Science S3 Harshal C103 Data Structures I3 Amit Sir Computer Science In this table, data is repeated, like the instructor name and department, causing redundancy. If we need to change the instructor’s name, we have to update it in multiple rows, which could lead to inconsistency (update anomaly). Decomposition Process Step 1: Identify Repeated or Dependent Data We notice that InstructorName and Department are repeated for every course, even though they only depend on InstructorID and CourseID. This suggests that we can decompose the table to reduce redundancy. Step 2: Decompose into Smaller Tables We can break this table into two or more smaller tables that preserve the information but reduce redundancy. 1. Student-Course Table: This table stores which student takes which course. StudentID StudentName CourseID S1 Harsh C101 S1 Harsh C102 S2 Aryan C101 S3 Harshal C103  2. Course-Details Table: This table contains the details of each course, including the instructor. CourseID CourseName InstructorID C101 Database Systems I1 C102 Web Development I2 C103 Data Structures I3 3. Instructor-Details Table: This table stores the details of each instructor. InstructorID InstructorName Department I1 Suraj Sir Computer Science I2 Prashant Sir Computer Science I3 Amit Sir Computer Science Q.3) List and explain different types of anomalies. Ans: Types of Anomalies in DBMS In database systems, anomalies are issues that arise when we store redundant or improperly structured data in a table. These problems often occur when a table is not normalized. There are three main types of anomalies: Update Anomaly, Insert Anomaly, and Delete Anomaly. These anomalies can cause inconsistencies and errors when manipulating the data. 1. Update Anomaly Definition: An Update Anomaly occurs when multiple records in a table need to be updated to reflect a single change, and failing to update all occurrences leads to inconsistent data. This happens when the same piece of information is stored redundantly in multiple rows of a table. 2. Insert Anomaly Definition: An Insert Anomaly occurs when certain data cannot be inserted into a database without the presence of some other related data. This happens because a table may require the presence of information that is not yet available or not related to the new data being inserted. 3. Delete Anomaly Definition: A Delete Anomaly occurs when the deletion of certain data unintentionally removes other important data. This typically happens when a single table is responsible for storing different types of information that should be stored separately. Q.4) What is functional dependency? Explain its different types in detail Ans: Functional Dependency in DBMS Definition: A Functional Dependency (FD) in a database refers to the relationship between two sets of attributes within a relation (table). It indicates that the value of one attribute (or a set of attributes) uniquely determines the value of another attribute (or set of attributes). In other words, if we know the value of one attribute, we can uniquely determine the value of another attribute. Notation: If attribute X determines attribute Y, we represent it as: X→Y Here, X is called the determinant, and Y is the dependent attribute. This means that for every value of X, there is a unique value of Y. 1. Trivial Functional Dependency Definition: A functional dependency is said to be trivial if the dependent attribute is a subset of the determinant. In simple terms, the dependency is trivial if the right-hand side (dependent) attribute is already contained in the left-hand side (determinant). Notation:  X → Y is trivial if Y is a subset of X. Example:  StudentID, StudentName → StudentID: This is a trivial dependency because StudentID is part of the determinant (left-hand side). 2. Non-Trivial Functional Dependency Definition: A non-trivial functional dependency occurs when the dependent attribute is not a subset of the determinant. This type of dependency provides meaningful information about the relationship between attributes. Notation:  X → Y is non-trivial if Y is not a subset of X. Example:  StudentID → StudentName: Knowing the StudentID uniquely determines the StudentName, and StudentName is not a subset of StudentID. 3. Partial Functional Dependency Definition: A partial functional dependency occurs when a non-key attribute is functionally dependent on part of a composite primary key (i.e., a primary key made up of more than one attribute), but not on the whole composite key. Notation:  If A and B together form a composite key, and X → Y, where Y is functionally dependent on A alone (and not the entire composite key A, B), this is a partial dependency. Example: StudentID CourseID StudentName S1 C101 Harsh S1 C102 Harsh S2 C101 Aryan Here, we have a partial dependency:  StudentID → StudentName: Knowing just the StudentID tells us the StudentName, even though (StudentID, CourseID) is the composite primary key. 4. Full Functional Dependency Definition: A full functional dependency occurs when an attribute is functionally dependent on the entire composite key, and not just a part of it. This means that the non-key attribute depends on both parts of the composite primary key. Notation:  X → Y is a full dependency if removing any part of X means Y is no longer functionally dependent on it. 5. Transitive Functional Dependency Definition: A transitive functional dependency occurs when a non-key attribute depends on another non- key attribute, which in turn depends on the primary key. This creates an indirect relationship between the primary key and the non-key attribute through another non-key attribute. Notation:  If X → Y and Y → Z, then X → Z is a transitive dependency. Example:  CourseID → InstructorID (direct dependency)  InstructorID → InstructorName (direct dependency) Q.7) Explain different types of Transaction properties. Ans: Transaction Properties in DBMS In a database management system (DBMS), a transaction is a sequence of operations performed as a single logical unit of work. Transactions are crucial in ensuring data integrity, especially in systems where multiple transactions are processed concurrently. To ensure that transactions are executed reliably, they must adhere to the ACID properties:  Atomicity  Consistency  Isolation  Durability 1. Atomicity Definition: Atomicity ensures that a transaction is treated as a single, indivisible unit of work. It guarantees that either all operations of the transaction are executed successfully, or none of them are applied to the database. Key Points:  If any operation within the transaction fails, the entire transaction is aborted, and the database state is rolled back to what it was before the transaction started.  This prevents partial updates, ensuring that the database remains consistent. Example: Consider a banking system where a transaction involves transferring $100 from Account A to Account B. This transaction involves two operations: 1. Deducting $100 from Account A. 2. Adding $100 to Account B. If the deduction from Account A is successful but adding to Account B fails (e.g., due to a system crash), atomicity ensures that the deduction is rolled back, and neither account is updated. 2. Consistency Definition: Consistency ensures that a transaction takes the database from one valid state to another valid state, maintaining the integrity constraints defined on the database. Key Points:  Before and after a transaction, the database must remain in a consistent state.  All rules, such as primary keys, foreign keys, and other integrity constraints, must be satisfied. Example: In a university database, a transaction may involve enrolling a student in a course. If a course has a maximum limit of students, the consistency property ensures that enrolling a new student does not exceed this limit. If the limit is reached, the transaction will not be allowed to proceed, thus keeping the database in a consistent state. 3. Isolation Definition: Isolation ensures that transactions are executed independently of one another. The operations of one transaction should not affect the operations of another transaction. This property is critical in concurrent transaction processing. Key Points:  Even if multiple transactions are occurring at the same time, the system must ensure that they do not interfere with each other.  Isolation is often managed through locking mechanisms, ensuring that a transaction holds exclusive access to the data it is modifying until it is complete. Example: If Transaction 1 is transferring money from Account A to Account B while Transaction 2 is trying to check the balance of Account A, isolation ensures that Transaction 2 does not see an intermediate state of Account A’s balance. It will either see the balance before Transaction 1 begins or the balance after it completes. 4. Durability Definition: Durability guarantees that once a transaction has been committed, its effects are permanently recorded in the database, even in the event of a system failure (e.g., power loss, crashes). Key Points:  After a transaction is committed, the changes made by the transaction will survive any subsequent failures.  The DBMS uses techniques like transaction logs to ensure that committed transactions can be recovered. Example: After a successful transaction that updates a customer’s address in the database, if the system crashes immediately afterward, the updated address must still be present when the system restarts. Durability ensures that the change is preserved despite the failure. Q.8) What you do mean by Stored Procedures. Ans: Stored Procedures in DBMS Definition: A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit in a relational database management system (RDBMS). They are used to perform a specific task, encapsulating logic and operations that can be reused multiple times. Key Features of Stored Procedures 1. Precompiled: Stored procedures are compiled once and stored in the database. When they are executed, the database does not need to recompile the SQL statements, which can lead to performance improvements. 2. Modular: They allow for modular programming, where complex tasks can be broken down into smaller, manageable procedures. This makes it easier to maintain and update code.  3. Reusable: Stored procedures can be reused across different applications and users, promoting code reuse and reducing redundancy. 4. Parameter Support: Stored procedures can accept input parameters and return output parameters, making them flexible and adaptable to different contexts. 5. Security: Stored procedures can enhance security by restricting direct access to the underlying tables. Users can be granted permission to execute the stored procedure without being given direct access to the underlying data. Q.9) Explain following Transaction control commands: (i) Commit (ii) Rollback (iii) Save point Ans: (i) COMMIT Definition: The COMMIT command is used to save all the changes made during the current transaction to the database permanently. Once a transaction is committed, the changes are made permanent, and the database is updated accordingly. (ii) ROLLBACK Definition: The ROLLBACK command is used to undo all the changes made during the current transaction. It restores the database to the last committed state, effectively canceling any operations that have been performed in the transaction. (iii) SAVEPOINT Definition: The SAVEPOINT command is used to create a point within a transaction to which you can later roll back without affecting the entire transaction. It allows for finer control over transactions by enabling partial rollbacks. Q.10) Explain serializability of transactions. Ans: Serializability of Transactions Definition: Serializability is a key concept in database management systems (DBMS) that ensures the correctness of transactions. It refers to the property that ensures that the outcome of executing concurrent transactions is equivalent to some serial execution of those transactions. In simpler terms, even when transactions are executed concurrently, the final result should be the same as if they were executed one after the other, in some sequential order. Importance of Serializability 1. Data Integrity: Serializability helps maintain data integrity in the database by ensuring that the final state of the database remains consistent, regardless of how transactions are executed. 2. Consistency: It ensures that the database remains in a consistent state before and after the execution of transactions. 3. Isolation: Serializability is closely related to the isolation property of transactions in the ACID principles, which states that transactions should operate independently without interference.