Full Answer Section

• Example: A simple database for a library might have tables for books, authors, and borrowers. A query to find all books by a specific author would involve joining the books and authors tables.

Object Databases

• Data Structure: Data is organized into objects, which encapsulate both data and behavior.
• Data Model: Based on the object-oriented model, which uses objects and classes to represent real-world entities and their relationships.
• Query Language: Object-oriented query languages (OQL) are used to query and manipulate data.
• Example: In an object database, a book object might have attributes like title, author, and publication year, as well as methods like “borrow” and “return.” A query to find all books borrowed by a specific user could directly invoke the “borrowedBy” method on each book object.

Key Differences

2. Cursors in Embedded SQL

A cursor is a database object that allows sequential access to a result set, row by row. It’s used in embedded SQL to process large result sets efficiently and to perform complex data manipulations.

How Cursors Work:

1. Declaration: A cursor is declared with a name and an associated SQL statement.
2. Opening: The cursor is opened to execute the SQL statement and retrieve the result set.
3. Fetching: Rows are fetched from the result set one at a time using a fetch operation.
4. Processing: The fetched row is processed as needed, such as updating or inserting data.
5. Closing: The cursor is closed to release system resources.

Example:

DECLARE CURSOR book_cursor CURSOR FOR
  SELECT * FROM books WHERE author = 'John Doe';

OPEN book_cursor;

FETCH NEXT FROM book_cursor INTO @book_id, @book_title;

WHILE @@FETCH_STATUS = 0
BEGIN
  -- Process the fetched book
  UPDATE books SET price = price * 1.1 WHERE book_id = @book_id;

  FETCH NEXT FROM book_cursor INTO @book_id, @book_title;
END

CLOSE book_cursor;
DEALLOCATE book_cursor;

In this example, the book_cursor is declared to retrieve books by John Doe. The cursor is opened, and rows are fetched one by one. Each fetched row is processed to increase the price by 10%. The cursor is closed and deallocated when all rows have been processed.