Question

You are hired as a DBA for a medium size online retailer company ‘Super Buy’. The company maintains information about the products that it sells, such as product identification numbers, the category into which the product falls, orders information, the supplier, the availability status of the product, a list price, etc. Inventory information is also recorded for all products, including the warehouse where the product is available and the quantity on hand. Customer information is also tracked.

The database design is complete (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 ‘Super Buy’ 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 (up to 70 marks)

1. You are required to create a tablespace tbs_ext in your database. This tablespace is needed to store extra inventory information for a transactional type of application with initial data of size 100Mb. Every day new data of a size around 10Mb will be entered into tables to be stored in this tablespace and be updatable on a regular basis. Please decide on the best properties to be used.


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.
[Up to 15 marks]

Answer 1

The code to use in creating a tablespace tbs_ext is:

SQL

CREATE TABLESPACE tbs_ext

DATAFILE 'tbs_ext.dat'

SIZE 50M

AUTOEXTEND ON NEXT 10M

MAXSIZE UNLIMITED;

The above CREATE TABLESPACE statement has specified properties is the best choice for the tbs_ext tablespace to store extra inventory information for a transactional type of application with the given data characteristics.

So, The AUTOEXTEND ON NEXT 10M clause and the MAXSIZE UNLIMITED clause ensure that the tablespace will have enough space to store the growing data, while the SIZE 50M clause provides an initial size that is sufficient for the initial data load.