Database Systems,Data Definition and Data Manipulation Language (IP3)

CS660 Database Systems

Swift Retail Store: Data Definition and Data Manipulation Language (IP3)

Colorado Technical University

Josh Thomas

12/8/20

Table of Contents

Database System Overview 3

Database Goals and Objectives 4

How Proposed Database System Addresses Problem(s) 4

Mission and Goals Alignment 5

Analysis of How Project Fulfills Mission/Goals of Target Organization 6

Entity Relationship Model 7

Subjects of Interests 7

Rules 9

Entity Relationship (ER) Diagram 10

Table of Entities, Attributes, Relationships and Cardinality Constraints 10

Analysis of how Project Fulfills Mission/Goals of Target Organization 13

Structured Query Language (SQL) Scripts 14

DDL SQL – Create & Insert Statements 14

DML SQL – Delete & Update Statements 14

REPORT SQL – Select, Crosstab & Aggregate Function Statements 14

Analysis: How This Fulfills the Case Study Organization Mission & Goals 14

Database Administration Plan 15

Future Database System Implementation Plan 16

References 17

 

Database System Overview

The purpose of this project is to explore how the implementation of a fully-comprehensive database system can advance a retail store’s business bottomline via an electronics system ‘presence’ online. As database consultants, it will be our job and purpose to design a system efficient and comprehensive enough to handle any amount of traffic across the platform for product/service searches, information of products/services, etc. with advanced load balancing mechanisms throughout the centralized web server. According to Oracle.com (23 November, 2020), “A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database” (p.1).

Environment

The Swift Retail Store is my selected theoretical organization to propose a fully comprehensive and thoroughly designed database system that will enhance their online business ‘bottom-line’ from a technological and general standpoint. The Swift Retail Store will be comprised of 5-10 employees, two newly hired database technicians included in this range (once refreshed and running at prime optimization), and 2 store locations, each with a specific role played within the business operational scheme (store 1: products, store 2: service supply chain & operations), and one primary location that will handle all computational and database systems objectives. Both locations will reside within the Houston, TX area.

Database Goals and Objectives

The goals and objectives of the database is to offer a sufficient means for the store to run its electronic business transactions with optimal runtime, at least 95% secured uptime and availability, while have all system perimeters properly secured and its integrity fortified with the best in-class protocols, security mechanisms and advanced networking schemes accordingly to run its back-end web and database servers at a 24/7 capacity. There are shoppers of all kinds looking for online products and services at all times, so it will be a critical measure to assure our servers can be up-and-running at all times with confidence, assurance and minimal downtime during general maintenance and patch management schedules. Our proposed database software (i.e. Microsoft SQL Server Edition) will serve as the foundation to execute this advancement project, with futuristic intentions to upgrade to more advanced database software once the business growth demands the necessary upgrades.

How Proposed Database System Addresses Problem(s)

The database system currently installed within the main store has been non-functional, causing delays for electronic sales and general use implementations. From our analysis, the system has been compromised via weak security protocols and unpatched attack surfaces, thus rendering it essentially useless and unsafe to operate. Our newly designed database ideas will resolve such security issues and concerns for Swift Retail, as well as offer a very easy and efficient online experience both for the technicians making necessary updates to products and services, and the End-Users online shopping for desired items.

Mission and Goals Alignment

Before setting the mission and goals of this database technologies and design updates-project, we must first thoroughly understand both and their distinct descriptions in-relation. According to Ketchen (2014, September 12), “A mission statement which engages stakeholders will help develop an understanding of why they should support the organization and make clear what important role or purpose the organization plays in society – also called a ‘social license to operate’… To work toward achieving these overall aspirations, organizations also need to create goals—narrower aims that should provide clear and tangible guidance to employees as they perform their work on a daily basis. The most effective goals are those that are: Specific, Measurable, Achievable, Realistic, and Time-bound (S.M.A.R.T.)”. In-connection, we must assure that our short-term goals throughout this process are strategically aligned with our overall mission: to create an optimal database solution for The Swift Retail Store, with comprehensive capabilities to operate efficiently and effectively as an online vendor, satisfaction guaranteed. Our goals should be easy and measurable enough to accomplish on a daily and weekly basis, and should offer standard ‘checkpoints’ to determine the status of each segment without the need for much rework as we go. We have a synopsis of our goals as they stand today, with certain time frames designed for potential updates, changes, and alterations to any portion of the database system during design and implementation cycles. For the purposes of brevity, we have utilized the S.M.A.R.T. System to sum up our primary goals that will support our end result:

  • S.pecific – To fully revamp current database system processes with new database technologies with advanced capabilities to seamlessly drive business bottom-line objectives with customer-base.
  • M.esurable – Day-to-day assignments should allot a 2-5 hour range to be completed, and redesigned or escalated with extra support to assure completion within the set time frame.
  • A.ggressive – Every goal should offer room for professional and leadership skills advancement to ‘stretch’ every team member’s capabilities confidence for future project assignments.
  • R.ealistic – Each assigned responsibility should be doable and able to completed each day assigned, in-scope from a standard perspective.
  • T.ime-bound – As the team works through project objectives, time management will be considered top-priority to assure completion via the set due date as negotiated. This will include, but not limited to, after and weekend hours worked to assure our clients have their product before or by the deadline agreed upon.

Analysis of How Project Fulfills Mission/Goals of Target Organization

Our overall analysis of project mission & goals fulfillment post-completion will be determined by Swift’s online presence and customer-base and satisfaction-increase accordingly, within the first 3 months after the launch of the improved database system. Although marketing and sales are the major departments for customer growth, the database serves as the ‘back-end’ mechanism to assure all customer, both new and current, have the ability to surf Swift’s front-end database with minimal interruption via split load protocols, web traffic sufficiency across load, and customer satisfaction with shopping experience both from an application and product lines standpoint. Current project efforts fulfill Swift’s end-goal with a viable database design advanced enough to handle their current electronics on-line business, with database server coverage left for future improvements and upgrades to overall system capacity.

 

Entity Relationship Model

Subjects of Interests

  • Customers
    • Transportation|Trucking Organizations
    • Truck Drivers (Heavy Duty, HazMat, Mid-Size, Etc.)
    • Truck Mechanics (Heavy Duty, HazMat, Mid-Size, Etc.)
  • Orders
    • Single Units (1)
    • Grouping Packages (4-6)
    • Lg. Bundled Packages (6+)
  • Products
    • Major Truck Parts
      • Lights
      • Cables
      • Fluids
      • Electrical Components
      • Paints
      • Straps
    • Trucking Tools
    • Filters
    • Heavy Duty Tires
    • PPE
  • Services
    • Comprehensive Diagnostics
    • Oil Maintenance
    • Fluid(s) Checks & Refills
    • Tires Maintenance
    • Tires Rotation
    • HVAC Diagnostics
    • Electrical Diagnostics
    • State Inspections
  • Product(s) Training Workshops
    • General Supplies
    • Mechanical
    • Electrical
    • HVAC
    • Hydraulics
    • Suspension
    • Brakes
  • Utilization & Installation(s) Workshops
    • Mechanical Parts
    • Electrical Parts
    • Brakes Parts
    • HVAC Systems
    • Hydraulics Systems
    • Suspension Systems

Rules

  • Every Customer|Client will be connected to a single Customer ID
  • Each customer|client can have similar locations
  • All Member IDs will be required for any products and service promotionals across stores
  • All Members will need to present IDs as requirements for all workshops
  • Products per customer will be tracked via Customer ID.
  • Every Workshop can offer additional Workshops for discounts or promotions
  • Workshops will occur at one location per Event
  • Each Workshop will be sponsored by one Branch
  • Each Store will be recognized as a single unique location
  • Every Swift Employee is connected with a single store as their primary location
  • Multiple employees can have similar locations
  • Employee Types include: Representatives, Associate Manager, General Manager or Store Manager
  • Products may have multiple promotions active
  • Every Product should have the following: Product Name, Product Category, Reorder Quantity and Reorder Threshold setup within the system.
  • Each Product can exist in a single category only.
  • Orders are able to have zero or more Memberships ( non-members are allowed to shop as ‘Guest’ shopper).
  • Every Order must be associated to a single Store
  • Every Order will be entered by a single Employee
  • Orders must contain one or more products.

Entity Relationship (ER) Diagram

Below is a simple ER Diagram, to be used through the new database design process:

Shareable link for easier review is include: https://lucid.app/lucidchart/invitations/accept/2c2cc79f-b141-4352-b9e6-4ef114cfca7c

Table of Entities, Attributes, Relationships and Cardinality Constraints

Entities (Tables) Attributes

(Rows)

Relationships Cardinality Constraints
Customers CustomerID (PK) (Char10) Has MemberID 1:1
Customer Name (Varchar50)
StoreID (Char10) Has a Location 1:Many
MemberID (Char10) Directly associated to Every Customer 1:1
Contact Phone Number (Int)
Contact Email (VarChar200)
Store Branch StoreID (Char10) Can Host Workshops

Can Create Orders

Has a Location

Has Employees

1:Many

1:Many

1:1

1:Many

Store Name (Varchar50)
LocationID (Char10) Has a Location 1:1
OpeningTimes (Varchar500)
Employee EmployeeID (Char10) Connected a Store Branch

Can Create Orders

1:1

1:Many

Employee Position (Varchar25)
Employee Name (Varchar50)
LocationID (Char10) Has a Location One Location can have more than one Employee
StoreID (Char10) Is connected to a Store 1:1
PayRate (Decimal 6,2)
Pay Occurrence (Varchar20)
Member MemberID (Char10) Requires a Customer

Registers at Workshops

Msy receive Promotions

1:1

1:Many

1:Many

Member Type (Varchar50)
Member Level (Char2)
CustomerID (Char10) Has Member ID 1:1
WorkshopID (Char10) Member ID Required to Register 1:Many
Total Spent (Decimal 10,2)
PromotionID (Char10) Can have multiple Promotions 1:Many
Orders OrderID (Char10) Connected to Stores

Initiated by Employee

May have Promotions

May have Member Status

Must have Products

1:1

1:1

1:Many

1:1

1:Many

Transaction Date (Date)
MemberID (Char10) Can have Member ID 1:1
StoreID (Char10) Connected to Store 1:1
EmployeeID (Char10) Employee makes Sale 1:1
Total Sale (Decimal 10,2)
Sales Tax (Decimal 5,2)
PrmotionID (Char10) May have Promotions 1:Many
ProductID (Char10) Must have Products 1:Many
Promotions PromotionID (Char10) Can be connected to Member ID

Can be Associated to Workshops

Can be Associated to Direct Product Sales

1:Many

1:Many

1:Many

Promotion Name (Varchar200)
Promotion Type (Varchar50)
Discount (Decimal 5,2)
Date Start (Date)
Date End (Date)
Products ProductID (Char10) May have Promotions 1:Many
Product Name (Varchar200)
Product Brand (Varchar100)
Product Category (Varchar 50)
Description (Varchar2500)
Count Available (Int)
Reorder Threshold (Int)
Reorder Quantity (Int)
PromotionID (Char10) Can have more than 1 simultaneously 1:Many
Workshops WorkshopID (Char15) Is connected to a Store Branch

Requires Member Status to Attend

Performed at Location

1:1

1:Many

1:1

Workshop Name (Varchar200)
Workshop Purpose (Varchar2500)
Workshop Date (Date)
Attendee Cap (Int)
MemberID (Char10) Defines Allowed Attendees 1:Many
Count of Attendees (Int)
PromotionID (Char10) Can have Promotions 1:Zero or More
LocationID (Char10) Performed at Location 1:1
StoreID (Char10) Owned by Store 1:1
Location LocationID (Char10) Is Connected to Customers

Is Connected to Employees

Is Connected to Stores

Is Connected to Workshops

1:Many

1:Many

1:1

1:Many

Address Line 1 (Varchar150)
Address Line 2 (Varchar150)
City (Varchar50)
State (Char2)
Zip Code (Char5)
County (Varchar50)
Country (Varchar50)

Analysis of how Project Fulfills Mission/Goals of Target Organization

The purpose of this section is to add detailed information on all technical components of the projected database system, to assist Swift with their electronics shop-vending aspirations. Embedded into the system will be the Third Norm Form (3FN) as its primary design. According to Geeksforgeeks.com (2019, July 31), “A relation is in third normal form, if there is no transitive dependency for non-prime attributes as well as it is in second normal form.

A relation is in 3NF if at least one of the following condition holds in every non-trivial functional dependency X –> Y:

  • X is a super key.
  • Y is a prime attribute (each element of Y is part of some candidate key).

In other words, A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF)” (p.1). We need to utilize this form to best design the database as deemed fit, with multiple tables and entries running parallel and separated from primary key components.

As for the mission for the targeted organization, the database system and detailed technicalities will assist Swift with keeping track of all major items across system perimeters. The system will also track all customer, client and guest transactions and products|services items throughout their electronics experience. This effort should also help to fully-automate products and service transactions across the board via selections – through – checkout and payment submissions accordingly.

Structured Query Language (SQL) Scripts

DDL SQL – Create & Insert Statements

Create DDL

Insert DML

DML SQL – Delete & Update Statements

Updating and Deletion of DML

REPORT SQL – Select, Crosstab & Aggregate Function Statements

Analysis: How This Fulfills the Case Study Organization Mission & Goals

As our team is zeroing in on our final database product for The Swift Retail Store, we are excited to provide a fully comprehensive database code that should satisfy every necessary requirement based on the tables structure completed in Unit 2. This portion has, by far, taken the most extensive amount of time to create and execute in-assurance that every portion of every table was embedded and linked to their proper table relationships, for the ultimate success of the database system. I have not currently worked with database software so intimately before this project, so most of the programming effort was a challenge and brand new to me. I worked with fairly standard tactics to expand data processing perimeters as much as possible, taking into consideration there may be better methods to the ‘madness’ when an End-User logs in a request for particular data desired. Further, from my observation of the database created, there were certain tables that I noted could use some futuristic updates to serve as better efficiency for the database system altogether. One update is concerned with the Products Table and eventually inserting code that will essentially send an alert to the database sales analyst or the sales team, notifying them of the ‘low’ inventory currently experienced. By doing so, the company can rest assured they never ‘accidentally’ run out of products, especially those products that run more sales over others. Another relational table that will potentially need some upgrades in the long-run concerns the Orders Table, and having coding in-place to balance simultaneous transactions across multiple server technologies to assure optimal uptime, availability and fault tolerance accordingly. Certain tables created did not require a max record count of 20 as certain table relationships work together to ultimately reduce workloads on one another.

Database Administration Plan

TBD

Future Database System Implementation Plan

TBD

References

Ketchen, D., Short, J., Try, D., & Edwards, J. (2014, September 12). Vision, Mission, and Goals. Retrieved from:

https://opentextbc.ca/strategicmanagement/chapter/vision-mission-and-goals/

Third Normal Form (3NF). (2019, July 31). Retrieved from

https://www.geeksforgeeks.org/third-normal-form-3nf/

What is a database? (2020, November 23). Retrieved from:

https://www.oracle.com/database/what-is-database/

What is an Entity Relationship Diagram (ERD)? (n.d.). Retrieved December 02, 2020, from

https://www.lucidchart.com/pages/er-diagrams

CS660 Database Systems

Swift Retail Store: Data Definition and Data Manipulation Language (IP3)

Colorado Technical University

Josh Thomas

12/8/20

Table of Contents

Database System Overview 3

Database Goals and Objectives 4

How Proposed Database System Addresses Problem(s) 4

Mission and Goals Alignment 5

Analysis of How Project Fulfills Mission/Goals of Target Organization 6

Entity Relationship Model 7

Subjects of Interests 7

Rules 9

Entity Relationship (ER) Diagram 10

Table of Entities, Attributes, Relationships and Cardinality Constraints 10

Analysis of how Project Fulfills Mission/Goals of Target Organization 13

Structured Query Language (SQL) Scripts 14

DDL SQL – Create & Insert Statements 14

DML SQL – Delete & Update Statements 14

REPORT SQL – Select, Crosstab & Aggregate Function Statements 14

Analysis: How This Fulfills the Case Study Organization Mission & Goals 14

Database Administration Plan 15

Future Database System Implementation Plan 16

References 17

 

Database System Overview

The purpose of this project is to explore how the implementation of a fully-comprehensive database system can advance a retail store’s business bottomline via an electronics system ‘presence’ online. As database consultants, it will be our job and purpose to design a system efficient and comprehensive enough to handle any amount of traffic across the platform for product/service searches, information of products/services, etc. with advanced load balancing mechanisms throughout the centralized web server. According to Oracle.com (23 November, 2020), “A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database” (p.1).

Environment

The Swift Retail Store is my selected theoretical organization to propose a fully comprehensive and thoroughly designed database system that will enhance their online business ‘bottom-line’ from a technological and general standpoint. The Swift Retail Store will be comprised of 5-10 employees, two newly hired database technicians included in this range (once refreshed and running at prime optimization), and 2 store locations, each with a specific role played within the business operational scheme (store 1: products, store 2: service supply chain & operations), and one primary location that will handle all computational and database systems objectives. Both locations will reside within the Houston, TX area.

Database Goals and Objectives

The goals and objectives of the database is to offer a sufficient means for the store to run its electronic business transactions with optimal runtime, at least 95% secured uptime and availability, while have all system perimeters properly secured and its integrity fortified with the best in-class protocols, security mechanisms and advanced networking schemes accordingly to run its back-end web and database servers at a 24/7 capacity. There are shoppers of all kinds looking for online products and services at all times, so it will be a critical measure to assure our servers can be up-and-running at all times with confidence, assurance and minimal downtime during general maintenance and patch management schedules. Our proposed database software (i.e. Microsoft SQL Server Edition) will serve as the foundation to execute this advancement project, with futuristic intentions to upgrade to more advanced database software once the business growth demands the necessary upgrades.

How Proposed Database System Addresses Problem(s)

The database system currently installed within the main store has been non-functional, causing delays for electronic sales and general use implementations. From our analysis, the system has been compromised via weak security protocols and unpatched attack surfaces, thus rendering it essentially useless and unsafe to operate. Our newly designed database ideas will resolve such security issues and concerns for Swift Retail, as well as offer a very easy and efficient online experience both for the technicians making necessary updates to products and services, and the End-Users online shopping for desired items.

Mission and Goals Alignment

Before setting the mission and goals of this database technologies and design updates-project, we must first thoroughly understand both and their distinct descriptions in-relation. According to Ketchen (2014, September 12), “A mission statement which engages stakeholders will help develop an understanding of why they should support the organization and make clear what important role or purpose the organization plays in society – also called a ‘social license to operate’… To work toward achieving these overall aspirations, organizations also need to create goals—narrower aims that should provide clear and tangible guidance to employees as they perform their work on a daily basis. The most effective goals are those that are: Specific, Measurable, Achievable, Realistic, and Time-bound (S.M.A.R.T.)”. In-connection, we must assure that our short-term goals throughout this process are strategically aligned with our overall mission: to create an optimal database solution for The Swift Retail Store, with comprehensive capabilities to operate efficiently and effectively as an online vendor, satisfaction guaranteed. Our goals should be easy and measurable enough to accomplish on a daily and weekly basis, and should offer standard ‘checkpoints’ to determine the status of each segment without the need for much rework as we go. We have a synopsis of our goals as they stand today, with certain time frames designed for potential updates, changes, and alterations to any portion of the database system during design and implementation cycles. For the purposes of brevity, we have utilized the S.M.A.R.T. System to sum up our primary goals that will support our end result:

  • S.pecific – To fully revamp current database system processes with new database technologies with advanced capabilities to seamlessly drive business bottom-line objectives with customer-base.
  • M.esurable – Day-to-day assignments should allot a 2-5 hour range to be completed, and redesigned or escalated with extra support to assure completion within the set time frame.
  • A.ggressive – Every goal should offer room for professional and leadership skills advancement to ‘stretch’ every team member’s capabilities confidence for future project assignments.
  • R.ealistic – Each assigned responsibility should be doable and able to completed each day assigned, in-scope from a standard perspective.
  • T.ime-bound – As the team works through project objectives, time management will be considered top-priority to assure completion via the set due date as negotiated. This will include, but not limited to, after and weekend hours worked to assure our clients have their product before or by the deadline agreed upon.

Analysis of How Project Fulfills Mission/Goals of Target Organization

Our overall analysis of project mission & goals fulfillment post-completion will be determined by Swift’s online presence and customer-base and satisfaction-increase accordingly, within the first 3 months after the launch of the improved database system. Although marketing and sales are the major departments for customer growth, the database serves as the ‘back-end’ mechanism to assure all customer, both new and current, have the ability to surf Swift’s front-end database with minimal interruption via split load protocols, web traffic sufficiency across load, and customer satisfaction with shopping experience both from an application and product lines standpoint. Current project efforts fulfill Swift’s end-goal with a viable database design advanced enough to handle their current electronics on-line business, with database server coverage left for future improvements and upgrades to overall system capacity.

 

Entity Relationship Model

Subjects of Interests

  • Customers
    • Transportation|Trucking Organizations
    • Truck Drivers (Heavy Duty, HazMat, Mid-Size, Etc.)
    • Truck Mechanics (Heavy Duty, HazMat, Mid-Size, Etc.)
  • Orders
    • Single Units (1)
    • Grouping Packages (4-6)
    • Lg. Bundled Packages (6+)
  • Products
    • Major Truck Parts
      • Lights
      • Cables
      • Fluids
      • Electrical Components
      • Paints
      • Straps
    • Trucking Tools
    • Filters
    • Heavy Duty Tires
    • PPE
  • Services
    • Comprehensive Diagnostics
    • Oil Maintenance
    • Fluid(s) Checks & Refills
    • Tires Maintenance
    • Tires Rotation
    • HVAC Diagnostics
    • Electrical Diagnostics
    • State Inspections
  • Product(s) Training Workshops
    • General Supplies
    • Mechanical
    • Electrical
    • HVAC
    • Hydraulics
    • Suspension
    • Brakes
  • Utilization & Installation(s) Workshops
    • Mechanical Parts
    • Electrical Parts
    • Brakes Parts
    • HVAC Systems
    • Hydraulics Systems
    • Suspension Systems

Rules

  • Every Customer|Client will be connected to a single Customer ID
  • Each customer|client can have similar locations
  • All Member IDs will be required for any products and service promotionals across stores
  • All Members will need to present IDs as requirements for all workshops
  • Products per customer will be tracked via Customer ID.
  • Every Workshop can offer additional Workshops for discounts or promotions
  • Workshops will occur at one location per Event
  • Each Workshop will be sponsored by one Branch
  • Each Store will be recognized as a single unique location
  • Every Swift Employee is connected with a single store as their primary location
  • Multiple employees can have similar locations
  • Employee Types include: Representatives, Associate Manager, General Manager or Store Manager
  • Products may have multiple promotions active
  • Every Product should have the following: Product Name, Product Category, Reorder Quantity and Reorder Threshold setup within the system.
  • Each Product can exist in a single category only.
  • Orders are able to have zero or more Memberships ( non-members are allowed to shop as ‘Guest’ shopper).
  • Every Order must be associated to a single Store
  • Every Order will be entered by a single Employee
  • Orders must contain one or more products.

Entity Relationship (ER) Diagram

Below is a simple ER Diagram, to be used through the new database design process:

Shareable link for easier review is include: https://lucid.app/lucidchart/invitations/accept/2c2cc79f-b141-4352-b9e6-4ef114cfca7c

Table of Entities, Attributes, Relationships and Cardinality Constraints

Entities (Tables) Attributes

(Rows)

Relationships Cardinality Constraints
Customers CustomerID (PK) (Char10) Has MemberID 1:1
Customer Name (Varchar50)
StoreID (Char10) Has a Location 1:Many
MemberID (Char10) Directly associated to Every Customer 1:1
Contact Phone Number (Int)
Contact Email (VarChar200)
Store Branch StoreID (Char10) Can Host Workshops

Can Create Orders

Has a Location

Has Employees

1:Many

1:Many

1:1

1:Many

Store Name (Varchar50)
LocationID (Char10) Has a Location 1:1
OpeningTimes (Varchar500)
Employee EmployeeID (Char10) Connected a Store Branch

Can Create Orders

1:1

1:Many

Employee Position (Varchar25)
Employee Name (Varchar50)
LocationID (Char10) Has a Location One Location can have more than one Employee
StoreID (Char10) Is connected to a Store 1:1
PayRate (Decimal 6,2)
Pay Occurrence (Varchar20)
Member MemberID (Char10) Requires a Customer

Registers at Workshops

Msy receive Promotions

1:1

1:Many

1:Many

Member Type (Varchar50)
Member Level (Char2)
CustomerID (Char10) Has Member ID 1:1
WorkshopID (Char10) Member ID Required to Register 1:Many
Total Spent (Decimal 10,2)
PromotionID (Char10) Can have multiple Promotions 1:Many
Orders OrderID (Char10) Connected to Stores

Initiated by Employee

May have Promotions

May have Member Status

Must have Products

1:1

1:1

1:Many

1:1

1:Many

Transaction Date (Date)
MemberID (Char10) Can have Member ID 1:1
StoreID (Char10) Connected to Store 1:1
EmployeeID (Char10) Employee makes Sale 1:1
Total Sale (Decimal 10,2)
Sales Tax (Decimal 5,2)
PrmotionID (Char10) May have Promotions 1:Many
ProductID (Char10) Must have Products 1:Many
Promotions PromotionID (Char10) Can be connected to Member ID

Can be Associated to Workshops

Can be Associated to Direct Product Sales

1:Many

1:Many

1:Many

Promotion Name (Varchar200)
Promotion Type (Varchar50)
Discount (Decimal 5,2)
Date Start (Date)
Date End (Date)
Products ProductID (Char10) May have Promotions 1:Many
Product Name (Varchar200)
Product Brand (Varchar100)
Product Category (Varchar 50)
Description (Varchar2500)
Count Available (Int)
Reorder Threshold (Int)
Reorder Quantity (Int)
PromotionID (Char10) Can have more than 1 simultaneously 1:Many
Workshops WorkshopID (Char15) Is connected to a Store Branch

Requires Member Status to Attend

Performed at Location

1:1

1:Many

1:1

Workshop Name (Varchar200)
Workshop Purpose (Varchar2500)
Workshop Date (Date)
Attendee Cap (Int)
MemberID (Char10) Defines Allowed Attendees 1:Many
Count of Attendees (Int)
PromotionID (Char10) Can have Promotions 1:Zero or More
LocationID (Char10) Performed at Location 1:1
StoreID (Char10) Owned by Store 1:1
Location LocationID (Char10) Is Connected to Customers

Is Connected to Employees

Is Connected to Stores

Is Connected to Workshops

1:Many

1:Many

1:1

1:Many

Address Line 1 (Varchar150)
Address Line 2 (Varchar150)
City (Varchar50)
State (Char2)
Zip Code (Char5)
County (Varchar50)
Country (Varchar50)

Analysis of how Project Fulfills Mission/Goals of Target Organization

The purpose of this section is to add detailed information on all technical components of the projected database system, to assist Swift with their electronics shop-vending aspirations. Embedded into the system will be the Third Norm Form (3FN) as its primary design. According to Geeksforgeeks.com (2019, July 31), “A relation is in third normal form, if there is no transitive dependency for non-prime attributes as well as it is in second normal form.

A relation is in 3NF if at least one of the following condition holds in every non-trivial functional dependency X –> Y:

  • X is a super key.
  • Y is a prime attribute (each element of Y is part of some candidate key).

In other words, A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF)” (p.1). We need to utilize this form to best design the database as deemed fit, with multiple tables and entries running parallel and separated from primary key components.

As for the mission for the targeted organization, the database system and detailed technicalities will assist Swift with keeping track of all major items across system perimeters. The system will also track all customer, client and guest transactions and products|services items throughout their electronics experience. This effort should also help to fully-automate products and service transactions across the board via selections – through – checkout and payment submissions accordingly.

Structured Query Language (SQL) Scripts

DDL SQL – Create & Insert Statements

Create DDL

Insert DML

DML SQL – Delete & Update Statements

Updating and Deletion of DML

REPORT SQL – Select, Crosstab & Aggregate Function Statements

Analysis: How This Fulfills the Case Study Organization Mission & Goals

As our team is zeroing in on our final database product for The Swift Retail Store, we are excited to provide a fully comprehensive database code that should satisfy every necessary requirement based on the tables structure completed in Unit 2. This portion has, by far, taken the most extensive amount of time to create and execute in-assurance that every portion of every table was embedded and linked to their proper table relationships, for the ultimate success of the database system. I have not currently worked with database software so intimately before this project, so most of the programming effort was a challenge and brand new to me. I worked with fairly standard tactics to expand data processing perimeters as much as possible, taking into consideration there may be better methods to the ‘madness’ when an End-User logs in a request for particular data desired. Further, from my observation of the database created, there were certain tables that I noted could use some futuristic updates to serve as better efficiency for the database system altogether. One update is concerned with the Products Table and eventually inserting code that will essentially send an alert to the database sales analyst or the sales team, notifying them of the ‘low’ inventory currently experienced. By doing so, the company can rest assured they never ‘accidentally’ run out of products, especially those products that run more sales over others. Another relational table that will potentially need some upgrades in the long-run concerns the Orders Table, and having coding in-place to balance simultaneous transactions across multiple server technologies to assure optimal uptime, availability and fault tolerance accordingly. Certain tables created did not require a max record count of 20 as certain table relationships work together to ultimately reduce workloads on one another.

Database Administration Plan

TBD

Future Database System Implementation Plan

TBD

References

Ketchen, D., Short, J., Try, D., & Edwards, J. (2014, September 12). Vision, Mission, and Goals. Retrieved from:

https://opentextbc.ca/strategicmanagement/chapter/vision-mission-and-goals/

Third Normal Form (3NF). (2019, July 31). Retrieved from

https://www.geeksforgeeks.org/third-normal-form-3nf/

What is a database? (2020, November 23). Retrieved from:

https://www.oracle.com/database/what-is-database/

What is an Entity Relationship Diagram (ERD)? (n.d.). Retrieved December 02, 2020, from

https://www.lucidchart.com/pages/er-diagrams

CS660 Database Systems

Swift Retail Store: Data Definition and Data Manipulation Language (IP3)

Colorado Technical University

Josh Thomas

12/8/20

Table of Contents

Database System Overview 3

Database Goals and Objectives 4

How Proposed Database System Addresses Problem(s) 4

Mission and Goals Alignment 5

Analysis of How Project Fulfills Mission/Goals of Target Organization 6

Entity Relationship Model 7

Subjects of Interests 7

Rules 9

Entity Relationship (ER) Diagram 10

Table of Entities, Attributes, Relationships and Cardinality Constraints 10

Analysis of how Project Fulfills Mission/Goals of Target Organization 13

Structured Query Language (SQL) Scripts 14

DDL SQL – Create & Insert Statements 14

DML SQL – Delete & Update Statements 14

REPORT SQL – Select, Crosstab & Aggregate Function Statements 14

Analysis: How This Fulfills the Case Study Organization Mission & Goals 14

Database Administration Plan 15

Future Database System Implementation Plan 16

References 17

 

Database System Overview

The purpose of this project is to explore how the implementation of a fully-comprehensive database system can advance a retail store’s business bottomline via an electronics system ‘presence’ online. As database consultants, it will be our job and purpose to design a system efficient and comprehensive enough to handle any amount of traffic across the platform for product/service searches, information of products/services, etc. with advanced load balancing mechanisms throughout the centralized web server. According to Oracle.com (23 November, 2020), “A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database” (p.1).

Environment

The Swift Retail Store is my selected theoretical organization to propose a fully comprehensive and thoroughly designed database system that will enhance their online business ‘bottom-line’ from a technological and general standpoint. The Swift Retail Store will be comprised of 5-10 employees, two newly hired database technicians included in this range (once refreshed and running at prime optimization), and 2 store locations, each with a specific role played within the business operational scheme (store 1: products, store 2: service supply chain & operations), and one primary location that will handle all computational and database systems objectives. Both locations will reside within the Houston, TX area.

Database Goals and Objectives

The goals and objectives of the database is to offer a sufficient means for the store to run its electronic business transactions with optimal runtime, at least 95% secured uptime and availability, while have all system perimeters properly secured and its integrity fortified with the best in-class protocols, security mechanisms and advanced networking schemes accordingly to run its back-end web and database servers at a 24/7 capacity. There are shoppers of all kinds looking for online products and services at all times, so it will be a critical measure to assure our servers can be up-and-running at all times with confidence, assurance and minimal downtime during general maintenance and patch management schedules. Our proposed database software (i.e. Microsoft SQL Server Edition) will serve as the foundation to execute this advancement project, with futuristic intentions to upgrade to more advanced database software once the business growth demands the necessary upgrades.

How Proposed Database System Addresses Problem(s)

The database system currently installed within the main store has been non-functional, causing delays for electronic sales and general use implementations. From our analysis, the system has been compromised via weak security protocols and unpatched attack surfaces, thus rendering it essentially useless and unsafe to operate. Our newly designed database ideas will resolve such security issues and concerns for Swift Retail, as well as offer a very easy and efficient online experience both for the technicians making necessary updates to products and services, and the End-Users online shopping for desired items.

Mission and Goals Alignment

Before setting the mission and goals of this database technologies and design updates-project, we must first thoroughly understand both and their distinct descriptions in-relation. According to Ketchen (2014, September 12), “A mission statement which engages stakeholders will help develop an understanding of why they should support the organization and make clear what important role or purpose the organization plays in society – also called a ‘social license to operate’… To work toward achieving these overall aspirations, organizations also need to create goals—narrower aims that should provide clear and tangible guidance to employees as they perform their work on a daily basis. The most effective goals are those that are: Specific, Measurable, Achievable, Realistic, and Time-bound (S.M.A.R.T.)”. In-connection, we must assure that our short-term goals throughout this process are strategically aligned with our overall mission: to create an optimal database solution for The Swift Retail Store, with comprehensive capabilities to operate efficiently and effectively as an online vendor, satisfaction guaranteed. Our goals should be easy and measurable enough to accomplish on a daily and weekly basis, and should offer standard ‘checkpoints’ to determine the status of each segment without the need for much rework as we go. We have a synopsis of our goals as they stand today, with certain time frames designed for potential updates, changes, and alterations to any portion of the database system during design and implementation cycles. For the purposes of brevity, we have utilized the S.M.A.R.T. System to sum up our primary goals that will support our end result:

  • S.pecific – To fully revamp current database system processes with new database technologies with advanced capabilities to seamlessly drive business bottom-line objectives with customer-base.
  • M.esurable – Day-to-day assignments should allot a 2-5 hour range to be completed, and redesigned or escalated with extra support to assure completion within the set time frame.
  • A.ggressive – Every goal should offer room for professional and leadership skills advancement to ‘stretch’ every team member’s capabilities confidence for future project assignments.
  • R.ealistic – Each assigned responsibility should be doable and able to completed each day assigned, in-scope from a standard perspective.
  • T.ime-bound – As the team works through project objectives, time management will be considered top-priority to assure completion via the set due date as negotiated. This will include, but not limited to, after and weekend hours worked to assure our clients have their product before or by the deadline agreed upon.

Analysis of How Project Fulfills Mission/Goals of Target Organization

Our overall analysis of project mission & goals fulfillment post-completion will be determined by Swift’s online presence and customer-base and satisfaction-increase accordingly, within the first 3 months after the launch of the improved database system. Although marketing and sales are the major departments for customer growth, the database serves as the ‘back-end’ mechanism to assure all customer, both new and current, have the ability to surf Swift’s front-end database with minimal interruption via split load protocols, web traffic sufficiency across load, and customer satisfaction with shopping experience both from an application and product lines standpoint. Current project efforts fulfill Swift’s end-goal with a viable database design advanced enough to handle their current electronics on-line business, with database server coverage left for future improvements and upgrades to overall system capacity.

 

Entity Relationship Model

Subjects of Interests

  • Customers
    • Transportation|Trucking Organizations
    • Truck Drivers (Heavy Duty, HazMat, Mid-Size, Etc.)
    • Truck Mechanics (Heavy Duty, HazMat, Mid-Size, Etc.)
  • Orders
    • Single Units (1)
    • Grouping Packages (4-6)
    • Lg. Bundled Packages (6+)
  • Products
    • Major Truck Parts
      • Lights
      • Cables
      • Fluids
      • Electrical Components
      • Paints
      • Straps
    • Trucking Tools
    • Filters
    • Heavy Duty Tires
    • PPE
  • Services
    • Comprehensive Diagnostics
    • Oil Maintenance
    • Fluid(s) Checks & Refills
    • Tires Maintenance
    • Tires Rotation
    • HVAC Diagnostics
    • Electrical Diagnostics
    • State Inspections
  • Product(s) Training Workshops
    • General Supplies
    • Mechanical
    • Electrical
    • HVAC
    • Hydraulics
    • Suspension
    • Brakes
  • Utilization & Installation(s) Workshops
    • Mechanical Parts
    • Electrical Parts
    • Brakes Parts
    • HVAC Systems
    • Hydraulics Systems
    • Suspension Systems

Rules

  • Every Customer|Client will be connected to a single Customer ID
  • Each customer|client can have similar locations
  • All Member IDs will be required for any products and service promotionals across stores
  • All Members will need to present IDs as requirements for all workshops
  • Products per customer will be tracked via Customer ID.
  • Every Workshop can offer additional Workshops for discounts or promotions
  • Workshops will occur at one location per Event
  • Each Workshop will be sponsored by one Branch
  • Each Store will be recognized as a single unique location
  • Every Swift Employee is connected with a single store as their primary location
  • Multiple employees can have similar locations
  • Employee Types include: Representatives, Associate Manager, General Manager or Store Manager
  • Products may have multiple promotions active
  • Every Product should have the following: Product Name, Product Category, Reorder Quantity and Reorder Threshold setup within the system.
  • Each Product can exist in a single category only.
  • Orders are able to have zero or more Memberships ( non-members are allowed to shop as ‘Guest’ shopper).
  • Every Order must be associated to a single Store
  • Every Order will be entered by a single Employee
  • Orders must contain one or more products.

Entity Relationship (ER) Diagram

Below is a simple ER Diagram, to be used through the new database design process:

Shareable link for easier review is include: https://lucid.app/lucidchart/invitations/accept/2c2cc79f-b141-4352-b9e6-4ef114cfca7c

Table of Entities, Attributes, Relationships and Cardinality Constraints

Entities (Tables) Attributes

(Rows)

Relationships Cardinality Constraints
Customers CustomerID (PK) (Char10) Has MemberID 1:1
Customer Name (Varchar50)
StoreID (Char10) Has a Location 1:Many
MemberID (Char10) Directly associated to Every Customer 1:1
Contact Phone Number (Int)
Contact Email (VarChar200)
Store Branch StoreID (Char10) Can Host Workshops

Can Create Orders

Has a Location

Has Employees

1:Many

1:Many

1:1

1:Many

Store Name (Varchar50)
LocationID (Char10) Has a Location 1:1
OpeningTimes (Varchar500)
Employee EmployeeID (Char10) Connected a Store Branch

Can Create Orders

1:1

1:Many

Employee Position (Varchar25)
Employee Name (Varchar50)
LocationID (Char10) Has a Location One Location can have more than one Employee
StoreID (Char10) Is connected to a Store 1:1
PayRate (Decimal 6,2)
Pay Occurrence (Varchar20)
Member MemberID (Char10) Requires a Customer

Registers at Workshops

Msy receive Promotions

1:1

1:Many

1:Many

Member Type (Varchar50)
Member Level (Char2)
CustomerID (Char10) Has Member ID 1:1
WorkshopID (Char10) Member ID Required to Register 1:Many
Total Spent (Decimal 10,2)
PromotionID (Char10) Can have multiple Promotions 1:Many
Orders OrderID (Char10) Connected to Stores

Initiated by Employee

May have Promotions

May have Member Status

Must have Products

1:1

1:1

1:Many

1:1

1:Many

Transaction Date (Date)
MemberID (Char10) Can have Member ID 1:1
StoreID (Char10) Connected to Store 1:1
EmployeeID (Char10) Employee makes Sale 1:1
Total Sale (Decimal 10,2)
Sales Tax (Decimal 5,2)
PrmotionID (Char10) May have Promotions 1:Many
ProductID (Char10) Must have Products 1:Many
Promotions PromotionID (Char10) Can be connected to Member ID

Can be Associated to Workshops

Can be Associated to Direct Product Sales

1:Many

1:Many

1:Many

Promotion Name (Varchar200)
Promotion Type (Varchar50)
Discount (Decimal 5,2)
Date Start (Date)
Date End (Date)
Products ProductID (Char10) May have Promotions 1:Many
Product Name (Varchar200)
Product Brand (Varchar100)
Product Category (Varchar 50)
Description (Varchar2500)
Count Available (Int)
Reorder Threshold (Int)
Reorder Quantity (Int)
PromotionID (Char10) Can have more than 1 simultaneously 1:Many
Workshops WorkshopID (Char15) Is connected to a Store Branch

Requires Member Status to Attend

Performed at Location

1:1

1:Many

1:1

Workshop Name (Varchar200)
Workshop Purpose (Varchar2500)
Workshop Date (Date)
Attendee Cap (Int)
MemberID (Char10) Defines Allowed Attendees 1:Many
Count of Attendees (Int)
PromotionID (Char10) Can have Promotions 1:Zero or More
LocationID (Char10) Performed at Location 1:1
StoreID (Char10) Owned by Store 1:1
Location LocationID (Char10) Is Connected to Customers

Is Connected to Employees

Is Connected to Stores

Is Connected to Workshops

1:Many

1:Many

1:1

1:Many

Address Line 1 (Varchar150)
Address Line 2 (Varchar150)
City (Varchar50)
State (Char2)
Zip Code (Char5)
County (Varchar50)
Country (Varchar50)

Analysis of how Project Fulfills Mission/Goals of Target Organization

The purpose of this section is to add detailed information on all technical components of the projected database system, to assist Swift with their electronics shop-vending aspirations. Embedded into the system will be the Third Norm Form (3FN) as its primary design. According to Geeksforgeeks.com (2019, July 31), “A relation is in third normal form, if there is no transitive dependency for non-prime attributes as well as it is in second normal form.

A relation is in 3NF if at least one of the following condition holds in every non-trivial functional dependency X –> Y:

  • X is a super key.
  • Y is a prime attribute (each element of Y is part of some candidate key).

In other words, A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF)” (p.1). We need to utilize this form to best design the database as deemed fit, with multiple tables and entries running parallel and separated from primary key components.

As for the mission for the targeted organization, the database system and detailed technicalities will assist Swift with keeping track of all major items across system perimeters. The system will also track all customer, client and guest transactions and products|services items throughout their electronics experience. This effort should also help to fully-automate products and service transactions across the board via selections – through – checkout and payment submissions accordingly.

Structured Query Language (SQL) Scripts

DDL SQL – Create & Insert Statements

Create DDL

Insert DML

DML SQL – Delete & Update Statements

Updating and Deletion of DML

REPORT SQL – Select, Crosstab & Aggregate Function Statements

Analysis: How This Fulfills the Case Study Organization Mission & Goals

As our team is zeroing in on our final database product for The Swift Retail Store, we are excited to provide a fully comprehensive database code that should satisfy every necessary requirement based on the tables structure completed in Unit 2. This portion has, by far, taken the most extensive amount of time to create and execute in-assurance that every portion of every table was embedded and linked to their proper table relationships, for the ultimate success of the database system. I have not currently worked with database software so intimately before this project, so most of the programming effort was a challenge and brand new to me. I worked with fairly standard tactics to expand data processing perimeters as much as possible, taking into consideration there may be better methods to the ‘madness’ when an End-User logs in a request for particular data desired. Further, from my observation of the database created, there were certain tables that I noted could use some futuristic updates to serve as better efficiency for the database system altogether. One update is concerned with the Products Table and eventually inserting code that will essentially send an alert to the database sales analyst or the sales team, notifying them of the ‘low’ inventory currently experienced. By doing so, the company can rest assured they never ‘accidentally’ run out of products, especially those products that run more sales over others. Another relational table that will potentially need some upgrades in the long-run concerns the Orders Table, and having coding in-place to balance simultaneous transactions across multiple server technologies to assure optimal uptime, availability and fault tolerance accordingly. Certain tables created did not require a max record count of 20 as certain table relationships work together to ultimately reduce workloads on one another.

Database Administration Plan

TBD

Future Database System Implementation Plan

TBD

References

Ketchen, D., Short, J., Try, D., & Edwards, J. (2014, September 12). Vision, Mission, and Goals. Retrieved from:

https://opentextbc.ca/strategicmanagement/chapter/vision-mission-and-goals/

Third Normal Form (3NF). (2019, July 31). Retrieved from

https://www.geeksforgeeks.org/third-normal-form-3nf/

What is a database? (2020, November 23). Retrieved from:

https://www.oracle.com/database/what-is-database/

What is an Entity Relationship Diagram (ERD)? (n.d.). Retrieved December 02, 2020, from

https://www.lucidchart.com/pages/er-diagrams

 

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
Open chat
1
You can contact our live agent via WhatsApp! Via + 1 929 473-0077

Feel free to ask questions, clarifications, or discounts available when placing an order.

Order your essay today and save 20% with the discount code GURUH