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 lang

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 lang
22 Database Design and Data Modeling 03/12/2022 Contents Table of Content………………………………………………………………………………………………………………………………………2 1 Contents 1 Week 1 IP1: System Overview of the Database. 3 Introduction 3 The Objective statements 4 The System Goals 4 Proposed database system addressing the presenting business problem 5 The Narrative 6 The goals and mission of the retail store. 7 Week 2 IP2 Entity Relationship Model 9 The requirement 9 The subjects of interests 9 The business rules 10 The proposed entities, attributes, relationships, and constraints. 12 Entity-relationship diagram 15 The fulfillment of mission and goals. 16 Structured Query Language Scripts (Week 3) 17 DDL 17 DML 17 Reporting 17 Analysis of how project fulfills mission/goals of target organization 17 Database Administration Plan (Week 4) 18 Transaction Management Plan 18 Database Security procedure 18 Backup Plan and Recovery procedure 18 Analysis of how project fulfills mission/goals of target organization 18 Future Database System Implementation Plan (Week 5) 19 Database Differences 19 Changes needed to build a data warehouse/data mart. 19 Distributed Database Considerations 19 Business Intelligence 19 Data Warehouse Benefits 19 Return on Investment 19 Competitive Advantage 19 Increased Productivity 19 Data Warehouse Problems 20 Analysis of how project fulfills mission/goals of target organization 20 References 21 Week 1 IP1: System Overview of the Database. Introduction For any organization today, a Database is as essential as other assets. Most of the organization’s activities like making inventories tracking, and organizing details normally rely on the accuracy of the management and Database software information. For this paper, we will be addressing the issue of a retail store that deals with products like sales of drones, mobile phones, internet devices, laptops, etc. Recently, the organization has been experiencing significant growth marked by an increased number of clients; therefore, it’s focusing on engaging in online sales. Mukherjee (2019). Currently, the organization also has a website that is not reliable for sales since it may not effectively support online sales and customer interaction. The business also has its Database on the physical store, which will not be effective like the virtual databases to implement online sales. It’s essential for the organization to develop a database that is scalable, secure, and can be integrated into the online business. The Objective statements The major role of the database system within Tri-mart Company is the promotion of effective management of routine transactions, proper maintenance of customer’s records, employee records, promoting online commerce, and storage of vital organizational information. The System Goals The major goal of this system is reducing downtime and increasing efficiency, especially when managing the Tri-mart company’s transactions. The core reason for the establishment of the Database is the gradual growth of the organization; hence, therefore, is a need to maintain maximum data regarding the company’s performance; hence it will promote a link between the physical and online purchases. Other advantages associated with relevant databases within our company includes Ensuring there are complementary sales. Better services to customers. Sending information to customers through emails and newsletters. It is used in direct marketing for companies. Companies can announce when new products and services are available within their stores. Ensuring customers know when special offers and discounts are available Knowing the exact customer’s needs, tastes and preferences. Proposed database system addressing the presenting business problem All the information and data from an organization is normally collected by the Microsoft SQL server express. This creates a database that is essential for managing information, and it also developed a simple retail website that is helpful in the management of customers” online purchases. Therefore, through the Database and website, there will be a seamless integration between the customers and website, which will help in expanding Tri-mart Company. The collected data will also be processed by the company to understand customers” trends, tastes, and preferences. Additionally, information will be used to track new customers and those returning to purchase electronics. As the company grows, there are numerous aspects of the organization that shall be transformed; firstly, there will be easier decision-making. When data is collected, it may be meaningless to the organization unless it’s processed to provide insights about relevant adjustments that should be made. The Database will also help in the collection of data regarding the trends and culture within the store; it will also be used in tracing employees” productivity and customers ‘ needs which are among the customer’s needs. Choi et al. (2021). The Narrative The proposed system to the organization is a Microsoft SQL server that is directly aligned to the objectives, goals, and visions of Tri-mart Company. The company has been recently growing significantly, and there is a need to enhance performance and promote better customer service. Therefore, if the company uses Microsoft SQL to integrate between the physical stores and online purchases, it will meet the diverse needs of different customers. Additionally, SQL will increase the organization’s scalability and the ability for configuration and management of information. Therefore, the organization’s goals include increasing the current sales and promoting customer satisfaction which is difficult without relevant software. Hence, Microsoft SQL shall ensure that the organization understands the needs of the customers and promotes efficiency. By a proper database, the inventory will be managed based on the frequency of purchases by customers. Therefore, as products are purchased faster, they have to be replaced more often to avoid depletion. Hayat & Soomro (2018). The goals and mission of the retail store. For the Tri-mart business to meet its goals and mission, it has to effectively apply the Microsoft SQL server, which is aligned to its objectives. The reason includes that its configuration process is relatively straightforward, especially when managing different organizations’ transactions. Additionally, the database system will be easy to manage, especially for people with less IT skills, because one does not have to manually update the system due to the automated updates system. There are also no installation toolkits required when installing the system Due to the continued failure of the current physical Database at the company, Microsoft SQL will be the most effective system to promote proper data management, especially when an organization has been growing significantly. Another reason for the application of Microsoft SQL is that it will optimize the company’s information storage system. Hence management of customer and company records will be straightforward since relatively few troubleshooting and maintenance processes shall be made. When managing customers’ information, it will be easier to update details, delete and change without complexities. The SQL database shall also save a lot of time for the IT managers and employees at the organization since it will be integrated into the website and another system hence collection, processing, and management of data shall be automatic. Finally, the data collected will be effectively analyzed to provide insights and promote effective management of the organization. Hence the managers will understand areas they should make changes to promote efficiency. Kilavo et al. (2021). Finally, due to the promotion of information security, Microsoft SQL will be related to the mission and goals of the company. SQL seeks to ensure that the organization is protected through the security of customers ‘ information. Over the past years, there has been a gradual increase in cyber security incidents. When a data theft occurs, the customers lose trust in the organization’s ability to keep their data; it may be exposed to lawsuits and fines, which are relatively expensive. Therefore, the security will ensure business continuity is met while customers feel happier. Week 2 IP2 Entity Relationship Model The requirement For this incident, the Tri-mart retail company seeks to upgrade its database and implement a Microsoft server edition that aligns with the firm’s needs and objectives. Defining database and business entries precisely to promote daily processes is essential. Therefore, the requirements of the model shall be listed below. The subjects of interests Due to the needs and importance of the new database, some of the subjects of interest when developing the programs shall include. Company staff Products Customers Orders Stores Suppliers Ordered items Brands Categories Stock The database entities lists must be continuously assessed, and potential changes based on their needs. Additionally, they shall be edited throughout the business process. The business rules It’s essential to have appropriately defined rules during the development process of any relational database system. The rules are used to develop an effective data model with relationships, attributes, constraints, and entities based on the organizational standards. Therefore, some rules to be adhered to include. A user ID shall be created for all online clients. The client’s ID shall be used during the rewards programs. If a client buys anything from the store, they should use the order ID to collect it. There will be tracking of the mode of the transaction (whether in-store or online) for each order. Details like the client’s ID, order ID, and product ID when a purchase is made will be recorded. Each product may have different brands. Ma et al. (2018) The item ID shall be related to the discounted product. The purchase order shall be verified after checking the supply available. The details of the supplier shall include the store and product’s ID. Details will be sent to the store and inventory managers if the supply of a product reduces. When a product is purchased online, there will be a relation between the order ID and tracking codes. There will be individualized promotion offers under each company’s location. Each staff member can only work under one store unless otherwise; they can serve as inventory managers, store managers, cashiers, customer service representatives, or assistant managers. Simon & Kiszl (2021). The proposed entities, attributes, relationships, and constraints. Customer Table: tblCustomer Staff Table: tblstaff Stores Table: tblstore Order Table: tblorder Order Items Table: tblorder_items Product Table: tblproduct Categories Table: tblcategory Stock Table: tblstock Brand Table: tblbrand Entity-relationship diagram For these entries, the Boyce-Codd normal form (1, 2 and 3 NF) was used with foreign key and primary key constraints. 1NF: is also the first normal form that seeks to ensure that all the records remain unique. To meet this, assigning special keys to any entity’s attributes will be assigned. 2NF: second normal form: when this is applied, we find on different tables to have duplicates; however, for duplicates to be avoided under the columns, there is the usage of a foreign key. 3NF: this is the third normal form applied to ensure data management is simplified, there is referential integrity, and less data duplication. Zhang (2020). The fulfillment of mission and goals. This will be the best database model since it will ensure that organization can handle and process all the transaction requests. For example, we observe primary and foreign keys on each table that will ensure that relational data is easily accessible. Therefore, the 3NF principles shall be applied to ensure data management is simplified; there is referential integrity and less data duplication. Therefore, employees using the database shall observe the supplier’s details, transaction histories with the customers, and the available stock data. Therefore, the system shall be essential in solving all issues employees face within the firm. Patni et al. (2022).  Structured Query Language Scripts (Week 3) DDL DML Reporting Analysis of how project fulfills mission/goals of target organization Database Administration Plan (Week 4) Transaction Management Plan Database Security procedure Backup Plan and Recovery procedure Analysis of how project fulfills mission/goals of target organization Future Database System Implementation Plan (Week 5) Database Differences Changes needed to build a data warehouse/data mart. Distributed Database Considerations Business Intelligence Data Warehouse Benefits Return on Investment Competitive Advantage Increased Productivity Data Warehouse Problems Analysis of how project fulfills mission/goals of target organization References Choi, H., Lee, S., & Jeong, D. (2021). Forensic recovery of SQL server database: Practical approach. IEEE Access, 9, 14564-14575. https://ieeexplore.ieee.org/iel7/6287639/9312710/09328241.pdf Hayat, Z., & Soomro, T. R. (2018). Implementation of Microsoft SQL server using ‘AlwaysOn’for high availability and disaster recovery without shared storage. International Journal of Experiential Learning & Case Studies, 3(1), 09-17. https://www.academia.edu/download/56964676/2-CS.pdf Kilavo, H., Mrutu, S. I., & Dudu, R. G. (2021). Securing Relational Databases against Security Vulnerabilities: A Case of Microsoft SQL Server and PostgreSQL. Journal of Applied Security Research, 1-15. https://www.tandfonline.com/doi/pdf/10.1080/19361610.2021.2006032 Mukherjee, S. (2019). Indexes in Microsoft SQL Server. arXiv preprint arXiv:1903.08334. https://arxiv.org/pdf/1903.08334 Ma, L., Van Aken, D., Hefny, A., Mezerhane, G., Pavlo, A., & Gordon, G. J. (2018, May). Query-based workload forecasting for self-driving database management systems. In Proceedings of the 2018 International Conference on Management of Data (pp. 631-645). https://www.cs.cmu.edu/~malin199/publications/2018.forecasting.sigmod.pdf Patni, J. C., Sharma, H. K., Tomar, R., & Katal, A. (2022). Database Management System: An Evolutionary Approach. Chapman and Hall/CRC. https://www.taylorfrancis.com/books/mono/10.1201/9780429282843/database-management-system-jagdish-chandra-patni-hitesh-kumar-sharma-ravi-tomar-avita-katal Simon, A., & Kiszl, P. (2021). Aspects of the Long-Term Preservation of Digitized Catalogue Data: Analysis of the Databases of Integrated Collection Management Systems. Preservation, Digital Technology & Culture, 50(2), 51-64. https://www.degruyter.com/document/doi/10.1515/pdtc-2021-0007/html Zhang, H. (2020). Memory-E client Search Trees for Database Management Systems. http://reports-archive.adm.cs.cmu.edu/anon/anon/home/ftp/usr0/ftp/2020/CMU-CS-20-101.pdf
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 lang
JOE’S SPORTING CASE NAME: INSTITUTION COURSE NUMBER COURSE TITLE: INSTRUCTOR’S NAME: DUE DATE: JOE’S SPORTING CASE Each organization has different strengths and weaknesses that may determine its failure or success potential. Therefore for Joe’s company, we understand that it’s a high-performing organization that has been recently affected by increasing competition; additionally, Joe has ignored the issue of implementing a more effective database. Therefore my recommendation is that Joe considers implementing alternative options to act as the solutions. He can apply MySQL and MS Access to manage the database appropriately. Especially MySQL is an essential tool that will ensure effective data extraction and analysis to make informed decisions in business. He should avoid the current designer and seek refunds for a sketchy job from the current one. Blankenberg et al. (2022). There are numerous effects of lack of documents to all stakeholders within Joe’s company; firstly, there will be no informed decision-making. Processed data is usually used as a reference point when making decisions; however, decisions made may be wrong or invalid since there is no database. Additionally, it will not be possible to track the company’s financial improvements without documents. Therefore, stakeholders and investors cannot evaluate their investment’s outcomes and may sue Joe for poor transparency and accountability. There are numerous ways that technology will promote efficiency and quality of documentation within Joe’s company. Firstly, after implementing the SQL technology, the process of collections and development of business reports will be straightforward. Therefore a shorter time will be taken by employees when developing these reports. Additionally, the technology will be user-centered; therefore, employees without information technology expertise can use it effectively. In Joe’s situation, I would identify newer alternatives to address the documentation issues within the organization. Firstly would be through procuring MySQL software to manage the organization’s database seamlessly. Numerous organizations have used this program and have saved time and costs. Additionally, the organization’s information will be securely stored, preventing cyberattacks. Secondly, I would request the current designer for a refund since the process is often relatively expensive, the money would be spent on deploying the new service. Meqdad et al. (2020). I would apply the lesson learned by ensuring proper documentation within my organization. This is through having an effective database and data processing and reports generator system. The information processed would help me make better decisions. Secondly, I have to ensure that a designer deploys software that is related to my organization’s goals and objectives. The current designer at Joe’s company has deployed an un-integrating software, therefore, reducing his company’s competitiveness. Ohyver et al. (2019). References Blankenberg, C., Gebel-Sauer, B., & Schubert, P. (2022). Using a graph database for the ontology-based information integration of business objects from heterogeneous Business Information Systems. Procedia Computer Science, 196, 314-323. https://www.sciencedirect.com/science/article/pii/S1877050921022420 Meqdad, M. N., & Al Imari, M. H. O. (2020). US Department of Agriculture and Rural Development Dataset in MySQL Database Security: Using Sisense Tool. US Department of Agriculture, 7(08). https://www.researchgate.net/profile/Maytham-N-Meqdad/publication/343827202_US_Department_of_Agriculture_and_Rural_Development_Dataset_in_MySQL_Database_Security_Using_Sisense_Tool/links/5f438ca492851cd302224805/US-Department-of-Agriculture-and-Rural-Development-Dataset-in-MySQL-Database-Security-Using-Sisense-Tool.pdf Ohyver, M., Moniaga, J. V., Sungkawa, I., Subagyo, B. E., & Chandra, I. A. (2019). The comparison firebase real-time database and MySQL database performance using wilcoxon signed-rank test. Procedia Computer Science, 157, 396-405. https://www.sciencedirect.com/science/article/pii/S1877050919311500