The implementation and the final report to support the following case study and requirements.
The “YourGame” is a middle size on-line retail company that specialises in selling computer games to young adults. Customers can browse “YourGame” games on their website and when ready to purchase they need to register and provide a shipping address. The payment process is outsourced to another company. “YourGame” uses Oracle 12c database to store information about its products, registered customers, their orders etc. The order entry process is using the ERD below.
The company maintains information about their products, 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 and it is developed in the oe schema (see below).
You are hired as a DBA for this company to complete necessary database administration tasks: user management, database security, database backup policy, performance management etc.
The “YourGame” company has number of employees working 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 description.
Your task is to implement DBA requirements specified below and describe your solutions for potential DBA problems as specified in the Problem solving requirements.
- Create new permanent tablespace called inv_tbs to store extra inventory information. The size of this information is expected to grow from 70Mb to 250 on a daily basis. You need to decide on all parameters of that tablespace: space management, maximum size, extents management and others.
You must provide SQL code and explanation with discussion on each parameter of this tablespace: why you decided to choose that value for the parameter, what are benefits and drawbacks, etc. [Up to 10 marks]
- Create several new users with the following authentication requirements:
- Two users for Customer Service department and one user for the Inventory department.
All of the above users should have unique usernames and complex passwords. Password should be at least 8 characters long (only this rule should be enforced).
- Default tablespace for all users should be the newly created tablespace inv_tbs and temporary tablespace should be TEMP
- The users should be forced to change their passwords on the first login and every 6 months.
- If during the login they specified their password incorrectly 3 times, the account should be locked for 24 hours.
- The users can have only up to the 10 min of the idle time in their sessions and no more than 2 concurrent sessions.
[Up to 20 marks]
- New users should have the following access rights:
- All of the above users should be able to login to the database.
- Both Customer Service department users should have Read/Only access to PRODUCT_INFORMATION, PRODUCT_DESCRIPTIONS, ORDERS, ORDER_ITEMS and CUSTOMERS tables.
- The user for the Inventory department should have Read/Write access to the tables: INVENTORIES and WAREHOUSES as well as Read/Only access to PRODUCT_INFORMATION and PRODUCT_DESCRIPTIONS tables.
- This user should also be allowed to create new tables and new views.
[Up to 20 marks]
- You are required to create a view cust_orders_view that presents the following information about customers and their orders:
customer ID, customer first name, customer last name and their orders information including order date, order id and order total.
Both Customer Service users should be able to run that view.
[Up to 20 marks]
Problem solving requirements:
- One of your users calls you and informs you that they accidently deleted the table warehouses. You need to recover that table. Discuss the best functionality to use to solve this problem? Write the SQL statements you will need to execute to recover the data. Discuss how this recovery functionality is implemented.
[Up to 15 marks]
- One of your database users mgr101 can login to the database successfully, but when they try to execute SQL command SELECT * FROM products; they get an error ORA-00942: table or view does not exist.
Discuss all possible reasons for this problem.
Describe how you would solve this problem including necessary SQL commands.
[Up to 15 marks]
A report (PDF file) including following components:
- Description of your implementation for each requirement: SQL commands and explanation with the rationale justifying the design decisions you have made; evaluating alternatives, potential problems and limitations.
- A brief (less than half a page) description of any special strength of your implementation or additional features that you added to the system, that you think should be taken into account in awarding a mark.
- A concise list of any bugs and/or weaknesses of your system.
- An evaluation of your system and of your learning experience. Write between 500 and 800 words evaluating the components that you have produced and the way you went about completing the coursework.
Try to be specific and state exactly what you learned by your own effort and from other students. Make clear what you would do differently if you were to do the coursework again. Some points that you could cover include: how you managed your time, how you found out necessary information to complete the assignment, how well designed you think your solution is, what changes would need to be made to your system before it could become a product that could be used by real users.
This sort of discussion will form an important part of your final project report so use this opportunity as a way of practicing your skills in writing an evaluation.