Project Deliverable 4: Infrastructure and Security

10

PROJECT DELIVERABLE 3: DATABASE AND PROGRAMMING

Project Deliverable 3: Database and Programming

Section 1: Design Document

February 14, 2021

Running Head: PROJECT DELIVERABLE 3: DATABASE AND PROGRAMMING

E-Commerce for a Startup Company Business Database and Programming

Section 1: Design Document

Company Database Schema

A database schema is a representation of the entire database’s logical view. Therefore, this defines a way of organizing data and the association of their relations. In addition, it also stipulates the entities of the schema itself and the relationship created in them (Batini et al, 1986).

A database schema is categorized into two, that is:

· Logical database schema. Views, integrity, and tables constraints are defined in this schema. This is achieved when all the logical constraints applied on the data stored are defined.

· Physical database schema. The way in which data is stored in any secondary storage is defined in this schema. This is achieved by the data actual storage and storage forms such as indices and files.

A well-designed database for the storage data of users is needed for this system. This is greatly achieved when the database meets the specifications of the users to make sure that there is proper storage of the same data (Batini et al, 1986).

In addition, a strong query language was used in designing the database in this project, which was achieved by applying a standard language, for instance, the Structured Query Language (SQL). MYSQL is also used in the database to simplify data definition and manipulation. The database implemented also allows multiple access to the data by the users at the same time (Batini et al, 1986).

The following figure shows the database schema for the business and processes of the company:

Figure 1: E-Commerce Database Schema Diagram

In the above schema, relationships, indexes, views, fields, and tables are clearly depicted to demonstrate how database effectively stores users’ data.

In this database schema, we have various tables which includes countries, merchants, products, orders, order items and users. Users in this case includes all persons who interact with the system especially customers and there are various fields that identify and describes them, which includes their email address, gender, country code to determine their country of origin, date of birth and a unique identification know as a primary key that is used by the system to uniquely identify each system in the database (Gómez-Pérez et al, 2006).

Furthermore, the tables in this schema are linked according to their relationship with each. For instance, users’ table is linked to the orders that have been in the system. This is because the action of ordering has been initiated by these users and they are identified according to the details stored in the database. The status of the order also gives more information, that is, on the actual condition of the product that has been placed by users as an order.

Tables with appropriate field-naming conventions has been depicted from the schema above. This is shown below with their various foreign and primary keys:

The following tables shows primary keys that has been used in these tables:

Figure 2: Primary Key Diagram

Primary keys refer to the special relational table columns of the database that are involved in unique identification of each record in the table. They are used as unique identifiers for parsing data within the table. In addition, each row of data in the table must have a unique value and null values cannot be used in such rows. Therefore, from the above figure, primary keys include product_id, merchant_id, item_id and order_id.

On the other hand, we have foreign keys which are depicted in the figure below:

Figure 3: Foreign Key Diagram

Conversely, foreign keys refer to a group of columns in a table of a relational database that provides a link between data in tables (mostly two). In addition, a foreign key also acts as a cross-reference between tables since it offers a reference of the primary key of a different table which therefore establishes a link between them.

Therefore, from figure 3 above, users table has merchant_id as a foreign key and user_orders table has order_id, merchant_id and user_id as foreign keys which are used to offer references between them. For instance, order_id from user_orders table offers reference of primary key from the orders table in the database.

Normalization of the database tables to third normal form (3NF)

Normalization which is a design technique of the database helps in reducing the redundancy of data and eliminating undesirable features such as update, deletion, and insertion anomalies. This technique also divides tables that are large into small tables and then linking them together using relationships. Redundant data elimination is the main purpose of normalization in SQL and ensuring logical storage of the same data (Gómez-Pérez et al, 2006).

Therefore, the following table shows a normalized database table to third normal form (3NF):

Figure 4: Third Normal Form (3NF) Diagram

To achieve the above third normal form, it must be in the second normal form previously and should not have transitive functional dependencies. Division of the table is required to move the second normal form (2NF) into third normal form (3NF) (Gómez-Pérez et al, 2006).

Entity-Relationship (E-R) Diagram

This diagram is particularly useful in describing the database structure which is in other words a blueprint or design that can be implemented as an actual database for storage of data. The main components of an Entity-Relationship model include entity set and relationship set, the former refers to a group of entities that are similar in nature and they have features too. Entity-Relationship diagram depicts completed the database structure logically.

The figure below represents an Entity-Relationship (E-R) diagram for the e-commerce website for a startup:

Figure 5: Entity-Relationship (ER) Diagram for the E-commerce Website

Figure 5 above shows a relationship between various project activities and how they interact with various entities. For instance, the e-commerce website supports entities such as customer, products, category, and others which includes how customers are going to make payment for the orders they made (Boley et al, 2012).

In addition, the website has a category of items which has a category_id and category_name which is linked to products which has various attributes such as product_name, product_id, product_price and product_model. Customers also belong to the e-commerce website which attributes such as address, password, username, customer_id and so on and they can buy products from the website, add items to the cart and make payment for them (Boley et al, 2012).

Data Flow Diagram for E-Commerce Website

A data flow diagram shows the flow of information through the system or process. It therefore has data inputs and outputs too, has places to store data and sub processes where data moves through. In addition, as shown below Data Flow Diagrams are built using standardized symbols and notation that does entity description and revealing their relationships.

Figure 6: Data Flow Diagram for E-Commerce Website

The diagram above shows the interaction and relationship between the shipping agent, system admin and the user of the system with e-commerce website. The diagram also shows various activities that these entities can carry out on the website, for instance, the user be provided with orders from the system, accept the order or reject it, the website can deliver the order to the shipping agent who will confirm the order in return, the system admin can add or update the catalogue and the commerce will provide them with billing details of the customer or order (Li & Chen, 2009).

Sample queries that will support reporting requirements of the organization include:

i. How many orders can a customer place at the same time?

ii. How many customers can be given access to the system or website at the same time?

Some of the screen layouts that can be utilized by the organization are as follows:

Reference

Batini, C., Lenzerini, M., & Navathe, S. B. 1986. A comparative analysis of methodologies for database schema integration. ACM computing surveys (CSUR), 18(4), 323-364.

Boley, J. L., Shelby, R. D., Griggs, R. C., & Smith, K. I. 2012. U.S. Patent No. 8,296,332. Washington, DC: U.S. Patent and Trademark Office.

Gómez-Pérez, A., Fernández-López, M., & Corcho, O. 2006. Ontological Engineering: with examples from the areas of Knowledge Management, e-Commerce, and the Semantic Web. Springer Science & Business Media.

Li, Q., & Chen, Y. L. 2009. Data flow diagram. In Modeling and Analysis of Enterprise and Information Systems (pp. 85-97). Springer, Berlin, Heidelberg.

Menascé, D. A. 2002. TPC-W: A benchmark for e-commerce. IEEE Internet Computing, 6(3), 83-87.

Layout 1: Login Interface

Layout 2: Order

Layout