DBMS

aigle-levant · October 3, 2024

Obscura

Just notes. Nothing to see here

DBMS

Unit 3

SQL

Dynamic SQL : Dynamic SQL refers to SQL queries that are constructed and executed at runtime rather than being predefined.

Embedded SQL : Embedded SQL refers to the integration of SQL queries directly within the code of a host programming language, such as C, C++, Java, or Python.

Features - Advanced SQL

Go and read Subqueries, Joins, Views, Triggers, Dynamic SQL, then see this :

Stored Procedures and Functions : Stored Procedures are SQL scripts that can be saved and reused. They allow you to execute complex logic inside the database and improve performance by reducing the number of database calls.

Indexing : An index improves the speed of data retrieval operations in large databases. Indexes are created on columns that are frequently used in queries, WHERE clauses, or join conditions.

Transactions : A transaction is a sequence of SQL operations that are treated as a single unit of work. If any operation within a transaction fails, the entire transaction is rolled back, ensuring data consistency.

Distributed Databases : In a distributed database, data is stored across multiple physical locations (servers, data centers, or even countries). This provides benefits like improved performance, fault tolerance, etc.

Partitioning : Partitioning is a database optimization technique where a large table is divided into smaller, more manageable pieces.

Issues - Fundamental SQL

While SQL (Structured Query Language) is a powerful and widely used language for managing relational databases, users often encounter several common issues or challenges when working with SQL. Here are some fundamental SQL issues

Syntax Errors : SQL syntax errors occur when the SQL commands are not properly formatted or structured. This could include missing keywords, etc.

Data Type Mismatches : Attempting to insert or compare values with incompatible data types can lead to errors. Each column in a table has a defined data type, and operations must conform to these types.

NULL Handling : Understanding and handling NULL values can be challenging. NULL represents an unknown value and behaves differently than other data types.

Security Issues : Poorly designed SQL queries can lead to SQL injection attacks, where attackers can manipulate queries to gain unauthorized access to the database

Data Manipulation

Database Users

In DBMS, creating and managing users involves handling database access, permissions, and roles for users.

  • A database user is an account that can connect to the database and perform operations such as querying data, updating records, etc.
  • Roles are predefined sets of permissions that can be assigned to users.
  • Permissions specify the actions that users can perform on database objects
Creating user
create user `user`@`host` identified by `password`;
Modify user

We can modify a user by changing their password.

alter user `user`@`host` identified by `newPassword`;

We can also change the host from which the user is allowed to connect.

update mysql.user set host='%' where user='user' and host='localhost';
Dropping user
drop user `user`@`host`;
Granting Permissions

After creating a user, you need to grant privileges so that the user can perform certain operations.

grant privilege_type on dbName.tableName to `user`@`host`;
Revoking Permissions

We can revoke permissions that were previously granted.

revoke privilege_type on dbName.tableName to `user`@`host`;

Views

A view is a virtual table based on the result of a query. It is not stored physically and is fetched from the existing table.

  • They restrict access to specific rows / columns.
  • Used for simplifying complex queries via encapsulation.
  • Data is retrieved in real-time when view is queried.
Creating view
create view viewName as select col1, col2 from tableName where condition;

Once it is created, query it like a table :

select * from viewName;
Modify view

CREATE OR REPLACE VIEW statement is used to create a new view or replace it with another view.

  • If the created view exists, it is replaced with the new query definition instantly.
  • Allows modification of view’s structure or query.
create or replace view viewName as select col1, col2 from tableName where condition;
Deleting view

DROP VIEW statement is used to remove an existing view from the database. Once a view is dropped, it is permanently deleted and cannot be used.

drop view viewName;
Renaming view

To rename an existing view in SQL, you generally cannot directly rename it. Instead, you have to:

  • Drop the old view.
  • Create a new view with the desired name.
drop view viewName;
create view newView as select col1, col2 from tableName where condition;

Joins

Joins combines data from 2 or more tables based on related columns between them.

Self-join

Self join means the table is joined with its copy.

  • Used to compare rows of the same table or to retrieve related data from the same dataset.
  • Commonly used when data has a parent-child relationship.
select col1, col2 from tableOne join tableTwo on tableOne.col3 = tableTwo.col4;
Equi-join

Equi join combines rows from 2 or more tables based on a common column [that has equal values]. It uses = [equality operator] to compare tables’ columns.

Types

Inner Join : Returns rows from the tables that have matching values as filtered by the given condition.

select columns from tableOne inner join tableTwo on tableOne.column = tableTwo.column;

Left Join [Left Outer Join] : Returns all rows from left table and matched rows from right table. If there’s no match, right side becomes NULL.

select columns from tableOne left join tableTwo on tableOne.column = tableTwo.column;

Right Join [Right Outer Join] : Returns all rows from right table and matched rows from left table. If there’s no match, left side becomes NULL.

select columns from tableOne right join tableTwo on tableOne.column = tableTwo.column;

Full Join [Full Outer Join] : Returns all rows when either left or right table has a match. If not, it returns NULL for missing matches on either side.

select columns from tableOne full outer join tableTwo on tableOne.column = tableTwo.column;

Cross Join : Returns Cartesian product of 2 tables; combines all rows from first table with all rows from second table.

select columns from tableOne cross join tableTwo;

Sub-queries

Subquery [inner query] is a query nested inside another query [outer query].

  • Used to return data to be used by outer query.
  • Often used in WHERE, FROM, SELECT clauses.
Types

Single Row Subquery : Returns a single row of data. Usually combined with comparison operators.

select col1, col2 from tableOne where column = (select column from tableTwo where condition);

Multiple Row Subquery : Returns multiple rows. Usually used with IN, ANY, ALL.

select col1, col2 from tableOne where column in (select column from tableTwo where condition);

Correlated Subquery : Refers to columns in the outer query; is executed atleast once for each row processed by outer query.

select col1, col2 from tableOne where col1 = (select col2 from tableTwo where tableOne.col3 = tableTwo.col4);

Non-Correlated Subquery : Independent; can be run separately from outer query.

select col1, col2 from tableOne where column = (select column from tableTwo where condition);

Scalar Subquery : Returns a single value [a scalar]; often used with SELECT clause.

select col1, (select column from tableTwo where condition) as alias from tableOne;

Clauses

UNION : Combines the result sets of 2 or more SELECT statements into a single result set; eliminates duplicate rows from final output.

select col1, col2 from tableOne union select col1, col2 from tableTwo;

INTERSECT : Finds intersection of 2 datasets by returning only the rows that are common to both result sets of 2 SELECT statements.

select col1, col2 from tableOne intersect select col1, col2 from tableTwo;

MINUS [EXCEPT] : Finds difference between 2 datasets by returning rows from first SELECT statement that aren’t present in the second SELECT statement.

select col1, col2 from tableOne except select col1, col2 from tableTwo;

Triggers

A trigger is a database object that’s executed when certain events occur in the database.

  • Automatic Execution: Triggers are automatically invoked by database actions such as INSERT, UPDATE, or DELETE.
  • Event-Driven: Triggers are tied to events (e.g., before or after a data change occurs).
  • Associated with Tables or Views: Triggers are typically bound to a table or a view.
  • Action Types: Triggers can act before or after the triggering event (e.g., before inserting a record).
CREATE TRIGGER Total_sal1
AFTER INSERT ON EMPLOYEE
FOR EACH ROW
WHEN ( NEW.Dno IS NOT NULL )
UPDATE DEPARTMENT
SET Total_sal = Total_sal + NEW.Salary
WHERE Dno = NEW.Dno;
Types

Row-level triggers : Execute once for each row affected by the triggering event; Commonly used when you need to track or modify data at the level of each row.

Example: For an UPDATE query that affects 10 rows, the trigger will fire 10 times, once for each row.

Statement-level trigger : These triggers execute once for the entire SQL statement, regardless of how many rows are affected; Used when the logic applies to the entire operation, not individual rows.

Example: For an UPDATE query that affects 10 rows, the trigger will fire only once, not 10 times.

Before trigger : These triggers run before the triggering event occurs.

After Triggers : These triggers run after the triggering event has occurred.

Unit 4

Functional Dependency [FD]

A functional dependency is a relationship between two sets of attributes in a table such that the value of one attribute uniquely determines the value of another attribute.

Notation: FD: 𝑋→𝑌 Here, X is Determinant and Y is Dependent

Armstrong’s Axioms

Functional dependencies obey several rules known as Armstrong’s Axioms.

Reflexivity: If Y is a subset of X, then X → Y.

Augmentation: If X → Y, then X,Z → Y,Z for any attribute Z.

Transitivity: If X → Y and Y → Z, then X → Z.

Union: If X → Y and X → Z, then X → Y, Z.

Decomposition/Splitting: If X → Y,Z , then X → Y and X → Z.

Pseudo-transitivity: If X → Y and Y,Z → W, then X, Z → W.

Composition: If X → Y and Z → W, then X,Z → Y,W.