Structured Query Language (SQL) statement scripts needed to create the database schema for the relational database system……………………………………………………………………………13 How Scripts Support Goals and Objectives of the Company…………………………...…………17

Database Administration Plan (TBD) 19

Future Database System Implementation Plan (TBD) 20

References 21

Database System Overview

At a time when information technology is integrated into every process of business management, it is only prudent to ensure that the chosen technology works properly and helps an organization to meets its strategic goals and objectives. Consumers have become increasingly aware of their value and want nothing but the best services. They also need convenience when they show for products. This explains why online shopping has become a significant trend today. In order for organizations to be able to offer their products online and to make sure that there is efficiency in the process, a proper database system needs to be developed. Online purchases mean high volume of information which have to be collected, maintained and modified. There is need to ensure that the database system has no errors. There should be consistence, accuracy and integrity when both customers and employees interact with the system. This will guarantee satisfaction on the part of customers as well as other stakeholders like employees and investors. This part of the database system development and implementation plan shall provide an overview of the database system. This shall be based on a scenario that shall be briefly presented while describing the general business environment of the selected store. The goals and objectives of the system shall be described and information on how the system shall address the presented problem shall be provided. Finally, this part shall be concluded by discussing how the system development and implementation is aligned with the goals and mission of the organization.

Description of General Business Environment

The retail store is the first store under an organization that designs and sells sports apparel. The products that are offered by the organization are meant to be used for sporting activities as well as for purposes of making a fashion statement. This means that the target population of the organization are individuals who not only want to keep fit but also look good and make a significant fashion stamen while at it. The products can be won while working out and also during casual events or for daily wear. This means that the age groups that are targeted by the organization range between those that are in their adolescence to individuals in their middle adulthood, that is between 35-65 years. This means that the targeted populations have not only access to smart device but also internet services. These are individuals that have come to appreciate the convenience that comes with shopping online. It is hard to ignore the fact that E-commerce has taken over the way many organizations operate and increase in social trust has significantly improved online economic activities and growth (Bucko, Kakalejcik & Ferencova, 2018).

The store has a total of 13 employees including the store manager, the customer service representative, cashiers and the product manager. As stated, the store operates in the sports apparel industry which is dominated by major brands such as Nike, Adidas, Puma, Fila and Umbro among others. The products offered by the store are quality products but they are sold at a cheaper price compared to those offered by major brands. This means that consumers who want affordable products are keen to buy from the store. The only challenge is that the existing system is poor in managing inventories, tracking orders and it fails to facilitate the adherence to store policies and procedures. This frustrates both employees and customers.

Database System Goals and Objectives

The database system that shall be developed and implemented is supposed to solve most of the problems that have been presented in the case study. As such, it is important to discuss the goals and objectives of the system. Under the goals, the database system shall allow customers to place their orders online and track them, help in proper and accurate management of inventory and to provide different payment options that customers can select when making the purchase. The goal of the database system shall also be to allow customers to see which products are out of stock and reserve them for when thy are available. In addition, the database system will set specific requirements for customers to qualify for discounts or free promotions. The objectives of the database system are as follows: To prevent errors in data management such as errors in data entry, inconsistency of data, update anomalies and variations in data views (Zygiaris, 2018); to improve adherence to store policies and procedures, to maximize on profit and cost effectiveness, to improve customer experience and satisfaction and to improve job satisfaction on the part of employees hence leading to decreased turnover rate. There is evidence to the effect that job satisfaction does not only increase performance of employees but also reduces their turnover rate (Li & Huang, 2020).

Addressing the Problems through the Database System

From the goals and objectives of the database system, it is clear that the prevailing problems shall be addressed in a manner that is sustainable. First, the system will ensure that there is no confusion in regard to availability of products in the store. Effective inventory management shall be achieved since the system shall automatically update the changes once a purchase has been made or more stock has arrived. The employees will have the ability to communicate effectively with customers and update them about their purchases by the touch of a few buttons. This means that the turnaround time in responding to queries shall improve significantly. The customers will have the ability to reserve their orders when the products that they want are out of stock. This means that they shall be prioritized ones the products are available. In addition, considering that their phone numbers shall be updated in the database, they will be notified through automatic texts that their products have arrived and they can proceed to purchase them.

Notably, since the convenience of the customers and their experience are prioritized by the organization, the proposed database system shall allow customers to choose how they want to make payments and this may be through the courier after delivery or through credit cards before delivery. Customers will have an option to return the products within a week and they shall be refunded, provided that they meet specific requirements for returning the products as stipulated in company policies. Finally, the concerns about employees’ failure to adhere to store policies and procedures while offering discounts and free promotions shall be stopped using the database system. Strict rules shall be set and the employees can only give discounts and free promotions to consumers that meet the required thresholds as shall be set in the database system. It is important to note that with a proper database management system as the one that shall be developed and presented in this project, organizations are able to use the data that is collected and stored to forecast and make important decisions regarding marketing, inventory management, and production among others. Differently put, this system shall be an important tool for problem-based learning because more data shall be available than when there was no proper database system. If a problem-based learning approach is needed to solve problems, there is always need to gather data and analyze the data with the view of finding a proper solution (Da Silva et al., 2018). This means that through the database management system, the store will have the opportunity to foster a learning culture.

Alignment with Organization’s Goals and Mission Statement

Whenever a project is implemented, it is always important to make sure that it is aligned with the goals and mission of the organization. This prevents a situation where the project becomes redundant and fails to have returns on investments. In this case, the strategic goals of the organization include to offer affordable and quality fashionable sports wear to all populations, to improve the shopping experience of customers, to become a major brand in the sports apparel industry and to make shopping for sports apparel more accessible and fun. On the other hand, the mission of the organization is to integrate fashion with sports while maintaining affordability, accessibility and cost effectiveness. It is without a doubt that the store is having challenges in meeting the strategic goals and the mission as stated. However, the database system has goals and objectives that are clearly aligned with the goals and mission of the organization. In fact, it suffices to say that they complement each other. Meeting the goals and objectives of the database management system in the short run will lead to the achievement of the mission and strategic goals of the organization in the long-run.

How this Part of the Project Fulfills the Mission and Goals of the Organization

This part of the project provided only the overview of the database system. By doing this, a clear picture of what needs to be achieved is seen and this allows for proper planning of the development and implementation of the project. The overview acts as the blueprint for the database. This creates an important opportunity to effectively execute the project in a manner that will bring success. Ultimately, the mission and strategic goals of the organization shall be properly met.

Entity Relationship Model (TBD)

Subjects of Interest (proposed entities)

Customers: the relationship between customers, employees, and the organization in general needs to be improved to ensure that each client can adapt to the organizational culture and that the organizational culture is also responsive to the needs of the customers (Chen, 2002).

Orders: Streamlining of orders is critical because it influences the capacity of the organization to undertake to stock for orders that are active and to restock products that are out-of-stock within the appropriate framework (Sisense Inc., 2021). A system that influences how the data can be managed should be adopted.

Products: Since products have different types of demand, it is essential to look at the orders with frequent demand and ensure they are in stock at any given moment.

Employees: coordination of assignments by employees is important since each employee is required to handle a specific assignment and create a more informed position on each assignment. This coordination helps them understand the status of orders, products in-stock, and the customers expected in the short-term, medium-term, and long-term.

Deliveries: The status of active and completed deliveries is essential in promoting the organization’s capacity while enabling the organization to build a strong image with its clients (CodeAcademy, 2021).

Front office communications: While clients may have to wait to be served, a cordial and understanding front office communication is essential. This can be extended to digital communications were confirmation of inquiries is handled to ensure that each element is handled effectively (Nicasio, 2018).

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.

An inquiry must receive a satisfactory response before the ticket number is closed

Internal communications must be seamless before it is delivered to external entities

Any employee can respond to an inquiry if they have proper information

Entity-Relationship Model

The entity-relationship model is referred to as a conceptual way of showing data relationships through software development (CodeAcademy, 2021). The database modeling technique is essential to demonstrate because it influences the process of generation of abstract diagrams as well as visual representations of system data that is used in developing a relational database.

Business rules that will be enforced by the proposed database system

· All pending orders must be processed within the day they are placed

· Goods that are out of stock must be removed from the database

· All goods on high demand must provide regular updates on remaining items in stock

· All items in stock must be available through digital verification

· Inventory must be entered into the database when they arrive at the store

· All inventory must be captured and warehoused before they are entered into the database

· All out-of-stock products must be entered into the system the moment they are confirmed by the store manager

· Employee queries must be sent to both front office staff and inventory officer as well as the store manager

· Responses must be verified before they are delivered to the prospective customer

List of the entities, attributes (including data types), relationships, and cardinality constraints

Entities: Office supplies, Furnishings, Shoes, Sports goods, household goods, bicycles, electrical goods, writing materials, and kitchen products

Attributes: name, date of manufacture, class, quantity, expiry, and manufacturer, numbers, structure, function

Relationships: customers, orders, products, employees, shipping, communications

Cardinality constraints: Many-to-many cardinality, one-to-many cardinality, many-to-one cardinality, and one-to-one cardinality

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 a third normal form (3NF), and if necessary, provide documentation and justification for the use of a higher level of normal form.

The data design is in 3NF because of two primary reasons. First and foremost, the relation R (or the table) is established in the Second Normal Form 2NF. Secondly, the non-prime attribute of R is non-transitively dependent on every key of R.

Fulfilling the mission and goals of the case study organization

The case study organization demonstrates that the general influence of the data is displayed within the context of collaborative engagement between the employees, the customers, and management should be essentially synchronized to ensure proactive adaptations of the process (Chen, 2002). The collective adaptations of the communication process are the first step in ensuring that trust between employees and customers is restored. Secondly, the understanding of the challenges facing the organization is critical in influencing the long-term rules that govern how different players should treat each other (CodeAcademy, 2021; Nicasio, 2018). It is essential to also look at how the relational database can solve some of these problems by creating a streamlined process and system. The mission of the organization is to sell needed goods on retail through a B2C business model. Its goal is to sell fast-moving goods and strengthen the capacity of the organization to serve its customers with distinction and efficiency. The collaboration between employees and customers through proper communication and provision of services creates significant strength in the long-term (Nicasio, 2018; CodeAcademy, 2021). The organization’s capacity relies on its ability to adapt to the needs of the present environment by building a strong and effective relational database with the capacity for long-term growth.

Structured Query Language (SQL) Scripts (TBD)

Database Administration Plan (TBD)

Future Database System Implementation Plan (TBD)

References

Bucko, J., Kakalejčík, L., & Ferencová, M. (2018). Online shopping: Factors that affect consumer purchasing behaviour. Cogent Business & Management, 5(1), 1535751.

Da Silva, A. B., de Araújo Bispo, A. C. K., Rodriguez, D. G., & Vasquez, F. I. F. (2018). Problem-based learning. Revista de Gestão (25)2. Retrieved from https://www.emerald.com/insight/content/doi/10.1108/REGE-03-2018-030/full/html

Chen, P. (2002). Entity-Relationship Modeling: Historical Events, Future Trends, and Lessons Learned. Software pioneers. Springer-Verlag, 296–310.

CodeAcademy. (2021). What is a Relational Database Management System? Retrieved from Codeacademy: https://www.codecademy.com/articles/what-is-rdbms-sql

Guru99. (2021). MySQL Create Table | How to Create Database in MySQL. Retrieved from Guru99: https://www.guru99.com/how-to-create-a-database.html

Ian. (2016, May 30). How to Create a Database from a Script in MySQL. Retrieved from Database. Guide: https://database.guide/how-to-create-a-database-from-a-script-in-mysql/

Lin, C. Y., & Huang, C. K. (2020). Employee turnover intentions and job performance from a planned change: the effects of an organizational learning culture and job satisfaction. International Journal of Manpower

Morina, F. (2018, August 6). How to create database schemas quickly and intuitively with DBDesigner. Retrieved from FreeCodeCamp: https://www.freecodecamp.org/news/how-to-create-database-schemas-quickly-and-intuitively-with-dbdesigner-2f4adf79a29d/

Nicasio, F. (2018, February 14). 12 Examples of Retail Stores That Will Inspire You to Run Your Business Better. Retrieved from Vend: https://www.vendhq.com/blog/retail-examples-to-inspire-you-to-run-a-better-business/

Oracle Corporation. (2021). 9.3.1 Creating a Model. Retrieved from MySQL: https://dev.mysql.com/doc/workbench/en/wb-getting-started-tutorial-creating-a-model.html

Sisense Inc. (2021). What is a Relational Database Management System? Retrieved from Sisense: https://www.sisense.com/glossary/relational-database/

Zygiaris, S. (2018). Database Management Systems: A Business-Oriented Approach Using ORACLE, MySQL and MS Access. Emerald Group Publishing