ER Modelling

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)..........................2
Bottom-Up modelling approach to identify Functional dependencies........................................3
Combination of both order and customer table...........................................................................6
ER diagram..................................................................................................................................9
TASK B.........................................................................................................................................11
Database used............................................................................................................................11
Table creation statement............................................................................................................11
Sample data of tables.................................................................................................................16
Data Dictionary information (primary keys, foreign keys, constraints)....................................19
SQL code for querying tables....................................................................................................22
CONCLUSION..............................................................................................................................33
REFERENCES..............................................................................................................................34

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 two main task
will be focused. First task will focus upon analysis of case study has been done, top- down
modelling, bottom- up modelling and ER diagram has been developed. In second task Table
creation statements, Data dictionary information, SQL code for querying tables, and justification
of ER diagram and assumptions made.
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.
1

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
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-
Figure 1 Top down Approach
2

On the basis of case study, it has been produced the tool in which identifying the procedures of
Merill Hand tools manufacturing process.
Top dowm Model relationship-
Customer and customer order
 Each and every consumer may have rights to place the order
 Every order need to be placed by one consumers.
Customer and Sales
 A potential client has been established the connection to one or more marketing or sales
agent.
 Sales agent can help to manage one and many consumers.
Manufacturing line and inventory
 An efficient manufacturing line will be producing one or many finished goods.
 Each finished goods which belongs to one and one manufacturing line per day
Testing and Staff/HR
 Staff member can test one or many products
 Testing the products that can easily done by one or one staff members
Raw material requirements and manufacturing line
 Each and every material requirement produce one to one items
 Manufacturing line requests for one to many in raw materials needs.
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
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.
3

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.
The normalization process from zero normal form to 3rd normal form for the bottom up model
0NF 1NF
4

2NF
3NF Split into smaller tables such as order and customer
5

Functional Dependency-
Customer Customer_id customer_name Customer_PhoneNo customer_email customer address
Order Order_id Quantity
Combination of both order and customer table
 Functional dependency- customer id-> customer name, phone No, email, address
 Transitive dependency- order id-> quantity, customer name
6
Transitive dependency shows in
Customer Table