Various data types Data integrity, DDL DML and DCL statements.

Data integrity refers to the accuracy, consistency, and reliability of data in any given context. It is a fundamental aspect of data management and is essential for ensuring that data remains intact and trustworthy throughout its lifecycle. Data integrity is particularly important in various domains, including databases, information systems, and data storage, as well as in compliance with regulations and data security.

1.Accuracy: Data should be free from errors and represent the true and correct values or information. Inaccurate data can lead to flawed decision-making and operational problems.

2. Consistency: Data should remain uniform and coherent across various data sources, databases, or data sets. Inconsistent data can lead to confusion and hinder data analysis.

3. Completeness: Data must be complete, meaning that all expected data elements or fields are present and appropriately filled in. Incomplete data can result in incomplete analysis or reports.

4. Reliability: Data should be reliable and available when needed. Unreliable data or data that is frequently unavailable can disrupt business processes.

5. Security: Data integrity also involves ensuring that data is protected from unauthorized access, tampering, or corruption. Security measures like encryption and access controls are essential to maintaining data integrity.

Methods and practices for ensuring data integrity include:

1.Validation Rule: Implement validation rules and constraints to ensure that data entered into a system adheres to predefined criteria, such as data types, ranges, and formats.

2. Data Validation: Employ data validation techniques to check data for accuracy and consistency. This may involve data cleansing, deduplication, and normalization.

3. Backup and Recovery: Regularly back up data and have robust disaster recovery plans in place to ensure data can be restored in case of corruption or loss.

4. Access Control: Implement access controls and authentication mechanisms to prevent unauthorized access and modification of data.

5. Audit Trails: Maintain audit trails to record and track changes made to data, including who made the changes and when they occurred. This helps identify and rectify unauthorized alterations.

6. Hash Functions: Use cryptographic hash functions to create checksums or hashes of data. Comparing these hashes can reveal any unauthorized changes to the data.

7. Data Verification: Periodically verify the accuracy and consistency of data through reconciliation and verification processes.

8. Data Governance: Establish data governance policies and procedures to enforce data integrity standards across the organization.

Types of data integrity

There are mainly four types of Data Integrity: 

1. Domain Integrity

2. Entity Integrity

3. Referential Integrity

4. User-Defined Integrity

Domain Integrity

Domain, in this context, pertains to the acceptable values within a specified range. It denotes the scope of values that can be utilized and stored in a specific database column. The available data types primarily include integers, text, and dates, among others. It's important that any input entered into a column falls within the permissible range of the associated data type.

example-To store employee salaries in the 'employee_table,' it's possible to implement constraints that permit only integer values. Any input that deviates from this requirement, like text or character-based data, would be declined, and the Database Management System (DBMS) would generate error messages to indicate the violation of the defined constraint. This helps ensure that only valid integer values are accepted for salary entries in the database, maintaining data consistency and accuracy.

Entity Integrity

Every row representing an entity in a database table must have a distinct means of identification. 

This is typically achieved using primary keys, which serve as unique identifiers for each record. 

It's essential to enforce the entity constraint, which specifies that the primary key value must not be NULL.

 This requirement ensures that every record in the database has a specific and non-null primary key value.

When the primary key value is not NULL, it becomes possible to distinguish records from one another, even if all other field values are identical. In essence, primary keys enable the unequivocal identification of each individual record in the database.

Example:-In a customer database with a 'customer_table' containing attributes like age and name, it's crucial to ensure that each customer can be uniquely identified. Sometimes, there might be two customers with identical names and ages, leading to confusion when retrieving data. To address this challenge, primary keys are assigned to each table entry. These primary keys serve the purpose of uniquely identifying each record in the table, even in cases where other attributes like name and age might not be sufficient for differentiation.

Referential Integrity 

Referential Integrity is a crucial concept employed to uphold data consistency when managing two interconnected tables within a database. It involves establishing specific rules within the database structure to ensure that modifications, insertions, and deletions in the database do not compromise data integrity. These constraints for referential integrity dictate that when a foreign key in one table references the primary key of another table, every value of that foreign key in the first table must either be null or correspond to a valid entry in the second table.

Example:-Imagine we have two tables: "table 1" (with columns student_id, name, age, and course_id) and "table 2" (with columns course_id, course_name, and duration). 

In the context of referential integrity, it means that if any "course_id" exists in the "table 1" table, it must also exist in the "table 2" table; otherwise, this scenario is not permitted. 

In other words, the "course_id" in the "table 1" table should either be null or, if a "course_id" is present, it must be a valid entry in the "table 2" table. This way, referential integrity is maintained to ensure the consistency and accuracy of data between these two tables.

User-Defined Integrity

On occasion, domain, referential, and entity integrity alone may fall short in preserving data integrity. In such cases, additional measures are often employed, typically involving the use of triggers and stored procedures. Triggers are essentially sets of statements that automatically execute in response to predefined events, providing a means to enforce more intricate data integrity rules when necessary.

Example:-When a new row containing marks for various subjects of students is added to the student_table, an automatic calculation of the new average is performed and stored.

SQL commands 

• SQL commands are directives employed to interact with a database, facilitating the execution of particular actions, operations, and inquiries on data within the database.

• SQL has the capability to execute a range of functions, such as generating tables, inserting data into tables, deleting tables, altering table structures, and defining user permissions.

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL. 

DDL (Data Definition Language)

DDL, which stands for Data Definition Language, is a subset of SQL (Structured Query Language) used for defining and managing the structure of a database. DDL commands allow you to create, modify, and delete database objects like tables, indexes, and constraints. DDL is primarily concerned with defining the schema or structure of the database. Common DDL commands include: 

1.CREATE: Used to create new database objects such as tables, indexes, and views. 

2.ALTER: Used to modify the structure of an existing database object, like adding or dropping columns from a table. 

3.DROP: Used to delete a database object like a table, index, or view. 

4.TRUNCATE: Used to remove all rows from a table but keep the table structure intact. 

5.COMMENT: Used to add comments or descriptions to database objects for documentation purposes. 

6.CREATE INDEX: Used to create an index on one or more columns of a table for faster data retrieval. 

7.CREATE VIEW: Used to create a virtual table based on the result of a query. 

DDL commands are typically used by database administrators and developers to design and manage the database's structure. They are essential for defining how data should be organized and ensuring data integrity within a database system. 

DML(Data Manipulation Language) 

DML, which stands for Data Manipulation Language, is a subset of SQL (Structured Query Language) that is used for managing and manipulating data stored in a database. DML commands allow you to perform operations on the data itself, such as inserting, updating, and deleting records in database tables. The primary DML commands are: 

1.INSERT: Used to add new rows (records) of data into a database table. 

2.UPDATE: Used to modify existing data in a database table. 

3.DELETE: Used to remove rows (records) from a database table based on specified criteria. 

DML commands are essential for maintaining and changing the data within a database. These commands enable users and applications to insert new data, update existing data, and remove unwanted data, ensuring that the database remains accurate and up-to-date. 

DCL (Data Control Language) 

DCL, which stands for Data Control Language, is a subset of SQL (Structured Query Language) used for controlling and managing permissions and access rights within a database management system (DBMS). DCL commands are essential for ensuring data security and access control by specifying which users or roles have the authority to perform certain actions on database objects. The two primary DCL commands are: 

1.GRANT: The GRANT command is used to give specific privileges or permissions to users or roles. These privileges can include the ability to perform actions like SELECT, INSERT, UPDATE, DELETE, or even the ability to create or modify database objects. 

For example, to grant SELECT permission on a table to a user: 

2.REVOKE: The REVOKE command is used to remove previously granted privileges from users or roles. 

For example, to revoke SELECT permission on a table from a user: 

DCL commands play a crucial role in controlling who can access and manipulate data within a database, ensuring data integrity and security. Database administrators use these commands to define and enforce access policies, restrict unauthorized access, and manage the permissions of users and roles in the database system. Properly configured DCL commands help protect sensitive data and maintain the integrity of the database. 

TCL (Transaction Control Language) 

TCL, which stands for Transaction Control Language, is a subset of SQL (Structured Query Language) used for managing database transactions. Transactions in a database are sequences of one or more SQL statements that are treated as a single, indivisible unit of work. TCL commands are used to control the beginning and ending of transactions, ensuring data consistency and integrity. The primary TCL commands are: 

1.COMMIT: The COMMIT command is used to permanently save the changes made during the current transaction. Once a COMMIT is issued, all changes are made permanent and cannot be rolled back. 

2.ROLLBACK: The ROLLBACK command is used to undo changes made during the current transaction and restore the database to its previous state. It cancels all the changes made since the last COMMIT or SAVEPOINT. 

3.SAVEPOINT: The SAVEPOINT command is used to set a point within a transaction to which you can later roll back if needed. It allows you to create intermediate savepoints within a transaction. 

TCL commands are critical for maintaining data consistency and ensuring that a series of related SQL statements are executed as a single, atomic operation. Transactions help protect data integrity and ensure that the database remains in a consistent state even in the presence of errors or interruptions. The use of COMMIT and ROLLBACK commands is essential for managing the success or failure of database operations within a transaction. 

DQL(Data Query Language) 

Data Query Language (DQL) is a subset of SQL (Structured Query Language) specifically designed for retrieving and querying data from a relational database. DQL commands are used to interact with the data stored within database tables, allowing users to retrieve, filter, and manipulate data to extract meaningful information. The primary DQL command is:

SELECT: The SELECT command is the core of DQL and is used to retrieve data from one or more tables in a database. It enables you to specify the columns you want to retrieve, apply filtering conditions, and sort the result set.

In addition to SELECT, DQL may involve using clauses like WHERE to filter data, JOIN to combine data from multiple tables, GROUP BY for grouping data, HAVING for filtering grouped data, and more.

DQL is fundamental for extracting and presenting data in a structured and meaningful way, making it one of the most commonly used components of SQL, especially for reporting and analysis purposes. It allows users to interact with and retrieve data from a database, helping them make informed decisions based on the information stored in the database.