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 visualize database structure. The project then details the creation of a database using MySQL, including the creation of tables, SQL queries, and the use of MySQL Workbench. Forms are created to enter sales and purchase orders, and the project delves into data analysis using pivot tables and charts on two datasets: employment rates and a public art collection. The analysis includes identifying key findings and insights from the datasets. The project concludes with a discussion of the database design, data analysis and the tools used, referencing relevant sources. The project also uses MS Access to create a relationship diagram and forms. The assignment covers the entire process from database design to data analysis and visualization.

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