Quiz

• Please answer all questions

(1) Consider the relational database schema above. 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, D, and E:

(a) Account A can retrieve or modify any relation except orderdetails, orders and payments and can grant any of these privileges to other users. 

(b) Account B can retrieve all the attributes of customers except for creditLimit

(c) Account C can retrieve or modify offices but can only retrieve the employeeNumber, lastName and firstName attributes of employees and the productCode, productName and productDescription attributes of products.

(d) Account D can retrieve any attribute of orderdetails or orders  and can modify payments.

(e) Account E can retrieve any attribute of employees but only for employees tuples that have officeCode = 3.

Write SQL statements to grant these privileges. Use views were appropriate.

(2) What are the advantages and disadvantages of DDBMS?

(3) What is the difference between data mining and data warehousing?

(4)  Draw the serializability (precedence) graphs for each schedule below and state whether each schedule is serializable or not

      i.        SA: r1(x); r2(z); r1(x); r3(x); r3(y); w1(x); w3(y); r2(y); w2(z); w2(y) 

    ii.        SB: r1(x); r2(z); r3(x); r1(z); r2(y); r3(y); w1(x); w2(z); w3(y); w2(y) 

   iii.        SC : r1(X); w1(X); r1(Y); r2 (X); w2(X); w1(Y);

   iv.        SD: r1(X); w1(X); r2(X); r 1 (Y); w1 (Y); w2 (X); 

(6) Map the EER diagram in Figure below into relational schemas using (i) Option 8A:  multiple relations – superclass and subclass relations and (ii) Option 8C:  single relation with one type attribute. Compare the schemas produced in (i) and (ii) with respect to the storage used, semantic preservation, and complexity of update manipulation.