Develop a solution in the form of a Database System Development Plan for your retail store you choose:
Scenario:
The retail store has experienced significant growth in the recent months. The customers have expressed an interest in being able to purchase the retail store’s products electronically. Currently, the retail store has a simple Web site, but it is not clear that the system can support dynamic interaction on the Web site or online purchases. The main database system that is used in the physical store has not been working correctly, however, and this is an issue. The retail store wants to be able to eventually integrate an online database system with the current order entry system that is used to support sales made in the local store. The retail store also needs the database system to enforce the rules that have been established for their business practices. What can be done to support the goals and objectives for meeting the needs of their customers?
Database System Overview (4-5 pages):
1) Decide on the type of retail store you want to form your Database System Development Plan.
2) Describe the general business environment for the case study organization you choose.
3) Include a database system goals and objectives statement.
4) Include a narrative that discusses how the proposed database system addresses the presenting business problem(s).
5) Include a narrative that demonstrates how the proposed system aligns to the mission statement and strategic goals of the organization.
6) Identify the mission and goals of the case study retail store, and provide your analysis as to how this part of the project fulfills the mission and/or goals of the case study organization.
Must include all sources and should be cited both in-text and in References.
Entity Relationship Model (4-5 pages):
The retail store has provided you with a list of some of the business rules that they use to conduct business on a daily basis. The company has also provided you with the subjects of interest to them, namely those things that the company wants to track in the process of taking orders from their customers. What can be done to effectively document the relational database system solution that you are proposing to the retail store?
The project deliverables are as follows:
1) Subjects of Interest (proposed entities):
- Customers
- Orders
- Products
- Add a minimum of 3 of your own subjects of interest based on your retail store.
2) Business Rules:
- Each product is assigned to a maximum of 1 category.
- A person who has placed at least 1 order is a customer.
- All products have a minimum reorder level.
- Add a minimum of 3 of your own business rules based on your retail store.
3) Entity–Relationship Model:
- Include a list of the business rules that will be enforced by the proposed database system.
- Include a list or table of the entities, attributes (including data types), relationships, and cardinality constraints.
- Include an entity–relationship (E–R) diagram that uses crow’s foot notation and graphically depicts the entities, attributes (including data types), and relationships (including degree and cardinality constraints).
- Verify that the data design that is depicted in the E–R diagram adheres to a minimum of third normal form (3NF), and if necessary, provide documentation and justification for the use of a higher level of normal form.
- Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case study organization.
Note: Use Microsoft Visio or equivalent to create the E–R diagram. You will embed the diagram in the Word document and also provide it as an attachment.
Must include all sources and should be cited both in-text and in References.
Structured Query Language (SQL) Scripts (4-5 pages):
The case study retail store has provided a list of reports and data manipulation tasks that are needed in the processing of orders for their customers. Answer the following:
1) What structured query language (SQL) statement scripts are needed to create the database schema for the relational database system and manipulate the data in the solution that you are proposing to the company?
2) How does each of these scripts specifically support the goals and objectives of the company?
It is assumed that Microsoft SQL Server Express Edition (with management tools) was chosen as the database platform.
The project deliverables are as follows:
A) Data Manipulation Tasks
- Insert 20 records into each table for testing purposes.
- Delete an entire order by using the unique identifier for that order.
- Update the price of a product by using the unique identifier for that product.
- Add a minimum of 3 of your own data manipulation language (DML) scripts based on the needs and specifications of your retail store.
B) Report List
- Total revenue (sales) per month, grouped by customer
- Total revenue (sales) per month, grouped by product
- Total count of products, grouped by category
- Add minimum of 3 of your own report scripts based on the needs and specifications of your retail store (one must be a CROSSTAB)
C) SQL (4–5 pages)
- Include the database definition language (DDL) scripts to CREATE to database schema as described in the entity–relationship (E–R) diagram.
- Include the database manipulation scripts (DML) that will be used to INSERT, DELETE, and UPDATE data in the proposed database system.
- Include the SELECT, CROSSTAB, and AGGREGATE FUNCTION statements that will be used to read data from the proposed database system.
- Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case study organization.
- Provide the following attachments (in addition to embedding in document):
- DDL.sql (including CREATE and INSERT statements so that they execute in the correct order [top-down])
- DML.sql (including DELETE and UPDATE statements so that they can be executed in any order as selected)
- REPORT.sql (including SELECT, CROSSTAB, AGGREGATE FUNCTION statements so that they can be executed in any order as selected)
Note: You will embed each script in the Word document and also provide it as an attachment.
Must include all sources and should be cited both in-text and in References.
Database Administration Plan (4-5 pages):
The case study retail store is concerned about the possibilities of losing data because of database system malfunctions or downtime. Security is also a major concern to the company because it is common knowledge that engaging in online business can be risky because of known vulnerabilities on the Internet. The company also realizes that its in-store database system is the top priority at this time. What solutions can you propose to effectively manage database transactions, maintain security, and recover the data that are lost from system failure or downtime?
The assumptions are as follows:
- A high volume of the orders often occurs during the daytime.
- One person will serve the role of database administrator.
- The database administrator account will serve as database owner.
- The transaction log must be backed up.
- Point-in-time recovery is required.
- There is an always-on availability group.
- The ability to purchase products online will be addressed in a future database project.
The project deliverables are as follows:
A) What solutions can you propose to effectively manage database transactions, maintain security, and recover the data that are lost from system failure or downtime?
B) What is your rationale for the transaction management plan, database security procedure, backup plan, and recovery model that you proposed for the case study organization?
C) Database Administration Plan
o Create a database administration plan that is specific to the needs of your retail store.
o Include a transaction management plan that includes a flowchart for how each transaction will be handled (including rollback and commit cases).
o Include a database security procedure that includes provisions for access control, user authentication, and availability.
o Include a backup plan and a recovery model
o Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case study organization.
Must include all sources and should be cited both in-text and in References.
Future Database System Implementation Plan (4-5 pages):
The case study retail store has expressed a desire to eventually be able to analyze the data that are collected from engaging in business, both in its brick-and-mortar store and in its online store. Because the system had to be redesigned from the ground up, the goal of taking the business online using a Web-based database was pushed back. Eventually, the company wants to be able to run statistical analyses against the data that it is collecting and to be able to drill down through the data to transform them into various desired formats. In addition, the company would like to acquire data sets from other providers to engage in decision-support initiatives. What recommendations can you propose to support these business intelligence goals?
Part 1: Future Database System Implementation Plan
A) What fundamental differences exist between object-oriented and object-relational database systems and Web-based database systems?
- Would these differences impact your retail store?
B) Include details of what changes would need to be introduced to the database if it was used to build a data mart or a data warehouse.
C) Include details of what considerations would need to be made if the database were to become a distributed database.
D) What specific types of business intelligence could be gathered from the database?
- How would this information assist in the decision-making process for your retail store?
E) How would your retail store benefit from data warehousing in the following areas?
- Return on investment on business intelligence initiatives (Provide a 3-year estimate.)
- Competitive advantage (based on local or target area)
- Increased productivity of decision-makers (related to business process decision-making)
F) How would you address the following data warehousing problems if they occurred in your retail store?
- Required data were never captured.
- There is a high demand for disk space and other resources.
- There are hidden problems with source systems.
G) Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case study organization.
Must include all sources and should be cited both in-text and in References.
Part 2:
Argumentation PowerPoint (5-slide PowerPoint excluding title slide and References):
An argument in problem solving is used to permit the problem solver the opportunity to offer a set of reasons or evidence in support of his or her solution and related conclusions from the inquiry and research process. The argument is not an opinion and is not the conclusion restated, but rather a demonstration of the intellectual inquiry that you have made during the problem-solving process. Naturally, for the argument to gain credibility, it must be reinforced with scholarly references because the support of the conclusion comes from the quality of evidence that has been gathered.
There are some different forms of arguments that could be made, as follows:
Categorical Arguments: An assertion that there is 1 of 4 relationships between X and Y. You could show these 4 forms as follows:
a) All Xs are Ys. Example: All mice are mammals.
b) No Xs are Ys. No mice are mammals.
c) Some Xs are Ys. Some mice are mammals.
d) Some Xs are not Ys. Some mice are not mammals.
Predictive Arguments: A case is made for anticipating or predicting events based on the characteristics of a category (X or Y) or based on the relationship between X and Y. Some sample arguments are below (the combination of X and Y is not represented).
A) All Xs have this characteristic, behave this way, or are in this state. Therefore, I predict about X.
o Example: All mice eat cheese. Therefore, the cheese on the table will be eaten by the mouse on the table.
o All Ys have this characteristic, behave this way, or are in this state. Therefore, predict __about Y.
- Example: All cheese will be eaten by mice. Therefore, the cheese on the table will be eaten by the mouse on the table.
o Some Xs have this characteristic, behave this way, or are in this state. Therefore, I predict__ about X.
- Example: Some gray mice eat cheese. Therefore, the cheese on the table may be eaten by the gray
B) Some Ys have this characteristic, behave this way, or are in this state. Therefore, I predict __ about Y.
- Example: Some yellow cheeses are desired by mice. Therefore, the yellow cheese on the table may eaten by the mouse on the table.
C) No Xs have this characteristic, behave this way, or are in this state. Therefore, I predict __ about X.
- Example: No mice have been found to have 2 tails in this region. Therefore, the mouse in this region will have 1 tail.
D) No Ys have this characteristic, behave this way, or are in this state. Therefore, I predict __ about Y.
- Example: No ballots were left uncounted. Therefore, the voting system should be fair and equitable.
Change Arguments: A case can be made that something is different in the characteristics (or state) of X and/or Y between 2 or more observations made at different times. A change argument is simply a comparison of before and after and any differences that are noted from the first observation to the subsequent observations. When making a case for observed change, you would typically use a different form of argument (categorical or predictive) to explain why that change occurred.
Example:
o At observation 1, all Xs had this characteristic (or were in this state).
o At observation 2, all Xs do not have this characteristic (or were not in this state).
o Therefore, X no longer possesses this characteristic (or is no longer in this state).
Envision that you now have to present your solution to your retail store client. You need to make a formal presentation to a group of stakeholders, and you need to be prepared to answer their questions. Create a PowerPoint presentation in which you provide the following:
o In the Notes pages of each slide and based on the context of the information on that slide, provide a narrative of 2–3 paragraphs of your rationale for the solution to the problem described in the IP.
o On each slide, identify the type(s) of argument(s) that you are making for the problem presented in the unit IP and the evidence that you have to support the argument(s)
Must include all sources and should be cited both in-text and in References.