University Database Report: Sales and Marketing System Design

This report provides a comprehensive analysis of a sales and marketing database management system. It begins with an introduction to the importance of such a system in managing inventory, direct sales, and marketing operations. The report details the concepts of database management systems (DBMS), emphasizing the need to reduce data redundancy and maintain data integrity. An Entity Relationship Diagram (ERD) is developed to capture detailed requirements for the database system. The report then outlines the rules for creating an Extended Entity Relationship Diagram (EERD), followed by a derivation of a set of well-normalized tables and populating those tables with data. The report includes SQL query statements and their results, demonstrating how to create, retrieve, update, and delete (CRUD) information from the database. The report includes a discussion on sales and marketing management, the concepts of DBMS, ERD, rules for EERD, reduced relational schemas, entity sets, key attributes, tables, and queries. The report also includes a pivot table analysis of employment data in the UK. The conclusion highlights the benefits of the sales and marketing database in terms of efficiency and data integrity.

Running head: DATABASES AND BUSINESSES INTELLIGENCE
Databases and businesses intelligence
Name of the student
Name of the University
Author note

1
DATABASES AND BUSINESSES INTELLIGENCE
Table of Contents
Introduction................................................................................................................................3
Discussion..................................................................................................................................3
DBMS Concepts.........................................................................................................................3
ERD............................................................................................................................................4
Rules for EERD......................................................................................................................4
Reduced Relational Schemas.................................................................................................5
Entity Sets Representing as Schemas.....................................................................................5
Key attributes.............................................................................................................................5
Tables.........................................................................................................................................5
Queries.......................................................................................................................................5
Conclusion..................................................................................................................................6
References..................................................................................................................................7

2
DATABASES AND BUSINESSES INTELLIGENCE
Introduction
The sales and marketing database management system is referred to the process and
system to manage the marketing and sales that is it manages the inventory of the organization
associated with the technological system (Laudon & Laudon, 2016). The system solves the
different problem which affecting direct purchase and sales in the marketing environment.
Without proper implementation of marketing and sales database in an organization, a large
market organization may run out of stock of some important items. In this report, we develop
an Entity Relationship Diagram (ERD) that captures the detailed requirements for the database
system. Next from we derive a corresponding set of well-normalised tables and populate those
tables with data. Finally, showing both the SQL query statement and the query results relevant to
the sales and marketing database which helps to create, retrieve, update and delete (CRUD)
information from the database in a well-organized manner, hence reduce data redundancy and
increase data integrity.
Discussion
Sales and marketing management is all about specifying the placement and size of
goods in stock to meet future demand. Sales and marketing management also concerns
associated with the assessment, product management, market valuation, marketing feasibility,
costs of products, future and sales price forecasting (Balducci & et al., 2015). This system
stores all the information related to the organization such as store details, stock details, details
of every order, details of customers, details of the employees in the organization, generate
sales and marketing report weekly or daily based. The database management system
characterized the individual aspects of marketing and sales for the organization.

3
DATABASES AND BUSINESSES INTELLIGENCE
DBMS Concepts
A database is a system that helps to manage a set of related information. It can be
defined as a collection of coherent and meaningful data. In an organization, multiple users
are included, and they share the same recourses. So most of the data is stored repeatedly.
Storing the same data multiple times is termed as redundancy which gives several problems
such as problem in updating records, wastage of memory that causes data inconsistency
(Kasbe, 2016). To maintain a large amount of data in a well-organized manner by using the
database approach.
In early days, the applications of the database systems were directly built based on the
traditional file systems. All the system records are stored into files. The traditional operating
system supports the file-processing systems (Suseela & Uma, 2017). But the traditional file
processing system has certain disadvantages such as data inconstancy, data redundancy,
accessing data are difficult, data isolation (different files, formats), integrity problems like
insert, modify or delete constraints, atomicity problem, simultaneous accessing of the
database by several users, security problems. The database system provides solutions to
overcome all those problems.

4
DATABASES AND BUSINESSES INTELLIGENCE
ERD
Figure.1: Sales and marketing management ERD
(Image Source: created by author)
Rules for EERD
 An entity set or an attribute is used to represents an object.
 A strong and weak entity set is used to represent strong entities and weak entities.
 Generalization and specialization are used for modularity in the database designing.
 Use of binary relations and ternary relations.
 Use of aggregation-aggregate each entity set into a single unit, without knowing the
details of the internal structure of the database (Al-Masree, 2015).

5
DATABASES AND BUSINESSES INTELLIGENCE
 The best way to express a real-world concept by a relationship set or by a set of
entities.
Reduced Relational Schemas
 Each schema consists of multiple columns generally associated with attributes with
unique names.
 The primary key is used to define an entity set, or relationship set uniformly, and the
content of the database are expressed (Oktafianto & et al, 2018).
 The set of database schemas are used to represent an ER diagram.
 Each entity set has its unique schemas that are associated with a corresponding
relationship set or entity set.
Entity Sets Representing as Schemas
 Strong-entity set is used to reduce the schema associated with the same attributes.
 Weak-entity set forms a table that has a primary key column which identifies the
strong-entity set.
Key attributes
Primary key – uniquely identify each row in the table (Connolly & Begg, 2015). It must be
not null.
Foreign key – used to represent the relationship between two tables.
Super key - set of attributes (one or more), which is used to identify a row in a table
uniquely.
Tables
Customer

6
DATABASES AND BUSINESSES INTELLIGENCE
Manager
Product
Store

7
DATABASES AND BUSINESSES INTELLIGENCE
Order
Queries
Total sales
Shipped confirmed
Market level

8
DATABASES AND BUSINESSES INTELLIGENCE
Dataset Details:
Description of the dataset:
The following excel datasheet represents the amount of employment of the persons in
different cities in UK. It represents the total amount of male employment and female
employment that has been changed over the different months in a year.
a. Screenshot of the Excel Spreadsheet

9
DATABASES AND BUSINESSES INTELLIGENCE
1. Screenshot of Pivot table analysis
Pivot Table 1
Pivot Table 2
3. This is the pivot chart representation of the employment information of various city in
UK. The chart represents how the percentage amount of male and female employment
has changed over the time. This is the column chart presentation of the male and
female employment information. The sum function is used to calculate the total
amount of employment in specific months.
a.

10
DATABASES AND BUSINESSES INTELLIGENCE
Conclusion
In conclusion, the sale and marketing database provides a faster and reliable way to process
various orders. The sales and management database contains a valid authentication checking
process. However, this application contains a unique number for each sales product, customer
to whom the order is to be delivered and the managers to ensure that the integrity of the
transaction. Therefore, it will help to maintain the total sales, order details, order status, and
the marketing level in a single database.

11
DATABASES AND BUSINESSES INTELLIGENCE
References
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3),
15-26.
Balducci, M., Manvydas, T. C., Kohl, N., & Koelfgen, P. (2015). U.S. Patent No. 9,152,727.
Washington, DC: U.S. Patent and Trademark Office.
Connolly, T., & Begg, C. (2015). Database systems. Pearson Education UK.
Kasbe, T. (2016). DBMS Concepts-A Practical Approach. Educreation Publishing.
Laudon, K. C., & Laudon, J. P. (2016). Management information system. Pearson Education
India.
Oktafianto, M. R., Al Akbar, Y. F., Zulkifli, S., & Wulandari, A. M. (2018). Dismissal
working relationship using analytic hierarchy process method. International Journal
of Pure and Applied Mathematics, 118(7), 177-184.
Suseela, V. J., & Uma, V. (2017). Data Management for Libraries: Understanding DBMS,
RDBMS, IR Technologies & Tools. Ess Ess Publications.