The retail store is a firm that deals with the sale of smaller quantities of goods and services to the general public. The store is dealing with electrical and electronic equipment and devices, and it purchases these goods in large numbers from manufacturers or wholesalers and splits them into smaller quantities available and affordable to the general public. In the scenario presented, the retail store has been transacting manually with customers being required to visit the store personally. Customers pay for goods and services on cash on the Point of Sale, and the cashiers receive the money on cash. The retail store has a simple website meant primarily for advertisement and its database does not support the online transaction. The store can sell to those people who are nearby, thus limiting the store’s market (Kabat, Cline, Li, Ha, & Stathakis, 2016).

The customers are appreciating for the products and services being provided to them by the retail store but the problem is the inconvenience in service delivery. A typical scenario that presents a sense of disadvantage is the long queue that the customs need to follow when they are being served. On the other hand, the servicemen in the store are facing difficulty in handling many such people in the queue as the customer is impatient and each need to be served quickly. The management is facing challenges in working through paperwork as 90 % of the business transaction is done manually on papers – records of the transactions are kept on non – electronic files that will eventually call for manual audits. The retail store is, therefore, considering building an electronic database that integrates with the store’s website.

Mission Statement. The retail store exists at the outcast the city to provide high quality, genuine, and affordable electrical and electronic types of equipment and devices to the general public. The primary customers of the organization are electronics users including building and construction industries, computerized offices, and schools.

The Database System Goals and Objective Objectives

Goals and objective drive every project in business. Establish a plan without aim and achievements is a critical factor to failure for this two is the key factors in a business that needs to realize huge profits.

Goals of database system plan. The following goals power the development and implementation plan of the new database system for the retail store.

a. To increase the market for the retail store by making people far from the firm be able to buy goods and services online.

b. To increase efficiency in customer service as the online system is known best for serving many people at ones.

c. To correctly account for the business transaction, more so monetary transaction that results in the trustworthiness of every transaction made by the users.

Objectives of database system plan . These goals will be realized in ore the operation of the retail store utilizing the following objectives.

a. To ensure that in one day a person or people leaving far from the store gets access to the on the online basis.

b. To ensure that the database system serves 20 people requesting for the same service in 1 second – concurrent processing of data.

c. To automate the calculation of daily transaction instantly whenever a transaction is made and generate the report for the day transaction

How this database system addresses business problems

According to (Watts, Lawrence, Drawz, Carter, Shumaker, & Kern, 2016) development of database system will bring automation to business activities. The result of automation is manifested in faster and accurate transaction. This is going to solve the retail store's business problem in the following ways.

Better customer service . The developed database system will be integrated into the store's website. The website will be transformed from performing advertisement into an online shopping cart in order to leverage the database system. The customer will be making orders by adding their goods and service to the cart and submit the order to store's database. The customers are also able to make payment online as the database will have payment integration with monetary industries. This will greatly assist the store serving the high number of customers currently shopping in the store.

Increase in the market for the store . The integration of the new database with the old database system and connecting it to the website will popularize the store. The products and services for the store will be accessed globally hence increasing the market for the store. It will also save customer spare time that they could have used in visiting the store to effect transaction

The accuracy of transaction processing and proper record keeping . The connection of database system to store’s website will accurately account for orders and manual sales details. This will reduce errors made in the manual transaction. Implementation of the database system using SQL database programming will be utilizing programming functionalities such auto sum and other database functions. The database system will provide a means for electronic filing system the will help the store realize proper record keeping and easy retrieval of information whenever need by simply querying the database. This will saves time for monetary computation if this would have been done manually

How the proposed system is in line with the mission statement and organizations strategic goals

Many customers are in need of well-customized services – faster, convenience, and cheap services to cut off their expenses. In order to make development and implementation of the proposed system aligned to organization’s mission statements and objectives must be accomplished (Kratz, Thomas, Hora, Vera, Lutz, & D Johnson, 2017).

User requirements analysis. The users of the system are the stakeholders and the customers. The user requirements were analyzed to see what the stakeholders and customers need off the system before it is designed and implemented.

System analysis . The proposed database system was analyzed to ensure the system will meet that identified user requirements.

System design . A suitable system design methodology and engineering model is chosen. The development of the system will best accomplish using iterative software engineering techniques.

Implementation. The database system will be constructed using Structured Query Language (SQL) as required by the stakeholders.

System testing and maintenance . The implemented database system will be tested using customers and stakeholders test data to ensure user requirements are satisfied. Errors will be identified and removed, and the system documentation will be produced. Finally, user requirements are continuously analyzed to see if there are new changes. In the case of the change in user requirement, the system is upgraded to accommodate for changes during the system's lifetime (Kabat et al., 2016)

Mission and goals of the case study and analysis of fulfillment of the organization's mission and goals

Goals	Critical Success Factor
To accurate account for the business transaction and record keeping (100 %)	To develop error-free database system using the stable programming language
To increase the market for retail store by 15 %	Development of online shopping cart connected to store’s database
To improve efficiency in customers service (99 %)	Incorporation of concurrent programs in the database system
To automate the calculation of daily transaction (100 %)	Incorporating automatic subsystem in the database system

Table 1 Goal and Critical Success Factors.

Quantitative and Qualitative Analysis of the Organization’s goals and Critical Success Factors

Goals	Quantitative / Qualitative Measurement	Data Source
To accurately account for the business transaction	Time taken retrieve a file in the electronic database as compared with manual retrieval (Quantitative Variable) The ease of making an order for goods and services online as compared to visiting the store in person (Qualitative Variable)	Customers using the online shopping cart (Database) Employs of the retail store (Database)
To increase the market for retail store	Baseline count of customers using online shopping cart compared to count of customers purchasing goods and services manually (Quantitative Variable)	Customers using the online shopping cart(Database) and Customers purchasing manually (Paper files)
To automate the calculation of daily transaction	The sum of transaction done online in an hour compared to the amount of transaction done manually (Quantitative Variable)	Customers purchasing manually (Paper files) Customers using the online shopping cart(Database) and
To increase efficiency in customers service	Baseline count of customers using online shopping cart compared to count of customers purchasing goods and services manually (Quantitative Measure)	Customers using the online shopping cart(Database) and customers purchasing manually (Paper files)

Table 2 Quantitative and Qualitative Analysis of the Organization's goals and Critical Success Factors.

Entity-Relationship Model

An entity relationship diagram will be drawn to depict the relationship between the entities of the database system. It shows a clear picture of how tables in the database are relating and show entities alongside their attributes and values.

Structured Query Language (SQL) Scripts

SQL command line will be used to write Structured Query Language scripts that will be used in creating and populating tables in the SQL database of the system. Populating the tables of the database using SQL command line will be possible because SQL is a relational database.

Database Administration Plan

According to (Kabat et al., 2016) database administration plan is a guide to database administrators. This document will be written by the development team to document the functionality of the database. The administrators of the database will refer to this document whenever they experience a challenge when monitoring the database.

Future Database System Implementation Plan

This document will be written after implementation of the database system to explain in details about the possibilities of its expansion. The document should document the process of expanding the database to accommodate future changes in user requirements. It also states new technologies and the environment in which the existing database will be implemented in the future (Watts et al., 2016).

The Database Proposed Entities

· Customers

· Orders

· Products

· Department

· Stock Movement

· Category

· Stock

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

There are data which cannot be presented without constraint in the database model. The database rues will specify which data types will be required and which types of data cannot be allowed in the database. Each table has columns with the data types specified, for example in the employee table, the employee ID should be unique and such values are important in the logical statements.

The database has also been designed to ensure integrity entity in mind. Every table has a primary key and if possible a related foreign key. Neither part of the primary key or foreign Keys can have null values because they can be used in identifying rows. In the Inventory table and the order table, the item ID cannot have a null value because it will be used in cross references.

Referential integrity

Referential integrity is ensured when every table has a primary key. Neither the primary key nor any part of the primary key will contain any null values. When there are null values in the primary key, it means that some rows cannot be identified. The referential constraint is specified between the two tables with parent –child relationship. The relationship is important in maintaining the correspondence between the tables. All references rows must therefore have valid data. In order to ensure that there is no orphan data in the database, it is very important to reinforce inferential integrity in the database. To allow joins and complex queries, there should be no orphan in the database. When the tables are created, there is a referential integrity which are set up.

Enterprise constraints

These constraints are also called enterprise constraints. These are the additional rules which can be used by different database administrators and can be based on additional tables. Order table can have a maximum of history of two years. Inventory will also keep track of the orders which are not older 4 years to give up spaces for the additional data.

The following deliverables will be achieved by the database system

The database will have tables with their relations and the database has been designed using access as a software. The relational data modeling was chosen because of the ease of use and the hierarchical model became not fit for the design. The database design has been made to ensure that the minimal storage is used by the database and it should not exceed the 300MB disk space required for the database. The database will have the staff members included and it has not been possible to have different operations because it was possible to have the entities combined into a single entity. During the database construction, no partition was were performed for the tables.

The relational model will have the following Entities:

The following figure shows the pictorial Relations model.

ER Diagram for the above Database

Data Dictionary

Table Name	Column Name	Col_Datatype	ColumnEnglishName	PK	FK
Department Orders	emp_id	int	Empliyee ID	Y
	item_id	int	Item ID		Y
	depatment	varchar(100)	Deparment where the Item was
	order	varchar(100)	Order id
	issue	varchar(100)	Isue ID
	date_ordred	varchar(100)	The date orf order
Stock Movement	StockID	int	Mailing Address of Point of Contact	Y
	Reference	varchar(100)	Telephone Number of Point of Contact		Y
	Exp_date	date	Electronic Address for Report
	opening_balance	varchar(100)	Full Name of Point of Contact
	Issues	varchar(100)	Title of Point of Contact
	Closing_balance	varchar(100)	Organization of Point of Contact
	Issuing_officer	varchar(100)	Mailing Address of Point of Contact
	Service_point	varchar(100)	Telephone Number of Point of Contact
Order Inventory	batch_no	varchar(100)	Making a FOIA Request	Y
	name	varchar(100)	Acronyms
	units	int	Served
	pack_size	varchar(100)	Basic Terms
	quantity	varchar(100)	Quantity
	available_quantity	varchar(100)	Available_Quantity
	price	double	Price
	supplier	varchar(100)	Supplier
Items Movement	Item ID	int	Item Id	Y
	Date_of_issue	varchar(100)	Date_Of_Issue
	references	varchar(100)	References
	exp_date	date	Exp_Date
	opening_balance	int	Opening_Balance
	isuing_officer	varchar(100)	Isuing_Officer
	Service_point	varchar(100)	Service_Point
		varchar(100)
Inventory	code	int	Code	Y
	inventory_name	varchar(100)	Inventory_Name
	note	varchar(100)	Note
	unit	int	Unit
	price	double	Price
	buying_price	double	Buying_Price
	stock_in_hand	int	Stock_In_Hand
	min_amount	int	Min_Amount
	exp_date	date	Exp_Date
	batch_no.	int	Batch_No.

How the database adhere to the third Normal Form

For a table to be on the third normal form, it must be in the second normal form. The table must serve a single purpose. In second normal form, the might be dependencies between the columns that may course inconsistency of data. The tables presented above are therefore on their third normal forms, because it contains the columns that are non-transitively dependent on the primary key.

There was need for redesigning the database, the customer would make an order and wait for a long time before the employees could respond to the phone, tis meant that there was more work necessary to complete a single order on the side of the customer than required, the company need to increase the sales of the inventory and fast track the orders, that is why the orders have been put online for prompt customer feedback. The main aim of the design is to give company employees enough time to do other work than responding to customer requests, this will be done online using the website based system. With the new system requiring new database, the current system has no order department neither order categories, it is therefore very hard to categorize the orders. The new system will also allow the clients to customize their own orders by picking the different types of goods themselves.

The database provided provides a better design and ensures that every order is fast tracked, giving the employees time to concentrate in the ordering systems, with the customer data also captured, it will give the users more time to make complete their orders. The system also allows the orders

ERD diagram

References

Elmasri, R., & Navathe, S. (2010). Fundamentals of database systems. Addison-Wesley Publishing Company.

Batini, C., Ceri, S., & Navathe, S. B. (1992). Conceptual database design: an Entity-relationship approach (Vol. 116). Redwood City, CA: Benjamin/Cummings.

Suarez, M. J & Stajner, I. (2008). The GEOS-5 Data Assimilation System-Documentation of Versions.

Tolman, H. L. (2009). User manual and system documentation of WAVEWATCH III TM version 3.14. Technical note, MMAB Contribution.