Merrill Hand Tools Ltd: ER Modeling Report and Database Design

This report presents an analysis of a case study involving Merrill Hand Tools Ltd (MHT), focusing on the application of Entity Relationship (ER) modeling for database design. The introduction defines ER modeling as a graphical approach for designing databases, emphasizing its role in defining data elements and their relationships. The report analyzes the MHT case study, a family-run business facing database and system issues, and proposes a major systems reengineering project. Task A includes a case study analysis, followed by discussions of top-down and bottom-up modeling approaches. The top-down approach is used to create an ER diagram, while the bottom-up approach identifies functional dependencies within the system. The report then presents an ER diagram illustrating the entities and their relationships within the MHT database. The conclusion summarizes the different modeling approaches and the importance of ER diagrams in database development. References to relevant books and journals are also included.

ER MODELLING

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
TASK A...........................................................................................................................................1
Analysis of Case study.................................................................................................................1
Top- down Modelling approach to produce Entity relationship Diagram (ER)..........................1
Bottom-Up modelling approach to identify Functional dependencies........................................2
ER diagram..................................................................................................................................3
CONCLUSION................................................................................................................................4
REFERENCES................................................................................................................................5

INTRODUCTION
Entity relationship modelling also known as ER modelling is a kind of graphical
approach which is used for designing a database (Hossain, Rajan and Schwitter, 2019). It is a
high- level data model which is used for defining data elements and their relationship for a
specified software system. It helps in explaining entity types and relationship between them. This
assessment will lay emphasis upon a case study of Merrill Hand Tools Ltd (MHT) who have
decided of undertaking major systems reengineering project. In this assessment analysis of case
study has been done, top- down modelling, bottom- up modelling and ER diagram has been
developed.
TASK A
Analysis of Case study
This case study is about Merrill Hand Tools Ltd (MHT) which is a family run business in
Sheffield that manufacturers different range of hand tools from general tooling to specialist
woodworking tools and agricultural implements. Currently they have three divisions: Product
Development, Manufacturing, and Marketing and Sales and each of these divisions has a vice
precedent who report to CEO. All the three divisions are facing some kinds of issues such as:
programmer in manufacturing department can only fix superficial user interface issues and repair
corrupted data files but he cannot be a part of original program development. Their current
database has several problems such like limit to number of simultaneous users, data
inconsistencies etc. Due to this, they have decided to undergo major systems reengineering
project in which new information system division will be developed along with three existing
systems. This new department will be led by Chief Information Officer who will hire Lead
Programmer, a Database Administrator, and a Lead Analyst. CIO will be developing a data
administration plan, roadmap that documents all application programs required for supporting
corporate operations, developing and hosting a Web app, ER diagrams and data dictionaries, and
will implement planned system.
Top- down Modelling approach to produce Entity relationship Diagram (ER)
A top down approach is based on the process that can essentially breaking down of system
to gain insight into its compositional sub-system in a reverse engineering fashion (Suraprajit,
2019). By using this model, it is basically describing the overview of system which are
1

formulated without going into detail for any part. Each and every node that can refined into
specified manner until the overall specification is detailed enough to validate model.
Sometimes, it is breaking down of system in order to gain more insight into compositional
sub system within reverse manner. Sometimes, it is completely formulated the entire system
while specifying but not detail. As per considered the top down approach which become easier to
manipulate the data. However, black box may fail due to clarifying the elementary mechanism or
detailed enough in the realistic validate the model. This kind of top down approach is starts with
big picture which breaks down from there into smaller segments.
As per given scenario, top-down approach is consider as investment strategy that can select
various industries and tries to achieve a balance in the investment portfolio. Through this
approach, it is primarily analysing the risk by aggregating the certain impact of internal
operational failure. it means that measure the variances in the economic variables that are
explained by its current external macro-economic factors.
Top down ER model-
Bottom-Up modelling approach to identify Functional dependencies
Bottom- Up modelling is a kind of an Approach that helps in piecing a system together in
order to give rise to more complex systems (Streicher and et.al., 2019). It helps in making sub
system of existing system. It helps in linking large components that are linked together until
complete system is formed. It can also be said that it helps in developing small seeds of the
2

system that together helps in growing complete and complex projects together. Some of the main
advantages of this modelling approach are: it helps in taking decision related to reusable low-
level utilities for identifying ways in which these reusable low- level utilities can be put together
for creation of high- level construct. This approach completely contrasts with top- down
modelling.
Bottom Up modelling is used to resolve smaller problems so that it can be integrated as
whole and complete solution. This modelling is also used to reduce redundancy using data
encapsulation and data hiding. It helps in identifying functional dependencies within a project so
that it can be completed without any issues in a project and appropriate manner.
3

ER diagram
Entity relationship diagram can be defied as a structure of database that helps in
development of database in a proper and accurate manner (Ghosh and et. al., 2018). It can also
be said that ER diagram is a blueprint of database using which database is developed in a proper
and accurate manner. this diagram helps in identifying main entities and their attributes in a
proper and effective manner. it further helps in explaining relationship between all the entity sets
in a proper manner. it is used while designing and developing a database for identifying main
tables of database and type of data those tables will be storing in a proper and appropriate
manner. Below ER diagram clearly explains that there are seven main entities in this database,
that are: Customer, SalesOrder, product, inventory_item, Staff, PurchaseOrder, and Supplier.
4

CONCLUSION
From the above assignment it has been summarized that there are various kinds of
approaches or models that can be used for developing a database. One of the approaches is top-
down approach which is used for breaking down a system or a project into its compositional sub-
system in order to understand a system in more appropriate manner. Whereas Bottom- Up
approach is used for identifying ways in which sub components of a system can be put together
for development of a new complex system. Lastly it has also been analysed that ER diagram is
an important model which is used for identification of main entities of the database for
development of database in a proper and accurate manner.
5

6

REFERENCES
Books and Journals
Ghosh, S., and et. al., 2018, December. Automated Generation of ER Diagram from a Given
Text in Natural Language. In 2018 International Conference on Machine Learning and
Data Engineering (iCMLDE) (pp. 91-96). IEEE.
Hossain, B.A., Rajan, G. and Schwitter, R., 2019. CNL-ER: A Controlled Natural Language for
Specifying and Verbalising Entity Relationship Models. In Proceedings of the The 17th
Annual Workshop of the Australasian Language Technology Association (pp. 126-135).
Streicher, K.N., and et.al., 2019. Analysis of space heating demand in the Swiss residential
building stock: Element-based bottom-up model of archetype buildings. Energy and
Buildings. 184. pp.300-322.
Suraprajit, P., 2019. Bottom-up vs top-down model: The perception of reading strategies among
Thai university students. Journal of Language Teaching and Research. 10(3). pp.454-
460.
7