Database Design and Development 1 Report: Analysis of Mahrud Bees

This report provides a comprehensive analysis of database design and development, focusing on a case study of Mahrud Bees. It begins with an introduction to database design principles, including the creation of an Entity Relationship (ER) diagram to model the database structure. The report then delves into the implementation of the database, covering the design of tables, constraints, indexes, forms, and macros, along with security measures. It emphasizes the importance of the third normal form (3NF) to minimize data redundancy and ensure data integrity. The report includes SQL queries for data retrieval and manipulation, followed by a discussion of data flow diagrams and database testing methods. It also evaluates the design process, identifies areas for improvement, and reviews the database implementation process. The report concludes with recommendations for a suitable database management system (DBMS) for Mahrud Bees, comparing its needs with those of larger organizations, and exploring how the database connects to other applications for business purposes. References to academic sources are also included.

Database Design and Development
1

Contents
INTRODUCTION...........................................................................................................................3
Activity-1.........................................................................................................................................3
Activity-2.........................................................................................................................................8
Activity-3.......................................................................................................................................15
Activity-4.......................................................................................................................................17
CONCLUSION..............................................................................................................................19
REFERENCES..............................................................................................................................20
2

INTRODUCTION
Database design is a type of process which mainly producing detailed information about the
data model. It contain all necessary logical as well as physical design as per choice while
considerations of multiple storage parameter. In order to generate detailed design and understand
the concept which help for establishing a relationship between different entities. The database
design must conform the particular standards which surely handle the large data anomalies. It is
nearly possible to construct a proper design of Mahrud Bees database. Furthermore, it will
discuss about the normal form which frequently accessed data or information. However, it will
develop the database tables, constraints, indexes, forms, Macros and other consideration of
security measurements.
Activity-1
Produce the design for complex relational database based on the response to client. Discuss
about third normal form.
A complex normalization relational database has performed the different functionality. It
needs to understand that how they interact with each other. It is mainly including the different
elements such as entities which established relation with another one (Arabshahi, Gharaei-
Moghaddam and Tavakkolizadeh, 2020). Entity relationship diagram is consists of entities and
their attributes, primary key, foreign key and normalization technique. In this way, it can be
performed different technique that support for handling overall process of Mehrud Bees database
system.
Figure 1 Entity relationship Diagram
3

Entity relationship is based on the model that mainly describe the entire structure of database. It
is mainly used for representing multiple field and their attributes. This type of ER model is
consider as blueprint of database that can be implemented as DB. In another words, Entity
relationship diagram shows the relationship of entity set that stored in database.
 Entity: It is based on the component and object of data which can represent as relational
between one or more database tables.
 Attributes: it is described the property of each entity where shows as significant values
within database.
Data Dictionary
It is a set of files that consists of various metadata which mainly used within database. It
has been recorded the objects and also establish the relationship with other one. Data dictionary
is the most crucial part in term of data collection because they can store information about
multiple entities, primary key and attributes.
Entities Attributes Relationship
Queen Queen_id (PK)
Queen_name
Bee_id (FK)
One to one
Bee Bee_id (PK)
Bee_name
Bee_produced
Supplier_id (FK)
One to many
Payment Payment_id (PK)
Payment_date
Order_id (FK)
One to one
Order Order_id (PK)
Order_date
Product_id (FK)
Customer_id (FK)
Quantity
Price
Many to many
Staff Staff_id (PK) One to many
4

Staff_name
Staff_email
Staff_contactNo
Bee_id (FK)
Order_id (FK)
Customer Customer_id (PK)
Customer_name
Customer_email
Customer_address
Customer_contactNo
Many to many
Table: 1
In above table, it has been described about the various type of entities that mainly used in
database system. Each and every entities are established the relation with another database table
by using foreign key.
Third Normal form:
3NF is based on the database schema design that mainly used within database, normalise
the principle to minimise duplication in database. It also useful for avoiding the data anomalies
which must ensure the referential integrity, simplifying the data management.
For Example-
5

Order and payment are two different table that has established the relationship between them by
using foreign key. Order_id. Afterwards, it is satified the the 2NF.
Furthermore, it can be created into single table which contain both payment_id and
order_id. In this way, they can remove duplication and also satisfying 3NF.
Describe the design documentation in response to client brief ensuring that shows database
tables, constraints, indexes, forms, macors and other security measures.
SQL Queries
SELECT Bee.[Bee_id], Bee.[Bee_name], Bee.[Bee_produced]
FROM Bee;
SELECT customer.[customer_id], customer.[customer_name], customer.[customer_contactNo],
customer.[customer_address], customer.[customer_email]
FROM customer;
SELECT Order.[order_id], Order.[quantity], Order.[Unit_price], Order.[order_date]
FROM [Order];
6

SELECT payment.[payment_id], payment.[payment_date]
FROM payment;
SELECT product.[product_id], product.[product_name], product.[description], product.
[product_type]
FROM product;
SELECT Queen.[Queen_id], Queen.[Queen_name]
FROM Queen;
SELECT Staff.[Staff_id], Staff.[staff_name], Staff.[staff_address], Staff.[staff_email], Staff.
[staff_contactNO]
FROM Staff;
SELECT Supplier.[supplier_id], Supplier.[Supplier_name], Supplier.[supplier_address],
Supplier.[supplier_email], Supplier.[supplier_contactNO]
FROM Supplier;
Data Flow Diagram
It is a type of process that mainly representing flow of information within system. DFD
provide the brief information about input and output of entities and processes. It has no such
control flow for maintaining overall process.
7
Customer
Process
Order
Assemble items
Search item
Payment
Delivered the
product

This diagram is mainly representing the flow of information or data whereas how they can
establish connection between one ends to another one. In Mahrud Bee, database system is
managing and controlling large amount of data in proper manner.
Activity-2
Implement database by using designs which created and include features in the graphical user
interface.
Database tables:
Bee table
8

Customer table
Order table
Payment table
Product table
9

Queen table
Staff table
Supplier table
10

Forms
11

12