Database Design Report: MS Access, MySQL, and Pivot Tables

This report delves into the principles of database design, focusing on relational databases, entity-relationship diagrams, and normalization techniques. It explores the use of MS Access and MySQL for database implementation, including the design of tables, primary and foreign keys, and the formulation of database queries. The report also examines the application of pivot tables and charts within Microsoft Excel for data analysis and visualization, using datasets to represent product information, order details, and demographic data. The analysis includes examples of data sorting, aggregation, and the creation of pivot charts to provide insights into various datasets. The report concludes with a successful design of an efficient database system and an analysis of pivot tables and charts by using Microsoft Excel.

Database design in MS Access and MYSQL
1

TABLE OF CONTENTS
INTRODUCTION…………………………………………………………….………4
PART 1……………………………………………………………………………...…4
PART 2……………………………………………………………………….……….11
CONCLUSION…………………………………………………………………….…15
2

INTRODUCTION
Database is a collection of elements that manage, update, insert, retrieve the information
in form of tables and columns. This report will discuss about relational database and analyses the
information and data. It also designs an effective and efficient database that help for managing
the entire database in a proper manner.
PART 1
1. Entity relationship diagram
Figure: 1
Entity relationship diagram is a graphical representation which help for providing the
information of particular organizations. Entity relationship diagram is creating an
effective relationship between entities and attributes. Fig: 1 show entities and their
specific attributes.
Entities:
Primary key:
Foreign key:
For Example-
Inventory:
Primary key: inventory_id
3

Foreign Key: customer_id
Customer:
Primary key: customer_id
Foreign Key: product_id
Sales order:
Primary key: sale_id
Foreign Key:
Supplier:
Primary key: supplier_id
Foreign key: sales_id, inventory_id
Purchased order:
Primary key: purchased_id
Foreign key: customer_id, supplier_id
4

2. Relational database tables
Customer table
5

Queries
6

Inventory table
Queries:
7

Product table
Queries:
8

Purchase Table
9

Queries:
10

Sale order table
11

Queries:
12