only paraphrase the third question

Consider the following relational database schema. Suppose that all the relations were created by (and hence are owned by) user X, who wants to grant the following privileges to user accounts A, B, C, and D:

EMPLOYEE

Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno

DEPARTMENT

Dname, Dnumber, Mgr_ssn, Mgr_start_date

DEPT_LOCATIONS

Dnumber, Dlocation

PROJECT

Pname, Pnumber, Plocation, Dnum

WORKS_ON

Essn, Pno, Hours

DEPENDENT

Essn, Dependent_name, Sex, Bdate, Relationship

Write SQL statements to for the following privileges. Use views were appropriate.

(a) Account A can modify DEPARTMENT and PROJECT relations.

GRANT UPDATE ON DEPARTMENT, PROJECT TO A

(b) Account B can retrieve only DNAME, MGR_SSN, DNUMBER and DLOCATION attributes of DEPARTMENT and DEPT_LOCATION.

CREATE VIEW DEPARTMENT_INFO as

SELECT DNAME,MGR_SSN,DNUMBER,DLOCATION FROM DEPARTMENT, DEPT_LOCATIONS WHERE DEPARTMENT.DNUMBER= DEPT_LOCATIONS. Dnumber;

Select d.dname,d.mgr_ssn,d.dnumber,l.dlocation from department d,dept_location l where

d.dnumber=l.dnumber

GRANT SELECT ON DEPARTMENT_INFO TO USER B

(c) Account C can insert or update on WORKS_ON relation and can grant any of these privileges to other users.

GRANT INSERT, UPDATE ON WORKS_ON TO C WITH GRANT OPTIONS;

(d) Account D can retrieve any attribute of DEPENDENT and WORKS_ON.

GRANT SELECT ON DEPENDENT, WORKS_ON TO D;

1. Consider the Student_Course table for the IT344 with the following fields

a. Std_ID

b. Course_ID

c. Std_Course_Mark

And the Course Table with the following fields

a. Course_ID

b. Course_Name

c. Maximum_Mark

d. Minimum_Mark

When the new row in the Student_Course table is added, you need to update the Maximum_Mark and the Minimum_Mark.

Write the trigger called ‘Max_Min’ allowing to update the Maximum_Mark and the Minimum_Mark.

CREATE TRIGGER Max_Min on Student_Course

FOR INSERT

as declare @Course_ID int

begin

select @Course_ID=i.Course_ID from Student_Course i;

update Course set

Maximum_Mark=(select max(Std_Course_Mark) from Student_Course where Course_ID=@Course_ID),

Minimum_Mark=((select min(Std_Course_Mark) from Student_Course where Course_ID=@Course_ID))

where Course_ID=@Course_ID;

end

Question Three

2 Marks

Learning Outcome(s):

Be able to analyze an algorithm for query processing and to optimize it

(a) What are the advantages of fragmentation in distributed database management system? Chapter 25

Fragmentation transparency:

Allows to fragment a relation horizontally (create a subset of tuples of a relation) or vertically (create a subset of columns of a relation).

Horizontal fragmentation (Rows one to many)

It is a horizontal subset of a relation which contain those of tuples which satisfy selection conditions.

Consider the Employee relation with selection condition (DNO = 5). All tuples satisfy this condition will create a subset which will be a horizontal fragment of Employee relation.

A selection condition may be composed of several conditions connected by AND or OR.

Derived horizontal fragmentation: It is the partitioning of a primary relation to other secondary relations which are related with Foreign keys.

Vertical fragmentation (Columns one to one)

It is a subset of a relation which is created by a subset of columns. Thus a vertical fragment of a relation will contain values of selected columns. There is no selection condition used in vertical fragmentation.

Consider the Employee relation. A vertical fragment of can be created by keeping the values of Name, Bdate, Sex, and Address.

Because there is no condition for creating a vertical fragment, each fragment must include the primary key attribute of the parent relation Employee. In this way all vertical fragments of a relation are connected.

Another Solution

Here are the advantages of Fragmentation: -> Usage -> Efficiency -> Parallelism -> Security

=> Usage : In real time most of the operations done with views rather than entire relations. So, the data distribution seems appropriate to work with subsets of relation as unit of distribution.

=> Efficiency : In this data could be stored which is most frequently used. And as well it doesn't store the data which is not usinging.

=> Parallelism : In this fragmentation a transaction can be divided into serveral sub queries that operate on fragments. Thhis can increase the rate of concurrency or parallelism in system. So the transaction execute in a safe mode parallelly.

=> Security : The data which is not using by the applications will not stored, So the data couldn't be available to unauthorized users.

(b) What is concurrency control? Describe problems with concurrent execution of transactions in Distributed database, which are not present in centralized databases? Chapter 21 and Chapter 25

concurrency control: In a multiprogramming when multiple transactions can be executed simultaneously, one transaction results may conflict other so that inorder to address this issue use concurrencycontrol which ensures atomicity, isolation, and serializability of concurrent transactions.

Problems Page 748

The Lost Update Problem

This occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database item incorrect.

The Temporary Update (or Dirty Read) Problem

This occurs when one transaction updates a database item and then the transaction fails for some reason

The updated item is accessed by another transaction before it is changed back to its original value.

The Incorrect Summary Problem

If one transaction is calculating an aggregate summary function on a number of records while other transactions are updating some of these records, the aggregate function may calculate some values before they are updated and others after they are updated.

concurrency control which are not present in centralized databases. Some of them are listed below. Page 909 from the book

Dealing with multiple copies of data items

Failure of individual sites

Communication link failure

Distributed commit

Distributed deadlock