Relational Database Design: Concepts, Functional Dependencies, Concurrency Control, and NoSQL

Posted on Aug 18, 2024 in Computers

Relational Database Design: Concepts and Best Practices

1. Informal Design Guidelines for Relational Schema Design

Informal design guidelines for relational schema design help ensure that the database is efficient, flexible, and easy to manage. Here are some key guidelines:

  • Simplicity: Aim for a schema that is easy to understand and maintain. Avoid excessive complexity in the design, as it can lead to difficulties in query formulation and data management.
  • Naming Conventions: Use meaningful and consistent names for tables and columns. This enhances readability and makes it easier to write and understand SQL queries. For example, use Employee instead of Emp for a table name.
  • Eliminate Redundancy: Apply normalization techniques (up to the third normal form) to minimize data redundancy. Redundant data increases the risk of anomalies and inconsistencies. For example, avoid storing the same address information in multiple places.
  • Design for Performance: Consider indexing frequently queried columns to speed up retrieval operations. Analyze query patterns and design the schema to optimize join operations and reduce query execution time.
  • Referential Integrity: Use foreign key constraints to enforce referential integrity between tables. This ensures that relationships between records in different tables remain consistent and prevents orphaned records.
  • Normalization: Normalize the schema to eliminate redundant data and ensure data dependencies are logical. For instance, in the third normal form (3NF), every non-key attribute must be fully functionally dependent on the primary key.
  • Scalability: Design the schema to handle future growth in data volume. Consider partitioning large tables and using appropriate data types and sizes to accommodate expanding datasets.
  • Data Integrity Constraints: Define constraints such as PRIMARY KEY, UNIQUE, NOT NULL, and CHECK to enforce data integrity rules. For example, ensure that employee IDs are unique and not null.
  • Documentation: Document the schema design, including the purpose of each table, the meaning of each column, and the relationships between tables. This aids in maintenance and facilitates easier onboarding for new developers.
  • Iteration and Feedback: Continuously review and refine the schema based on feedback and performance analysis. Schema design should be an iterative process, with periodic revisions to adapt to changing requirements or optimizations.

2. Functional Dependencies

Functional Dependency (FD): In a relational database, a functional dependency (FD) is a constraint that describes the relationship between attributes in a relation. Specifically, a functional dependency X→Y means that if two tuples (rows) of a relation have the same values for attributes in X, then they must also have the same values for attributes in Y. Here, X and Y are sets of attributes, and X is called the determinant.

Inference Rules for Functional Dependencies

There are 6 inference rules for functional dependencies, which are defined below:

  • Reflexive Rule: According to this rule, if B is a subset of A then A logically determines B. Formally, B ⊆ A then A → B.
    Example: Let us take an example of the Address (A) of a house, which contains so many parameters like House no, Street no, City etc. These all are the subsets of A. Thus, address (A) → House no. (B).
  • Augmentation Rule: It is also known as Partial dependency. According to this rule, If A logically determines B, then adding any extra attribute doesn’t change the basic functional dependency.
    Example: A → B, then adding any extra attribute let say C will give AC → BC and doesn’t make any change.
  • Transitive rule: Transitive rule states that if A determines B and B determines C, then it can be said that A indirectly determines B.
    Example: If A → B and B → C then A → C.
  • Union Rule: Union rule states that If A determines B and C, then A determines BC.
    Example: If A → B and A → C then A → BC.
  • Decomposition Rule: It is perfectly reverse of the above Union rule. According to this rule, If A determined BC then it can be decomposed as A → B and A → C.
    Example: If A → BC then A → B and A → C.
  • Pseudo Transitive Rule: According to this rule, If A determined B and BC determines D then BC determines D.
    Example: If A → B and BC → D then AC → D.

3. Equivalence of Functional Dependency Sets

To determine if two sets of functional dependencies F and E are equivalent, we need to check if they imply each other. In other words, F is equivalent to E if:

  1. Every functional dependency in F can be derived from E.
  2. Every functional dependency in E can be derived from F.

Let’s analyze the given sets:

Sets of Functional Dependencies:

  • Set F:
    1. A → C
    2. AC → D
    3. E → AD
    4. E → H
  • Set E:
    1. A → CD
    2. E → AH

Checking if F and E are equivalent:

  1. Does F imply E?
    • From A → C and AC → D, you can derive A → CD because knowing A determines C and AC determines D, so A determines both C and D.
    • From E → AD and E → H, you can derive E → AH because E determines AD (which includes A) and E also determines H.
  2. Does E imply F?
    • From A → CD, you can derive A → C directly, and AC → D because A → CD means knowing A determines both C and D.
    • From E → AH, you can derive E → AD (since E determines A and also D) and E → H, which is already given in E.

Conclusion:

Since each set of functional dependencies F implies E and vice versa, the two sets F and E are equivalent.

Concurrency Control in Databases

4. Assertions and Triggers

Assertions and triggers are crucial tools in relational databases for enforcing rules and automating actions.

Assertions

Assertions are conditions applied to ensure data integrity across the database. An assertion is a global constraint that must always be true. For example, if we want to ensure that no employee earns less than $30,000, we can use an assertion to enforce this rule.

CREATE ASSERTION CheckMinSalary CHECK (NOT EXISTS ( SELECT * FROM Employees WHERE Salary

In this example, CheckMinSalary is an assertion ensuring no employee’s salary falls below $30,000. If an insertion or update violates this condition, the database will reject the operation, maintaining data integrity.

Triggers

Triggers are automated actions executed in response to specific events, such as insertions, updates, or deletions. They help maintain consistency and automate repetitive tasks. For instance, if we have an Orders table and want to automatically update a LastUpdated timestamp whenever an order is modified, we can create a trigger:

1. Table Definition:

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE, LastUpdated TIMESTAMP );

2. Trigger Definition

CREATE TRIGGER UpdateLastUpdated BEFORE UPDATE ON Orders FOR EACH ROW BEGIN SET NEW.LastUpdated = CURRENT_TIMESTAMP; END;

Here, UpdateLastUpdated is a trigger that sets the LastUpdated column to the current timestamp whenever an order is updated. This ensures that the timestamp reflects the last modification time without manual updates.

In summary, assertions ensure data adheres to specified rules, while triggers automate actions based on database changes. Both are vital for maintaining data integrity and efficiency.

5. System Log in Database Transactions

A transaction log in a database is a vital record of all actions executed by a database management system (DBMS). It ensures the ACID properties (Atomicity, Consistency, Isolation, and Durability) are maintained, even during crashes or hardware failures.

Key Functions of the Transaction Log:

  • Recording Transactions:
    • Logs all inserts, updates, and deletions, capturing before and after states of data modifications.
  • Individual Transaction Recovery:
    • Rolls back incomplete transactions to ensure data consistency.
  • System Startup Recovery:
    • During startup, rolls forward modifications not yet written to data files and rolls back incomplete transactions.
  • Point-in-Time Recovery:
    • Allows restoration of a database to a specific point in time using log backups to roll forward completed transactions and roll back incomplete ones.
  • Supporting Replication and High Availability:
    • Essential for features like Always On availability groups, database mirroring, and log shipping, ensuring data consistency across replicas and disaster recovery scenarios.

6. Two-Phase Locking Protocol

The Two-Phase Locking (2PL) Protocol is essential for ensuring serializability and concurrency control in database management systems. It consists of two phases: the growing phase and the shrinking phase.

Growing Phase:

  • Lock Acquisition: Transactions can acquire but not release locks, expanding control over needed data items.
  • Types of Locks: Shared locks (for read operations) and exclusive locks (for write operations). Shared locks allow multiple transactions to read the same data item, while exclusive locks ensure only one transaction can write to a data item.

Shrinking Phase:

  • Lock Release: Transactions can release but not acquire new locks, completing operations and relinquishing control over accessed data items.
  • No New Locks: Prevents transactions from interfering with others needing access to the same data items.

Ensuring Serializability:

  • Conflict Resolution: 2PL ensures transactions execute in a serializable manner, producing the same outcome as sequential execution by preventing conflicts through locks.
  • Deadlock Prevention: While ensuring serializability, 2PL can lead to deadlocks. Additional mechanisms like deadlock detection and prevention protocols are used to handle them.

Variations of 2PL:

  • Strict 2PL: Transactions hold all exclusive locks until they commit or abort, ensuring strict schedules for recoverability.
  • Rigorous 2PL: Transactions hold all locks (shared and exclusive) until they commit or abort, making implementation easier than strict 2PL.

The Two-Phase Locking Protocol is crucial for maintaining data integrity and consistency in multi-user database environments.

7. Why Concurrency Control is Needed?

Concurrency control is essential in database management systems (DBMS) to ensure the consistency and integrity of data when multiple transactions are executed simultaneously. Without proper concurrency control, several issues can arise, leading to incorrect data and system anomalies.

Why Concurrency Control is Needed:

  1. Preventing Data Inconsistency: When multiple transactions access and modify the same data concurrently, it can lead to data inconsistency. Concurrency control mechanisms ensure that transactions are executed in a way that maintains data consistency.
  2. Ensuring Isolation: Concurrency control enforces isolation among transactions, meaning that the operations of one transaction are not visible to other transactions until the transaction is committed. This prevents transactions from interfering with each other.
  3. Maintaining Database Integrity: By controlling the concurrent execution of transactions, concurrency control helps maintain the integrity of the database, ensuring that all transactions are executed in a manner that preserves the correctness of the data.

Example:

Consider an airline reservation database where two transactions, T1 and T2, are executed concurrently:

  • Transaction T1: Transfers 5 seat reservations from Flight X to Flight Y.
  • Transaction T2: Reserves 4 seats on Flight X.

Initially, Flight X has 80 reserved seats. The operations of T1 and T2 are interleaved as follows:

  1. T1 reads the number of reserved seats on Flight X (80).
  2. T2 reads the number of reserved seats on Flight X (80).
  3. T1 subtracts 5 seats from Flight X (80 – 5 = 75).
  4. T2 adds 4 seats to Flight X (80 + 4 = 84).
  5. T1 writes the updated number of reserved seats for Flight X (75).
  6. T2 writes the updated number of reserved seats for Flight X (84).

In this scenario, the final value of reserved seats on Flight X is incorrect (84 instead of 79). This issue, known as the Lost Update Problem, occurs because T2 reads the value of X before T1 updates it, causing the update from T1 to be lost. Concurrency control mechanisms, such as locking and timestamp ordering, prevent such problems by ensuring that transactions are executed in a controlled manner, preserving the consistency and integrity of the database.

NoSQL Databases

8. NoSQL and the CAP Theorem

NoSQL stands for “Not Only SQL” and refers to a variety of database technologies designed to handle large volumes of data, diverse data types, and distributed data architectures. Unlike traditional relational databases (SQL), NoSQL databases are built to scale horizontally and can manage unstructured data, making them ideal for big data and real-time web applications. There are several types of NoSQL databases, including document databases, key-value stores, column-family stores, and graph databases.

CAP Theorem (Consistency, Availability, Partition Tolerance), also known as Brewer’s Theorem, is a fundamental principle in distributed database systems. It states that it is impossible for a distributed data store to simultaneously provide all three of the following guarantees:

  1. Consistency: Every read receives the most recent write or an error. This means that all nodes in the distributed system see the same data at the same time. For example, if a user updates their profile information, all subsequent reads should reflect this update immediately.
  2. Availability: Every request (read or write) receives a response, without guarantee that it contains the most recent write. This ensures that the system remains operational and responsive, even if some nodes are down. For instance, a web application should still be able to serve user requests even if some of its servers are offline.
  3. Partition Tolerance: The system continues to operate despite an arbitrary number of messages being dropped or delayed by the network between nodes. This means that the system can handle network failures that split the network into disjoint segments. For example, if a network partition occurs, the system should still function correctly in the remaining segments.

According to the CAP theorem, a distributed system can only achieve two out of these three guarantees at any given time. Therefore, database designers must make trade-offs based on the specific requirements of their applications.

9. NoSQL Graph Databases: Neo4j

A NoSQL graph database is a type of database designed to handle and store data in a graph format, which is particularly useful for representing complex relationships and interconnected data. Unlike traditional relational databases that use tables, graph databases use nodes, edges, and properties to represent and store data. This structure allows for more efficient querying and management of highly connected data.

Key Features of NoSQL Graph Databases:

  • Nodes and Edges: Nodes represent entities (e.g., people, products), and edges represent relationships between these entities (e.g., friendships, purchases).
  • Properties: Both nodes and edges can have properties (key-value pairs) that store relevant information.
  • Efficient Traversal: Graph databases are optimized for traversing relationships, making them ideal for applications like social networks, recommendation systems, and fraud detection.

Neo4j

Neo4j is a leading open-source graph database that implements a true graph model down to the storage level. It is designed to handle highly connected data and complex queries efficiently.

Key Features of Neo4j:

  • Native Graph Storage: Neo4j stores data as a graph, not as a graph abstraction on top of another technology. This ensures optimal performance and scalability.
  • ACID Compliance: Neo4j supports ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring reliable and consistent data operations.
  • Cypher Query Language: Neo4j uses Cypher, a powerful and expressive query language specifically designed for working with graph data. Cypher allows for easy and intuitive querying of the graph.
  • High Performance: Neo4j is optimized for fast read and write operations, making it suitable for real-time applications.
  • Scalability: Neo4j supports clustering and sharding, allowing it to scale horizontally to handle large datasets and high query loads.
  • Rich Ecosystem: Neo4j offers a range of tools and integrations, including visualization tools, data import/export utilities, and support for various programming languages.

10. Document-Based NoSQL Systems: MongoDB

Document-based NoSQL systems are databases that store data in documents, typically using formats like JSON, BSON, or XML. Unlike traditional relational databases that use tables, rows, and columns, document-based systems store data in a more flexible, schema-less structure. This allows for a more intuitive and dynamic way to handle data, making it easier to evolve the data model as application needs change.

CRUD Operations in MongoDB:

MongoDB is a popular document-based NoSQL database. CRUD stands for Create, Read, Update, and Delete, which are the four basic operations for managing data.

  1. Create: To add a new document to a collection, you use the insertOne() or insertMany() methods.
    Example:
    db.collection.insertOne({ name:"John Do", age: 30 });
    db.collection.insertMany([{ name:"Jane Do", age: 25 }, { name:"Jim Do", age: 35 }]);
  2. Read: To retrieve documents from a collection, you use the find() method. You can specify query criteria to filter the results.
    Example:
    db.collection.find({ name:"John Do" });
    db.collection.find({ age: { $gt: 25 } });
  3. Update: To modify existing documents, you use the updateOne(), updateMany(), or replaceOne() methods. You can specify the criteria for selecting documents and the changes to apply.
    Example:
    db.collection.updateOne({ name:"John Do" }, { $set: { age: 31 } });
    db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status:"youn" } });
  4. Delete: To remove documents from a collection, you use the deleteOne() or deleteMany() methods.
    Example:
    db.collection.deleteOne({ name:"John Do" });
    db.collection.deleteMany({ age: { $lt: 25 } });

Embedded SQL and Cursors

11. Cursors in Embedded SQL

A cursor in Embedded SQL is a database object used to retrieve, manipulate, and navigate through a set of rows returned by a query. It allows row-by-row processing of query results, which is essential when the host language (like C or Java) does not support set-oriented operations.

Properties of Cursors

  1. Declaration: A cursor is declared with a SQL query. For example
    DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table_name WHERE condition;
  2. Opening: The cursor is opened to establish the result set
    OPEN cursor_name;
  3. Fetching: Rows are fetched one at a time from the cursor:
    FETCH cursor_name INTO :host_variable1, :host_variable2;
  4. Closing: The cursor is closed when done:
    CLOSE cursor_name;

Example:

  1. Declare the Cursor:
    DECLARE sailor_cursor CURSOR FOR SELECT sname, age FROM sailors WHERE rating > :min_rating;
  2. Open the Cursor:
    OPEN sailor_cursor;
  3. Fetch Rows:
    FETCH sailor_cursor INTO :sname, :age;
  4. Close the cursor:
    CLOSE sailor_cursor;

Additional Properties

  • Scrollable Cursors: Allow moving forward and backward through the result set.
  • Updatable Cursors: Enable updating or deleting rows in the result set.
  • Holdable Cursors: Remain open after a transaction is committed.

Cursors provide a powerful way to handle complex queries and row-by-row processing in embedded SQL, making them essential for database applications.

Stored Procedures in SQL

12. Stored Procedures in SQL

A stored procedure in SQL is a set of SQL statements that can be stored and executed on the database server. Stored procedures help in encapsulating complex operations, improving performance, and ensuring consistency.

Benefits of Stored Procedures:

  • Reduced Network Traffic: Since the procedure is executed on the server, only the call to the procedure and the results are sent over the network.
  • Reusability: Procedures can be reused by multiple applications and users.
  • Security: Access to data can be controlled by granting permissions to execute the procedure rather than direct access to the tables.
  • Performance: Stored procedures are precompiled, which can lead to faster execution.

Creating a Stored Procedure:

To create a stored procedure, you use the CREATE PROCEDURE statement. Here’s a simple example:

CREATE PROCEDURE GetEmployeeDetails @EmployeeID INT AS BEGIN SELECT FirstName, LastName, Department FROM Employees WHERE EmployeeID = @EmployeeID; END;

In this example:

  • GetEmployeeDetails is the name of the stored procedure.
  • @EmployeeID is an input parameter.
  • The procedure retrieves the first name, last name, and department of the employee with the specified EmployeeID.

Calling a Stored Procedure:

You can call a stored procedure using the EXEC or CALL statement:

EXEC GetEmployeeDetails @EmployeeID = 1;

 

7. Explain the two phase locking protocol used for concurrency control

The Two-Phase Locking (2PL) Protocol is essential for ensuring serializability and concurrency control in database management systems. It consists of two phases: the growing phase and the shrinking phase.

Growing Phase:
– Lock Acquisition:Transactions can acquire but not release locks, expanding control over needed data items.
– Types of Locks: Shared locks (for read operations) and exclusive locks (for write operations). Shared locks allow multiple transactions to read the same data item, while exclusive locks ensure only one transaction can write to a data item.

Shrinking Phase:
– Lock Release: Transactions can release but not acquire new locks, completing operations and relinquishing control over accessed data items.
– No New Locks: Prevents transactions from interfering with others needing access to the same data items.

Ensuring Serializability:
– Conflict Resolution: 2PL ensures transactions execute in a serializable manner, producing the same outcome as sequential execution by preventing conflicts through locks.
– Deadlock Prevention:While ensuring serializability, 2PL can lead to deadlocks. Additional mechanisms like deadlock detection and prevention protocols are used to handle them.

Variations of 2PL:
– Strict 2PL: Transactions hold all exclusive locks until they commit or abort, ensuring strict schedules for recoverability.
– Rigorous 2PL: Transactions hold all locks (shared and exclusive) until they commit or abort, making implementation easier than strict 2PL.

The Two-Phase Locking Protocol is crucial for maintaining data integrity and consistency in multi-user database environments.


8. Why concurrency control is needed? Explain with an example. Concurrency control is essential in database management systems (DBMS) to ensure the consistency and integrity of data when multiple transactions are executed simultaneously. Without proper concurrency control, several issues can arise, leading to incorrect data and system anomalies. Why Concurrency Control is Needed: 1. Preventing Data Inconsistency: When multiple transactions access and modify the same data concurrently, it can lead to data inconsistency. Concurrency control mechanisms ensure that transactions are executed in a way that maintains data consistency. 2. Ensuring Isolation: Concurrency control enforces isolation among transactions, meaning that the operations of one transaction are not visible to other transactions until the transaction is committed. This prevents transactions from interfering with each other. 3. Maintaining Database Integrity: By controlling the concurrent execution of transactions, concurrency control helps maintain the integrity of the database, ensuring that all transactions are executed in a manner that preserves the correctness of the data. Example: Consider an airline reservation database where two transactions, T1 and T2, are executed concurrently:  Transaction T1: Transfers 5 seat reservations from Flight X to Flight Y1.  Transaction T2: Reserves 4 seats on Flight X2. Initially, Flight X has 80 reserved seats. The operations of T1 and T2 are interleaved as follows: 1. T1 reads the number of reserved seats on Flight X (80). 2. T2 reads the number of reserved seats on Flight X (80). 3. T1 subtracts 5 seats from Flight X (80 – 5 = 75). 4. T2 adds 4 seats to Flight X (80 + 4 = 84). 5. T1 writes the updated number of reserved seats for Flight X (75). 6. T2 writes the updated number of reserved seats for Flight X (84). In this scenario, the final value of reserved seats on Flight X is incorrect (84 instead of 79). This issue, known as the Lost Update Problem, occurs because T2 reads the value of X before T1 updates it, causing the update from T1 to be lost3. Concurrency control mechanisms, such as locking and timestamp ordering, prevent such problems by ensuring that transactions are executed in a controlled manner, preserving the consistency and integrity of the database.


9. What is NoSQL? Explain the CAP theorem. NoSQL stands for “Not Only SQL” and refers to a variety of database technologies designed to handle large volumes of data, diverse data types, and distributed data architectures. Unlike traditional relational databases (SQL), NoSQL databases are built to scale horizontally and can manage unstructured data, making them ideal for big data and real-time web applications. There are several types of NoSQL databases, including document databases, key-value stores, column-family stores, and graph databases. CAP Theorem (Consistency, Availability, Partition Tolerance), also known as Brewer’s Theorem, is a fundamental principle in distributed database systems. It states that it is impossible for a distributed data store to simultaneously provide all three of the following guarantees: 1. Consistency: Every read receives the most recent write or an error. This means that all nodes in the distributed system see the same data at the same time. For example, if a user updates their profile information, all subsequent reads should reflect this update immediately. 2. Availability: Every request (read or write) receives a response, without guarantee that it contains the most recent write. This ensures that the system remains operational and responsive, even if some nodes are down. For instance, a web application should still be able to serve user requests even if some of its servers are offline. 3. Partition Tolerance: The system continues to operate despite an arbitrary number of messages being dropped or delayed by the network between nodes. This means that the system can handle network failures that split the network into disjoint segments. For example, if a network partition occurs, the system should still function correctly in the remaining segments. According to the CAP theorem, a distributed system can only achieve two out of these three guarantees at any given time. Therefore, database designers must make trade-offs based on the specific requirements of their applications.


11.What is NoSQL graph database? Explain Neo4j. A NoSQL graph database is a type of database designed to handle and store data in a graph format, which is particularly useful for representing complex relationships and interconnected data. Unlike traditional relational databases that use tables, graph databases use nodes, edges, and properties to represent and store data. This structure allows for more efficient querying and management of highly connected data. Key Features of NoSQL Graph Databases:  Nodes and Edges: Nodes represent entities (e.g., people, products), and edges represent relationships between these entities (e.g., friendships, purchases).  Properties: Both nodes and edges can have properties (key-value pairs) that store relevant information.  Efficient Traversal: Graph databases are optimized for traversing relationships, making them ideal for applications like social networks, recommendation systems, and fraud detection. Neo4j: Neo4j is a leading open-source graph database that implements a true graph model down to the storage level. It is designed to handle highly connected data and complex queries efficiently. Key Features of Neo4j:  Native Graph Storage: Neo4j stores data as a graph, not as a graph abstraction on top of another technology. This ensures optimal performance and scalability.  ACID Compliance: Neo4j supports ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring reliable and consistent data operations.  Cypher Query Language: Neo4j uses Cypher, a powerful and expressive query language specifically designed for working with graph data. Cypher allows for easy and intuitive querying of the graph.  High Performance: Neo4j is optimized for fast read and write operations, making it suitable for real-time applications.  Scalability: Neo4j supports clustering and sharding, allowing it to scale horizontally to handle large datasets and high query loads.  Rich Ecosystem: Neo4j offers a range of tools and integrations, including visualization tools, data import/export utilities, and support for various programming languages


12.Explain the cursor and its properties in embedded SQL with an example.  Cursor in Embedded SQL  A cursor is a database object used to retrieve, manipulate, and navigate through a set of 
rows returned by a query. It allows row-by-row processing of query results, which is 
essential when the host language (like C or Java) does not support set-oriented 
operations. 
Properties of Cursors 
1. Declaration: A cursor is declared with a SQL query. For example 
DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table_name 
WHERE condition; 
2. Opening: The cursor is opened to establish the result set 
OPEN cursor_name; 
3. Fetching: Rows are fetched one at a time from the cursor: 
FETCH cursor_name INTO :host_variable1, :host_variable2; 
4. Closing: The cursor is closed when done: 
CLOSE cursor_name; 
Example: 
1. Declare the Cursor: 
DECLARE sailor_cursor CURSOR FOR 
SELECT sname, age FROM sailors WHERE rating > :min_rating;[^2^][2] 
2. Open the Cursor: 
OPEN sailor_cursor; 
3. Fetch Rows: 
FETCH sailor_cursor INTO :sname, :age; 
4. Close the cursor: 
CLOSE sailor_cursor; 
Additional Properties 
 Scrollable Cursors: Allow moving forward and backward through the result set. 
 Updatable Cursors: Enable updating or deleting rows in the result set. 
 Holdable Cursors: Remain open after a transaction is committed3. 
Cursors provide a powerful way to handle complex queries and row-by-row processing in 
embedded SQL, making them essential for database applications.


15. Demonstrate working of assertion and riggers in SQL? explain with an example Assertions in SQL Assertions are used to enforce constraints on the data in a database. They ensure that certain conditions are always true. For example, consider a university database where we want to ensure that the salary of an employee is not greater than the salary of their manager1. Example: CREATE ASSERTION SALARY_CONSTRAINT[^2^][2] CHECK (NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.Salary > M.Salary [^3^][3] AND E.Dno = D.Dnumber [^4^][4] AND D.Mgr_ssn = M.Ssn )); Triggers in SQL Triggers are procedures that automatically execute in response to certain events on a particular table or view56. They can be used to enforce business rules, maintain audit trails, and more. Example: Suppose we want to create a trigger that logs any salary changes in an EMPLOYEE table. Step 1: Create a log table CREATE TABLE SALARY_LOG ( EmpID INT, OldSalary DECIMAL(10, 2), NewSalary DECIMAL(10, 2), ChangeDate DATETIME ); Step 2: Create the trigger CREATE TRIGGER SalaryChangeTrigger AFTER UPDATE OF Salary ON EMPLOYEE FOR EACH ROW BEGIN INSERT INTO SALARY_LOG (EmpID, OldSalary, NewSalary, ChangeDate) VALUES (OLD.EmpID, OLD.Salary, NEW.Salary, NOW()); END; This trigger logs the old and new salary values along with the date of change whenever an employee’s salary is updated. Summary  Assertions enforce constraints across the database, ensuring data integrity.  Triggers automate actions in response to specific events, helping maintain consistency and enforce business rules.


10.What are document based NoSQL systems? basic operations CRUD in MongoDB. Document-based NoSQL systems are databases that store data in documents, typically using formats like JSON, BSON, or XML. Unlike traditional relational databases that use tables, rows, and columns, document-based systems store data in a more flexible, schema-less structure. This allows for a more intuitive and dynamic way to handle data, making it easier to evolve the data model as application needs change. 
CRUD Operations in MongoDB: 
MongoDB is a popular document-based NoSQL database. CRUD stands for Create, Read, 
Update, and Delete, which are the four basic operations for managing data. 
1. Create:  To add a new document to a collection, you use the insertOne() or insertMany() methods. 
Example: 
db.collection.insertOne({ name: “John Doe”, age: 30 }); 
db.collection.insertMany([{ name: “Jane Doe”, age: 25 }, { name: “Jim Doe”, age: 35 
}]); 
2. Read: To retrieve documents from a collection, you use the find() method. You can specify query criteria to filter the results. 
Example: 
db.collection.find({ name: “John Doe” }); 
db.collection.find({ age: { $gt: 25 } }); 
3. Update: To modify existing documents, you use the updateOne(), updateMany(), or replaceOne() methods. You can specify the criteria for selecting documents and the changes to apply. 
Example: 
db.collection.updateOne({ name: “John Doe” }, { $set: { age: 31 } }); 
db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status: “young” } }); 
4. Delete: To remove documents from a collection, you use the deleteOne() or deleteMany() methods. 
Example: 
db.collection.deleteOne({ name: “John Doe” }); 
db.collection.deleteMany({ age: { $lt: 25 } });