Q6: What Is Trigger?

Ans: A trigger is a SQL process that executes when an event (INSERT, DELETE, or UPDATE) happens. The database management system (DBMS) stores and manages triggers. Triggers are used to preserve referential data integrity by systematically altering data. A trigger cannot be invoked or executed; instead, the DBMS fires the trigger due to a data change to the related table. Triggers and stored procedures are similar in that both consist of procedural logic stored at the database level.

On the other hand, stored procedures are not event-driven and are not tied to a single database like triggers are. Stored procedures are run directly by issuing a CALL to the method, whereas triggers are done implicitly. Triggers can also be used to run stored procedures.

Q7: Define primary key, foreign key, candidate key, super key?

Ans:

• Primary key:  The main key is the only key that does not accept duplicate or null values. A primary key can be specified at the column or table level. There can only be one primary key per table.
• Foreign key:  Only values from the referred column can be used as a foreign key. It accepts duplicate and null values. It may be specified at either the column or table level. It can point to a column with a unique/primary key.
• Candidate Key:  A Candidate key is the bare minimum of a super key; no appropriate subset of Candidate key attributes may be a super key.
• Super Key:  A super key is a collection of characteristics of a relation schema on which all of the schema's attributes are somehow reliant. No two rows can have the same super key attribute value.

Q8: What Is Functional Dependency? When Is A Functional Dependency F Said To Be Minimal?

Ans: A functional dependence is indicated by X Y between two sets of attributes X and Y that are subsets of R, and it defines a restriction on the potential tuple that can create a relation state r of R. The constraint is that for every two tuples t1 and t2 in r, if t1[X] = t2[X], then t1[Y] = t2[Y]. This indicates that the value of a tuple's X component influences the value of its Y component uniquely.

• Each dependence in F has a single right-hand side attribute.
• We cannot have a set of dependencies equal to F if we substitute any dependence X A in F with a dependency Y A where Y is a valid subset of X.
• We cannot remove any dependence from F while still having a collection of dependencies identical to F.

Q9: What Do You Mean By Correlated Subquery?

Ans: Subqueries, also known as nested queries, return a collection of rows for usage by the parent query. The subquery can be run once for the parent query or once for each row returned by the parent query, depending on how it is constructed. A correlated subquery executes the subquery for each row of the parent.

A linked subquery may be easily detected if its WHERE clause references the parent subquery columns. Columns from the subquery cannot be referenced in the parent query. The example below shows a non-correlated subquery.

E.g., Select * From CUST Where '10/03/1990' IN (Select ODATE From ORDER Where CUST.CNUM = ORDER.CNUM)

Q10: What Is Oracle Block? Can Two Oracle Blocks Have The Same Address?

Ans: When the database files are first generated, Oracle "formats" them into several Oracle blocks, making it easier for the RDBMS software to handle the files and read data into memory regions. The block size should be a multiple of the block size of the operating system. Regardless of block size, the full block is not accessible for data storage; Oracle takes up some space to handle the block's contents. This block header has a minimum size, but it can increase.

These Oracle blocks are the smallest storage unit. Increasing the Oracle block size can enhance speed, but it should only be done when the database is built first. For each database file, each Oracle block is sequentially numbered, beginning with 1. If two blocks are in different database files, they might have the same block address.