Question 1
(A)(i)Imagine you are the DBA responsible for the operation of a large database which performs on-line transaction processing and requires up to the minute recovery from any failure. The database is in use during normal working hours and no kind of mirroring technique is being used due to the expense being prohibitive.
The time is 15:30 on a Friday afternoon and a disk head crash occurs, the result of which is the total loss of all database data on that disk.
Outline what steps you would need to take in this situation both BEFORE and AFTER the event. Your answer should include details of what help the Database Management system itself would give you.
(B)Early on a Monday morning you are told by a user that an item of incorrect data was entered into the database last week. What steps would you take?
Question 2
(a)Explain the current interest that large organisations have in Data Warehousing.
(b)RDBMS vendors are building a number of features into their products aimed at supporting the implementation of data warehouses. For each of the following technologies, give a brief description of what they entail, the problems they are designed to alleviate and how they would be used in a data warehouse environment.
(i)Parallel server technology
(ii)Parallel ‘query’
(iii)Star queries
(iv)Bitmapped indexes
(c)Data Mining is an important new way of interrogating data warehouses. Describe Data Mining technology and give examples of its use.
Question 3
a.Securing a database can involve the combination of three main aspects
Authentication
Authorisation
Audit
Explain the above three terms and how they relate to user activity.
b.(i)Explain with examples the technique of SQL Injection.
(ii)Show how SQL Injection can be detected and prevented.
c.Give reasons why it is recommended to make database security measures database-centric.
d.(i)Explain the current interest in encrypting database data.
(i)Discuss the issues that need to be considered and addressed when encrypting data in a database.
e.Audit data is often captured by the use of database triggers. Discuss how this can affect normal database operations.
Question 4
a.Describe how the use of roles can help the DBA in the management of security on a database.
b.Study the following sequence of commands which have been successfully issued by a database user called fred on an Oracle database that is using definers rights for security.
CREATE ROLE r1;
CREATE ROLE r2;
GRANT r1 TO r2;
GRANT SELECT, UPDATE ON employee TO r1;
GRANT DELETE ON employee TO r2;
GRANT r2 TO amy;
The user called amy already has the ability to create procedures and tables. Which of the following routines can now be successfully executed by amy? In each case explain your reasoning.
(i)SELECT * FROM fred.employee;
(ii)DELETE FROM fred.employee
WHERE ename = SCOTT;
(ii) ALTER TABLE fred.employee
ADD proj_id NUMBER(4);
(iv)CREATE PROCEDURE x
…
UPDATE fred.employee SET sal = 4000
WHERE ename = SMITH;