Liquor and Beer Retail Store Assignment | Get Paper Help
Description
This paper will be about a Liquor and Beer retail store. In addition to writing a paper, there will be an assignment regarding in database using Microsoft SQL server (See the instructs.doc file for more details
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?
Examples are provided here to help with this assignment.
It is assumed that Microsoft SQL Server Express Edition (with management tools) was chosen as the database platform. It can be downloaded for free at this Web site.
You have been asked to develop a solution in the form of a Database System Development Plan for your retail store using the following format:
- Database System Development and Implementation Plan
- Use Microsoft Word
- Title page
- Course number and name
- Project name
- Your name
- Date
- Table of contents (TOC)
- Use auto-generated TOC
- Maximum of 3 levels deep
- Be sure to update the fields of the TOC so it is up-to-date before submitting your project.
- Section headings (create each heading on a new page with TBD as content except for sections listed under new content below)
You will add a section to this Database System Development and Implementation Plan and submit it for grading. As a preview, each section will contain the following:
- Database System Overview (Week 1 IP)
- Entity Relationship Model (Week 2 IP)
- Structured Query Language (SQL) Scripts (Week 3 IP)
- Database Administration Plan (Week 4 IP)
- Future Database System Implementation Plan (Week 5 IP)
Database System Overview (4–5 pages)
The project deliverables are as follows:
- Describe the general business environment for the case study organization.
- Include a database system goals and objectives statement.
- Include a narrative that discusses how the proposed database system addresses the presenting business problem(s).
- Include a narrative that demonstrates how the proposed system aligns to the mission statement and strategic goals of the organization.
- 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.
- All sources should be cited both in-text and in References using APA format.
————————————————————————————————
Entity Relationship Model
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?
Click here to see worked examples.
The project deliverables are as follows:
- Subjects of Interest (proposed entities)
- Customers
- Orders
- Products
- Add a minimum of 3 of your own subjects of interest based on your retail store.
- 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.
- Entity–Relationship Model (4–5 pages)
- 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.
- All sources should be cited both in-text and in References using APA format.
—————————————————————
Structured Query Language (SQL) Scripts
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:
- 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?
- How does each of these scripts specifically support the goals and objectives of the company?
Examples are provided here to help with this assignment.
As indicated in Unit 1, it is assumed that Microsoft SQL Server Express Edition (with management tools) was chosen as the database platform. It can be downloaded for free at this Web site.
The project deliverables are as follows:
- 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.
- 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)
- 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 (Unit 2).
- 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.
- All sources should be cited both in-text and in References using APA format.
—————————————————————————————————
Database Administration Plan
Key Assignment Draft
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?
Examples are provided here to help with this assignment.
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:
- What solutions can you propose to effectively manage database transactions, maintain security, and recover the data that are lost from system failure or downtime?
- 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?
- Database Administration Plan (4–5 pages)
- Create a database administration plan that is specific to the needs of your retail store.
- Include a transaction management plan that includes a flowchart for how each transaction will be handled (including rollback and commit cases).
- Include a database security procedure that includes provisions for access control, user authentication, and availability.
- Include a backup plan and a recovery model
- Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case study organization.
- All sources should be cited both in-text and in References using APA format.
————————————————————————–
Future Database System Implementation Plan
Key Assignment
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?
Future Database System Implementation Plan (4–5 pages)
- What fundamental differences exist between object-oriented and object-relational database systems and Web-based database systems?
- Would these differences impact your retail store?
- 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.
- Include details of what considerations would need to be made if the database were to become a distributed database.
- 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?
- 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)
- 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.
- Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case study organization.
All sources should be cited both in-text and in References using APA format.
