Database Concepts and DB2 Fundamentals

When a cursor is used without setting a fetch size (batch size), each fetch operation typically retrieves one row at a time, requiring a separate network roundtrip to the database server. This is inefficient for large result sets. Modern database APIs allow setting a fetch size to retrieve multiple rows per roundtrip, but the default behavior described in the statement is accurate.

T-SQL (Microsoft SQL Server) variables are case-insensitive. @MyVar and @myvar refer to the same variable. This is consistent with SQL Server's default case-insensitive collation behavior. The only case-sensitive elements in T-SQL are string comparisons (if using a case-sensitive collation) and object names in case-sensitive databases.

A database is divided into records (also called rows or tuples). Each record represents a single entity, item, or entry in the database. Records are composed of fields (attributes/columns), but 'records' is the fundamental unit of division. Fields, articles, and names are components or abstractions, not the primary division.

To solve this question, the user needs to have a basic understanding of database management systems and the structure of a database.

The correct answer is:

C. one article from a magazine, journal or newspaper

Each record in a database of newspaper, journal or magazine articles represents a single article, not an entire publication or the author of the article. Therefore, option C is the correct answer.

Option A is incorrect because it suggests that each record represents a single magazine, journal or newspaper, which is not true.

Option B is also incorrect because it suggests that each record represents a single author, which is not true either.

Option D is incorrect because it includes options A and B, which are both incorrect.

In a library catalogue database, each record typically represents one bibliographic item (book, journal, video, CD, etc.). While a record CONTAINS information about the author(s) and subject(s), it doesn't represent the author or subject themselves - those would be in separate tables in a normalized database. 'All of the above' is incorrect because a single record doesn't represent an author or subject.

Electronic article indexes (bibliographic databases) contain citations, not full text. A citation includes the author, title, source (journal name, volume, issue, page numbers, date), and sometimes an abstract/summary. The database does NOT contain the whole article (that would be full-text) or just the title or summary alone - it contains the complete bibliographic citation.

In a movies database where you're organizing your Top 100 list, each record should represent one movie. The record would contain fields for Title, Actor(s), Director, Year, Genre, etc. A record representing an actor, director, or year would only capture partial information - you want the complete entity (the movie) as your primary record.

DB2 Express Edition is IBM's free/low-cost community edition of DB2, designed for small to medium workloads. DB2 Everyplace is for mobile devices, DB2 Workgroup Server Edition is for small-medium business (higher cost), and DB2 Enterprise Server Edition is for large enterprises (highest cost). For a Windows server with 2 CPUs, Express Edition is the lowest cost legally installable option.

In DB2, SELECT privilege can be granted on tables, views, and nicknames. Nicknames are federated database objects that reference remote data sources and support the same privilege model as local tables. Views are virtual tables that also support SELECT privilege. Indexes are internal structures with no privilege control, and sequences use USAGE or NEXT VALUE privileges, not SELECT.

Sequences in DB2 are explicitly referenced in INSERT statements using expressions like NEXT VALUE FOR sequence_name or PREVIOUS VALUE FOR sequence_name to generate unique numeric values for a column. Identity columns are implicitly auto-generated and not externally referenced. Table functions return row sets, not single values. Triggers execute code but don't generate values directly for INSERT.

Database triggers are event-driven procedures that fire only on data modification operations: INSERT, UPDATE, and DELETE. SELECT operations query data without modifying it, so they never activate triggers. This is by design since triggers are meant to enforce business rules, audit changes, or maintain referential integrity when data changes.

In DB2, INT (4 bytes), CHAR (fixed by definition), and DOUBLE (8 bytes) are all fixed-length data types. XML is a variable-length data type because XML documents have inherently unpredictable sizes and are stored using LOB-like mechanisms internally. The XML type dynamically allocates storage based on the actual document size.

Identity columns in DB2 require exact numeric types with a scale of zero (whole numbers): SMALLINT, INTEGER, or BIGINT. DECIMAL/NUMERIC with scale 0 also work. DOUBLE is a floating-point type with imprecise representation and cannot be used for identity columns because identity values must be exact, predictable integers.

When creating a table, the table name, column names, and column data types are all required - a table cannot exist without them. A default constraint is optional because columns can be created without default values. NULL/NOT NULL is also optional but not listed among the choices.

A default constraint only provides a fallback value when no value is specified - it doesn't restrict which values can be inserted. Check constraints restrict values to specific conditions. Referential constraints (foreign keys) restrict values to existing values in another table. Indexes can enforce uniqueness via unique constraints but don't restrict values otherwise.

Views are virtual tables that don't store data - they execute the underlying query each time they're referenced. A view populates dynamically whenever any executable SQL statement (SELECT, INSERT, UPDATE, DELETE) references it. Views don't cache results and don't populate at creation time.

DB2 locking mechanisms can lock tables, rows, and other database objects at various granularities. Buffer pools are memory cache areas used for data page storage and are not database objects that can be locked - locking applies to logical data structures (tables, rows), not memory management structures.

In NPS (Netezza Performance Server), the minimum row overhead is 28 bytes. This is the storage overhead per row regardless of column data. This overhead includes metadata and structure information NPS needs to manage each row. The other values are incorrect NPS overhead specifications.

The answer is False because while rowid provides fast access, it's not recommended as a primary key in NPS. Rowids can change during operations like DELETE and INSERT, making them unreliable as permanent identifiers. Best practice is to use explicit surrogate keys or natural keys that remain stable. Rowid is useful for performance but not as a primary key.