Project Deliverable 3: Database and Programming Design

Strayer University

CIS 498

Section 1: Design Document

Introduction

The presence of a suitable database schema helps the concerned organization to manage their business with limited internal resources. A suitable database schema has been included in this design document for the overall business benefit.

a. Creation of Database Schema

The database design needed for the present organization includes the intermediate modules of Customer Information, Transactional Information, and Supplier Information. The present database aims to include the details of customers and suppliers, relates to the company in terms of inventory and product sale percentage (Hoffer et al. 2016). The products sold by the company in their e-commerce portal are supplied by reliable suppliers of the company. These products are stored in the inventory storage of the company. The related product details include Product Name, Product ID and Product Price and relevant Supplier details. Furthermore, the Supplier Details include Supplier Name, Supplier ID and Supplier Address. These stored products in the inventory are dispatched to customer address as per their requirements specified in the order (Elmasri & Navathe, 2017). The related customer details stored in company database include Customer Name, Customer ID, and Purchase details. These confidential data are organized in a structured format within the database.

Figure 1: Database Schema

(Source: Created by Learner)

b. Explanation of Database Schema

The relevant Database Schemas aids in the development of a definite structure for data storage within the organization (Pavlo et al. 2017). The present database schema includes the intermediate modules of Supplier Details, Product Details, Inventory Details, Purchase Details, and Customer Details. These database modules in relation to database schema are explained below:

Supplier Details

The reliable suppliers of the company are listed in the database along with the details of Supplier Name, Supplier ID, Supplier Address, Frequency of Supply, and Product ID. The attribute of Supplier ID is identified as the unique attribute for identification (Patil et al. 2018). Each product of the company is supplied to the company from different reliable supplier sources.

Product Details

The company possesses a large number of diversified products for the business benefit of the company (Noll et al. 2019). The details of the product are included in the company database in terms of their attributes that include Product Name, Product ID, Price, and Supplier ID. These attributes aids in the monitoring of product sale and supply from suppliers.

Inventory Details

The products are stored in the inventory before they are dispatched to the customer address as per the requirement (Poerner & Winkelmann, 2017). This temporary storage of products in the inventory is carried out using the related attributes of Product ID, Product Name, Stock Status, Reinforcement Date, Supplier ID, and Last Order Placed.

Purchase Details

The company generates its profit percentage from the product sold to the customers (Lu & Holubová, 2017). These product sale transactions are recorded in the database using the attributes of Purchase Date, Purchase ID, Product ID, and Customer ID.

Customer Details

The customer details of a company are included in the company database based on the relevant details of customers (Prananosa et al. 2019). These relevant details include Customer Name, Customer ID, Customer Address, Last Purchase Amount, and Purchase ID. The loyal customers are identified by the company representatives based on the relevant Customer ID and last Purchase Amount.

c. Creation of Database Tables

The relevant Database tables are created within the database design to store the data related to different entities of the database. The database tables store the relevant information in terms of unique attributes related to entities. The unique identification of entities is carried out using the conceptual aspect of Primary Key. As per the opinion of Peñaherrera et al. (2018), the primary key is defined to be an attribute having a unique value for speedy identification of concerned entity. In the subsequent database design, the attribute of Supplier ID is used for the identification of suppliers from the database design. Referential integrity is included in the process of database designing for the overall business benefit of the company. As per the observation of Hingorani et al. (2017), the referential integrity of a database is defined as the property of data stating that it is related to validity of database entries.

Figure 2: Database Tables

(Source: Created by Learner)

Identification of Foreign Keys and Primary Keys

The present database tables include the participation of Supplier Details, Product Details, Inventory Details, and Purchase Details. Each of these tables is interconnected to each other in terms of Primary Key present in each individual database table (Amin et al. 2019). In Database Table of Supplier Details, the primary key is identified to be Supplier ID. This is because the value of Supplier ID is allocated to be unique by nature. The relevant Foreign Key is identified to be Product ID. This is because, unique value of Product ID from Product Details is used to search for a supplier and the corresponding product delivered by the supplier. The Primary Key of Product Details Database Table is Product ID and the Foreign Key is Supplier ID. The value of Product ID for the product range of the company is allocated to be unique (Sharma, 2017). The unique value of Supplier ID is used by company representatives to identify the products supplied by individual suppliers. The Primary Key of Inventory Details Database Table is considered to be Product ID and Foreign Key is identified to be Supplier ID. The primary Key attribute of Product ID is allocated to a unique value in terms of its effective identification. The Foreign Key related to Supplier ID is used by company employees to track the products supplied by supplier and is presently stored in the company inventory.

The Primary Key of Purchase Details Database Table is considered to be Purchase ID. On the contrary, the relevant Foreign Keys are identified to be Product ID and Customer ID. The Primary Key of Purchase ID is presented with a unique value (Papenbrock & Naumann, 2017). Additionally, the Foreign Keys of Product ID and Customer ID are related to the identification of a specific purchase transaction involving the customer and the product. The Primary Key of Customer Details Database Table is identified to be Customer ID and the related Foreign Key is identified to be Purchase ID. The Primary Key of Customer ID is allocated with a unique value for identification. The corresponding Foreign Key of Customer ID is used to relate the customers with their specific product purchases.

Fulfillment of Referential Integrity in Database Table

The referential integrity in a database table is ensured by the representation of attributes as Primary Key in a primary table. Additionally, these attributes of Primary Key is represented as Foreign Keys in secondary tables (Győrödi et al. 2016). The primary keys are used to identify uniquely an entry in the database. The foreign keys are used to identify the underlying relationship between the entities of a database table. In the present database table, the database administrators are directed by company leaders in relation to locking the attribute of Primary Key in the database table to avoid the chances of editing.

d. Database Normalization

The intermediate database tables of database designing are subjected to normalization procedure for the business benefit of the company (Tyagi & Singh, 2017).

Supplier Details Database Table

Table 1: Supplier Details

(Source: Created by Learner)

Normalized Form in 3NF

Table 2: Normalized Form

(Source: Created by Learner)

Table 3: Normalized Form

(Source: Created by Learner)

Product Details Database Table

Table 4: Product Details

(Source: Created by Learner)

Normalized Form in 3NF

Table 5: Normalized Form

(Source: Created by Learner)

Table 6: Normalized Form

(Source: Created by Learner)

Inventory Details Database Table

Table 7: Inventory Details

(Source: Created by Learner)

Normalized Form in 3NF

Table 8: Normalized Form

(Source: Created by Learner)

Table 9: Normalized Form

(Source: Created by Learner)

Purchase Details Database Table

Table 10: Purchase Details

(Source: Created by Learner)

Normalized Form in 3NF

Table 11: Normalized Form

(Source: Created by Learner)

Table 12: Normalized Form

(Source: Created by Learner)

Customer Details Database Table

Table 13: Customer Details

(Source: Created by Learner)

Normalized Form in 3NF

Table 14: Normalized Form

(Source: Created by Learner)

Table 15: Normalized Form

(Source: Created by Learner)

e. Entity-Relationship Diagram

The Entity-Relationship (ER) Diagram is used for the benefit database designing in reputed companies (Burke, 2017). These diagrams indicate the origin of data and its subsequent destination within the database design. [Referred to Appendix 1]

f. Rationale for ERD

The present ERD includes the contribution of different data sources within the database design. The presumed sources of data within database design include Supplier Details, Product Details, Inventory Details, Purchase Details, and Customer Details. These data sources belong to the relevant departments of Suppliers, Products, Inventory, Purchase, and Customer (Hoffer et al. 2016). The relevant data content is entered into a database using a suitable query language.

g. DFD

[Referred to Appendix 2]

h. Rationale for DFD

The selection of Data Flow Diagram (DFD) ensures that the relevant flow of data can be tracked from its source to the destination (Elmasri & Navathe, 2017). This tracking of data flow is beneficial for the concerned company in terms of business opportunities.

i. Sample Queries

Query for Customer Table Creation

create database company_name;

Query for Data Entry into Customer Table

use company_name;

create table customers

(

Customers_ID int not null,

Customers_Name varchar(255) not null,

Customers_Address varchar(255),

Customers_Last_Purchase_Amount int,

Purchase_ID int,

);

j. Screen Layouts

Layout for Database Creation

Figure 4: Table Creation

(Source: Created by Learner)

Layout for Data Entry into Database

Figure 5: Insertion of Data into Table

(Source: Created by Learner)

Conclusion

The deployment of effective database design is beneficial for the overall business benefit of the company. The suitable database designs are developed by related administrators to accommodate the total amount of data generated in company.

Reference List

Books

Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern database management (p. 600). London: Pearson.

Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems (Vol. 7). London: Pearson.

Journals

Pavlo, A., Angulo, G., Arulraj, J., Lin, H., Lin, J., Ma, L., Menon, P., Mowry, T.C., Perron, M., Quah, I. and Santurkar, S., 2017, January. Self-Driving Database Management Systems. In CIDR (Vol. 4, p. 1).

Patil, N. S., Kiran, P., Kiran, N. P., & KM, N. P. (2018). A survey on graph database management techniques for huge unstructured data. International Journal of Electrical and Computer Engineering, 8(2), 1140.

Noll, S., May, N., Böhm, A., Mühlig, J., & Teubner, J. (2019). From the Application to the CPU: Holistic Resource Management for Modern Database Management Systems. IEEE Data Eng. Bull., 42(1), 10-21.

Poerner, N., & Winkelmann, R. (2017). Interfacing the BAS speech science web services and the EMU speech database management system. Preface & Acknowledgements, 141.

Lu, J., & Holubová, I. (2017, March). Multi-model Data Management: What’s New and What’s Next?. In EDBT (Vol. 17, pp. 602-605).

Prananosa, A. G., Wahyudi, M., & Widodo, S. E. (2019). Model Development of School Database Management Information System. OSF Preprints. January, 13, pp.1-10.

Peñaherrera, F., Reinhardt, R., & Kousaiti, A. (2018). Conceptual design for data flow for a database management system for the sustainable recovery of critical metals from waste electrical and electronic equipment. Global NEST Journal, 20(4), 700-705.

Hingorani, K., Gittens, D., & Edwards, N. (2017). Reinforcing Database Concepts by Using Entity Relationships Diagrams (ERD) and Normalization Together For Designing Robust Databases. Issues in Information Systems, 18(1), 148-155.

Amin, M., Romney, G. W., Dey, P., & Sinha, B. (2019). Teaching Relational Database Normalization in an Innovative Way. The Journal of Computing Sciences in Colleges, 48.

Sharma, N. (2017). Overview of the Database Management System. International Journal of Advanced Research in Computer Science, 8(4), pp.1-14.

Papenbrock, T., & Naumann, F. (2017). Data-driven Schema Normalization. In EDBT (Vol. 17, pp. 342-353).

Győrödi, C., Győrödi, R., Ștefan, A., & Bandici, L. (2016). A Comparative Study of Databases with Different Methods of Internal Data Management. Database, 7(4), pp.1-8.

Tyagi, N., & Singh, N. (2017). Comparative Analysis of Graph Database and a Relational Database. M. Tech Scholar1, Assistant Professor2 Dept of Computer Science & Engineering GEU Dehradun, India, IJESC, 7, pp.1-8.

Online Articles

Burke, E. (2017). Database Management Systems–Kernel. Database Systems, 381.

Appendices

Appendix 1: ERD

(Source: Created by Learner)

Appendix 2: DFD

(Source: Created by Learner)

PROJECT DELIVERABLE 3: BUSINESS REQUIREMENTS

Section 2: Project Gantt Chart

Figure 1: Gantt Chart

(Source: Created by Learner)

The database designing involves a pre-planning phase that includes the selection of a suitable database administrator. The present database has been developed after including the relevant ERD and DFD diagram in the project-planning phase.