Report On Sales Process For ABC Shop: User And System Requirements, Relational Database System Design, And Effectiveness Assessment

User and Systems Requirements

The database can be considered as the organized set of data, conventionally recorded and retrieved digitally from the Information System. The database management system or DBMS is the technology that allows the user to organize the database and manipulate it as per need. The normalization allows the database structure to be re-design and create a more stable solution that can prevent the database from creating any anomaly. The input and output design are created in the form of the interfaces of the system.

The report is based on the sales process of ABC Shop. The organization has an e-retail store and have B2C and C2C e-commerce models. The sales executive of ABC Shop can easily collect the data from the mobile device. The data can be product, sales or marketing related. The main concentration of the report is on the retail system as it will be the base of every internal operations. The sales staff will also communicate with the customers through this retail system. The organization sales various types of products like big name in the retail such as Amazon, eBay and many more. The customers of the organization buys product from the mobile application directly. The customers can also sale products to other customers but the customer must work as a retailer. Each product sold by the customer must be available in at least in terms of quantity. The organization handles the process of delivering the product. The organization has its own warehouse and various other retailers can buy products from ABC shop in bulk. The database attached to the system store every data such as retailer, manufacturer, warehouse, product, customer and more.

The report consist of system and user requirements. These requirements shows the structure of the system and what should be integrated into the system. Based on the findings in the system requirements and user stories, the database and interface has been designed.

The system requirements can be categorized into two categories such as functional and non-functional requirements. Both the functional and non-functional requirements are vital for the system. It is because if functional requirement states what the system will do then the non-functional requirement will describe the quality of performance of the system. The functional requirements of the system are as following.

Business Rules: The business rules serves as the key of maintaining the business operations. The first business rule will be a customer can purchase and sell products through the application. Each of the purchase and sales data will be stored differently. Another business rules is that a product can be provided by a single manufacturer. A customer can purchase many products. No two or more customers can purchase products together.

Authentication: Authentication is the process of checking whether the user has permission to enter into the system. The authentication is done through checking the user id and password provided at the time of login. The user sets the user Id and password during the registration process. Each customer will have different user id which will act as the primary key of the customer.

Design Documentation

Authorization: The authorization is also checking the user privileges but it is done to check whether the user request is valid or not. The authorization is done after the customer is logged in. Taken as an example, the system will not show the customers list of products bought by another customer. However, the list can be seen by the sales team.

Database Storage: The system will store the data into a centralized database that will be accessed by the sales team also. The database will hold the data of the customer, staff, product, manufacturer, order and many more. The sales team will be accessing the data to access the customer data and their purchase history. The system will collect the data and process then to generate various patterns.

Reporting: The system will generate various reports like sales report, purchase report, customer feedback report and more. All the reports will be shared with the management and sales team automatically. The reports can be downloaded manually by the sales team.

Audit: The system audit is essential for maintaining the quality of the system. The audit will be done automatically under the supervision system administrator.

The non-functional requirements of the system are as following.

Security: The security is an essential part of the system. The system will be used by the customers to buy products and make payment. The system will secure the payment details of the customer.

Usability: The system will have a very high usability requirement. As it is a retail application, the customer must be able to use the system very easily and learn new items of the application very quickly.

Real-Time: The application will run the data in real-time within the organization. The sales team will be able to see as soon as a product is ordered or purchased. The real-time data flow happens throughout the system and before the data is stored in the system, it should be visible to user.

The user stories are as following.

1. As a customer I want to login to the system so that I can track my purchase and other activities
2. As an administrator I want the system to register customer before giving the login id and password so that we can track their  data
3. As a customer I want to purchase product from the application so that I do not need go to other website to purchase the product
4. As a customer I want to add the product to cart so that I can select various products at a time to buy more than one product together
5. As a customer want the system to allow me making payment through it so that I do not have to wait more time for making payment.
6. AS a sales team we want the system to allow me downloading reports so that we can track progress

Normalization Decisions: The sales database created has been normalized up to BCNF or Boyce Code Normal Form, 1NF or the first normal form of the normalization proposes that an each of the entity of the database must have atomic value for every column. Each of the table must have a unique identifier that can be used as the primary key. The second normal form states that the no non-key column should be reliant on the primary key. Each of the entity of a database in the second normal form must have a primary key. Each of the other attributes in that table must be fully dependent on the primary key (or composite primary key). The third normal form is done for removing transitive dependency from the database tables. The transitive dependency states that if an entity has x, y and z attributes and y is dependent on x, z is dependent on y but y is not dependent on x then three columns has transitive dependency among them.

First Normal Form: The following table has all the values in the database in a same table. As per the description of the table, it is in the first normal form. However, if multiple products are bought in the same order then product attributes will have multiple values.

retailerNumber	11111
retailerName	ABC Shop
retailerAddress	5 Delan Road, BOOLBOONDA QLD 4671
retailerTelephoneNumber	123456789
maximumCredit	17000.00
retailJoinDate	29/11/2018
staffName	Will Virtue
staffAddress	76 Dabinett Road, CAURNAMONT SA 5238
staffPosition	1234509876
staffSalary	3500.00
staffJoinDate	25/12/2016
unitPrice	1256.50
productQuantity	1
totalPrice	1256.50
orderDate	25/12/2018
totalCost	1256.50
manufacturerName	Will Virtue
manufacturerAddress	75 Plug Street, FALCONER NSW 236
manufacturerTelephoneNumber	1234509876

productNumber	11111
productName	Samsung Ultra HD 40’’
productType	Smart TV
productPrice	$1265.50
availableQuantity	100

Second Normal Form: The staff attributes depend on the staffNumber. This prevents the database tables to be in second normal form. The table can be separated into two tables. The tables are staff, manufacturer and retail table.

staffNumber	11111
staffName	Will Virtue
staffAddress	76 Dabinett Road, CAURNAMONT SA 5238
staffPosition	1234509876
staffSalary	3500.00
staffJoinDate	25/12/2016

As mentioned above, the manufacturerNumber can be used for searching the attributes like manufacturerName, manufacturerAddress and manufacturerTelephoneNumber.

manufacturerNumber	11111
manufacturerName	Will Virtue
manufacturerAddress	75 Plug Street, FALCONER NSW 236
manufacturerTelephoneNumber	1234509876

The other two tables are as below.

retailerNumber	11111
retailerName	ABC Shop
retailerAddress	69 Junction St, CONARGO NSW 2710
retailerTelephoneNumber	123456789
maximumCredit	13500.00
retailJoinDate	29/11/2018
orderNumber	11111
unitPrice	1256.50
productQuantity	3
totalPrice	3702.70
orderDate	25/12/2018
totalCost	3702.70

productNumber	11111
productName	Samsung Ultra HD 40’’
productType	Smart TV
productPrice	$1265.50
availableQuantity	100

Third Normal Form: The product attributes are dependent on the product number. That is why in the new product table, the productNumber is considered as the primary key.

productNumber	11111
orderNumber	11111
productQuantity	3
totalPrice	3702.70

The new table is as following.

retailerNumber	11111
retailerName	ABC Shop
retailerAddress	69 Junction St, CONARGO NSW 27106
retailerTelephoneNumber	123456789
maximumCredit	13500.00
retailJoinDate	29/11/2018
orderNumber	11111
orderDate	25/12/2018
totalCost	3702.70

BCNF: the orderNumber and retailerNumber has trivial functional dependency among them. And retailerNumber is the super key.

retailerNumber	11111
retailerName	ABC Shop
retailerAddress	69 Junction St, CONARGO NSW 27106
retailerTelephoneNumber	123456789
maximumCredit	13500.00
retailJoinDate	29/11/2018

orderNumber	11111
orderDate	25/12/2018
totalCost	3702.70

productNumber	11111
productName	Samsung Ultra HD 40’’
productType	Smart TV
productPrice	$1265.50
availableQuantity	100

Final Database Tables:

retailerNumber	11111
retailerName	ABC Shop
retailerAddress	69 Junction St, CONARGO NSW 27106
retailerTelephoneNumber	123456789
maximumCredit	13500.00
retailJoinDate	29/11/2018

orderNumber	11111
orderDate	25/12/2018
totalCost	3702.70

productNumber	11111
productType	Samsung Smart TV 40’’
unitPrice	1256.50

productNumber	11111
orderNumber	11111
productQuantity	3
totalPrice	3702.70

manufacturerNumber	11111
manufacturerName	Will Virtue
manufacturerAddress	75 Plug Street, FALCONER NSW 236
manufacturerTelephoneNumber	1234509876

staffNumber	11111
staffName	Will Virtue
staffAddress	75 Plug Street, FALCONER NSW 236
staffPosition	1234509876
staffSalary	3500.00
staffJoinDate	25/12/2016

productNumber	11111
productName	Samsung Ultra HD 40’’
productType	Smart TV
productPrice	$1265.50
availableQuantity	100

2 Table Name: Retailer
Attribute	Data Type	Data Format	Field Size	Description	Example
retailerNumber	INT	NNNNN	5	It is the unique attribute in the entity which defines every row of the table individually	11111
retailerName	Varchar		40	This attribute defines the name of the retailer. It is possible that two retailer has the same name	ABC Shop
retailerAddress	Varchar		40	The location address of the retailer store or headquarter. The address will consist of street, city, state and zip	69 Junction St, CONARGO NSW 2710
retailerTelephoneNumber	INT	NNNNN	10	The contact number of the retailer. The code will be identified based on the state mentioned in address	123456789
maximumCredit	Decimal	NNNN.NN	10,2	This attribute defines the maximum price which the retailer will allowed to purchase without paying the cash instantly	13500.00
joinDate	Date	DD/MM/YY	Default	This attribute defines on which day the retailer was registered with the business	29/11/2018

Table Name: Staff
Attribute	Data Type	Data Format	Field Size	Description	Example
staffNumber	INT	NNNNN	5	It is the unique attribute in the entity which defines every row of the table individually	11111
staffName	Varchar		40	This attribute defines the employee name. This name will be consist of first, middle and last name	Will Virtue
staffAddress	Varchar		40	This attribute defines the location address of the employee. The address will consist of street, city, state and zip.	75 Plug Street, FALCONER NSW 236
staffPosition	Varchar		10	This attribute defines in what position the employee works or its designation	1234509876
staffSalary	Decimal	NNNN.NN	10,2	This attribute defines the how much salary the employee gets	3500.00
joinDate	Date	DD/MM/YY	Default	This attribute defines from which day the employee started working with the retailer	25/12/2016

Table Name: Order
Attribute	Data Type	Data Format	Field Size	Description	Example
orderNumber	INT	NNNNN	5	It is the unique attribute in the entity which defines every row of the table individually	11111
orderDate	Date		40	This attribute defines on which day the order was made	25/12/2018
productNumber	INT	NNNNN	40	This attribute defines the product primary key. It is the foreign key referring the product entity.	11111
productQuantity	INT	NNNNN	5	This attribute defines the amount of product bought in the order.	3
totalPrice	Decimal	NNNN.NN	10,2	This attribute defines the price of product multiplied by amount	3702.70
totalCost	Decimal	NNNN.NN	10,2	This attribute defines the cost of the order based on tax, delivery and other factors	3702.70

Table Name: Manufacturer
Attribute	Data Type	Data Format	Field Size	Description	Example
manufacturerNumber	INT	NNNNN	5	It is the unique attribute in the entity which defines every row of the table individually	11111
manufacturerName	Varchar		40	This attribute defines the name of the manufacturer. No two manufacturer can have the same name as these names are copyrighted.	Will Virtue
manufacturerAddress	Varchar		40	This attribute defines the location address of the manufacturer headquarter. The address will consist of street, city, state and zip	75 Plug Street, FALCONER NSW 236
manufacturerTelephoneNumber	INT		5	This attribute defines the contact number of the manufacturer headquarter	1234509876

Table Name: Product
Attribute	Data Type	Data Format	Field Size	Description	Example
productNumber	INT	NNNNN	5	It is the unique attribute in the entity which defines every row of the table individually	11111
productName	Varchar		40	This attribute defines the name of the product which the organisation is currently selling.	Samsung Ultra HD 40’’
productType	Varchar		40	This attribute defines the category of the product	Smart TV
productPrice	Decimal	NNNN.NN	10,2	This attribute defines the cost of the product. The product can be altered as per the quantity available or other factors	$1265.50
availableQuantity	int	NNNNN	5	This attribute defines the amount of products available in the stock	100

Getting information from the customers is essential part of a system. Collecting customer data and then storing each activity of the customer is vital for sales processes. The sales department will collect the customer data and purchase data to determine various sales patterns which can assist in future business expansion. The registration interface has made it possible for the system to collect the customer data for once and use it by the sales team as much as they want. The login is for recognizing the customer and store the purchase details for that specific customer. As the usability is a very vital aspect of interface, the settings page has been considered to be a part of the interface. This shows how the system will allow the customer to adjust the usability settings.

The designs of the interface has been done by following the conventional interface designing methods. Each of the interface has similar design which makes the interface much more effective in terms of consistency in UI design. The design has total nine pages of the mobile application which the customer will be using. The customer will open the application and the home page will appear on the screen. The slide show in the home page will show various products with offers and other initiatives by the ABC shop. This will be very effective in connecting the customer with the application. The sales team can continuously harvest the sales and customer need related data from the database as the mobile application will store the customer buying history and comments in the central database. The design shows that the customer can see the products that are on offer. The customer can access all the products through the same page. This will not increase only the interaction with the system but also enhance the quality of the content. The interface shows the customer can also provide the details of the products they want to sell. This makes the interface more convenient to the business requirements. The interface for product sale shows that the customer have to upload the picture of the product they want to sell. This makes sense as the website will present the product along with a picture. This picture will be the key for attracting the customer interest. The interface also shows the menu page. The customer can navigate through the application using the menu page.

Adrienne Watt and Watt, A., 2014. Database Design – 2nd Edition. 2nd ed. s.l.:BCampus.

Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., Motwani, R., Srivastava, U. and Widom, J., 2016. Stream: The stanford data stream management system. In Data Stream Management (pp. 317-336). Springer, Berlin, Heidelberg.

Batkovskiy, A.M., Batkovskiy, M.A., Semenova, E.G., Fomina, A.V. and Khrustalev, ?.I., 2015. Linguistic Analysis of High-Tech Production Complex. Mediterranean Journal of Social Sciences, 6(4), p.130.

Blackman, K. R., 1998. Technical note: IMS celebrates thirty years as an IBM product. IBM Systems Journal, 37(4), pp. 596-603.

Chambers, M. L., 2009. Build Your Own PC Do-It-Yourself For Dummies. Hoboken: For Dummies.

Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.

Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.

Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.

Gouhar, A., 2017. Database Management System. International Journal of Engineering Science, 11766.

Guèye, M., Ndiaye-Guèye, M.D., Kane-Guèye, S.M., Diallo, M., Cissé, M., Fall, K., Daff, H.M.B., Abdoulaye, M., Gahungu, S., Ka, S. and Moreau, J.C., 2016. E_Breast: A Computerized Database Management System for Breast Diseases Patients in a Low Income Country. Journal of Obstetrics and Gynecology, 6, pp.754-760.

Haigh, T., 2016. How Charles Bachman Invented the DBMS, a Foundation of Our Digital World. Communications of the ACM, 59(7), pp. 25-30.

Hoffer, J., Venkataraman, R. & Topi, H., 2015. Modern Database Management. Global Edition ed. s.l.:Pearson Education Limited.

Jamal, A. and Pramila, R.S., 2018. An Overlay Database Management System. network, 5(05).

Jamsheer, K., 2018. An overview of SQL and NoSQL with it’s pros and cons. [Online]
Available at: https://acodez.in/sql-and-nosql-an-overview/#Disadvantages_of_SQL
[Accessed 31 August 2018].

Laudon, K. C. & Laudon, J. P., 2017. Management information systems managing the digital firm Global. Harlow: Pearson Global edition.

Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson Education India.

Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., Geschwind, J.F. and Lin, M., 2015. How I do it: a practical database management system to assist clinical research teams with data collection, organization, and reporting. Academic radiology, 22(4), pp.527-533.

MariaDB, n.d. Understanding the Network Database Model. [Online]
Available at: https://mariadb.com/kb/en/library/understanding-the-network-database-model/
[Accessed 29 August 2018].

Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible highly available and scalable database management system. FernUniversität, Fakultät für Mathematik und Informatik.

Pokorny, J., 2013. NoSQL databases: a step to database scalability in web environments. International Journal of Web Information Systems, 9(1), pp. 69-78.

Powell, G., 2006. Beginning Database Design. Indianapolis: Wiley Publishing Inc..

Rai, D. P. K. & Singh, P., 2015. International Journal of Computer Science and Mobile Computing. A Monthly Journal of Computer Science and Information Technology, 4(5), p. 834 – 838.

Raza, B., Kumar, Y.J., Malik, A.K., Anjum, A. and Faheem, M., 2018. Performance prediction and adaptation for database management system workload using case-based reasoning approach. Information Systems, 76, pp.46-58.

Reddy, T.B., Thomas, A.D., Stamatis, D., Bertsch, J., Isbandi, M., Jansson, J., Mallajosyula, J., Pagani, I., Lobos, E.A. and Kyrpides, N.C., 2014. The Genomes OnLine Database (GOLD) v. 5: a metadata management system based on a four level (meta) genome project classification. Nucleic acids research, 43(D1), pp.D1099-D1106.

Savage, N., 2015. Forging Relationships. Communications of the ACM, 58(6), pp. 22-23.

Shroff, R., 2010. Computer Systems and Applications. Rev ed. ed. Mumbai: Himalaya Publishing House.

Souza, R., Silva, V., Oliveira, D., Valduriez, P., Lima, A.A. and Mattoso, M., 2015. Parallel execution of workflows driven by a distributed database management system. In ACM/IEEE Conference on Supercomputing, Poster.

Tamboli, A., Shinde, P., Pariskar, P., Sonawane, M. and Anaokar, C., 2017. Institute Administration Automation and Student Database Management System.

Teorey, T. L. S. N. T. &. J. H., 2011. Database Modeling and Design : Logical Design. San Francisco: Elsevier Science & Technology.

Thakur, D., n.d. Network Model. [Online]
Available at: https://ecomputernotes.com/fundamental/what-is-a-database/network-model
[Accessed 30 August 2018].

UKEssays, 2013. The Hierarchical Model. [Online]
Available at: https://www.ukessays.com/essays/information-technology/hierarchical-data-model.php
[Accessed 29 August 2018].

Ung, M. & Philip, K. a. C.-Y.-S., 1999. DATABASE LANGUAGES. Encyclopedia Of Electrical & Electronics Engineering, Volume 4, pp. 568-576.

Zhang, B., Van Aken, D., Wang, J., Dai, T., Jiang, S., Lao, J., Sheng, S., Pavlo, A. and Gordon, G.J., 2018. A Demonstration of the OtterTune Automatic Database Management System Tuning Service. Proceedings of the VLDB Endowment, 11(12).