CIS498 Final Project: Project Plan

Project Deliverable 3: Database and Programming Design

Leo Austin

Professor Joe Scott

CIS498 – Information Technology Capstone

08/22/2018

Introduction

Bicycle Trader being a constantly growing internet-based company requires the collection of an abundance of data to analyze for continued operations. Whether customers signup for services or browse through the website, data is gathered to allow the website to adapt to demands and cater to the customers’ needs and determine what will make using the site more user-friendly. Most importantly is the need to gather data in order to facilitate the entry and archiving of customer input data and use by other entities or departments within the business. Various database models can be taken into consideration for the needs of this business, and the relational database model is the most applicable due to the data sorting requirements for the website.

Not only is the rational database model the ideal database solution, but because they primarily consist of tables used to manage and store data, they are relatively easy to create and maintain. Many organizations choose this approach as it facilitates access to understandable data assets. Separating data by implementing tables also allows for the ability to adequately secure data by distinguishing each with their own classifications. Sorting data into tables also means that data can be added or withdrawn without having to overhaul the entire database.

Implementing data warehousing alongside relational databases provides further practicality and presents many advantages. By doing so, we can take advantage of its ability to “store large quantities of historical data and enable fast, complex queries across all the data, typically using Online Analytical Processing (OLAP)” (Panoply, n.d.). Data warehouses are essentially a collection of data from various sources that can be used by organizations for reporting and analysis. Because of the nature of Bicycle Trader and the abundance of like items that will be sold be by users on the website, a data warehouse will be the most practical solution for archiving data, because unlike most databases which normalize data in order to eliminate redundant data, a data warehouse uses a denormalized data structure. This means that fewer data tables with more grouping are used and redundancies aren’t excluded.

This combination of relational data systems, the data warehouse and relational database, can be hosted internally by the organization on its’ mainframe, and stored in their cloud. Using a cloud yields more advantages as it is the easiest and most cost-effective approach. By using this method, data can easily be accessed from several locations. Additionally, this allows for fewer physical resources as it eliminates some of the costs associated with expensive systems and equipment, expert staff, and energy consumption by alternatively utilizing the cloud computing service provider’s resources.

Database Schema

Bicycle Trader’s database schema will define the way the information exists throughout tables in a central repository. This data will be made available through the implementation of comprehensive relationships that will meet business function requirements that will cater to all facets of the organizations. Examples of such elements of information provided will be for sales and inventory aspects.

The ability to retrieve, edit, and delete the required information is dependent on well-defined entities within the database. Methodical arrangement of these entities is crucial to the effective interaction between the user interface and the infrastructure. These entities will be the basis for all data and transactions occurring within database and cataloging in the data warehouse.

Outlined below is a schema for some of the tables and fields:

· Clients - people utilizing the website to sell items

· Customer Number (INT) PK

· First Name (VARCHAR)

· Last Name (VARCHAR)

· Company (VARCHAR)

· Zip Code (INT)

· Customers - people purchasing from Clients

· Customer Number (INT) PK

· First Name (VARCHAR)

· Last Name (VARCHAR)

· Zip Code (INT)

· Inventory – items and descriptions

· Item Number (INT) PK

· Description (VARCHAR)

· Quantity (INT)

· Transactions – items purchased by Customers

· Item Number (INT) PK

· Order Number (INT) FK

· Price (CURRENCY)

Relationships between fields in the tables will define behaviors and parameters for visualization. For instance, each table has a unique primary key which provides foreign keys a target to reference. Primary keys also allow a table to establish a relationship with another table. For example, one customer can purchase multiple items, but each item will only have one unique number. To ensure referential integrity, there will be no Null values (a value that doesn’t reference anything) and any foreign key field will reference the corresponding primary key. For example, a particular order number will reference the item number for the item purchased.

Along with establishing the best-suited structure, a normalization process will also need to be implemented in order to eliminate potential problems and be able to retrieve, view, and maintain relevant information. Some redundancy problems normalization will avoid encountering are; insertion, deletion, and updation anomalies. These can occur when relationships aren’t defined well. It is the process of one change impacting other areas of data.

The principles in which the integrity of data is ensured is known as the database normalization principles, that are provided by first normal form (1NF) and second normal form (2NF). 1NF ensures that a table will contain one piece of information in each call with no duplicated rows. 2NF eliminates partial dependency which means that a candidate key can be determined by or functionally dependent of a prime key. This brings us to third normal form(3NF). 3NF is satisfied when it meets 1NF and 2NF requirements and does not have transitive dependency, a functional dependency which is formed by two functional dependencies (ie. A -> B, B -> C, therefore A-> C).

Entity Relationship Diagram