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

13

Reports
14

Activity-3
Discuss about the different types of testing method and how it could be tested the database.
Produce the test plan which identified the test.
Testing is based on the process that mainly used with database system, eliminating any
errors, bugs in codes. It can be divided into different types.
 Structural database testing: in this phase. Database can be tested the related elements such
as stored procedures, view tests, column, table etc. in this way, it can easily verifying and
validating the correct data as per rules (Beasley, 2020).
 Functional testing: this type of testing is related to functionality of database, identify that
how overall system works in properly. The most common test approach used such as
black box and white box testing.
 Non-functional testing: It is all about concern the data risk, load and stress which are
based on the concept of entire database performance.
However, a relational database is mainly implementing the test procedure to identify any data
errors and handling through different technique such as queries, tables, forms. These are applied
for adding, modifying and viewing the data. On the other hand, it will be analysed performance
in order to determine flow of data and automatically optimised in proper manner.
15

Test data type
In case if it can add other data type so that it will generate errors message.
16

Null value check
The primary key cannot contain null value if it has found so that it automatically check by using
testing.
Activity-4
Evaluate the design process, documentation that produced for Mahrud Bees database identifying
improvement.
As per design documents, it has been attention to detail about the system which mainly
produced the satisfactory after revision of ERD diagram. The normalization is part of system that
can reduce the duplication (Nie, Wen and Yan, 2020). In this way, it also considered the data
flow diagram which useful for representing overall structure and layout.
To documentation that has been balanced the between excessive and information in which
creating the confusion and also reduce efficiency. During implementation stage, it is clearly
define the following stages which provide clear picture and easy to manage. Furthermore,
improvement has been undertaken in future development. It could be require to establish
collaborative approach when it produce documentation. The important facts that developing a
well-documentation which help for processing the database.
17

Review the database implementation process.
The software development and implementation is an important step that will help for
producing the designed documentation. It must be considered an accurate data dictionary which
useful for understanding the different parameters.
Mahrud bee agree with the database implement process that will be delivered an efficient
environment which easily understand need and requirement of business. MySQL is an option
that will develop database but it will increase complexity (Nikaido, Hays and Reddish, 2020). In
this way, it has been decided to use MS Access in order to transform the proper documentation
into relational DBMS with good quality of services. Access program will help for testing the
efficiency of database by using tables, forms and queries.
Evaluate the role of database within Mahrud Bees, compare with large organizations.
Mahrud Bee is define the knowledge about the overall database system and perform
significant step to track the sales of different items such as sales, order, product and other
recorded. In the Mahrud Bee, it is also consider as imperative to understand about the direction
of enterprise which improvement can be done in company (Özsu and Valduriez, 2020). In order
to synchronise the overall structure of business operations.
Afterwards, it also compare with large organization which is important to design or
develop data set. It is mainly including the information about product, order, staff, bees, queen
and so on. The reason to use database such as:
 Identifying enterprise channels
 Certainty in decision-making
 Reduce cost
Understand that how Mahrud Bees connect with database to other applications, use for business
purpose.
Mahrud bee is based on the relational database system which has developed the connection
with other one by using Application programming interface (Nikaido, Hays and Reddish, 2020).
It is the most common approach that allows the application from access client side into database
from other server-side.
OBDC and JDBC are important model that has develop connectivity among database and
created by SQL language. The primary purpose is to access the date from different application.
18

In order to establish connection between application interface and database whereas they can
easily transmitting signals in proper manner.
Recommends to Mahrud Bees a database management system that can use to maintain database.
It has recommended that use Microsoft Access for developing database system, it will help
for understanding overall functionality. By using Access, Mahrud Bee data will be integrated or
migrated into SQL Server. In order to recommend the Mahrud Bees in short term will use
MySQL but it require to enter into professional solution (Nikaido, Hays and Reddish, 2020).
MySQL is the most popular in large website management because it directly establish
connection with web server. Sometimes, it also relates to the CMS and also linked with PHP.
There are some common feature contain the MySQL.
 It help for selecting various storage engine of every table.
 It also executing the transaction by using foreign key.
 It offer the system password and also used secure privilege.
CONCLUSION
From above discussion, it concluded that Database design is a type of process which mainly
producing detailed information about the data model. The database design must conform the
particular standards which surely handle the large data anomalies. The documentation is based
on the construct a proper design of Mahrud Bees database. It has summarised about the normal
form which frequently accessed data or information. However, it can be developed the database
tables, constraints, indexes, forms, Macros and other consideration of security measurements.
19

REFERENCES
Book and Journals
Arabshahi, A., Gharaei-Moghaddam, N. and Tavakkolizadeh, M., 2020, February. Development
of applicable design models for concrete columns confined with aramid fiber reinforced
polymer using Multi-Expression Programming. In Structures (Vol. 23, pp. 225-244).
Elsevier.
Beasley, R.E., 2020. Database Design, SQL, and Data Binding. In Essential ASP. NET Web
Forms Development (pp. 359-394). Apress, Berkeley, CA.
Nie, P., Wen, Y. and Yan, M., 2020. Design and Implementation of Real-Time Inquiry System
of the Stock Market. In Recent Trends in Intelligent Computing, Communication and
Devices (pp. 79-85). Springer, Singapore.
Nikaido, B., Hays, Z.B. and Reddish, B.J., 2020. Pterodactyl: Aerodynamic and Aeroheating
Database Development for Integrated Control Design of a Mechanically Deployed Entry
Vehicle. In AIAA SciTech 2020 Forum (p. 1010).
Özsu, M.T. and Valduriez, P., 2020. Distributed and Parallel Database Design. In Principles of
Distributed Database Systems (pp. 33-89). Springer, Cham.
20