University of Otago COSC430 Computer Science

Advanced Database Topics 2020

Oracle assignment information sheet

All parts to be completed by Friday 22 May 2020



Part 1 - Due 27/03/20


1) Managing Users Due 17/04/20 (10 marks)


Using the "test" instance you should have already created, create an Oracle account for  Cathy Chandra (the Admin Assistant for your NZetti Co. Ltd. client), and give her her username and password (securely). Create an account for Dr. David Eyers as well.

Configure Cathy's account so that any more than 4 incorrect login attempts will result in a 24-hour suspension of the account, and also that more than 20 minutes idle time will result in an automatic log-out of the user's current session.

Initially give Cathy the fewest privileges needed to log into Oracle and read the data (but not change it) in the table which you should have created for her.



Parts 2, 3, 4 and 5 - All parts to be completed by Friday 22 May 2020

- now worth 40 marks (includes overall performance)



2) General DBA performance (20 marks)


You should now create a new instance - which replicates the "test" instance - to use for the rest of the assignment. Then proceed with Parts 3, 4 & 5  by responding to queries from your client.

E.G. extend the privileges on the table you supplied for Cathy if / when she asks you to do so, import data as requested, provide backups as requested, create any other appropriate Oracle accounts and / or roles relating to the client's firm which Cathy asks for, help your client with Oracle  (e.g. sql statements), and generally assist her in providing documentation for the company to use should they decide to go ahead with contracting your firm's services.



3) Managing Data and Space (10 marks)


Create a table called cucurbit in your instance and load data into it using the statements in the  /coursework/430/pickup/chutney1.sql file, and give Cathy any requested access to it.

Import any extra data Cathy asks for (in such a way that she has all privileges asked for over any tables thus created).


4) Backup and retrieval (5 marks)


Institute a regular scheme for backing up  the client's data. (using the export utility).

Recover some data if, and when, Cathy asks you to do so.


5) Recovery (5 marks)


Recover an Oracle instance after it appears to have become corrupted.





Total marks (50)………………………………

(Worth 25% of COSC430)



                                                  C A Chandra

April 2020