COMP1802 Advanced Database Technologies | University of Greenwich

Detailed Specification 

This is an individual coursework. 

You need to complete the implementation and to write a final report to support the following  case study and requirements. 

Case Study: 

You are hired as a DBA for a medium size retailer company. The company stores  information about its products, orders and customers in a database.  

The database design is complete (please see the Entity Relationship Diagram below). At  the beginning of the module, you have been provided with the script to upload all the  necessary data into your VM Oracle Database. The owner of the tables is the user oe  having password oe. 

The employees of the company work in several departments: Sales, Marketing, Inventory,  Customer Service, HR, etc. They need to have different access rights to the tables in the  database, based on their job descriptions.

Your tasks are as follows: 

• to implement DBA requirements specified below using your Virtual Machine (VM) provided for the module and a created Oracle Database on the VM; 

• to describe your solutions for potential DBA problems as specified in the  Problem-solving requirements.

Entity Relationship Diagram (ERD) 

oe schema:

DBA requirements

1. Please create a new permanent tablespace tbse in your database to store  additional information that can be updated on a regular basis. This tablespace should  have an initial size of 50 megabytes and one data file. The space in this tablespace  should be managed locally and extend automatically by 10 megabytes. 

You must provide an SQL command for a tablespace creation and a  screenshot of successful execution of that SQL command. Please also provide  a detailed explanation on why you made those choices of tablespace properties and their values, and what are the benefits and drawbacks of your  choices.

2. You are required to create the following two new users with login requirements specified below: 

a) One user called cust for the Customer Service department and one user called invent for the Inventory department.  

b) The default tablespace with unlimited quota for both users should be set to tbse (created in Question 1). The temporary tablespace for both users should  be set to temp. The profile profdep should be assigned to both users. 

c) The user cust should be forced to change their password on the first login. The user invent does not have this requirement. 

d) Both users should be forced to change their passwords every 2 months.

You must provide SQL commands and screenshots of successful execution of  these SQL commands. Make sure you include an explanation on how all the  requirements are implemented, possible alternatives, their benefits and drawbacks. Please provide passwords (existing and new if changed) for the  created users.

3. Two new users (created in Question 2) should have the following access rights: a) Both users should be able to login to the database. 

b) The user cust should have Read/Only access to the OE.ORDERS and OE.CUSTOMERS tables. 

c) The user invent should have Read/Write access to the OE.WAREHOUSES table. 

d) Both users should be allowed to create new tables and views. 

You must provide SQL commands and screenshots of successful execution of  these SQL commands. Make sure you include an explanation on how all the  requirements are implemented, possible alternatives, their benefits and  drawbacks. 

4. New information about contractors working with the retailer company should be  inserted into the database. 

a) Create a new table Contractors in the schema of the user invent (created in  Question 2) and stored in the tbse tablespace (created in Question 1). Please use the structure described in the table below.  

Make sure all the constraints have user-defined names.

Problem solving requirements

5. You have accidently dropped an important schema (for example, hr) in your  database. Please discuss the best functionality to use in order to recover it quickly  and safely, providing SQL commands and explanations of the steps needed to  recover data. 

You must provide discussions and explanations, including SQL commands,  for the potential specified DBA problem. There is no need to execute these  SQL commands on your database. 

6. You try to execute SQL command SELECT * FROM MYTABLE; but get an error “ORA-00942: table or view does not exist”. Please explain all possible reasons for this error. Describe your solutions for this  error depending on each reason, including necessary SQL commands. 

You must provide discussions and explanations, including SQL commands,  for the potential specified DBA problem. There is no need to execute these  SQL commands on your database.