Vinod Sebastian – B.Tech, M.Com, PGCBM, PGCPM, PGDBIO

Hi I'm a Web Architect by Profession and an Artist by nature. I love empowering People, aligning to Processes and delivering Projects.

Hi I'm a Web Architect by Profession and an Artist by nature. I love empowering People, aligning to Processes and delivering Projects.


Category: Database

Database

  • Data Definition Language

    Data Definition Language

    Data Definition Language (DDL) in databases comprises a set of commands used to define the structure and properties of data within a database. These commands play a crucial role in creating, modifying, and deleting database objects.

    Key DDL Commands:

    • DEFAULT: Sets a default value for a column.
    • CONSTRAINT name_PK PRIMARY KEY (fieldname): Establishes a primary key constraint to uniquely identify rows in a table.
    • CONSTRAINT name_FK FOREIGN KEY (fieldname): Enforces referential integrity by linking one table’s key to another table’s key.
    • CONSTRAINT name_NN NOT NULL (fieldname): Ensures that a column cannot contain NULL values.
    • CONSTRAINT name_CK CHECK (fieldname IN(x,y)): Adds a condition to check the data before inserting or updating it.
    • CONSTRAINT name_UQ UNIQUE (fieldname): Ensures that all values in a column are unique.

    When DDL commands are executed, an implicit commit occurs, committing the transaction even if the command fails or is rolled back.

    Common DDL Operations:

    • CREATE DATABASE: Initiates the creation of a new database.
    • CREATE TABLE: Defines the structure of a new table including its columns, data types, and constraints.
    • ALTER TABLE: Modifies the structure of an existing table by adding, modifying, or dropping columns.
    • ALTER TABLE RENAME: Changes the name of a column in a table.
    • ALTER TABLE DROP: Removes a column from a table.
    • ALTER TABLE SET UNUSED COLUMN: Marks a column as unused to improve performance without deleting the column.

    Constraints and Validation:

    • ENABLE: Enforces constraints on data to maintain data integrity.
    • DISABLE: Allows data to be entered even if it violates constraints.
    • VALIDATE: Checks existing data against constraints to ensure they are met.
    • NOVALIDATE: Skips the validation process for existing data against constraints.

    Views and Sequences:

    • CREATE VIEW: Defines a virtual table based on the result of a query for data abstraction and security purposes.
    • CREATE SEQUENCE: Generates unique numeric values typically used for generating primary key values.

    Indexes and Synonyms:

    • CREATE INDEX: Enhances the performance of queries by speeding up data retrieval.
    • CREATE UNIQUE INDEX: Ensures the uniqueness of values in a column to prevent duplicate entries.
    • CREATE SYNONYM: Provides an alternative name or alias for a table, view, sequence, or other schema objects.

    Additional DDL Operations:

    • GRANT: Assigns specific access privileges to users or roles to manipulate database objects.
    • FLASHBACK: Allows the restoration of database objects to a previous state, enabling point-in-time recovery.

    Example of External Table Creation:

    CREATE TABLE statments
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
    SKIP 2
    FIELDS ( fieldname fieldtype)
    )
    LOCATION 'filepath'

  • Data Manipulation Language

    Data Manipulation Language

    Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used to interact with databases like Oracle. It encompasses various operations for managing data within a database system such as querying, inserting, updating, and deleting data from tables.

    SELECT Statement

    • The SELECT statement is fundamental in DML, allowing users to retrieve data from one or more tables in a database. Key features include:
    • Projection, selection, and joining capabilities for fetching specific data
    • Options like DISTINCT to remove duplicate rows, UNIQUE to ensure unique results, and ALL to include all rows
    • Pseudo columns such as ROWNUM for row numbers and ROWID for row identifiers
    • Usage of ASTERISK (*) for selecting all columns from a table
    • Manipulation of literals like numbers, strings, dates, and intervals within queries
    • Application of operators following standard precedence rules for calculations

    Views, Tables, and Synonyms

    • Views: Virtual tables generated by executing a SELECT query, providing a structured representation of data from one or more tables.
    • Tables: Physical storage structures in a database holding data, defined in the data dictionary and consisting of rows and columns.
    • Synonyms: Alias names for database objects such as tables, views, sequences, and procedures, simplifying queries and enhancing security by concealing actual object names.
    • DESCRIBE: SQL command displaying the structure of a table, including column names, data types, and constraints.

    Filtering and Sorting

    • Filtering data involves utilizing the WHERE clause with conditions like IN for matching values in a list, LIKE for pattern matching, IS NULL to identify null values, and NOT NULL to exclude null values
    • Understanding the behavior of NULL values in comparisons and logical operations
    • Sorting data using the ORDER BY clause to arrange results in ascending or descending order based on one or more columns

  • Transaction Control Language

    Understanding Transaction Control Language in Databases

    Transaction Control Language (TCL) plays a crucial role in managing transactions within a database. It allows users to control the flow of transactions by defining checkpoints, committing or rolling back changes, and creating savepoints.

    Key Features of Transaction Control Language:

    • Session-based: TCL operates on a per-session basis, allowing users to control transactions within a specific session without affecting others.
    • SAVEPOINT: TCL enables the creation of savepoints, which define a specific point within a transaction to which you can roll back if needed.
    • ROLLBACK WORK: This command is used to undo changes made within a transaction up to a specified savepoint or the beginning of the transaction.
    • COMMIT WORK: When you are satisfied with the changes made within a transaction, the COMMIT WORK command is used to permanently apply those changes to the database.

    By understanding and utilizing TCL effectively, database users can ensure data integrity and consistency while managing transactions efficiently.

  • Introduction

    Introduction to Oracle Database

    General

    SQL (Structured Query Language) is a 4th generation programming language commonly used for managing relational databases.

    Double quotation marks (“”) can be used in SQL to make column names case sensitive.

    In the context of databases, a schema is like a folder that contains objects owned by a user account, such as tables, views, and indexes.

    Normalization Forms

    • 1NF (First Normal Form): Ensures that there are no repeating groups within a table, maintaining a 2-dimensional structure.
    • 2NF (Second Normal Form): Requires that each table has a primary key that is not composite.
    • 3NF (Third Normal Form): Eliminates data that does not depend on the primary key, ensuring data integrity.
    • Boyce-Codd Normal Form: Addresses certain rare logical inconsistencies that may arise in a relational database.
    • 4NF (Fourth Normal Form): Ensures that every multivalued dependency is dependent on a super key.
    • 5NF (Fifth Normal Form): Specifies that every join dependency is a result of the candidate keys in the database.

    Namespace in Oracle Database

    • System level namespace includes USER, ROLES, and PUBLIC SYNONYMS.
    • Schema level namespace consists of TABLE, VIEW, SEQUENCE, PRIVATE SYNONYMS, and USER DEFINED TYPES.
    • INDEX and CONSTRAINT fall under the schema level namespace in Oracle databases.

    Data Types in Oracle

    • CHAR(n): Fixed-length character data type that retrieves values with spaces padded to the defined length.
    • VARCHAR2(n): Variable-length character data type.
    • NUMBER(n, m): Numeric data type where ‘n’ represents precision and ‘m’ represents scale.
    • DATE: Stores date and time information with formats controlled by NLS_DATE_FORMAT and NLS_TERRITORY settings.
    • TIMESTAMP(n): Stores date and time with fractional seconds precision ranging from 1 to 9 digits.
    • TIMESTAMP(n) WITH TIMEZONE: Extends TIMESTAMP data type to include time zone information.
    • TIMESTAMP(n) WITH LOCAL TIMEZONE: Stores date and time in the local time zone of the database.
    • INTERVAL YEAR(n) TO MONTH: Represents a period of time in years and months.
    • INTERVAL DAY(n1) TO SECOND(n2): Represents a period of time in days, hours, minutes, and seconds.
    • BLOB: Binary Large Object data type for storing large binary data.
    • CLOB: Character Large Object data type for storing large text data.
    • NCLOB: National Character Large Object data type for storing Unicode text data.

  • Data Control Language

    Data Control Language

    Role and System Privileges

    In database management, role and system privileges are essential for controlling access and permissions effectively. While roles and system privileges can be granted together, they cannot be granted with object privileges.

    Data Dictionary

    The data dictionary in a database contains metadata, which is data about the data stored within the database. Some important tables within the data dictionary include:

    • DBA_TABLES: Contains information about all tables in the database.
    • ALL_TABLES: Lists all tables that the current user has privileges to access.
    • USER_TABLES: Displays all tables owned by a specific user.

    Common attributes found in these tables include TABLE_NAME, STATUS, ROW_MOVEMENT, AVG_ROW_LEN, and OWNER.

    System Operations

    System operations involve managing privileges within a database. Some key operations include:

    • GRANT privilege TO user: Grants a specific privilege to a user.
    • REVOKE privileges FROM user: Removes previously granted privileges from a user.
    • Use ANY: Allows granting privileges to any user without specifying a particular user.
    • WITH ADMIN OPTION: Enables a user to grant the same privilege to others.
    • Revoking WITH ADMIN OPTION: Revocation of this option does not cascade to other users.
    • GRANT ALL PRIVILEGES TO user: Grants all available privileges to a user.
    • GRANT privileges To PUBLIC: Grants specific privileges to all users (public).

    Object Privileges

    Object privileges control access to specific objects in the database. Important points regarding object privileges include:

    • PUBLIC SYNONYMS: These do not require access permissions as they are public, but the table they reference needs specific permission.
    • GRANT user PRIVILEGES ON table TO user WITH GRANT OPTION: Allows a user to grant the same privileges to others.
    • ALL PRIVILEGES: Can be abbreviated as ALL when granting privileges on objects.
    • REVOKE privileges ON table FROM user: Revokes privileges on a specific table, cascading to related objects.

    Column-Level Privileges

    Column-level privileges involve granting permissions at the column level, usually limited to INSERT, UPDATE, and REFERENCES operations. When granting INSERT at the column level, all non-null columns in the row must be included.

    User Privileges

    Understanding user privileges is crucial for managing access within a database. Some key aspects include:

    • USER_SYS_PRIVS (PRIVILEGE, ADMIN_OPTION): Displays privileges for the current user.
    • USER_TAB_PRIVS: Shows on whom the user has granted privileges.

    Types of Users

    Users in a database are categorized based on their roles and permissions. Common types of users include:

    • CONNECT: Typically used for generic user sessions.
    • RESOURCE: Commonly assigned to application developers for creating tables.
    • DBA: Designates a database administrator with extensive privileges.
    • CREATE ROLE rolename: Creates a new role in the database.
    • GRANT privilege TO rolename: Grants a specific privilege to a defined role.
    • GRANT role TO user: Assigns a role to a user without cascading effects.
    • Role and privilege assignments: These assignments are independent of each other.

  • Miscellaneous

    Database Tips and Tricks

    Regular Expressions

    Regular expressions (regex) are powerful tools for pattern matching in strings. In Oracle, there are several functions that utilize regular expressions:

    • REGEX_SUBSTR(string, pattern, position, which_occurrence, instruction_of_match): Returns the character string matched.
    • REGEX_INSTR(string, pattern, position, which_occurrence, optional, instruction_of_match): Returns the position of the match. Set optional = 1 to get the position after the match.
    • REGEX_REPLACE(string, pattern, replace, position, optional, instruction_of_match): Replaces the matched pattern in the string.
    • CHECK (REGEXP_LIKE(column_name, regex_expression)): Checks if a column value matches the regex pattern.
    • Useful information:
      • Instructions for matching include: c for case-sensitive, i for case-insensitive, n to match newline, m for multiline, and x to ignore white spaces.
      • [ ] matches any one character, while [^ ] matches any character except the specified one.
      • [:graph:] is equivalent to [A-Za-z0-9punctuation].
      • Quantifiers: + for one or more, * for zero or more, ? for zero or one, and {n1}, {n1,}, {n1,n2} for specific repetitions.
      • (ab|bc|cd) uses | as an OR operator.
      • Use to escape special characters.
      • ^ denotes the start of a string, while $ denotes the end.
      • The first captured expression is referenced as 1.

    Session Control Statements

    In Oracle, session control statements are used to manage the current session:

    • ALTER SESSION: Modifies the current session’s settings.
    • SET ROLE: Assigns roles to the current session.

    System Control Statements

    System control statements in Oracle are used for system-wide changes:

    • ALTER SYSTEM: Alters system parameters.

    Embedded SQL Statements

    Embedded SQL statements refer to any Data Definition Language (DDL), Data Manipulation Language (DML), or Transaction Control Language (TCL) integrated into a third-generation programming language (3GL).

    Miscellaneous Tips

    Here are some additional miscellaneous tips and commands:

    • System Privileges vs. Object Privileges: System privileges are like having a license to drive any car, while object privileges are specific to driving a particular car.
    • CREATE USER username IDENTIFIED BY password: Creates a new user with the specified username and password.
    • ALTER USER username IDENTIFIED BY password: Modifies the password for an existing user.
    • DROP USER username: Removes a user from the database.
    • DROP USER username CASCADE: Drops a user and all objects owned by the user.
    • CONNECT username/password: Connects to the database using the specified username and password.

  • Managing the RDBMS

    Managing the RDBMS

    Introduction

    In the realm of databases, managing transactions and ensuring data integrity are crucial aspects. This article delves into the key concepts of managing a Relational Database Management System (RDBMS) effectively.

    Transactions in RDBMS

    A transaction in an RDBMS refers to a single logical operation on the data. It is essential for maintaining data consistency and integrity within the database.

    ACID Properties

    • Atomicity: Atomicity ensures that a transaction is treated as a single unit, following an “all or nothing” rule.
    • Consistency: Consistency guarantees that the database remains in a valid state, such as enforcing referential integrity through propagation constraints.
    • Isolation: Isolation ensures that concurrent transactions do not interfere with each other, preventing phenomena like dirty reads.
    • Durability: Durability ensures that committed transactions are permanently saved, typically through the use of transaction logs to recover data in case of system failures.

    Backing up a Database

    When it comes to safeguarding your database, regular backups are essential to prevent data loss.

    1. Physical Backup:
      • Cold Backup: This involves shutting down the database before taking a backup, which may result in longer downtimes.
      • Hot Backup: A backup taken while the database is online, though it may have limitations on transaction log recovery.
    2. Logical Backup: This type of backup involves capturing the logical structure of the database, but it may have limitations like the inability to perform point-in-time recovery and potential loss of referential integrity.

    Data Dictionary

    A data dictionary is a collection of tables that stores metadata information about the database, providing essential details about its structure and organization.

    Backup vs. Data Recovery

    While backup creates a duplicate copy of the data, data recovery involves restoring data from these backups when needed to recover lost or corrupted information.

    Transaction Log

    The transaction log, also known as the database log or binary log, records all actions executed by the RDBMS to ensure the ACID properties are maintained in the event of system failures or crashes.

    Conclusion

    Effectively managing an RDBMS involves understanding transactions, maintaining data integrity through backups, and leveraging tools like data dictionaries and transaction logs to safeguard critical information within the database. By adhering to best practices and principles, organizations can ensure the reliability and consistency of their data in the long run.

  • Insert

    Understanding SQL INSERT Statements

    When working with databases, the INSERT statement in SQL is used to add new records or rows into a table. It is essential for data manipulation and maintaining the integrity of the database.

    Syntax of INSERT Statement

    The basic syntax of the INSERT statement in SQL is as follows:

    INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
VALUES (value1, value2, value3);

    Explaining the Syntax

    • INSERT INTO tablename: This specifies the table where the data will be inserted.
    • (fieldname1, fieldname2, fieldname3): These are the columns in the table where the data will be inserted.
    • VALUES (value1, value2, value3): These are the corresponding values to be inserted into the specified columns.

    Example

    For example, let’s say we have a table named employees with columns emp_id, emp_name, and emp_salary. To insert a new employee record, the SQL query would look like this:

    INSERT INTO employees (emp_id, emp_name, emp_salary)
VALUES (101, 'John Doe', 50000);

    Conclusion

    Mastering the INSERT statement in SQL is crucial for anyone working with databases. It allows for seamless addition of new data into tables, ensuring efficient data management.

  • SQL

    Basic SQL Fundamentals

    Structured Query Language (SQL) is a standard programming language used to interact with databases. Here are some basic concepts:

    Character Values and Strings

    In SQL, character values such as text data or strings are enclosed in single quotes. For example:

    SELECT * FROM employees WHERE department = 'IT';

    When querying data, it is important to use single quotes to specify string values. Failure to do so may result in syntax errors.

  • View

    View in Databases

    A view in a database is a parsed SQL statement that retrieves records at the time of execution. It is a virtual table that is generated based on a predefined SQL query.

    Types of Views

    There are primarily two types of views:

    1. Simple View: A view that contains data from a single table.
    2. Complex View: A view that retrieves data from multiple tables or other views.

    Views can also be categorized based on their updatability:

    • Updatable Views: Views that allow modifications to the data they present.
    • Read-only Views: Views that do not permit any modifications.

    Additionally, there are Materialized Views, which store the results of the view query physically, providing faster access to data.

    Benefits of Using Views

    Views offer several advantages, including:

    • Enhanced Security: Views can restrict access to specific rows or columns of a table, providing a layer of security.
    • Improved Performance: By predefining complex queries within views, database systems can respond more quickly to user requests.
    • Complex Query Simplification: Views can simplify complex SQL queries by encapsulating the logic into a view that can be easily referenced.

    View Creation Syntax

    The syntax for creating a view in SQL is as follows:

    CREATE OR REPLACE VIEW view_name (column1, column2, ...)
AS
SELECT column1, column2, ...
FROM tablename
[WHERE condition]
[WITH READ ONLY] [WITH CHECK OPTION]

    When creating a view, you specify the columns to include, the source table, optional conditions for filtering data, and any additional options like read-only access or check constraints.