"Topic: Keys and Its Types, Constraints and Integrity Constraint and its Types, View and Its Types"

How is the data stored in relational database?

In a relational database, data is stored in structured tables with predefined columns and rows. The tables are organized based on relationships between them. Here's how the data is stored:

1. #Tables: Data is stored in tables, which represent entities or concepts. Each table has a name and consists of columns and rows.
2. #Columns: Columns represent the attributes or characteristics of the data. Each column has a name and a specific data type that defines the kind of data it can store, such as text, numbers, dates, etc.
3. #Rows: Rows, also known as records or tuples, represent individual instances or entries in the table. Each row contains data values corresponding to the columns.
4. #Key: Minimum set of columns/attributes to uniquely identify a row/tuple.
5. #PrimaryKey: Each table usually has a primary key column that uniquely identifies each row in the table. It ensures that each row has a unique identifier and helps establish relationships between tables.
6. #Relationships: Relationships define the connections or associations between tables. They are established through keys, such as primary keys and foreign keys. Common types of relationships include one-to-one, one-to-many, and many-to-many.
7. #Normalization: Relational databases follow normalization rules to eliminate data redundancy and ensure data integrity. Normalization involves breaking down data into multiple tables and organizing them based on their relationships.
8. #SQL: Structured Query Language (SQL) is used to interact with the relational database. SQL provides commands for creating tables, inserting, updating, and retrieving data, as well as defining relationships and performing various operations on the data.

By organizing data into tables and establishing relationships, a relational database allows for efficient storage, retrieval, and manipulation of data. It provides a flexible and scalable way to manage large amounts of structured data.

Types of Keys:

In the context of relational databases, a #key is an attribute/field/column or a combination of attributes/fields/columns that uniquely identifies a tuple/row/record within a table. There are several types of keys used to establish relationships and ensure data integrity. Here are the commonly used types of keys:

1. #Simplekey: Key with only one attribute.
2. #CompositeKey_OR_CompoundKey: A composite key is a primary key composed of two or more columns. It is used when a single column cannot uniquely identify a record, but the combination of multiple columns can.
3. #CandidateKey: A candidate key is a set of one or more columns that can uniquely identify a record in a table. Minimal super keys are called "Candidate keys". It is a potential key that can be chosen as a primary key.
4. #PrimaryKey: One of the Candidate keys that the DB designer or administrator chooses to maintain uniqueness. A primary key is a unique identifier for each record in a table. It uniquely identifies each row and ensures that there are no duplicate values and no null values. Each table can have only one primary key.
5. #AlternateKey: An alternate key is a candidate key that is not selected as the primary key. In a table, if there are multiple candidate keys, one is chosen as the primary key, and the remaining candidate keys become alternate keys. They are unique identifiers for the tuples in a table but not the primary means of identification.
6. #SuperKey: A super key is a set of one or more attributes that can uniquely identify a tuple within a table. It may contain more attributes than necessary to uniquely identify a tuple. In other words, a super key is a superset of a candidate key. It can include additional attributes that are not required for uniqueness. It is the superset which contains all possible combinations of super keys whatever we create.
7. #ForeignKey: A foreign key is a field or a set of fields in a table that refers to the primary key of another table. It establishes a relationship between two tables and helps maintain data integrity by enforcing referential integrity constraints.
8. #Self_referential_foreignkey: A self-referential foreign key is a foreign key in a table that references the primary key of the same table. It establishes a relationship between records within the same table.
9. #UniqueKey: A unique key ensures that each value in a column or a set of columns is unique within a table. Unlike a primary key, a unique key can allow null values and multiple unique key constraints can exist in a table.
10. #SurrogateKey: A surrogate key is an artificially generated unique identifier assigned to a record in a table. It is typically used as a substitute for natural keys when they are either not available or not suitable for primary key purposes.

These key types play a crucial role in establishing relationships between tables, enforcing data integrity rules, and facilitating efficient data retrieval and manipulation in a relational database system.

What is a schema with respect to relational database?

In the context of a relational database, a schema refers to the structure or blueprint that defines how the database is organized and the logical framework for storing and accessing data.

In simple terms, a relational schema: Table structure + constraints.

It provides a logical view of the database and includes the following elements:

1. #Tables: A schema defines the tables or relations in the database. Each table represents an entity or concept and has a unique name. Tables are used to store data in rows and columns.
2. #Columns: The schema specifies the columns or attributes within each table. Columns define the type of data that can be stored, such as text, numbers, dates, etc. They also have names that describe the information they hold.
3. #Relationships: The schema defines the relationships between tables. Relationships establish connections or associations between related data in different tables using keys, such as primary keys and foreign keys. Relationships ensure data integrity and enable data retrieval across multiple tables.
4. #Constraints: Schema includes constraints that enforce rules and restrictions on the data stored in the tables. Common constraints include primary key constraints, foreign key constraints, unique constraints, and check constraints. Constraints ensure data consistency and integrity.
5. #Views: A schema may include views, which are virtual tables that are derived from one or more tables. Views provide a way to present a customized or filtered view of the data without physically duplicating the data.
6. #SecurityPermissions: The schema may include security permissions that control access to the database objects. Permissions determine who can view, modify, or delete data in the tables and perform other operations on the database.
7. #Indexes: Indexes are used to improve the performance of data retrieval operations. They allow faster searching and sorting of data by creating a separate data structure that points to the actual data in the tables.

Overall, a schema defines the structure, relationships, constraints, and security of a relational database that determine how data is stored, accessed, and maintained. It serves as a blueprint for designing and organizing the database to ensure efficient data management and data integrity.

Integrity Constraints and its types:

#Integrity_Constraints:

Integrity constraints are rules or conditions that are enforced on a database to ensure the accuracy, consistency, and reliability of data. They can be used to enforce business rules or to ensure that data is entered correctly. Integrity constraints can also be used to enforce relationships between tables. They help maintain data integrity and prevent actions that could violate the database's integrity.

1) #Domain_Constraint: Domain constraint define the allowable values for a column in a table. They specify the data type and range of values that can be stored in a column. For example, a domain constraint on a "age" column may specify that the age value must be a positive integer between 18 and 100.

2) #Entity_Integrity_Constraint: Entity integrity constraint is used to ensure that the primary key cannot be null. A primary key is used to identify individual records in a table and if the primary key has a null value, then we can't identify those records. There can be null values anywhere in the table except the primary key column.

3) #User_Defined_Integrity_Constraint: User-defined integrity constraints allow you to define custom rules and conditions to enforce data integrity based on your specific requirements. These constraints are not predefined and can be created according to the business rules and logic of the database application.

4) #Referential_integrity_Constraint: Referential integrity is a concept in database management that ensures the consistency and accuracy of relationships between tables through the use of foreign keys. It ensures that the data in the referencing table (child table) properly refers to the data in the referenced table (parent table).

• The referenced table = The table that holds the primary key. It contains the unique values that are referenced by the foreign key in the referencing table.
• The referencing table = The table that contains the foreign key column(s) that reference the primary key column(s) of the referenced table. It establishes the relationship between the tables by storing values that correspond to the primary key values in the referenced table.

Changes to Referenced Table:

Insert: If we insert a record/tuple into the referenced table, nothing needs to be changed into referencing table.

Delete: If we want to delete a record in the referenced table, we can make changes in the referencing table by choosing the best suitable one among 3 options and they are;

• a) On delete no action: when we delete something, if you don't want to make any changes/no action is done in referencing table,if you let it be same we have to choose this option. However, here referential integrity(means relationship between 2 tables) is broken. This is dangerous.
• b) On delete cascade: whenever we delete a record, we have to make cascade changes that means we need to delete the related record in all the tables. This is very dangerous too, we have to avoid choosing this option unless we are aware of what we are doing because this can result in loss of data.
• c) On delete set null: This is safest option to choose and most used one. what it does is if we delete one record/row/tuple in referenced table, instead of deleting whole record, we can change the value in the foreign key as null.

Example: if we want to remove the record of Cust_Id i.e "21" in referenced table, we can replace all the records of foreign key in the referencing table, as "Null", where Cust_ID "21"is present.?

But imagine, if that foreign key is a part of primary key, we cannot set it to null, as it violates the entity integrity constraints that a primary key cannot be null.

Modify/Edit: what if we want to modify the record in the referenced table, again we have 3 options,

• a) No action: that means, there will be no change in referencing table.
• b) Cascade: as we know, we can change all the records in referencing table, wherever we modified in the referenced table. This is the safest option.
• c) set null: We can set as "Null"

Changes to Referencing Table:

Insert: Whenever we insert into referencing table (child table), There is a reference check happens that means, it checks whether the inserted record is exists in the referenced table (parent table), if not it will raise an error.

Deletion: Deletion in the referencing table do nothing to referenced table, because it cannot point out to any record of parent table.

Modify/edit: if we modify a record in foreign key in child/referencing table, it will check for any violations of foreign key constraints (it has to match the values in the primary key), if there is any violation it raises an error.

5) #Key_Constraint: Keys are the set of entities that is used to identify an entity within its entity set uniquely. An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table.

There are a number of key constraints in SQL that ensure that an entity or record is uniquely or differently identified in the database.

Here, some of the key constraints in SQL are:

Types of constraints in SQL or key constraints:

In SQL, constraints are used to define rules and restrictions on the data that can be inserted, updated, or deleted in a table. They ensure data integrity and enforce certain conditions on the data stored in the database. Here are some common types of constraints:

1. #PrimaryKey_Constraint: Ensures that each row in a table has a unique identifier. It enforces the uniqueness and non-nullability of the specified column(s) in the table.
2. #ForeignKey_Constraint:?A key used to link two tables together. Establishes a relationship between two tables based on a common column(s). It ensures that values in the foreign key column(s) match values in the primary key column(s) of the referenced table.
3. #Unique_Constraint: Ensures that the values in the specified column(s) are unique and not duplicated within the table.
4. #Check_Constraint: Defines a condition that must be satisfied for the data in a column(s). It allows you to specify custom rules or expressions to restrict the allowed values.
5. #NotNull_Constraint: Specifies that a column(s) cannot have a null value. It ensures that the specified column(s) must always contain a valid value.
6. #Default_Constraint: Sets a default value for a column(s) when no value is explicitly provided during an insert operation.

Constraints play a crucial role in maintaining data integrity, enforcing relationships between tables, and preventing invalid or inconsistent data from being inserted into the database. They provide a way to define and enforce business rules and ensure the accuracy and reliability of the stored data.

Views:

• A View is a "Virtual table" as it does not physically store any data. It is a logical representation of data that are built by choosing fields from one or more tables that are present in single or multiple databases.
• It is essentially a stored query that can be treated like a table, allowing us to retrieve and manipulate data from multiple tables or simplify complex queries.
• Views provide an abstraction layer that hides the complexity of underlying table structures and allows us to work with a customized and simplified representation of the data.
• Creating a view in SQL involves defining a query and giving it a name. The query can include joins, filters, calculations, and any other operations we would perform on a regular table. Once a view is created, we can use it just like a table in our SQL statements, including selecting, inserting, updating, or deleting data.

The benefits of using views in SQL include:

1. Simplifying complex queries: Views allow us to encapsulate complex logic into a single, reusable query, making it easier to write and understand queries.
2. Data security and access control: Views can be used to restrict access to certain columns or rows of data, providing a security layer on top of the underlying tables.
3. Data abstraction and consistency: Views can present a consolidated and consistent view of data from multiple tables, abstracting away the underlying table structure and providing a simplified view for users.
4. Performance optimization: Views can be used to pre-compute and store commonly used query results, improving query performance by reducing the need for repetitive and complex calculations.

This virtual nature of views allows for data abstraction, simplification, and enhanced security and control over the underlying data.

There are several types of views in SQL, including:

1. #SimpleView: A simple view is based on a single table or another view. It contains the columns and rows selected from the underlying table or view. Simple views can be used for querying and manipulating data.
2. #ComplexView: A complex view is based on multiple tables or views. It involves joins and can include calculations, aggregations, and other complex operations. Complex views provide a consolidated and customized view of data from multiple sources.
3. #MaterializedView: A materialized view is a precomputed view that stores the result of a query in a physical form. Unlike regular views, materialized views are stored on disk as tables and are updated periodically or on-demand. They can improve query performance by providing faster access to precomputed results.
4. #UpdatableView: An updatable view allows modifications (insert, update, delete) to the underlying tables through the view. It provides a convenient way to modify data without directly accessing the base tables. The updatable view must adhere to certain criteria, such as having a single table in the FROM clause and meeting specific conditions.
5. #IndexedView: An indexed view is a view that has an associated index. The index improves query performance by storing the view's data in a structured manner. Indexed views are especially useful for complex queries involving aggregations or joins, as they can speed up query execution.
6. #RecursiveView: A recursive view, also known as a hierarchical view, is used to query hierarchical data structures such as tree-like structures or parent-child relationships. Recursive views utilize recursive common table expressions (CTEs) to traverse the hierarchy and retrieve data at different levels.

These are some common types of views in SQL, each serving different purposes and offering specific functionalities for data retrieval, manipulation, and performance optimization.

Tables Vs Views:

Tables and views are two different database objects in SQL with distinct characteristics and purposes:

Tables:

• Tables are the fundamental building blocks of a database.
• They store structured data in rows and columns.
• Tables have physical storage on disk and are used to permanently store data.
• Tables define the structure, data types, and constraints of the data they hold.
• Tables are typically used for data storage, retrieval, modification, and deletion.
• Tables can be queried directly using SQL statements.

Views:

• Views are virtual tables derived from one or more tables or other views.
• Views do not store data physically; they are defined by queries that retrieve data from underlying tables.
• Views provide a way to present a subset of data or a customized perspective of data from one or more tables.
• Views can join tables, filter rows, compute derived columns, and perform other data transformations.
• Views can simplify complex queries, enhance data security by restricting access to specific columns, and provide a level of abstraction for users.
• Views are typically used to simplify query complexity, provide data security, and create logical representations of data.

In summary, tables are physical storage objects used for permanent data storage, while views are virtual representations of data derived from one or more tables, providing a customizable and simplified perspective of the data.