Question

11. A business rule says that an employee cannot earn more than his/her manager.
Assume that each employee row has a foreign key which refers to a manger row.
a. Can this business rule be implemented using a fixed format constraint?
b. Can this business rule be implemented using a trigger?
12. Triggers should be created sparingly. Why?
13. Should you use a trigger to check the uniqueness of a primary key?

14. What is a Distributed Database system?
15. What advantages does a Distributed Database have over a Centralized Database?
16. Why do some users require data from multiple sites in a Distributed Database?

17. What is Horizontal Fragmentation within a DDBMS?

Note: Questions are based on DBMS (Distributed database and triggers)

Answer 1

Ans a is - This rule cannot be implemented wih the help of fixed format constraint.

Explanation - There are 5 constraints are available Oracle. promary key, unique key, not null, check and foreign key referancial integrity. with this we canot not stop allowoing a new record while verifying the manager's salary of that employee.

Ans b is - yes we can enter record based on the condition.

Explanation:

we need to create a trigger insert before type like below.

CREATE OR REPLACE TRIGGER rules_employee_salary_lessthan_manager
BEFORE INSERT
ON employee
FOR EACH ROW

DECLARE
v_sal number;

BEGIN

SELECT sal into v_sal from FROM employees a INNER JOIN employees b
ON A.MANAGER_ID = B.EMPLOYEE_ID ORDER BY A.EMPLOYEE_ID ;

IF(:new.sal > v_sal) THEN
RAISE_APPLICATION_ERROR(-20000,'Employee sal canot be greater than manager');
END IF;
END;
/

Ans 12 -

Process of handling flag is like below:

All handlers are tested and matching handlers queued before invoking the first handler. thats why flags set by a handler will not trigger new matching handlers until after all of the current set of matching handlers are done.
This allows you to ensure some ordering of otherwise non-determined handler invocation by chaining f

But when trigger comes to picture it behave like below:

When a trigger is registered, then as soon as the flag given by when is set, the other flag is set or cleared at the same time.
Thus, there is no chance that another handler will run in between. Triggers are implicit, So trigger should be used sparingly.

Ans of 13:

Not needed to check the uniqueness of Promary key with the help of trigger. Primary key properties is not to allow duplicate and NULL values to the column where the primary key constraints assigned.

Ans of 14:

A distributed database is a collection of multiple interconnected databases, which are spread physically across various locations that communicate via a database link (db link).

Advantages of Distributed Databases
Modular Development − If the system needs to be expanded to new locations or new units, in centralized database systems, the action requires substantial efforts and disruption in the existing functioning. However, in distributed databases, the work simply requires adding new computers and local data to the new site and finally connecting them to the distributed system, with no interruption in current functions.
More Reliable − when one data base is down then only that component will fails but other functionality available in other database will work properly.
Better Response − If data is distributed in an efficient manner, then user requests can be met from local data itself, thus providing faster response.
Lower Communication Cost − In distributed database systems, if data is located locally where it is mostly used, then the communication costs for data manipulation can be minimized.

Ans of 15:

Following are the advantages of distributed databases over centralized databases.

Modular Development − If the system needs to be expanded to new locations or new units, in centralized database systems, the action requires substantial efforts and disruption in the existing functioning. However, in distributed databases, the work simply requires adding new computers and local data to the new site and finally connecting them to the distributed system, with no interruption in current functions.
More Reliable − In case of database failures, the total system of centralized databases comes to a halt. However, in distributed systems, when a component fails, the functioning of the system continues may be at a reduced performance. Hence DDBMS is more reliable.
Better Response − If data is distributed in an efficient manner, then user requests can be met from local data itself, thus providing faster response. On the other hand, in centralized systems, all queries have to pass through the central computer for processing, which increases the response time.
Lower Communication Cost − In distributed database systems, if data is located locally where it is mostly used, then the communication costs for data manipulation can be minimized. This is not feasible in centralized systems.

Ans of 16:

To generate report or to generate statistics for higher management or decession making team in the organization, report team/user need data from different site of database.

Ans of 17:

It refers to the division of a relation into subsets (fragments) of tuples (rows). Each fragment is stored at a different node, and each fragment has unique rows.
However, the unique rows all have the same attributes (columns). In short, each fragment represents the equivalent of a SELECT statement, with the WHERE clause on a single attribute.
Types of horizontal fragmentation :
i. Primary Horizontal Fragmentation
It is the fragmentation of primary relation
e.g. Employee table is fragmented for Department No.
ii. Derived Horizontal Fragmentation