What Are Database Objects?
Database objects are components within a database that store, manage, and organize data. These objects define the structure of the database and how data is stored, retrieved, and manipulated. Understanding these objects is crucial for designing efficient databases and ensuring that data is managed effectively.
Tables
Description: Tables are the fundamental storage units in a database. They consist of rows and columns where data is stored.
Components:
- Columns: Each column in a table has a specific data type and holds a particular type of information (e.g., integers, text, dates).
- Rows: Each row represents a single record or instance of the entity described by the table.
Example:
CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100) );
Views
Description: Views are virtual tables created by querying one or more tables. They provide a way to present data without storing it separately.
Characteristics:
- Virtual Nature: Views do not store data themselves but display data dynamically based on the underlying query.
- Security: Can be used to restrict access to specific data by providing a simplified view of the data.
Example:
CREATE VIEW CustomerEmails AS SELECT FirstName, LastName, Email FROM Customers WHERE Email IS NOT NULL;
Indexes
Description: Indexes are performance optimization tools that enhance the speed of data retrieval operations. They are built on one or more columns of a table.
Types:
- Single-Column Index: Created on a single column.
- Composite Index: Created on multiple columns.
Characteristics:
- Performance: Improve query performance by allowing faster data lookup.
- Cost: Can impact the performance of data modification operations (INSERT, UPDATE, DELETE).
Example:
CREATE INDEX idx_lastname ON Customers(LastName);
Stored Procedures
Description: Stored procedures are sets of SQL commands that are stored and executed on the database server. They encapsulate complex business logic and repetitive tasks.
Characteristics:
- Reusable: Can be executed multiple times with different parameters.
- Encapsulation: Hide complex SQL logic from users and applications.
Example:
CREATE PROCEDURE GetCustomerByEmail(IN email VARCHAR(100)) BEGIN SELECT * FROM Customers WHERE Email = email; END;
Functions
Description: Functions are similar to stored procedures but are designed to return a single value. They can be used in SQL queries to perform calculations or transformations.
Characteristics:
- Return Value: Always return a single value.
- Usage in Queries: Can be used directly in SQL queries for calculations and data manipulation.
Example:
CREATE FUNCTION GetFullName(first_name VARCHAR(50), last_name VARCHAR(50)) RETURNS VARCHAR(100) BEGIN RETURN CONCAT(first_name, ' ', last_name); END;
Sequences
Description: Sequences are database objects that generate a sequence of unique numbers. They are commonly used to create unique values for primary keys.
Characteristics:
- Automatic Generation: Automatically generate a sequence of numbers.
- Usage: Often used for primary keys and other unique identifiers.
Example:
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
Triggers
Description: Triggers are special stored procedures that are automatically executed in response to certain events (e.g., INSERT, UPDATE, DELETE) on a table.
Characteristics:
- Automatic Execution: Automatically executed when a specified event occurs.
- Event-Driven: Can be set to execute before or after the triggering event.
Example:
CREATE TRIGGER update_customer_log AFTER UPDATE ON Customers FOR EACH ROW BEGIN INSERT INTO CustomerLog (CustomerID, ChangeDate) VALUES (NEW.CustomerID, NOW()); END;
External Tables
Description: External tables allow a database to access data stored outside of the database, such as in flat files or other external data sources.
Characteristics:
- Direct Access: Enable direct querying of external data without importing it into the database.
- Flexibility: Useful for dealing with large datasets or integrating with external systems.
Example (Oracle):
CREATE TABLE external_data (
id INT,
name VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('data.csv')
);
Constraints
Description: Constraints are rules applied to columns in a table to enforce data integrity and ensure accuracy.
Types:
- PRIMARY KEY: Ensures each record is uniquely identifiable.
- FOREIGN KEY: Maintains referential integrity between tables.
- UNIQUE: Ensures all values in a column are unique.
- NOT NULL: Ensures a column cannot have NULL values.
- CHECK: Enforces domain integrity by limiting the values that can be entered.
Example:
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );