Lesson 10

In a relational database, columns in tables serve several fundamental and crucial purposes, each contributing to the effective organization, storage, and retrieval of data. These purposes include:

1. Data Organization and Structuring: Columns provide a structured framework for organizing data within a table. Each column is designed to hold a specific type of information, such as names, dates, prices, or quantities. This organization enables efficient storage and easy access to individual data elements.
2. Data Type Specification: Each column is assigned a specific data type, such as integer, character, date, or decimal. This specification ensures that the data stored in each column is consistent and adheres to a defined format, enhancing data integrity and quality.
3. Attribute Representation: Columns represent the attributes or properties of the entity modeled by the table. For instance, in a customer table, columns might represent attributes such as customer ID, name, address, and contact information. This representation allows for a comprehensive and detailed view of each entity.
4. Query and Analysis Support: Columns enable users to perform queries and analyses on the database. Users can select, filter, and manipulate data based on column values, facilitating data analysis, reporting, and decision-making processes.
5. Data Integrity Enforcement: Constraints and rules can be applied to columns to enforce data integrity. For example, a column can be set as a primary key to ensure uniqueness, or foreign key constraints can be used to establish relationships with other tables.
6. Default Value Setting: Columns can be configured to have default values. When a new record is inserted without a specific value for that column, the default value is automatically assigned, ensuring that the database contains complete and meaningful data.
7. Normalization Facilitation: In the process of database normalization, columns play a key role in eliminating redundancy and dependency issues. By organizing data into appropriate columns and tables, normalization enhances the efficiency and reliability of the database.
8. Indexing: Columns can be indexed to improve the speed and efficiency of data retrieval. Indexes created on columns optimize query performance, especially in large databases with vast amounts of data.

In essence, columns in a relational database table are vital for ensuring organized data storage, maintaining data integrity, supporting efficient data retrieval and analysis, and facilitating database normalization and indexing processes.

To use SQL effectively, you must understand how relational databases store information. In the next module, you will learn about the building blocks of databases, including columns and rows. In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed. The term field is often used interchangeably with column, although many consider it more correct to use field (or field value) to refer specifically to the single item that exists at the intersection between one row and one column.
A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged). Common usage of the term Relational Database Management System technically refers to the software used to create a relational database, but sometimes mistakenly refers to a relational database. The term relational database was originally defined and coined by E.F. Codd.

Implicit locking occurs for all SQL statements so that a user never needs to lock any resource explicitly, although Oracle does provide a mechanism to allow the user to acquire locks manually or to alter the default locking behavior. The default locking mechanisms lock data at the lowest level of restrictiveness to guarantee integrity while allowing the highest degree of concurrency. Whereas many DBMSs store information on row locks as a list in memory, Oracle stores row-locking information within the actual data block where the row is stored.
Some DBMSs also allow lock escalation. For example, if an SQL statement requires a high percentage of the rows within a table to be locked, some DBMSs will escalate the individual row locks into a table lock. Although this reduces the number of locks the DBMS has to manage, it results in unchanged rows being locked, thereby potentially reducing concurrency and increasing the likelihood of deadlock. Because Oracle stores row locks within the data blocks, Oracle never needs to escalate locks.