Storage Groups -

A Storage Group is a set of disk volumes (storage devices) used to store data. It represents the space where the databases, tables, indexes, and other objects will be physically stored. It ensures that large amounts of data are distributed across multiple storage devices and helps in organizing data storage and backups.

Example: A storage group might have multiple disk volumes to store different table spaces for fast access.

Databases -

A DB2 Database is a collection of data and objects such as tables, associated indexes, views, table spaces, index spaces and other structures that are organized to store and manage information. A database contains schemas (logical groups of objects) and table spaces (physical storage areas).

Example: A company may have a "Finance" database for accounting records and a "HR" database for employee information.

Table Space -

A table space is a logical storage area (set of volumes) in a database where tables and related objects are stored. It helps manage how data is stored on the physical disk by grouping its related objects. All the tables are stored in table spaces. A table space shoul have atleast one table and can have one or more tables.

Example: It is like a folder on our computer where we store related files, ensuring efficient use of space and better performance.

Table -

A Table is a collection of rows (records) and columns (fields) to store related data. It is like a spreadsheet: each row holds one record, and each column stores one type of information, such as a name, price, or date.

Tables are the core components of relational databases, where data is organized logically for easy access and management.

Example: A table named "Employees" could store employee IDs, names, and positions.

Structure: Each row in the table represents one record, and each column holds a specific type of data.

Types:

• Base Table: Holds actual data (e.g., customer details).
• Temporary Table: Stores data temporarily during a session or process.
• Partitioned Table: Splits a large table into smaller sections for faster access.
• Catalog Table: Stores metadata—information about other database objects.

Row -

A Column in a table defines the type of data that will be stored in that part of the table. It is a field that remains the same across all records (rows). Each column stores only one type of data, such as text, numbers, or dates.

Example: In an "Employee" table, columns might include "Employee ID," "Name," and "Hire Date".

Benefit: Columns help organize data in a structured manner, making it easier to query and retrieve specific information.

Column -

A Row is a horizontal entry in a table that represents a single record. Each row contains data for every column in that table, forming a complete entry or record.

Example: A row in a customer table might contain a customer’s name, phone number, and address.

Keys -

A key is a column or a set of columns that is used to uniquely identify each row in a table. Keys are important for maintaining data integrity and ensuring that each record in the table can be uniquely identified. keys play a critical role in defining the structure and relationships within a relational database.

Types:

• Primary Key: A column or a group of columns that uniquely identifies each row in a table. Only one primary key is allowed per table, and it cannot contain duplicate or null values.
• Foreign Key: A column or set of columns that creates a link between two tables by referencing the primary key of another table.
• Unique Key: Ensures that the values in a column are unique (no duplicates allowed), but it can contain null values.
• Composite Key: A key made up of multiple columns to uniquely identify a row.

Views -

A View is a virtual table that is alternate view of representing the data from one or more tables. It provides a specific view of data without storing it separately. Views simplify access to data and allow users to see only the information they need without modifying the original tables.

Example: A view might display only customers' names and email addresses from a larger customer table.

Uses:

• Simplify Complex Queries: Users can access frequently used queries through views.
• Restrict Data Access: Limit access to certain columns or rows by using views.
• Customize Data Display: Provide different users with different perspectives of the data.
• Reusability: Views allow for easy reuse of queries without having to rewrite them.

Schemas and Schema Qualifiers -

A schema is a logical collection of database objects, such as tables, indexes, and views. It organizes related objects under a common name and helps manage large databases efficiently.

Schema works like a namespace to group related database objects. The schema name acts as a prefix (qualifier) when accessing objects within it.

Uses:

• Helps organize database objects logically.
• Makes it easier to manage permissions and access.
• Allows objects with the same name in different schemas.

Index Space -

An Index Space is where indexes are stored. Index spaces keep the index separate from the actual table data, allowing fast data retrieval without scanning the whole table.

An index works like a table of contents in a book—helping the system quickly find specific data. Each index is defined with one or more columns in a table and stored separately in the index space.

Example: In a customer table, an index on the "Customer ID" column allows quick lookups by customer ID.

Benefit: Faster search and retrieval of data, improving performance.

Index -

An index is a set of ordered pointers to the row in the table that improves search performance. It functions like a book's table of contents, helping the system locate data more quickly. DB2 uses the index to improve the performace and maintains the uniqueness of the data.

Example: An index on the "Employee ID" column allows quick retrieval of employee records based on ID.

Structure: Indexes improve performance but consume extra space.

Others -

Synonym -

A Synonym is an alternate name for a database object, such as a table or view. It provides an easy way to refer to objects, especially if their original names are long or complex. Synonyms help users work more efficiently by hiding the complexity of original names.

Example: A synonym "CustomerInfo" could refer to the table "Customer_Data_2024".

Benefit: Useful in systems where table names are large and frequently change.

Alias -

An Alias is a temporary name given to a table or view within a query. It makes queries easier to write and read, especially when dealing with multiple tables. Aliases exist only for the duration of the query execution.

Example:

SELECT e.name, d.dept_id FROM employees e, departments d WHERE e.dept_id = d.id;

Here, "e" and "d" are aliases for the Employees and Departments tables.

Benefit: Reduces query complexity and improves readability.

Buffer Pool -

A Buffer Pool is a portion of memory where frequently accessed data is temporarily stored. This speeds up data retrieval and reduces the load on the storage disk. Instead of reading from the disk every time data is needed, the database fetches it from the buffer pool, which is much faster.

Example: If customer table data is frequently queried, it will be stored in the buffer pool to allow quick access.

Benefit: Reduces disk I/O operations and speeds up the database.

---