RDBMS (205)
RDBMS(205)
Assignment
1. List out Codd’s rule? Explain any four in detail.
Edgar F. Codd, a computer scientist and the inventor of the relational model for database management, proposed a set of rules known as “Codd’s Twelve Rules” to define what makes a database system relational. These rules are designed to ensure data integrity, simplicity, and flexibility in relational database systems.
Rule 0: The Foundation Rule
Rule 1: Information Rule
Rule 2: Guaranteed Access Rule
Rule 3: Systematic Treatment of Null Values
Rule 4: Active/Dynamic Online Catalog based on the relational model
Rule 5: Comprehensive Data SubLanguage Rule
Rule 6: View Updating Rule
Rule 7: Relational Level Operation (High-Level Insert, Update and delete)
Rule 8: Physical Data Independence Rule
Rule 9: Logical Data Independence Rule
Rule 10: Integrity Independence Rule
Rule 11: Distribution Independence Rule
Rule 12: Non Subversion Rule
Information Rule:
Explanation: All information in the database should be logically represented as values in a table. Each value should be accessible by specifying a table name, primary key value, and column name. This rule ensures that all data in the database is accessible through a table-based structure, promoting a consistent and organized representation.
Example:
Consider a table named “Employees” with columns “EmployeeID,” “FirstName,” and “LastName.” The information about employees is stored in this table, and each employee’s details can be accessed by specifying the table name (“Employees”), the primary key value (“EmployeeID”), and the column name (“FirstName” or “LastName”).
Guaranteed Access Rule:
Explanation: Each data value in a relational database should be accessible by specifying a table name, primary key value, and column name. This rule emphasizes the importance of a unique primary key for each table, ensuring that data retrieval is unambiguous and efficient.
Example:
In a table representing “Products” with a primary key “ProductID” and columns like “ProductName” and “Price,” the Guaranteed Access Rule ensures that each product’s details can be uniquely accessed by specifying the table name (“Products”), the primary key value (“ProductID”), and the column name (“ProductName” or “Price”).
Systematic Treatment of Null Values:
Explanation: The DBMS must allow each field to remain null, representing missing or undefined information. This supports the handling of incomplete or unknown data without compromising the integrity of the database.
Example:
In a table representing “Customers” with columns such as “CustomerID,” “FirstName,” “LastName,” and “PhoneNumber,” the Systematic Treatment of Null Values allows certain phone numbers to be null if the customer hasn’t provided that information. This flexibility accommodates scenarios where not all information may be available for every record.
Dynamic Online Catalog Based on The Relational Model:
Explanation: The structure of the database (metadata) must be stored in a catalog or data dictionary, and it should be accessible to authorized users. This catalog provides information about tables, columns, constraints, and other database components, allowing users to query and understand the database’s structure.
Example:
A system catalog might contain information about tables, such as “Employees” or “Products,” including details like column names, data types, and constraints. Users can query this catalog to retrieve metadata about the database structure, aiding in database administration, optimization, and understanding.
Codd’s Twelve Rules provide a solid foundation for relational database management systems, ensuring that these systems adhere to principles that promote data integrity, accessibility, and flexibility. The rules collectively contribute to the reliability and maintainability of relational databases.
What is cursor? Explain types of cursor with example.
A cursor is a database object used to manipulate and traverse the result set of a query. Cursors provide a way to iterate over the rows returned by a SELECT statement, allowing the application to process each row one at a time. Cursors are especially useful when dealing with large result sets or when processing rows sequentially.
There are two main types of cursors:
1. Implicit (or Default) Cursors:
->Implicit cursors are automatically created by the database management system (DBMS) to manage query results.
->They are often used for simple queries and do not require explicit declaration or management by the developer.
->Implicit cursors are suitable for queries that return a single row or a small result set.
DECLARE
employee_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO employee_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ‘ || employee_name);
END;
/
2. Explicit Cursors:
->Explicit cursors are declared, opened, fetched, and closed by the developer.
-> Developers have more control over the cursor’s lifecycle and can use them to handle complex scenarios such as iterating over multiple rows or dealing with dynamic queries.
->Explicit cursors are particularly useful when dealing with queries that return multiple rows.
DECLARE
CURSOR employee_cursor IS
SELECT employee_id, employee_name FROM employees WHERE department_id = 10;
employee_record employee_cursor%ROWTYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO employee_record;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || employee_record.employee_id || ‘, Name: ‘ || employee_record.employee_name);
END LOOP;
CLOSE employee_cursor;
END;
/
Explain exception handling in Oracle
Exception handling in Oracle refers to the mechanism by which errors or exceptional conditions that may occur during the execution of a PL/SQL block are identified, captured, and managed. PL/SQL provides a robust exception handling mechanism to deal with runtime errors and other exceptional situations. The key components of exception handling in Oracle are the EXCEPTION block, predefined exceptions, and user-defined exceptions.
Syntax of Exception Handling
DECLARE
— Declaration section
BEGIN
— Execution section
EXCEPTION
WHEN exception1 THEN
— Handler for exception1
WHEN exception2 THEN
— Handler for exception2
…
WHEN OTHERS THEN
— Default handler for any other exception
END;
Explanation:
-> The EXCEPTION block allows the specification of various handlers for specific exceptions.
-> Each WHEN clause corresponds to a specific exception or condition that the developer wants to handle.
-> OTHERS is a special case that serves as a catch-all for any exception not explicitly handled.
Example:
Consider a scenario where a division by zero error might occur:
DECLARE
numerator INTEGER := 10;
denominator INTEGER := 0;
result NUMBER;
BEGIN
— Attempting division
result := numerator / denominator;
— Rest of the code
DBMS_OUTPUT.PUT_LINE(‘Result: ‘ || result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
— Handler for division by zero
DBMS_OUTPUT.PUT_LINE(‘Error: Division by zero’);
WHEN OTHERS THEN
— Default handler for any other exception
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred’);
END;
/
Explanation:
-> In this example, there’s an attempt to divide numerator by denominator.
-> If denominator is zero, a ZERO_DIVIDE exception will be raised.
->The EXCEPTION block contains specific handlers for the ZERO_DIVIDE exception and a generic handler (OTHERS) for any other exceptions.
->Depending on the situation, additional logic or error logging can be added to each exception handler.
Predefined Exceptions:
Oracle provides a set of predefined exceptions that represent common error conditions. Some examples include ZERO_DIVIDE, TOO_MANY_ROWS, NO_DATA_FOUND, etc. Developers can use these exceptions in the WHEN clause to handle specific error scenarios.
DECLARE
result NUMBER;
BEGIN
SELECT 1/0 INTO result FROM dual; — Raises ZERO_DIVIDE exception
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(‘Error: Division by zero’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred’);
END;
/
User-Defined Exceptions:
Developers can also define their own exceptions to handle specific error conditions that are not covered by predefined exceptions. This allows for custom error handling based on the application’s requirements.
DECLARE
my_exception EXCEPTION;
BEGIN
RAISE my_exception; — Raises user-defined exception
EXCEPTION
WHEN my_exception THEN
DBMS_OUTPUT.PUT_LINE(‘Custom exception handled’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred’);
END;
/
Exception handling is a crucial aspect of writing robust PL/SQL code, ensuring that applications can gracefully handle unexpected situations and errors during execution.
Discuss PL/SQL block structure
A PL/SQL (Procedural Language/Structured Query Language) block is a logical unit of code that can contain declarations, executable statements, and exception handlers. PL/SQL blocks are used to group and organize code in a procedural manner, making it easier to read, maintain, and debug. The basic structure of a PL/SQL block consists of three main parts: the declaration section, the executable section, and the exception-handling section.
PL/SQL Block Structure:
DECLARE
— Declaration section
variable1 datatype1;
variable2 datatype2;
BEGIN
— Executable section
— SQL and PL/SQL statements go here
— Variables declared in the DECLARE section can be used here
EXCEPTION
— Exception-handling section
— Handlers for specific exceptions or conditions go here
END;
/
Explanation of Each Section:
Declaration Section:
->The DECLARE keyword marks the beginning of the declaration section.
->This section is optional, and it is used to declare variables, constants, cursors, types, and other programmatic elements.
->Variables declared here are local to the block and can be used in the executable section.
Example:
DECLARE
employee_name VARCHAR2(50);
employee_salary NUMBER;
2. Executable Section:
->The BEGIN keyword marks the beginning of the executable section.
->This section is mandatory and contains the actual PL/SQL statements that perform actions, calculations, or database operations.
->SQL and PL/SQL statements can be used here, and variables declared in the declaration section can be referenced.
Example:
BEGIN
— SQL and PL/SQL statements go here
SELECT salary INTO employee_salary FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE(‘Employee Salary: ‘ || employee_salary);
3. Exception-Handling Section:
->The EXCEPTION keyword marks the beginning of the exception-handling section.
->This section is optional and is used to handle exceptions that may occur during the execution of the block.
->It contains one or more WHEN clauses, each specifying a particular exception and the code to be executed if that exception occurs.
->The WHEN OTHERS clause is a catch-all for any exceptions not explicitly handled.
Example:
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No data found for the specified condition.’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred.’);
4. Block Terminator:
->The block is terminated with the / symbol. This is specific to the SQL*Plus environment and is used to execute the block.
Example:
/
Example of a Complete PL/SQL Block:
DECLARE
— Declaration section
employee_name VARCHAR2(50);
employee_salary NUMBER;
BEGIN
— Executable section
SELECT salary INTO employee_salary FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE(‘Employee Salary: ‘ || employee_salary);
EXCEPTION
— Exception-handling section
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No data found for the specified condition.’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred.’);
END;
/
Fun & Easy to follow
Works on all devices
Your own Pace
Super Affordable
Popular Videos
Play Video
UX for Teams
Learn the basics and a bit beyond to improve your backend dev skills.
Chris Matthews
Designer
Play Video
SEO & Instagram
Learn the basics and a bit beyond to improve your backend dev skills.
Chris Matthews
Designer