CS660 Database Systems
Swift Retail Store: Data Definition and Data Manipulation Language (IP3)
Colorado Technical University
Josh Thomas
12/8/20
Database Goals and Objectives 4
How Proposed Database System Addresses Problem(s) 4
Analysis of How Project Fulfills Mission/Goals of Target Organization 6
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
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.
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.
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.
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:
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.
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
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) |
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:
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.
Create DDL
Insert DML
Updating and Deletion of DML
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.
TBD
TBD
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
Database Goals and Objectives 4
How Proposed Database System Addresses Problem(s) 4
Analysis of How Project Fulfills Mission/Goals of Target Organization 6
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
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.
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.
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.
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:
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.
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
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) |
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:
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.
Create DDL
Insert DML
Updating and Deletion of DML
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.
TBD
TBD
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
Database Goals and Objectives 4
How Proposed Database System Addresses Problem(s) 4
Analysis of How Project Fulfills Mission/Goals of Target Organization 6
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
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.
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.
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.
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:
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.
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
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) |
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:
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.
Create DDL
Insert DML
Updating and Deletion of DML
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.
TBD
TBD
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
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.
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 moreEach 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 moreThanks 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 moreYour 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 moreBy 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