Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

School of Information and Physical Sciences

SAMPLE EXAMINATION

INFO6002 Database Management 2

Question 2.

Consider the following relational schema:

(empId        INT    PRIMARY KEY,    Employee Id

empName       VARCHAR(100),        -- Employee name

salary        FLOAT,               -- Employee salary

dno           INT,                 -- Department employee works in

managerId     INT REFERENCES Employee(empId)) -- Employee Id of employee's manager

go

Employee table stores information about employees working in ABC company. For each employee, an employee id (empId), employee’s name (empName), salary (salary), department  number of department employee  is working  in  (dno)  and manager’s employee id (managerId) are maintained.

Write the Transact-SQL (T-SQL) statement to do the following:

(a.)     Print   the   names   of  employees  making  the   highest  salary  for  each department

(b.)     The following business rule needs to be enforced:

A manager's salary is always greater than his/her subordinates

The following trigger does not always enforce the business rule (for example, when the salary of manager is equal to the subordinate).

CREATE TRIGGER enforceSalaryBusinessRule

ON Employee

FOR INSERT, UPDATE

AS

BEGIN

DECLARE @salary FLOAT

DECLARE @managerSalary FLOAT

DECLARE @managerId INT

-- Declare the cursor

DECLARE employeeCursor CURSOR

FOR Employee

FROM

FOR READ ONLY

-- Populate the cursor

OPEN employeeCursor

-- Fetch the first rom

FETCH NEXT FROM employeeCursor INTO @managerId, @salary

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT  @managerSalary = salary

FROM Employee

WHERE eid = @managerId

IF @salary > @managerSalary

BEGIN

ROLLBACK TRANSACTION

RAISERROR (N'Manager''s salary is less than employees salary',

9, 1)

END

-- Fetch the next rom

FETCH NEXT FROM employeeCursor INTO @managerId, @salary

END

CLOSE employeeCursor

DEALLOCATE employeeCursor

END

Review the above code for the trigger. Fix the above trigger so that the business rule is always enforced.

Question 3.

Consider the following schedule. Note that T1 and T2 denote transactions: (a.)    Find issues (e.g. conflicts, anomalies, etc.) in the following schedule.

(b.)   What is meant by a cascading abort? What is the cause for a cascading abort? Give an example of a schedule with a cascading abort. How does Strict 2PL protocol avoid cascading aborts?

Question 4.

(a.)     Inmon   (1993)   described   a   Data   Warehouse   as   a   “subject-oriented,

integrated, time-variant,  non-volatile  collection  of data”.  Explain  each  of these terms and why a data warehouse needs these properties.

(b.)     Describe how star, snowflake, and starflake schemas differ.

Question 5

Consider the following table

Account(accNo, acName, balance, openedDate)

accNo is the primary key in the Account table. There are 1,000,000 accounts in the table distributed equally in 10,000 pages on disk. Assume that an unclustered B+ Tree index with search key <accNo, balance> exists with height 2.

The query shown below, which obtains the balance for a given account number, is run very often and is important that it runs as efficiently as possible.

SELECT balance

FROM Account

WHERE accNo = ‘xxxx’

Would using the unclustered B+ tree index described above for this query be beneficial? Explain  your  answer  by  estimate  the  cost  in  terms  of  disk  I/Os  for the  query  plans considered.