Database Management Systems: Triggers, Views, Constraints, and Data Models

Posted on Sep 6, 2024 in Computers

Triggers

A trigger is a block of code that automatically executes in response to specific events on a particular table or view in a database. They are stored in the database and invoked repeatedly when the defined conditions are met. Triggers are associated with events like:

  • Data Definition Language (DDL) statements (CREATE, DROP, ALTER)
  • Data Manipulation Language (DML) statements (UPDATE, INSERT, DELETE)
  • Database operations (Startup, Shutdown, Login, Logout)

Trigger Syntax

CREATE OR REPLACE TRIGGER Trigger_Name
BEFORE/AFTER/INSTEAD OF INSERT/UPDATE/DELETE OF Column_Name
ON Table_Name [REFERENCING OLD AS O New AS N]
FOR EACH ROW
WHEN (Condition)
DECLARE Declaration Section
BEGIN Execution Section
END;

Views

A view is a virtual table based on the result-set of an SQL statement. Views do not store data themselves but provide a customized way to access data from one or more tables.

View Syntax

CREATE VIEW name_of_view AS
SELECT column1, column2, ...
FROM table_name1, table_name2, ...
WHERE condition;

Example

CREATE VIEW EmployeeDetails(EmpID, EName) AS
SELECT E.EmpID, E.EName
FROM Employee E
WHERE E.Salary > 10000;

Updating Views

UPDATE <view_name> SET <column1>=<value1>, <column2>=<value2>,... WHERE <condition>;

Constraints and Relationships

Constraints are rules enforced on data to maintain data integrity and consistency.

Membership Constraints

These constraints determine which entities can be members of a lower-level entity set.

  • Condition-defined: Membership is based on whether an entity satisfies a specific condition.
  • User-defined: Membership is manually specified.

Disjoint Constraints

Applicable when a superclass has multiple subclasses. If disjoint, an entity can belong to only one subclass.

Overlapping Constraints

Allows an entity to be a member of multiple subclasses (e.g., a Person can be both a Student and a Staff).

Completeness Constraints

Specifies whether an entity in the higher-level entity set must belong to at least one lower-level entity set.

Constraint Types

1. Domain Constraint

Defines the valid set of values (domain) for an attribute (e.g., data type, range).

2. Key Constraint/Null Constraint

Keys uniquely identify records in a table.

  • Primary Key: Uniquely identifies each record; cannot be NULL.

i) Entity Integrity Constraint

Ensures that the primary key attribute in a relation cannot be NULL.

ii) Referential Integrity Constraint

Ensures that foreign key values in a table reference valid primary key values in the related table, maintaining data consistency across relationships.

Characteristics of Database Systems

  1. Represents aspects of real-world applications.
  2. Systematically manages information.
  3. Provides multiple views of data.
  4. Enables efficient data operations (insertion, deletion, update).
  5. Maintains data for specific purposes.
  6. Represents logical relationships between records and data.

Index

An index is a data structure that improves the speed of data retrieval operations on a database table. It acts like a lookup table, similar to an index in a book.

Index Syntax

CREATE INDEX index_name ON table_name;

Storage Manager

The storage manager is a component of a database system that provides the interface between the physical data storage and the application programs and queries. It is responsible for storing, retrieving, and updating data.

Storage Manager Components

  • Authorization and Integrity Manager: Manages user access and enforces integrity constraints.
  • Transaction Manager: Ensures database consistency and handles concurrent transactions.
  • File Manager: Manages disk space allocation and data representation on disk.

Query Processor

The query processor simplifies and facilitates data access. It consists of:

  • DDL Interpreter: Interprets DDL statements and updates the data dictionary.
  • DML Compiler: Translates DML statements into an execution plan.
  • Query Evaluation Engine: Executes the instructions in the execution plan.

The query optimizer analyzes queries and generates an efficient execution plan based on data storage and statistics.

Data Models

A data model is a collection of concepts for describing data, relationships, semantics, and constraints. It provides a way to design a database at different levels (physical, logical, view).

Relational Model

Organizes data into tables (relations) with rows (tuples) and columns (attributes). Relationships between tables are established through foreign keys.

Entity-Relationship Model

Represents data as entities (objects) and relationships between them. Widely used for database design.

Object-Based Data Model

Combines object-oriented features with the relational model.

Semi-structured Data Model

Allows data items of the same type to have different attributes (e.g., XML).

Hierarchical Model

Represents data in a tree-like structure with a single root and parent-child relationships (1:N).

Network Model

An enhanced hierarchical model that allows many-to-many relationships. wOLQkgVBD9VggAAAABJRU5ErkJggg==