Database System Project: Sales and Marketing Management Analysis

This assignment presents a comprehensive project on designing and analyzing a sales and marketing database system. It begins with an introduction to the importance of critical thinking and technical skills in solving business problems, followed by the creation of an Entity Relationship (ER) diagram to...Read More

Sales and Marketing Management database System
1

Contents
INTRODUCTION...........................................................................................................................3
PART 1............................................................................................................................................3
PART 2..........................................................................................................................................22
CONCLUSION..............................................................................................................................27
REFERENCES..............................................................................................................................28
2

INTRODUCTION
Critical thinking and technical skills are one of the most basic requirements that are required
to solve any kind of authentic business problem. For this it is important to conceptualize business
problem, apply required technical skills and then solve complex problem. This assignment will
lay emphasis on Entity relationship diagram, creation of database using MySQL, creation of
forms to enter sales and purchase orders, analysis of pivot tables and graphs of two large data
sets.
PART 1
Design
Figure 1 ER Diagram
Entity relationship diagram is basically representing the graphical ways to establish a relational
between one or more table. It may consist of primary and foreign key for establishing the
relational between them.
Primary key: It is a type of special key that can use in relational database tables which designed
to uniquely identify all records of tables. It must contain unique values for each row (Sheridan,
2019). For Example- In customer table, customer_id is a primary key that can establish a
relationship with another table.
3

Foreign key: It is based on the group or columns in relational database that provide link between
one or more tables. Foreign key is also known as referencing key. It also become act as a cross
references among database tables because it references the primary key of another table. For
Example- In customer_table, order_id shows as a foreign key because it will establish relational
between customer and order tables.
Data Dictionary
Entities Attributes
Customer Customer_id (PK)
Customer_first_name
Customer_last_name
Customer_phone_no
Customer_address
Product_id (FK)
Order_id (FK)
Product Product_id (PK)
Product_name
Product_type
Product_quantity
Product_price
Order_detail Order_detail_id (PK)
Product_id (FK)
Order_id (FK)
Product_quantity
Delivery Delivery_id (PK)
4

Delivery_date
Supplier_id (FK)
Sales Sale_id (PK)
Sale_date
Sale_time
Product_id (FK)
Supplier Supplier_id
Supplier_name
Supplier_phone_no
Supplier_address
Sale_id (FK)
Order Order_id (PK)
Order_date
Table :1
5

SQL (Structure Query language)
It is based on the standard programming language to design database system because it
helps for create data, maintain, manipulate and retrieve the relational DB (Roy-Hubara and
Sturm, 2019). It easily executes the queries against the database in proper manner.
Create statement:
6

Insert Statement
7

8

By using MySQL Workbench
9

10

11

12

Database Tables:
Figure 2 Customer table
Figure 3 Sales table
13

Figure 4 Product table
Figure 5 Order detail table
14

Figure 6 Delivery table
Figure 7 Customer table
15

Identify the total sales of each product
Identify delivery Confirmation of every sales
16

By using MS Access
17

Relationship Diagram
18

Forms
19

20

21

PART 2
1st Dataset details
Dataset name: Employment Rates
a). URL of Dataset
https://data.london.gov.uk/dataset/employment-rates
b). Description of Dataset
This dataset consists of number and percentage of people between age group of 16 to 64
years who are employed by sex for rolling quarters ever since 1992 by region and country.
c) Screen capture showing the first page of excel spreadsheet containing data set.
2. Create Pivot chart for analysing to utilise data set and generate output.
22

3. Analysis of findings from Pivot table and chart
After selecting the data set of employement rate where it can be determined the sum of
employement rate according to age group in between 16 to 64. In above pivot chart or table
shown the overall rate of employement in specific age groups.
It also selecting the Pivot chart field to generate accurate results and outcome of data set
in proper manner.
23

It has been selecting the meta data as per descriptions of employment rate in UK.
2nd Dataset details
Dataset name: Public Art Collection
a). URL of Dataset
https://data.gov.au/dataset/ds-brisbane-1e11bcdd-fab1-4ec5-b671-396fd1e6dd70/
distribution/dist-brisbane-3c972b8e-9340-4b6d-8c7b-2ed988aa3343/details?q=
b). Description of Dataset
This dataset consists of details of public art collection held in Brisbane City Council area.
it consists of columns such as item name, artist, location, material, description, installed,
longitude and latitude.
24

c) Screen capture showing the first page of excel spreadsheet containing data set.
2. Create Pivot chart for analysing to utilise data set and generate output.
25

3. Analysis of findings from Pivot table and chart
As per analysis, it can be identified the sum of installed (Artist) and its total values. By using
Pivot table, it also selecting the specific values for particular entities. There are multiple number
of artist and found that the overall installed sum in every year.
26

CONCLUSION
As per discussion, it can be determined the entities for developing database of sales and
marketing where it helps for collecting large amount of data effectively. For successfully
calculating the values of large data set by using Pivot chart. Furthermore, it also designing the
database system by using MySQL work bench and Ms Access etc. +
27

REFERENCES
Book and Journals
Sheridan, M., 2019. They Ask, You Answer: A Revolutionary Approach to Inbound Sales,
Content Marketing, and Today's Digital Consumer, Revised & Updated. John Wiley &
Sons.
Roy-Hubara, N. and Sturm, A., 2019. Design methods for the new database era: a systematic
literature review. Software & Systems Modeling. pp.1-16.
28