OO Software Engineering Summar

Cheatsheet Content

### Unit-I: Overview of Database Management System #### Introduction to Data, Information, and Database ##### Data - **Definition:** Raw facts, concepts, or instructions. Building block of information. - **Classification in DBMS:** 1. Information needed by the organization. 2. "Metadata" (information about the database itself). - **Simply:** Facts related to any object. ##### Information - **Definition:** Processed, organized, or structured data that is meaningful. ##### Database - **Definition:** A well-organized collection of related data, accessed in different logical orders. - **Purpose:** Interpretation and storage of information. - **Key Principle:** Data integrity must be maintained, and information should not be recorded in two places (integrated). #### Database Management System (DBMS) - **Definition:** Collection of interrelated data (database) and a set of programs to access/manage that data. Software for maintaining and utilizing a database. - **Components:** - **Database (DB):** Interrelated and persistent data. - **Data Management System (MS):** Application programs to access, update, manage data. - **Nature:** General-purpose software (e.g., Oracle, Sybase) – not application-specific. - **Function:** Stores and accesses data, leaving application-specific tasks to application programs. - **User Interaction:** Allows input, sharing, editing, manipulation, and display of data. #### Objectives of DBMS - **Data Availability:** Data is accessible in a meaningful format at reasonable cost. - **Data Integrity:** Correctness and consistency of data in the database. - **Data Security:** Only authorized users can access data. Enforced by passwords. Prevents conflicting changes from multiple users. - **Data Independence:** Allows users to store, update, retrieve data efficiently. Provides an "abstract view" of data storage. #### File-Based System - **Overview:** Prior to DBMS, OS file systems stored information. Each application program defined and managed its own data. - **Example (University):** Separate application programs for student, faculty, courses, etc., each with its own files. #### Drawbacks of File-Based System 1. **Duplication of Data (Data Redundancy):** - Same data stored multiple times. - **Consequences:** Wasted storage space (cost increase), loss of data integrity (inconsistent data, e.g., updated address in one file but not another). 2. **Data Dependence (Program-Data Dependence):** - Application programs depend directly on data structure. - Changes in data structure require rewriting application programs. - Less flexible than data independence. 3. **Incompatible File Formats:** - File structure depends on application programming language (e.g., FORTRAN vs. C). - Difficult to process files generated by different languages. 4. **Separation and Isolation of Data:** - Data stored in separate files, making it difficult to access. - Requires application programmer to synchronize multiple files for correct data extraction. #### Database Approach - **Definition:** Data management method using a centralized database instead of separate files per application. Data is stored, managed, and accessed via a DBMS. #### Key Features of Database Approach 1. **Centralized Data:** All data in one central database, avoiding duplication. 2. **Data Sharing:** Multiple users and applications can access data simultaneously. 3. **Data Independence:** Changes in data structure don't affect application programs. - **Logical Data Independence:** Ability to modify conceptual schema without changing external schemas or applications. - **Physical Data Independence:** Ability to modify physical schema without rewriting conceptual schema or applications. 4. **Reduced Redundancy:** Minimizes duplicate data storage, saving space, improving consistency. 5. **Improved Data Consistency:** Same data value across applications, updates reflected everywhere. 6. **Data Security:** Access control (usernames, passwords, permissions) protects sensitive data. 7. **Backup and Recovery:** DBMS provides automatic backup and recovery. 8. **Concurrency Control:** Multiple users can access data without conflict. #### Classification of Database Management Systems ##### Classification by User Support - **Single-User Systems:** Support one user at a time (e.g., Microsoft Access for personal/small-scale use). - **Multi-User Systems:** Allow multiple users simultaneous access (e.g., PostgreSQL for collaborative/enterprise applications, data warehousing). ##### Classification by Database Distribution - **Centralized DBMS:** Data stored in a single location (e.g., one server/data center). All access and processing are central. - **Distributed DBMS (DDBMS):** Data stored across multiple locations, connected via network. Data processed locally while part of larger database (e.g., Amazon DynamoDB). Used for high-availability. ##### Classification by Cost and Licensing - **Open-Source DBMS:** Free to use and modify (e.g., MySQL, PostgreSQL). Ideal for small/medium businesses or budget-constrained projects. - **Commercial DBMS:** Licensing fees, robust customer support, additional features, SLAs (e.g., Oracle Database). Used by financial institutions/large corporations for reliable, scalable solutions. ##### Classification by Purpose - **General-Purpose DBMS:** Handles various data types and supports multiple applications (e.g., SQL Server). - **Special-Purpose DBMS:** Built for specific requirements (e.g., for streaming data, real-time processing in telecommunications). ##### Classification Based on Architecture - **The architecture of a DBMS can be single-tier or multi-tier.** ###### 1-Tier Architecture - All components (application, middleware, back-end data) on a single server/platform. - Simplest and most direct. ###### 2-Tier Architecture (Client-Server) - Direct communication between client and server. No intermediate layer. ###### 3-Tier Architecture - Adds an intermediate layer (application server) between client and database server. - Client interacts with application server, which then communicates with database. - **Benefits:** Client cannot directly communicate with server, end-user has no idea about database existence, database has no idea about other users beyond application server. - Most widely used for DBMS design, web applications, distributed applications. #### Three Schema Architecture of Database (Data Abstraction) - **Purpose:** To make the system efficient for data retrieval and reduce user complexity. - **Data Abstraction:** Hiding irrelevant details from the user. - *Example:* Accessing Gmail without knowing physical storage location or data model. ##### Levels of Data Abstraction 1. **View Level (External Schema):** - **Description:** How data is presented to the user/application. Different users can have different views of the same data. - *Example:* Student view (marks, attendance) vs. Faculty view (salary, edit marks) in a university system. 2. **Conceptual Level (Logical Level):** - **Description:** How data is actually stored and structured, including data models, relationships, attributes, and constraints. - **Blueprint:** Overall blueprint of the data. - *Example:* Defining columns like `student_name`, `age`, `mail_id`, `roll_no` for a `student` table in a relational model. 3. **Physical Level (Internal Schema):** - **Description:** Actual physical storage location and structure of data on disk drives. - **DBA's Role:** Database Administrator (DBA) decides data fragmentation, storage location, centralization vs. distribution. - Data is stored as files here, not tables. #### Advantages of Database Approach - **Centralized Data Management:** Integrated files reduce redundancies, improve efficiency. - **Data Independence:** Programs are isolated from changes in data structure. Physical data independence means applications don't worry about physical storage. Logical data independence means conceptual schema changes don't affect external schemas. - **Data Consistency:** DBMS ensures data consistency, avoiding different copies having different values (e.g., updated address in one place but not another). - **Data Integrity:** Essential for efficient and reliable transaction processing. - **Support for Multiple Views:** Provides security and user-specific access. #### Costs and Risks of Database Approach 1. **New Specialized Personnel:** Need to hire/train individuals for design, implementation, administration, and ongoing retraining due to rapid technological changes. 2. **Installation & Management Costs and Complexity:** High initial cost for multi-user DBMS, requires trained personnel, substantial annual maintenance, and potential hardware/communications upgrades. 3. **Conversion Costs:** Cost (money, time, commitment) of converting traditional file processing systems to database technology. 4. **Need for Explicit Backup & Recovery:** Comprehensive procedures required for data backup and restoration to ensure accuracy and availability. 5. **Organizational Conflict:** Shared database requires consensus on data definitions, ownership, and responsibilities, leading to potential conflicts over data definitions, formats, coding, and update rights. ### Unit-II: Data Models #### Data Models - **Definition:** Defines the logical design and structure of a database, and how data is stored, accessed, and updated. - **Purpose:** Models data description, semantics, and consistency constraints. Provides conceptual tools for describing database design at each level of data abstraction. - **Most Widely Used:** Relational Model. #### Classification Based on Data Model 1. **Hierarchical Model** 2. **Network Model** 3. **Entity-Relationship Model (ER Model)** 4. **Relational Model** 5. **Object-Oriented Data Model** 6. **Object-Relational Data Model** #### Hierarchical Model - **Description:** Organizes data in a tree-like structure, with a single root. Data expands like a tree, with child nodes linked to parent nodes. - Each child node has only one parent node. - **Real-world examples:** Index of a book, recipes. - **Relationship:** One-to-many relationship between data types. Only one path from parent to any node. - **Parent-Child Relationship:** Child node has one parent; parent can have multiple children. Multiple parents not allowed. - **Deletion Problem:** Deleting parent node automatically deletes child nodes. - **Pointers:** Reused to link parent and child nodes, used for navigation. - **Advantages:** Simple and fast traversal. Changes in parent node reflect in child node, maintaining data integrity. - **Disadvantages:** Complex relationships not supported (due to single parent constraint), automatic deletion of child nodes on parent deletion. #### Network Model - **Description:** Extension of Hierarchical model. Data organized like a graph, allowing more than one parent node per child. - Used to map many-to-many data relationships. - Most widely used before Relational Model. - **Advantages:** Faster data access due to multiple paths to a node, more related data. - **Disadvantages:** Can become complex with many relationships, user needs detailed knowledge of model, complex for updation/deletion/insertion. #### Relational Data Model - **Description:** Designs data in rows and columns within tables (relations). - **Introduced:** E.F. Codd in 1970. Most widely used database model for commercial data processing. - **Key Features:** - **Tuple:** Each row in the table. - **Attribute:** Each column in the table. - Intersection of row and column has a data value. - Rows and attributes can be in any order. - All rows are distinct. - Relations must have a key (set of attributes). - **Domain:** Set of possible values for an attribute. - **Degree:** Number of attributes (columns) in the relation. - **Cardinality:** Number of tuples (rows) in the relation. - **Table to Relation Rules:** Single (atomic) value at intersection, all entries in column of same type, unique column names, no two rows identical. - **Example:** `EMPLOYEE` table with `Emp_id`, `Emp_name`, `Job_name`, `Salary`, `Mobile_no`, `Dep_id`, `Project_id`. - **Advantages:** Simpler than network/hierarchical, easily scalable (add rows/columns), structural independence (change database structure without affecting data access). - **Disadvantages:** Hardware overheads (requires powerful hardware), can lead to poor database design if not implemented properly (due to ease of use). #### Entity-Relationship Model (ER) - **Definition:** High-level conceptual data model diagram. Displays relationships of entity sets in a database. - **Purpose:** Systematically analyze data requirements for well-designed database. - **Building Blocks:** Entity, Attribute, Relationship. ##### Entity - **Definition:** Real-world object that exists and is distinguishable (e.g., person, place, concept). - **Entity Type/Set:** Collection of similar entities (e.g., `STUDENT` in PSG, `COURSE` in PSG). - An entity can belong to more than one entity type (e.g., a person can be `LECTURER` and `STUDENT`). ##### Attribute - **Definition:** Properties of entity types, describing entities (e.g., `Brand`, `cost`, `weight` for `CELLPHONE`). - **Classification:** - **Single Value Attribute:** Only one value (e.g., `age`, `Roll number`). - **Multivalued Attribute:** More than one value (e.g., `skills` for `EMPLOYEE`). - **Derived Attribute:** Value derived from other attributes/entities (e.g., `Age` from `Date of Birth`). Represented by dotted ellipse. - **Null Value Attribute:** Special value when attribute has no applicable value (e.g., `phone_no` if person has no phone). - **Composite Attribute:** Subdivided into simpler attributes (e.g., `address` into `Street name`, `City`, `State`). ##### Relationship - **Definition:** Association of entities, including one entity from each participating entity type. - **Relationship Type:** Meaningful association between entity types (e.g., `Teaches` between `LECTURER` and `STUDENT`). ##### ER Diagram - **Graphical Modeling Tool:** Standardizes ER modeling. - **Components:** - **Rectangle:** Entity Set. - **Ellipse:** Attribute. - **Diamond:** Relationship. - **Lines:** Link attributes to entity sets, and entity sets to relationship sets. - **Example:** `STUDENT` (Roll number, name) and `CLASS` (Subject Name, Hall Number) with `Attends` relationship. ##### Classification of Entity Sets - **Strong Entity:** Existence does not depend on another entity. Has its own primary key (e.g., `STUDENT`). - **Weak Entity:** Existence depends on another strong entity. Often lacks a primary key. (e.g., `COURSE` depends on `STUDENT` if no students, `Loan` depends on `Customer`). ##### Relationship Degree - **Definition:** Number of entities participating in a relationship. - **Unary Relationship (Recursive):** One entity relates to itself (e.g., `Captain_of` relationship for `PLAYER`). - **Binary Relationship:** Two entities involved (e.g., `Is Assigned` between `Staff` and `Department`). - **Ternary Relationship:** Three entities involved (e.g., `Assigned-to` between `EMPLOYEE`, `PROJECT`, `LOCATION`). - **Quaternary Relationships:** Four entities involved (e.g., `PROFESSOR` `Teaches` `STUDENT` `using` `SLIDES` `in` `COURSE`). ##### Relationship Classification - **One-to-One:** One entity relates to one other entity (e.g., `President` and `Country`). Rare in true form. - **One-to-Many:** One entity relates to multiple other entities (e.g., `Country` and `States`). - **Many-to-Many:** Multiple entities relate to multiple other entities (e.g., `EMPLOYEE` and `PROJECT`). ##### Advantages of ER Modeling - **Easy to Understand:** Simple diagrams for developers, database designers, non-technical users. - **Better Database Design:** Clearly identifies entities/relationships, organizes data logically, avoids missing/unnecessary data, leads to well-structured database. - **Reduces Redundancy:** Proper relationships, primary/foreign keys minimize data duplication, save storage, improve consistency. - **Improves Data Integrity:** Constraints (cardinality, participation) ensure accurate and valid data. - **Acts as a Blueprint:** ER diagrams serve as blueprint for database implementation (MySQL, Oracle, SQL Server). - **Simplifies Coding:** Easier and less error-prone. - **Easy to Modify and Maintain:** Changes visualized and updated without rewriting entire database. - **Improves Communication:** Common language among designers, developers, users. #### Enhanced Entity-Relationship (EER) Model - **Definition:** Extension of basic ER model incorporating additional semantic concepts for complex database requirements. - **Purpose:** Better representation of inheritance, categorization, complex constraints. ##### Key Extensions Over Basic ER Model 1. **Specialization/Generalization:** Class/subclass relationships. 2. **Categorization (Union Types):** Entities belonging to multiple superclasses. 3. **Aggregation:** Treating relationships as higher-level entities. 4. **Enhanced Constraints:** More expressive participation and cardinality constraints. ##### Visual Representation in EER Diagrams - **Specialization:** Circle/triangle connecting superclass to subclasses. - **Attributes:** Subclass attributes listed in entity boxes. - **Constraints:** Notations added to connecting lines. - **"IS-A" Relationship:** Implicit in hierarchy. ##### Generalization - **Definition:** Abstracting common characteristics from multiple entities to create a more general entity (superclass). - **Example:** `Car` and `Truck` (with common attributes like `EngineType`, `Model`, `Color`) generalized into `Vehicle`. - **Diagrammatically:** Triangle with general entity at top, specialized entities (subclasses) branching out. - **Advantages:** Avoids redundancy, provides organized structure for similar entities. ##### Specialization - **Definition:** Breaking down a general entity into more specific sub-entities (subclasses), each with unique attributes/behaviors. - **Example:** `Employee` (with `EmployeeID`, `Name`, `Salary`) specialized into `Manager` (additional `Department`) and `Technician` (additional `Skillset`). - **Diagrammatically:** Triangle with general entity pointing to specific sub-entities. - **Advantages:** Refines data by specifying unique attributes/behavior for each subclass, creates more accurate model. ##### Relationship between Generalization and Specialization - Common enhancements to classical ER model. - Subclass inherits from superclass (`IS-A` relationships, e.g., "Technician IS-A Employee"). - Tools for organizing/simplifying data by abstracting/specifying entity relationships. - **Attribute Inheritance:** Subclasses inherit attributes from parent/superclass entity. - *Example:* `Dog` inherits `age`, `weight` from `Animal`, adds `breed`. - **Multiple Inheritance:** Subclass inherits attributes/behaviors from more than one parent. - *Example:* `Driver` inheriting from `Person` (Name, DOB, Address) and `Vehicle` (Make, Model, Year). - **Implementation in Relational DB:** Combining columns from parent tables into child table, or using linking tables. ##### Constraints on Specialization and Generalization - **Disjointness Constraint:** Specifies if an instance of a supertype can simultaneously be a member of two or more subtypes. - **Overlap:** Same entity instance can be member of multiple subclasses (e.g., `Frog` as `LAND ANIMAL` and `WATER ANIMAL`). - **Disjoint:** Entity instance can be member of only one subclass (e.g., `BOOK` cannot be `JOURNAL` or `PERIODICAL`). - **Completeness Constraint:** Addresses if a supertype instance must also be a member of at least one subtype. - **Total:** All instances of supertype must belong to a subtype. - **Partial:** Not all instances of supertype must belong to a subtype. #### Reducing ER Diagram to Tables - **Process:** Converting an ER model into a relational database schema. Essential for implementing database with relational DBMS (MySQL, Oracle, SQL Server). - **Basic Rules:** 1. Each entity becomes a table. 2. Attributes become columns. 3. Primary keys uniquely identify records. 4. Relationships implemented using foreign keys or separate tables. 5. Cardinality determines table structure. - **Strong Entity:** Has its own primary key (e.g., `STUDENT(Student_ID, Name, DOB)`). - **Weak Entity:** Depends on a strong entity. Primary key = Owner key + Partial key (e.g., `DEPENDENT(Emp_ID, Dep_Name, Age)`). - **Attributes:** - Simple attributes become columns. - Composite attributes are split. - Multivalued attributes create new tables. - Derived attributes are not stored. - **Relationships:** - **One-to-One:** Add foreign key in one table. - **One-to-Many:** Add foreign key on many side. - **Many-to-Many:** Create separate table. - **Specialization:** - Superclass and subclass tables created. - Subclass primary key is also foreign key. ### Unit-III: Relational Model #### Relational Model Overview - **Codd's Rules:** Proposed by Dr. Edgar F. Codd to ensure data integrity, consistency, and usability in RDBMS. 1. **Information Rule:** All data (user info or metadata) stored as values in table cells. 2. **Guaranteed Access Rule:** Each data element accessible logically via table name, primary key, and attribute name. 3. **Systematic Treatment of NULL Values:** Null values must be treated systematically and uniformly. 4. **Active Online Catalog Rule:** Database catalog (metadata) stored and accessed using the same RDBMS. 5. **Comprehensive Data Sublanguage Rule:** DBMS must offer an easily understandable Data Manipulation Language (DML). 6. **View Updating Rule:** All theoretically updatable views must also be updatable by the system. 7. **High-level Insert, Update, and Delete:** DBMS must facilitate high-level operations with ease through single query. 8. **Physical Data Independence:** Application programs unaffected by changes to physical storage structures. 9. **Logical Data Independence:** Application programs unaffected by changes to logical data structure (e.g., adding/modifying tables). 10. **Integrity Independence:** Integrity constraints specified separately from applications, stored in catalog, and automatically enforced. 11. **Distribution Independence:** Data distribution across locations invisible to users, handled transparently by DBMS. 12. **Non-Subversion Rule:** Low-level record access interface must not damage system or bypass security/integrity. #### Relational Data Model Components - **Definition:** Uses tables (relations) to represent data and relationships. - **Components:** Structural, Integrity, Manipulative parts. - **Structural Part:** Defines database as a collection of relations. - **Integrity Part:** Database integrity maintained using primary and foreign keys. - **Manipulative Part:** Relational algebra and calculus are tools to manipulate data. #### Relational Algebra - **Definition:** Theoretical language with operations on relations to define new relations without changing original. - **Closure Property:** Output of one operation can be input to another. - **Purpose:** Understand query execution and optimization in RDBMS. - **Classification of Operations:** Set operations, Database operations. - **Unary Operations:** Selection, Projection. - **Binary Operations:** Union, Difference, Cartesian Product, Join. ##### Selection Operation ($\sigma$) - **Function:** Works on a single relation R, defines a relation with tuples satisfying a specified condition (Predicate). - **Mechanism:** Row-wise filtering. - **Syntax:** $\sigma_{Predicate}(R)$. - **Example:** $\sigma_{GPA > 8}(Student)$ to list students with GPA greater than 8.0. ##### Projection Operation ($\pi$) - **Function:** Works on a single relation R, defines a relation with a vertical subset of R, extracting specified attributes and eliminating duplicates. - **Mechanism:** Column-wise filtering. - **Syntax:** $\pi_{a1, a2, ..., an}(R)$. - **Example:** $\pi_{Name, Salary}(Staff)$ to list names and salaries of staff. ##### Rename Operation ($\rho$) - **Function:** Returns an existing relation under a new name. - **Syntax:** $\rho_A(B)$ renames relation B to A. - **Purpose:** Useful for naming results of relational algebra operations for further expressions. - **Note:** Changes schema, not instance of database. ##### Union Compatibility - **Requirement:** For Union, Intersection, Difference operations, two relations must be union compatible. - **Condition:** Same number of attributes and attributes belong to the same domain. - **Mathematically:** R(A1, ..., An) and S(B1, ..., Bn) are union compatible if `dom(Ai) = dom(Bi)` for i = 1 to n. ##### Union Operation ($\cup$) - **Function:** Combines two relations R and S, defines a relation containing all tuples from R or S or both, with duplicate tuples eliminated. ### Unit-IV: PL/SQL #### PL/SQL Introduction - **Definition:** Procedural Language/Structured Query Language. Combines SQL power with procedural constructs (loops, conditionals, exception handling). - **Structure:** Consists of blocks, fundamental units of PL/SQL code. #### PL/SQL Block Structure - **Sections:** 1. **Declaration Section (Optional):** - **Keyword:** `DECLARE` - **Purpose:** Declare variables, constants, cursors, exceptions, other PL/SQL elements. - **Variables & Constants:** Define with specific data types for temporary data. - **Cursors:** Declare explicit cursors for multi-row processing. 2. **Executable Section (Mandatory):** - **Keyword:** `BEGIN` (starts), `END` (ends before `EXCEPTION`). - **Purpose:** Contains core logic, SQL statements (SELECT, INSERT, UPDATE, DELETE), procedural logic (IF, LOOP, assignments). 3. **Exception Section (Optional):** - **Keyword:** `EXCEPTION` - **Purpose:** Handle runtime errors or exceptions (e.g., `NO_DATA_FOUND`, `TOO_MANY_ROWS`). - **Handling:** Define specific actions for exceptions. `OTHERS` catches any other error. #### PL/SQL Block Types - **Anonymous Block:** Without a name. Used for ad-hoc scripts, one-time operations. - **Named Block:** Has a name. Used for stored procedures, functions, packages. Can be invoked multiple times. #### PL/SQL Language Elements ##### Variables and Constants - **Variables:** Store temporary data, can be modified. Must be declared before use. - **Constants:** Similar to variables, but value cannot be changed after initialization. ##### Data Types 1. **Scalar Data Types:** Store single values. - `NUMBER`: Numeric data (integers, decimals). - `VARCHAR2`: Variable-length character strings. - `DATE`: Date and time values. - `BOOLEAN`: TRUE, FALSE, NULL. 2. **Composite Data Types:** Store multiple values or rows. - `RECORD`: Collection of fields (like a row in a database table). - `TABLE`: Collection of elements of same type (like arrays). 3. **LOB (Large Object) Data Types:** Store large data (text, images, files). - `BLOB`: Binary Large Object. - `CLOB`: Character Large Object. ##### Control Structures - Control flow of execution within a block. 1. **Conditional Statements (IF-THEN-ELSE):** Execute code based on a condition. - `IF condition THEN ... ELSIF another_condition THEN ... ELSE ... END IF;` 2. **Looping Statements:** Iteration. - `LOOP`: Basic loop, runs until `EXIT` condition. - `FOR LOOP`: Loop with specified range. - `WHILE LOOP`: Loop continues as long as a condition is TRUE. ##### Cursors - Handle multi-row queries. 1. **Implicit Cursors:** Automatically created by Oracle for single SQL queries (e.g., `SELECT INTO`). 2. **Explicit Cursors:** Defined by programmer for complex queries, fetch results row by row. - **Steps:** `DECLARE` cursor, `OPEN` cursor, `LOOP` with `FETCH` and `EXIT WHEN`, `CLOSE` cursor. ##### Functions - **Definition:** Named PL/SQL block that returns a value. - **Purpose:** Perform computations or querying, return a value. - **Syntax:** `CREATE OR REPLACE FUNCTION function_name (params) RETURN return_datatype IS ... BEGIN ... RETURN value; END;` ##### Procedures - **Definition:** Named PL/SQL block that performs a specific task but does not return a value. - **Purpose:** Perform series of SQL or PL/SQL operations. - **Parameters:** Can define input, output, or both. - **Syntax:** `CREATE OR REPLACE PROCEDURE procedure_name (params) IS ... BEGIN ... END;` ##### Packages - **Definition:** Collection of related procedures, functions, variables, cursors. - **Parts:** 1. **Package Specification:** Defines public interface (procedures, functions, variables accessible outside package). 2. **Package Body:** Contains actual implementation of procedures, functions, variables. ##### Dynamic SQL (EXECUTE IMMEDIATE) - **Definition:** Construct SQL statements at runtime. - **Purpose:** Useful when SQL queries are not known until runtime. - **Syntax:** `EXECUTE IMMEDIATE 'SQL_QUERY' USING bind_variable;` ### Unit-V: Database Triggers #### Database Triggers Overview - **Definition:** Special stored procedure in a database, automatically executed or "fired" when a specific event occurs on a table or view. - **Purpose:** Data validation, auditing, enforcing business rules, maintaining referential integrity. - **Events:** INSERT, UPDATE, DELETE. #### Key Characteristics of Database Triggers 1. **Automatic Execution:** Fired automatically when defined event occurs. 2. **Event-Driven:** Respond to specific database events (INSERT, UPDATE, DELETE). 3. **Associated with Tables or Views:** Invoked when modification occurs on associated table/view. 4. **Fires Once for Each Row or Statement:** Can fire once per row or once for entire statement. #### Types of Triggers - Classified by **Timing** (when trigger fires) and **Event** (type of event). ##### 1. Timing of the Trigger - **BEFORE Trigger:** Fired *before* modification. - **Use Cases:** Data validation, modification before actual operation, preventing invalid data. - **Example:** `BEFORE INSERT` trigger to check for negative salary. - **AFTER Trigger:** Fired *after* modification is completed. - **Use Cases:** Logging/auditing changes, updating related tables. - **Example:** `AFTER UPDATE` trigger to log salary changes. - **INSTEAD OF Trigger:** Replaces default action (INSERT, UPDATE, DELETE) on a view with a custom action. - **Use Cases:** Modifying data in views, replacing default actions with custom logic. - **Example:** `INSTEAD OF DELETE` trigger to delete from base table when deleting from view. ##### 2. Events that Cause the Trigger to Fire - **INSERT Trigger:** Fired when a new record is inserted. - **UPDATE Trigger:** Fired when an existing record is modified. - **DELETE Trigger:** Fired when a record is deleted. ##### 3. Row-Level vs. Statement-Level - **Row-Level Trigger (`FOR EACH ROW`):** Fires once for *each row* affected by triggering statement. - **Use Cases:** Handling changes at individual row level (e.g., complex validations). - **Example:** `BEFORE INSERT` row-level trigger to log employee info for each new row. - **Statement-Level Trigger:** Fires once for the *entire SQL statement*, regardless of rows affected. - **Use Cases:** Actions for entire statement (e.g., logging operation type, checking conditions across all rows). - **Example:** `AFTER UPDATE` statement-level trigger to log message when update occurs. ##### 4. Special Trigger Types - **Compound Trigger:** Combines multiple triggering events (BEFORE and AFTER) into single execution unit. - **Purpose:** Avoid recursive triggers, better control over execution order. - **Use Cases:** Handle both BEFORE and AFTER logic, prevent issues with mutating tables. #### Syntax for Creating Triggers - `CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON table_name [FOR EACH ROW] BEGIN ... Trigger logic ... END;` - **Explanation:** `trigger_name`, `timing`, `event`, `table_name`, `FOR EACH ROW` (for row-level), `BEGIN/END` block for logic. #### Trigger Use Cases 1. **Data Validation:** Ensure data meets rules before insert/update. 2. **Auditing:** Keep log of changes to critical tables. 3. **Referential Integrity:** Enforce business rules for consistency. 4. **Complex Data Operations:** Automatically update related tables or perform operations based on data changes. #### Considerations and Best Practices 1. **Performance:** Triggers add overhead, can impact performance. 2. **Recursive Triggers:** Avoid situations where triggers call each other recursively. 3. **Debugging:** Can be difficult as executed automatically; log necessary info. 4. **Trigger Limits:** Be aware of database limitations on number/execution time of triggers. 5. **Use with Caution:** Use triggers carefully to avoid complex, hard-to-maintain logic.