Implementing DBA requirements for Quick Sale online retailer company: best practices

Plagiarism definition and procedures

Plagiarism is presenting somebody else's work as your own. It includes: copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort; copying another student's coursework; stealing coursework from another student and submitting it as your own work.  Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the procedures set down by the University. Please see your student handbook for further details of what is / isn't plagiarism.

You are hired as a DBA for a medium size online retailer company “Quick Sale”. The company sells several products, such as computer hardware and software, music, clothing, and tools. The company maintains information about these 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.     

The database design is complete (see ERD below) and you will be provided with the script to upload all necessary data in your VM database. The owners of the tables are user sh and user oe.

The “Quick Sale” 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 using your Virtual Machine provided for the module and describe your solutions for potential DBA problems as specified in the Problem-solving requirements.

You are required to create 2 tablespaces to store data needed for two different applications with different requirements:

a) First tablespace inv_tsp, will be required to store extra inventory information. This will be for a transactional type of application where every day a large amount of data (around 10Mb) will be entered into tables stored in inv_tsp tablespace. That data also will be updatable on a regular basis.

b) Second tablespace dw_tsp will be used for a data warehousing application with a large data (120Mb) loaded at the beginning and rarely updated.

You are required to create both tablespaces in your database, deciding on the best  properties to be used for each tablespeace. You must provide SQL command for each tablespace creation, a screenshot of successful execution of that SQL command and explanation on why you made those choices of tablespaces properties and their values for both tablespaces. What are the benefits, and drawbacks of your choices.

Overview of Quick Sale online retailer company

You are required to create several new users with the following login requirements:

a) Two users for the Customer Service department and one user for the Inventory department. Default tablespace for all users should be inv_tsp and temporary tablespace should be set to temp.

b) All of the above users should have unique usernames and passwords. Password should be at least 8 characters long and users should not be allowed to have a password that is shorter than 8 characters. There shouldn’t be any other enforcement on the password.

c) The users should be forced to change their passwords on their first login, then every 6 months and not allowed to reuse the same password.

d) If during the login they specified their password incorrectly 3 times, the account should be locked for 30 hours.

e) All new users should be allowed to leave their session without interacting with it for no more than 15 min.

Make sure you include an explanation on how these requirements are implemented, possible alternatives, their benefits and drawbacks. Please provide in your report the usernames and passwords for all users created.

New users should have the following access rights:

a) All of the above users should be able to login to the database.

b) Two users for the Customer Service department should have Read/Only access to tables, owned by user oe.

c) Customer Service users should be able to easily query information about all orders for a particular customer based on the customer id. (Remember, Customer service users might not be experienced SQL developers, so they will not be able to create a complex SQL statement with joins, etc. You need to simplify this task for them).

d) The user for the Inventory department should have Read/Write access to the tables:

e) The user for the Inventory department should also be allowed to create new tables and new views.

Create a table inv_categories in the schema of the newly created Inventory department with the structure described in the table below. Make sure all constraints have user-defined names and the table is stored in inv_tsp tablespace.

You need to implement the best way for this user to produce such reports. The output should include customer id and customer name as well as information about the products that were sold to this customer- product id, product name, quantity sold, and amount sold.

Implementing DBA requirements: best practices

You need to include a rationale for your implementation discussing alternatives, their benefits and drawbacks. Please provide in your report screenshots of sh user successfully running the above report for a particular customer.

If you accidently dropped an important schema (for example, hr) in your database, discuss the best functionality to use in order to recover it quickly and safely. Provide an explanation of the steps needed to recover data including the SQL commands for each step.

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

Discuss how different database failures can impact a business, including the financial costs, and what can be done to minimise the downtime cost. To illustrate your point, find a real-life example, based on your research, of the financial costs suffered by a company due to an Oracle systems failure; what lessons were learnt and what actions were taken to prevent future problems.

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.

An evaluation of your system and of your learning experience. Write around 600-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 MSc project report so use this opportunity as a way of practicing your skills in writing an evaluation.

If you have used code or ideas from anywhere other than the lecture notes and tutorial examples (e.g. from a book, somewhere on the web or a fellow student) then include a reference showing where the code or ideas came from and label your code carefully to show which bits are yours and which bits are borrowed.

Be aware that the marker will look for similarities between your code and that submitted by other students so please do not share your code with any other students as this is considered to be plagiarism.

Are all the required sections included and completed properly?

Is the report clear, well formatted and easy to read?  Does it have a logical structure?

Does it have a discussion on design decisions? Is the evaluation realistic, does it show that you have really thought about your system and how you went about developing it?