Database Administrator for Department Store

User Generated

negvzrfr12

Writing

Description

Imagine that you have been hired as the database administrator for a local department store. The department store has recently expanded by opening five (5) stores within your local region. They have also launched a series of marketing campaigns to attract new customers and increase sales. For your hired role, you will be responsible for creating and maintaining an enterprise-wide database system that will hold the sales and inventory data for the organization. The designed database will help operations in supporting its new business marketing strategy. Using the concepts presented throughout the course, you will develop a plan to create this database and establish a design that aligns with the product sales initiatives of the organization.

Write a twelve to fifteen (12-15) page paper in which you:

  1. Identify the potential sales and department store transactions that can be stored within the database.
  2. Design a database solution and the potential business rules that could be used to house the sales transactions of the department store.
  3. Evaluate all relationships of each entity within your database solution using the Crow’s Foot notation. Include all data elements, entities, relationships (such as 1:M, 1:1, M:N), and cardinalities for the department store database in your diagram. Note: The graphically depicted solution is not included in the required page length.
  4. Research the Internet for best practices of how retail stores use databases for retaining customers and increasing sales and complete the following:
    1. Justify how Big Data tools could be used for forecasting sales and inventory of the department store.
    2. Propose two (2) SQL Stored Procedures that use SQL functions to help sales associates perform explanatory or predictive analytics.
    3. Give your opinion on which of the two (2) ways you proposed in Question four (4) b provide greater value to expanding their business within the region.
    4. Provide a copy of your working SQL code as part of the paper.
  5. Research the Internet for database vendors that provide cloud computing services and complete the following:
    1. Estimate the types of costs involved or the pricing structure required when implementing a cloud-hosted solution for a database.
    2. Analyze security considerations and pricing of the different cloud implementation types.
    3. Rank the cloud services options of Software as a Service, Platform as a Service and Infrastructure as a Service in terms of functionality, mobility, and ability to provide distributed transaction concurrency. Compare how these cloud-based services fit within an environment where users are mobile. Determine the technical provisions that would be required to ensure data integrity.
  6. Evaluate whether the use of a distributed DBMS structure is appropriate and identify the optimization techniques that should be factored in to enhance the operations of the database in your design.
  7. Provide at least two (2) examples of how lost updates and uncommitted data may occur, keeping in mind that five (5) stores will be generating transactions.
  8. Determine the concurrency control factors that can be used to ensure valid execution of transactions within the current multiuser environment.
  9. Research the Internet for backup and restoration recovery solutions and database security threats that could be applicable to the department store. Upon your research, complete the following:
    1. Suggest at least two (2) options that could be made available to provide disaster management functions for the database system within the retail environment.
    2. Assess the types of security threats that may exist when managing the department store database and suggest measure(s) that can be performed to minimize these threats that are particular to retail.
  10. Use at least six (6) quality resources in this assignment. Note: Wikipedia and similar Websites do not qualify as quality resources.

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello, Check on the attachment,

RUNNING HEAD: DATABASE ADMINISTRATOR FOR DEPARTMENT STORE

Database Administrator for Department Store
Name
Institution

1

DATABASE ADMINISTRATOR FOR DEPARTMENT STORE

2

Store Transactions and Potential sales in department Store
In a department store, there can be the variety of products. A data bank contains all items
with their prices and details. There will include many tables that contain transaction details, order
details, pricing details, customer's details, payment details and shipping details. The department
store includes grocery items such as dry goods, sea foods, frozen, beverages, paper products,
cleaning products and house hold products. The transactions will be stored in a table that contains
transaction id, customer id, transaction time, and amount. One of the tables has product details
such as product id, product name, price, vendor, weight, quantity, product class. There will be one
customer's table which contains customer id, account, customer name and address, list of items
ordered, amount and bill. One transaction table includes details of payment having the id, payment
mode, payee, amount, account and time.
Database solution and the potential business rules
While implementing a database for a department store, RDBMS should be used. Relational
database stores all tables and have a reference with the other tables. Firstly, apply integrity rules
on the database tables so that all the records should have the unique id. There should be the unique
identifier for every transaction. Reference keys should be applied for creating references from one
table to the other using primary key. The Large table should be divided into small tables and apply
normalization for reducing redundancy. Time stamping should be utilized for every transaction.

DATABASE ADMINISTRATOR FOR DEPARTMENT STORE
Entity Relationship Diagram

3

DATABASE ADMINISTRATOR FOR DEPARTMENT STORE
Stored Procedures
1. Stored Procedure to find the average sales of any product.
CREATE PROCEDURE GetAverageSales
@ProductID INT
AS
BEGIN
SELECT AVG(UnitSales)
FROM Month_Sales
GROUP BY ProductID
HAVING ProductID = @ProductID
END
2. Stored Procedure to find the number of orders placed by each customer in particular year.
CREATE PROCEDURE GetOrderPlaced
@Year INT
AS
BEGIN
SELECT CustomerID, COUNT(1) AS [Number of Orders]
FROM Orders
GROUP BY CustomerID
HAVING CustomerID IN(
SELECT CustomerID FROM Orders
WHERE DATEPART(YEAR, OrderDate) = @Year)
END

4

DATABASE ADMINISTRATOR FOR DEPARTMENT STORE

5

Big data tools used for forecasting sales
By predictive analysis, these big data tools are used to analyze the past behaviour of
customers. By sales in the past to customers predict future sales and their demands. Analysis tools
help to analyse their buying capacity and understand their buying pattern. Does it capture details
about what product customer is buying and of what brand? There will be the analysis about what
buyers want by source of information that consumers searched in the database. The behavioral
analytic software can be used to analyze the behaviour of customers to understand what they need.
There are various tools such as Radian6 from Salesforce.com and much more that collects
feedback from customers online so that sales will be improved. Real time monitoring in specific
areas such as tracking order or any field helps in improving sales. One of the keys for increasing
sales is by generating a lot of leads. Big data helps in increasing leads. Other using Big data tools
such as Hadoop for analyzing the data so that number of sales can be improved by finding the
behaviour of customers.

DATABASE ADMINISTRATOR FOR DEPARTMENT STORE

6

Pricing Str...


Anonymous
Really great stuff, couldn't ask for more.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags