Excel代写 | CS669 Term Project Iteration 4

这个project是构建一个商店历史数据查询系统
CS669
Term Project Iteration 4
MarketingTracker Project Direction Overview:
I want to develop a platform named “MarketingTracker” for global retail chain stores
company. When an employee uses the platform, they will find the full purchase, which
included online purchases history and the history across all stores over the world, the
system will automatically update the data every 30 minutes. The reason I am building this
platform is that lots of traditional stores are facing a big challenge from E-commerce
companies such as Amazon and Alibaba, but they still have their advantages, so they are
looking for a better way with their business growth.
Corporation, sales and inventory departments could access this platform, once the
salesperson sell the products to a customer, he/she will use his/her device to put the data
into the system, which include the brand of product, number of purchases, time and date
when product sold, who is assistant with this purchases, etc. Inventory people could
prepare the product base on the systems to avoid the shortage. The corporation
department could look at the overview of sales performance to decide the products/store
allocation and marketing growing.
The reason I am choosing this topic is I still prefer to buy product in-store rather than online,
but I do think they need to improve, for example when you spend 30 minutes’ drive to a
store but they told you the product you looking for are out of stock, it makes the customers
feel depress and may change their shopping behavior to online shopping. So I hope
MarketingTracker could help retail stores for better management.
Since I want the corporation team could use MarketingTracker for better decision making,
So it does require lots of programming components, for this course, I am focusing on
database component. The database will store location(address/city/state/country), the
detail of each purchase (brand of product/type of product/quantity of sold/time of
purchases/place of purchases) and sales detail (Last name/ first name/ gender/ store he/she
working/number of sales).
Use Cases and Fields
One important usage of the database is when employees could sign up and access in to the
platform.
Account Signup Use Case for Employee
1. The employee who visit MarketingTracker application.
2. The application asks them to create an account for new users.
3. User enters their information and account is created in database.
From database perspective, the use case requires storing information from account, all the
steps above required employee to input their information not gather from database.
Significant fields are what I am choosing on the table below.
Field What is Stores Why it’s Needed
First Name First name of account
holder
This is important to display
person’s name for easier
communication and
searching purpose.
Last Name Last name of account holder This is important to display
person’s name for easier
communication and
searching purpose.
Employee ID Employee’s unique ID
number
This is important for
security purpose since
MarketingTracker is an
internal application.
Department Department of this
employee
This is important since
different department should
have different interface.
Another important usage of database is when a purchase is made, the system will
automatically store in MarketingTracking.
Account Signup Use Case for Vendor
1. The vendor who visit MarketingTracker application.
2. The application asks them to create an account for new users.
3. User enters their information and account is created in database.
Similar to the previous step, since MarketingTracking could also let third parties person with
authorization to access the system, so we could another login/sign in page for third parties
(vendors mostly) to access.
Field What is Stores Why it’s Needed
First Name Frist name of account
holder
This is important to display
person’s name for easier
communication and
searching purpose.
Last Name Last name of account holder This is important to display
person’s name for easier
communication and
searching purpose.
Vendor ID Vendor’s unique ID number This is important for
security purpose since
MarketingTracker is an
internal application.
Company name Name of Vendor’s Company This is important to display
Vendor’s company name for
easier communication and
searching purpose.
Purchase Tracking Use Case
1. The person visits a store/go online and make a purchase.
2. The MarketingTracking will record the information which related to the purchase
such as, brand name, type, price, sale time, who assist with etc.
Step 1&2 requires storing purchases information, step 3 apply will gather the information
directly from database (except who assist with). Significant fields are what I am choosing on
the table below.
Field What is Stores Why it’s Needed
Brand Name Name of the sold product It is useful for future ETL
analysis, so corporation
team could make better
decision on vendor
choosing.
Product ID Unique ID number for each
product
It is important for database
to stores the product record
with same format.
Type Type of sold product such as
clothing/shoes/Jewelry
It is useful for future ETL
analysis, so corporation
team could make better
decision on vendor
choosing.
Price The price the sold product It is useful for future ETL
analysis, so corporation
team could make better
decision on pricing analysis.
Purchase Time The time which product was
sold
It is useful for future ETL
analysis, so corporation
team could make better
decision on type of product
choosing.
Who assist with/if online go
with NA
Name of sales person who is
helping with, if online than
return NA
It is important for Sale’s
manager to see who has the
better performance.
Location Location of sold product
form by street
number/city/state/country.
It is important for
corporation to see the
overall sales performance
by location.
Another important usage of database is for employee to lookup sales performance.
Sales Performance Check Use Case (Employee perspective)
1. Employee sign into MarketingTracking
2. Employee selects options to lookup store location
3. Employee selects options to lookup the sales/inventory
4. MarketingTracking pulls a list of past purchases and gives employee option of lookup
way (by name/by quantity/time/price etc.).
5. The application pulls all the purchase which matching the criteria.
6. Employee could download the information or doing the analysis on application.
7. Employee could click Performance button to find what this specific store/product is
performing compare to the others (user could select who you want to compare or all
the store/include online)
Sales Performance Check Use Case (Vendor perspective)
1. Vendor sign into MarketingTracking
2. Vendor selects options to lookup store location
3. Vendor selects options to lookup the sales/inventory
4. The application pulls all the purchase which matching the criteria.
5. Vendor could check analyze the sales/inventory result and have a better preparation
for avoiding shortages or stockpile.
The database will use the fields in first, second and third use cases, this case will help
corporation team to see the overall sales performance in different perspective. Help vendor
for their product preparation.
Summary and Reflection
Since my goal is for those traditional retail companies has better marketing prediction, so
MarketingTracking will be a useful tool for the employee to look at their sales performance,
the vendor can also access this application (with permit) to have a better preparation. I
created a different field for the ETL processing, so the corporation team could use the
information from MarketingTracking to analyze where is the best-selling place/when is the
peak period/which products are more comfortable to sell/who sells the most, etc. All those
information will be useful for the ETL processing; marketing team can use this report to
determine the future steps, the sales team could use it to track their sales performance, it
will be very useful for management and marketing prediction. For the vendor’s perspective,
since they can access our system as third parties, so after check the sales performance, they
could have a better preparation to avoiding shortages or stockpile.
Structural Rules
Account Signup use case has a few unique things process. The database storing the
Employee and vendor’s personal information which is step 1 and 2, for step 3, Employee or
vendor could access and check the sales/inventory statues, I am focusing on how database
stores employees/vendors information, after they login, it will provide them different
interface for their using purpose and security reason.
Sales Performance check use case(Employees perspective) let employees in different
department to check the sales status for different decision making, for example, sales team
and look up the sales performance and gives the best-selling person award to find a
stimulate plan to increase sales performance, marketing team could look at the regression
of different selling product to give promotion advice on specific product. The corporation
team could look at the overall sales performance base on different facts to decide
where/when open/close a new/old store etc.
Sales Performance check use case (Vendors perspective) allow vendors to check the sales
and inventory status; they will have a different interface with the Employee’s login page;
this is for security purpose. From the vendor perspective, their meaningful use for the
system is to check which kind/brand of products at where/when has the bestselling
performance so that they could have more preparation on the inventory; it could avoid
shortage and stockpile.
Now, we can create some structural database rules below.
1. For vendor perspective, each Product ID associated with a Vendor (Company name),
each Vendor may be associated with many products.
The reason I created this role is because each vendor name and ID is unique, so it
will be easier for them to identify themselves. Vendor ID and company name will be
created once they register the system, no matter there is any sales or inventory
changes, once any sales or inventory changes, they will have a Product ID associate
with it.
2. For employee perspective, each sold product associated with an employee ID, each
employee id may be associated with many products.
Similar to the previous rule, since each Employee ID is unique, so it will be easier for
the identification, Employee ID will be created once they register the system, once
any product was sold and inventory changed, they will have a product ID associate
with it. Each Employee ID can have many Product ID.

3. Each sold or inventory changes store in database, the system will send nonfiction to
the vendor if any shortage or stockpile happen.
This rule indicates that every sold product will scan by sales person, this is many to
one relationship, once some products sold quickly and have less inventory, vendor
will get the notice, this could be one to many.

Entity-relationship diagram
Content above shows the rules I set for my system right now.
1. Each product associate with an Employee ID, each Employee ID may associate with
many products.
2. Each sold product associated to inventory and them reflect to associated to Vendor
ID, each vendor ID may associate to many products.
3. Each sold product will be recorded by an employee, it will automatically change the
record in inventory, so once shortage or stockpile happen, vendor could receive the
notification from the system.
Below is my ERD diagram for now.
Summary and Reflection
My MarketingTracking project is a database that could use by any retail stores, it could store
the employee and vendor’s personal information for better identification, it could also store
the product ID and its feature for employee and vendors to analyze marketing/inventory
plan, once the product was sold, the sales person will scan the item and store the
information to the database, each product associated with one employee ID and inventory
will automatically minus the number of sold product in record, once inventory has shortage
or stockpile, it will send the message to notification center and notification center will give
the message to vendor, for the vendor perspective, vendor could also access to see the
Product center to check which product is popular or hard to sell, so they could have a better
preparation to avoid any shortage or stockpile.
Since my primary goal for this system is helping retail store have better management and
understanding with their stores and product, so different department could access to this
platform and check the selling performance and decided for the next plan, for example,
corporation team could use the sold product information with its features to determine
which area could have more potential to open a new branch. This will be part of the ETL
process, which is my major goal of this project.
Also, I am wondering if I could have more use cases since I have two major ones right now. I
will keep learning and see if I could have more use cases in the future.
Adding Specialization-Generalization to MarketingTracking
After I review the cases, I have idea of adding a few features to my platform.
Account Signup Use Case for Employee
1. The employee who visit MarketingTracker application.
2. The application asks them to create an account for new users.
3. User enters their information and account is created in database.
As I notice, management level or stakeholders should have a different interface and
authorization to access more features.
Account Signup Use Case for Employee (New)
1. The employee (management level or stakeholders) who visit MarketingTracker
application.
2. The application asks them to create an account for new users.
3. User enters their information and account is created in database.
4. MarketingTracking provides a different interface from regular version, this version
has more features and able to access into vendor side as well.
For the step 4, the reason why I am creating this new version is I want to give management
level or stakeholders an overall of company’s sales status, since they will not have time to go
each detail, so give than authorization to access in different features will be more comfortable
for them to navigator their resources. I will not create a version for vendor perspective since
this platform is a major service for the company’s ETL processing; the vendor should only have
access to look up the sales and inventory record.
Another feature I am adding is online products status check.
Sales Performance Check Use Case (Employee perspective)
1. Employee sign into MarketingTracking
2. Employee selects options to lookup store location
3. Employee selects options to lookup the sales/inventory
4. MarketingTracking pulls a list of past purchases and gives employee option of lookup
way (by name/by quantity/time/price etc.).
5. The application pulls all the purchase which matching the criteria.
6. Employee could download the information or doing the analysis on application.
7. Employee could click Performance button to find what this specific store/product is
performing compare to the others (user could select who you want to compare or all
the store/include online)
Sales Performance Check Use Case (Employee perspective) (New)
1. Employee sign into MarketingTracking
2. Employee selects options to lookup store location
3. Employee selects options to lookup online products
4. Employee selects options to lookup the sales/inventory
5. MarketingTracking pulls a list of past purchases and gives employee option of lookup
way (by name/by quantity/time/price etc.).
6. The application pulls all the purchase which matching the criteria.
7. Employee could download the information or doing the analysis on application.
8. Employee could click Performance button to find what this specific store/product is
performing compare to the others (user could select who you want to compare or all
the store/include online)
Sales Performance Check Use Case (Vendor perspective)
1. Vendor sign into MarketingTracking
2. Vendor selects options to lookup store location
3. Vendor selects options to lookup the sales/inventory
4. The application pulls all the purchase which matching the criteria.
5. Vendor could check analyze the sales/inventory result and have a better preparation
for avoiding shortages or stockpile.
Sales Performance Check Use Case (Vendor perspective) (New)
1.Vendor sign into MarketingTracking
2.Vendor selects options to lookup store location
3.Vendor selects options to lookup online products
4.Vendor selects options to lookup the sales/inventory
5.The application pulls all the purchase which matching the criteria.
6. Vendor could check analyze the sales/inventory result and have a better preparation
for avoiding shortages or stockpile.
The reason I am adding step 3 on Employee version and step 3 on Vendor version is most of
the big retail stores are starting their e-commerce business(online shopping), so some of the
sold products may not belong to any salesperson, the system will mark the Employee ID which
associate with these products as NULL, and online shopping is a significant component for
nowadays, so vendor should also have the authorization to access and have a prediction for
the future manufactory. So, adding the version of an online product is very useful for
marketing analysis.
Now, another fundamental rule is management level, or stakeholders could have access to
the overall sales performance of the whole company, and adding an online product section is
more practical for nowadays retail business.
Below show my most recent structural rules:
Retail:
1. Each management level(stakeholders) Employee ID associate with many employee
and vendor ID.
2. Each product is associated with an Employee ID; each Employee ID may be
associated with many products.
3. Each sold product associated to inventory and them reflect to associated to Vendor
ID, each vendor ID may associate to many products.
4. Each sold product will be recorded by an employee, it will automatically change the
record in inventory, so once shortage or stockpile happen, vendor could receive the
notification from the system.
Online:
1. Each management level(stakeholders) Employee ID associate with many employee
and vendor ID.
2. All the products which sold online will recorded as Employee ID = NULL, so many
products associated with one Employee ID.
3. Each sold product associated to inventory and them reflect to associated to Vendor
ID, each vendor ID may associate to many products.
I add to my ERD diagram as below:
MY MarketingTracking Relationship Classification and Associative Mapping
Below is the Conceptual ERD for my project. And the associative relationships.
Zebang Ye | February 29, 2020
Employee
Product ID
Sold Product Inventory
Vendor
Notification
Mangment
Sold Product(online)
Management and Employee/vendor relationship is one to many; each
employee_management_id associate to many employee/vendor_id.
Employee_id or vendor_id to product_id is one to many, each employee_id or
vendor_associate to many products.
Notification and Vendor is one many to many relationships, different product_sold_id in
Notification will send message to different vendor_id which associate with these sold
products.
My MarketingTracking Specialization-Generalization Mapping
After all the steps above, I have three specialization generalization in my ERD, one is for
Employee and Vendor, one is for Product and another one is for Notification.
Zebang Ye | February 29, 2020
Managment
employee_managment_id
Decimal(12) PK
Employee
employee_id
Decimal(12)PK
Vendor
vendor_id
Decimal(12)PK
Sales(Retail and Online) and Inventory
product_id Decimal(12) PK
product_sold_id Decimal(12) FK
product_name Vachar
product_type Vachar
product_location Vachar
Product_sold_time Vachar
Notification
vendor_id Decimal(12)PK
product_sold_id Decimal(12)PK
Summary and Reflection
My MarketingTracking system is more transparent and convinces right now; mainly, after I
am adding the management and online features, the whole system works more efficiently for
the future ETL processing, but I still need to work on foreigners and first essential part to
making sure my SQL works appropriately. Meanwhile, I think all relationship is clear and ready
to go for my MarketingTracking project.
Adding Attributes to your DBMS physical ERD
Table Attributes Datatype
Management Management_name Varchar((16)
Management Management_id Decimal(7,2)
Employee Employee_name Varchar(16)
Employee Employee_id Decimal(7,2)
Vendor Vendor_name Varchar(16)
Vendor Vendor_id Decimal(7,2)
Sales (Retail and Online) and
Inventory
Product_id Decimal(7,2)
Sales (Retail and Online) and
Inventory
Product_sold_id Decimal(7,2)
Basic Database ER Diagram (Crow’s Foot)
Zebang Ye | February 29, 2020
Employee
employee_id
Decimal(12)PK
Product ID
product_id Decimal(12)PK
product_name Varchar
product_type Varchar
product_price Decimal(12)
product_location varchar
Sold Product(Retail)
product_sold_id Decimal(12) PK
product_sold_date Varchar
Inventory
product_left_id Decimal(12)PK
Vendor
vendor_id
Decimal(12)PK
Notification
vendor_id Decimal(12)PK
product_left_id Decimal(12)FK
Mangment
employee_Managment_id
Decima(12)PK
Sold Product(online)
product_sold_id_online Decimal(12) PK
product_sold_date Varchar
Sales (Retail and Online) and
Inventory
Product_name Varchar(255)
Sales (Retail and Online) and
Inventory
Product_type Varchar(255)
Sales (Retail and Online) and
Inventory
Product_location Varchar(255)
Sales (Retail and Online) and
Inventory
Product_sold_time date
Notification Vendor_id Decimal(7,2)
Notification Product_sold_id Decimal(7,2)
Here is the ERD with attributes below.
The Sales(Retail and Online) and inventory should have three more entities, for better
classification, I create three more tables Sold Product(Retail); Sold Product(Online),Inventory,
the reason I am adding these three is because my main purpose for this project is for ETL
Processing, more detail will be helpful for marketing analysis and inventory preparation.
This is what ERD looks like after I add in.
Indexing: Below is the list of all indexed.
Employee_Managment_id
Employee_id
Vendor_id
Product_id
All my foreigner keys need the index, below is the list.
Product ID.product_sold_id
Product ID.product_sold_date
Sold Product(Retail).product_sold_id
Sold Product(Retail).product_sold_date
Sold Product(online).product_sold_id
Sold Product(online).product_sold_date
Inventory.product_left_id
Below is the script for creating all tables in my database.
Below is the screenshot of some index.
Summary and Reflection
My MarketingTracking system is more transparent and convinces right now, I have all my
tables and index so far, later on, I will add date to my table and run it.