Project 4

Project Deliverable 3: Database and Programming Design

Student’s name

Professor’s name

Course title

Date

Table of Contents Section 1: Design Document 4 Introduction 4 a. Creation of Database Schema 4 b. Explanation of Database Schema 5 Supplier Details 6 Product Details 6 Inventory Details 6 Purchase Details 6 Customer Details 6 c. Creation of Database Tables 7 Identification of Foreign Keys and Primary Keys 8 Fulfillment of Referential Integrity in Database Table 9 d. Database Normalization 9 Supplier Details Database Table 9 Normalized Form in 3NF 9 Product Details Database Table 10 Normalized Form in 3NF 10 Inventory Details Database Table 10 Normalized Form in 3NF 11 Purchase Details Database Table 11 Normalized Form in 3NF 11 Customer Details Database Table 12 Normalized Form in 3NF 12 e. Entity-Relationship Diagram 12 f. Rationale for ERD 12 g. DFD 13 h. Rationale for DFD 13 i. Sample Queries 13 Query for Customer Table Creation 13 Query for Data Entry into Customer Table 13 j. Screen Layouts 13 Layout for Database Creation 13 Layout for Data Entry into Database 14 Conclusion 14 Reference List 15 Appendices 17 Appendix 1: ERD 17 Appendix 2: DFD 18 Section 2: Project Gantt Chart 20

The availability of an acceptable schema for the database allows the company involved handle their enterprise with minimal internal capital. For the company's overall impact an appropriate database scheme has been involved in this project plan.

The database architecture required for the present company involves the transitional Consumer Information, quantitative Information, and supplier evaluation modules . The purpose of this database is to provide consumer and manufacturer information related to the business in terms of the numbers of stock and service revenue (Hoffer et al, 2016). The goods that the business offers on its e-commerce site are provided by the corporation's reputable suppliers. Such goods are held in the firm's inventory. Details of the specific commodity provide Product Name, Product ID and Product Price, as well as applicable manufacturer information. The Supplier Specifics often provide Supplier Name, Supplier ID and Supplier Address . Such stored goods are delivered to the consumer's address as defined in the order according to their specifications (Elmasri & Navathe, 2017). The related consumer records that are recorded in the business database contain customer name, customer ID and descriptions of transactions. All sensitive records are grouped within the archive, in a hierarchical format.

Figure 1: Database Schema

(Source: Created by Learner)

The corresponding Server Schemas allows to establish a specific data management system within the company (Pavlo et al, 2017). The new information system contains Supplier Information, Item Info, Stock Data, Order Details, and User Info intermediate sections. These software components are described below pertaining to the database schema:

The corporation's reputable vendors are specified in the database along with information of the Supplier Name, Supplier ID, Supplier Address, Distribution Rate and Brand ID. Supplier ID's characteristic is known as the special distinguishing feature (Patil et al. 2018). Each company commodity is distributed to the client from numerous reputable sources to distributors.

The corporation holds a significant range of diversified goods for the company's market gain (Noll et al, 2019). Consumer information are provided in the client inventory in terms of their characteristics and include Brand Title, Product ID, Cost, and Supplier ID . Such characteristics aid with tracking suppliers' commodity distribution and procurement.

The items are processed in the warehouse before being shipped according to the criteria to the consumer address (Poerner & Winkelmann, 2017). This transient inventory holding of goods is conducted utilizing the associated attributes of Product Type, Model Number, Stock Position, Date of Replacement, Supplier Type, and Last Order Put.

The organization derives the share of income from the goods offered to the clients (Lu & Holubová, 2017). This drug sales purchases are registered in the database using the Purchasing Date, Purchasing Type, Company Type and Consumer ID attributes.

A product’s consumer records are used in the business database on the basis of the actual customer information (Prananosa, 2019). This specific information include Consumer Name, Customer ID, Customer Address, Last Sales Number and Order Number. Depending on the correct Customer ID and last Transaction Amount, the business managers recognize the faithful customers.

Under the database architecture, the appropriate Database tables are generated to store information linked to the various database objects. The tables of the server store the necessary details in terms of special entity specific attributes. Unique identification of entities is performed using Primary core theoretical element. The key value is described as a characteristic that has a significant value for quick recognition of the entity involved (Peñaherrera, 2018). The Supplier ID identifier is included in the actual database design for distinguishing vendors from the database system. Referential honesty is used in the software cycle planning for the company's ultimate market benefit. As per description, a database's contextual honesty is characterized as the value of the data state that it is linked to the authenticity of the public databases (Hingorani, 2017).

Figure 2: Database Tables

(Source: Created by Learner)

The existing database object contains Manufacturer Information, Commodity Descriptions, Production Information, and Purchase Details. In terms of the available key present in each independent relational database, each of these tables is linked to each other (Amin et al, 2019). The primary key is listed as Supplier ID in the Server Table of Supplier Information. That is because the significance of the Supplier ID is determined to be specific by default. The Foreign Key concerned is known as Brand ID. That is since the special meaning of Commodity ID from Product Descriptions is used to scan for a manufacturer and the supplier's matching items. The Primary Key of Commodity Information Database Table is Commodity ID and the International Key is Manufacturer ID. Product ID value for the particular product selection is classified as special (Sharma, 2017). Business executives use the special benefit of Supplier ID to recognize the goods provided by specific suppliers. The preferred key of Import Information Server Table is known to be Item ID, and the Supplier ID is defined as Foreign Key. Brand ID’s primary key feature is given a special meaning as to its successful recognition. The Foreign Key connected to the Supplier ID is used by stakeholders of the company to monitor the supplied goods and is currently deposited in the company warehouse.

The main key of Purchase Information Server Table is known to be Purchase ID. On the opposite, the related International Keys are classified as Brand ID and Consumer ID. A special attribute is provided in the main key of Purchase ID (Papenbrock & Naumann, 2017). In comparison, the Company ID and Consumer ID International Keys are linked to defining a particular sales process between the client and the commodity. The main key of customer information relational database is recognized as Customer ID and the linked Foreign Key is recognized as the Purchase ID. Customer ID's main key is assigned with and uniquely identifies value. The accompanying Customer ID International Key is used to successfully connect to their unique transactions of an item.

Recognition of characteristics as main key in a related table maintains the contextual validity in a data base list. These unique constraint attributes are further listed in specific tables as International Keys (Győrödi, 2016). The main keys are used for the exclusive recognition of an item in the database. The primary keys are used to discover the common connection among a database table entities. Within this database table, the server managers are instructed by business members to lock the Primary Key feature in the database table in order to prevent editing risks.

To the commercial advantage of the client, the intermediary database tables for the construction of databases are entitled to the normalization process (Tyagi & Singh, 2017).

Table 1: Supplier Details

(Source: Created by Learner)

Table 2: Normalized Form

(Source: Created by Learner)

Table 3: Normalized Form

(Source: Created by Learner)

Table 4: Product Details

(Source: Created by Learner)

Table 5: Normalized Form

(Source: Created by Learner)

Table 6: Normalized Form

(Source: Created by Learner)

Table 7: Inventory Details

(Source: Created by Learner)

Table 8: Normalized Form

(Source: Created by Learner)

Table 9: Normalized Form

(Source: Created by Learner)

Table 10: Purchase Details

(Source: Created by Learner)

Table 11: Normalized Form

(Source: Created by Learner)

Table 12: Normalized Form

(Source: Created by Learner)

Table 13: Customer Details

(Source: Created by Learner)

Table 14: Normalized Form

(Source: Created by Learner)

Table 15: Normalized Form

(Source: Created by Learner)

The Entity-Relationship (ER) Diagram is utilized in renowned organizations for the configuration of the gain framework (Burke, 2017). Both diagrams show the root of the data inside the database architecture, and its corresponding path. [Referred to Appendix 1]

This ERD requires the involvement of multiple data providers within the framework of the database. Supplier Information, Item Info, Stock Data, Order Details, and Consumer Info are assumed data points within database architecture. Such data points relate to the Suppliers, Goods, Supply, Order, and Consumer divisions involved (Hoffer, 2016). The output of the related data is inserted into a server using a correct markup language.

[Referred to Appendix 2]

The Data Flow Diagram (DFD) collection means that the appropriate data flow can be traced from its reference to the endpoint (Elmasri & Navathe, 2017). Such data flow analysis is useful in terms of market prospects for the respective client.

create database company_name;

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,

);

Figure 4: Table Creation

(Source: Created by Learner)

Figure 5: Insertion of Data into Table

(Source: Created by Learner)

The implementation of successful database architecture improves the enterprise's total market profit. Appropriate software implementations are created by related managers to handle the overall volume of data produced in the organization.

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.

(Source: Created by Learner)

(Source: Created by Learner)

Figure 1: Gantt Chart

(Source: Created by Learner)

The database design requires an advance planning process which involves the selection of an appropriate database management system. The existing database was created after the project planning process integrated the appropriate ERD and DFD diagram.

Information Technology Upgrading and Expansion Plan Analysis Scope Analysis and Requirements ration System Design Hardware Installation and system development Testing Training Hardware Testing Software Testing Implementation Development Deployment Expansion Plan Completed 44044 44046 44099 44114 44146 44099 44129 44142 44146 44160 44160 44508 44143 Duration Information Technology Upgrading and Expansion Plan Analysis Scope Analysis and Requirements ration System Design Hardware Installation and system development Testing Training Hardware Testing Software Testing Implementation Development Deployment Expansion Plan Completed 60 15 5 12 7 16 53 9 5 153 30 51 1