ER Modelling

This assessment focuses on the analysis of a case study of Merrill Hand Tools Ltd (MHT) and the development of an Entity Relationship (ER) diagram using top-down and bottom-up modelling approaches.

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

0NF 1NF
Candidate key – Line_number
Functional dependency-
 Test_description
 Test_result
 Prototype_number
 Quantity produced
Transitive dependency-
 Line_number -> Manufacturing_date, quantity produced
7

2NF
3NF
8

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, customer Order, product, inventory item, Staff, sales, shipment, RM
requirement, RM inventory, testing and Supplier.
Figure 2 Entity Relationship Diagram
In above diagram, it has been represented the Entity relationship diagram according to the given
case study. In order to identify the different kind of entities and their attributes. These are helping
to create appropriate relationship between one or more database tables.
9

For Example-
Relationship between customer and customer order
Order id is a primary key in which representing as foreign key into customer table.
Therefore, it has been shown as relationship between both customer and order table. By using
foreign key, each and every customer can use as unique order number to access information of
particular products. In this way, it will be creating as one to many relationship. One customer can
identify the information different product information through order number.
Relationship between supplier and customer order
Order_id is stored in supplier table as foreign key in order to establish a relationship
between both CustomerOrder and Supplier table. In this way, supplier can select a particular
product through order Id and transport from another place. Therefore, it will create one to many
relational where one supplier can supply many orders. It can help to maintain or control supply
chain management process within manufacturing areas.
Relationship between testing and Manufacturing Line
In manufacturing line table, Line_number is a primary key in which represent as foreign key
into testing table. In this way, it has been developed the relationship between manufacturing and
testing table. Through this, it has been identified that test can happen on the basis of
manufacturing line of particular product. It should be categorised into line number and become
easier to identify the accurate result or outcome. One to many relationship has been established
between both testing and manufacturing line database tables.
10

TASK B
Database used
Database used for implementation of data model produced for MERRILL HAND
TOOLS LTD. is Microsoft SQL server (Mukherjee, 2019). Microsoft SQL server is a relational
database management server which is developed by Microsoft. It provides an advantage to
database developers to move their data to cloud, export or important data within current or
existing database,
Table creation statement
 CustomerOrder table
 CREATE TABLE [dbo].[CustomerOrder](
 [order_id] [varchar](50) NOT NULL,
 [Model_no] [varchar](50) NULL,
 [Quantity] [int] NULL,
 [product_name] [varchar](50) NULL,
 CONSTRAINT [PK_CustomerOrder] PRIMARY KEY CLUSTERED
 (
 [order_id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
11

Customers table
CREATE TABLE [dbo].[Customers](
[Customer_id] [varchar](50) NOT NULL,
[Sales_id] [varchar](50) NULL,
[Customer_name] [varchar](50) NULL,
[Customer_phone_no] [numeric](18, 0) NULL,
[Customer_email] [varchar](50) NULL,
[order_id] [varchar](50) NULL,
[product_id] [varchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Customer_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
HR/Staff table
 CREATE TABLE [dbo].[HR/Staff](
 [Staff_id] [varchar](50) NOT NULL,
 [Staff_name] [varchar](50) NULL,
 [Department] [varchar](50) NULL,
 [Staff_phoneno] [numeric](18, 0) NULL,
 [Line_number] [varchar](50) NULL,
 CONSTRAINT [PK_HR/Staff] PRIMARY KEY CLUSTERED
 (
 [Staff_id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
Inventory table
CREATE TABLE [dbo].[Inventory](
[Model_no] [varchar](50) NOT NULL,
[Item_description] [varchar](50) NULL,
[item_name] [varchar](50) NULL,
[Quantity] [int] NULL,
[finished_tool] [varchar](50) NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
(
[Model_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
12

ManufacturingLine table
CREATE TABLE [dbo].[ManufacturingLine](
[Line_number] [varchar](50) NOT NULL,
[Model_no] [varchar](50) NULL,
[manufacturing_date] [date] NULL,
[Quantity_produced] [int] NULL,
CONSTRAINT [PK_ManufacturingLine] PRIMARY KEY CLUSTERED
(
[Line_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Product table
CREATE TABLE [dbo].[Product](
[product_id] [varchar](50) NOT NULL,
[product_name] [varchar](50) NULL,
[product_category] [varchar](50) NULL,
[product_size] [int] NULL,
[product_price] [int] NULL,
[order_id] [varchar](50) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[product_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
RM requirement table
CREATE TABLE [dbo].[RM requirement](
[RMrequirement_id] [varchar](50) NOT NULL,
[production_quantity] [varchar](50) NULL,
[Line_number] [varchar](50) NULL,
[Raw_material_id] [varchar](50) NULL,
CONSTRAINT [PK_RM requirement] PRIMARY KEY CLUSTERED
(
[RMrequirement_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
13

RM_inventory table
CREATE TABLE [dbo].[RM_Inventroy](
[Raw_material_id] [varchar](50) NOT NULL,
[RM_name] [varchar](50) NULL,
[RM_quanity] [int] NULL,
CONSTRAINT [PK_RM_Inventroy] PRIMARY KEY CLUSTERED
(
[Raw_material_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Salespersons table
CREATE TABLE [dbo].[SalesPerson](
[Sales_id] [varchar](50) NOT NULL,
[sales_name] [varchar](50) NULL,
[sales_email] [varchar](50) NULL,
[sales_phoneno] [numeric](18, 0) NULL,
CONSTRAINT [PK_SalesPerson] PRIMARY KEY CLUSTERED
(
[Sales_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Shipment table
CREATE TABLE [dbo].[Shipping](
[Shipment_id] [varchar](50) NOT NULL,
[Shipment_date] [date] NULL,
[Shipping_address] [varchar](50) NULL,
[order_id] [varchar](50) NULL,
CONSTRAINT [PK_Shipping] PRIMARY KEY CLUSTERED
(
[Shipment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
14

Fault report
CREATE TABLE [dbo].[Fault_report](
[Fault_id] [varchar](50) NOT NULL,
[Customer_id] [varchar](50) NULL,
[order_id] [varchar](50) NULL,
[Product_id] [varchar](50) NULL,
[Fault] [varchar](50) NULL,
CONSTRAINT [PK_Fault_report] PRIMARY KEY CLUSTERED
(
[Fault_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Raw Material Order table
CREATE TABLE [dbo].[Raw_material_order](
[Rm_order_number] [varchar](50) NOT NULL,
[RMrequirement_id] [varchar](50) NOT NULL,
[Line_number] [varchar](50) NOT NULL,
[date_order] [date] NOT NULL,
[Quantity_order] [varchar](50) NOT NULL,
[Quantity_received] [varchar](50) NOT NULL,
[delivery] [varchar](1) NULL,
CONSTRAINT [PK_Raw_material_order] PRIMARY KEY CLUSTERED
(
[Rm_order_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Production line
CREATE TABLE [dbo].[Production_line](
[Model_no] [varchar](50) NOT NULL,
[line_number] [varchar](50) NOT NULL,
[manufacture_date] [date] NOT NULL,
[quantity produced] [varchar](50) NOT NULL,
[serial number] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
15

Suppliers table
CREATE TABLE [dbo].[Supplier](
[Supplier_id] [varchar](50) NOT NULL,
[Supplier_name] [varchar](50) NULL,
[Supplier_phnoeno] [numeric](18, 0) NULL,
[Supplier_email] [varchar](50) NULL,
[Order_id] [varchar](50) NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED
(
[Supplier_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Testing table
CREATE TABLE [dbo].[Testing](
[test_id] [varchar](50) NOT NULL,
[test_description] [varchar](50) NULL,
[test_results] [varchar](50) NULL,
[line_number] [varchar](50) NULL,
CONSTRAINT [PK_Testing] PRIMARY KEY CLUSTERED
(
[test_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Input / Insertion command for every database tables-
INSERT [dbo].[CustomerOrder] ([order_id], [Model_no], [Quantity], [product_name])
VALUES (N'Co_101', N'Model_102', 20, N'screwdrivers')
GO
INSERT [dbo].[CustomerOrder] ([order_id], [Model_no], [Quantity], [product_name])
VALUES (N'Co_102', N'Model_103', 30, N'edge')
GO
INSERT [dbo].[CustomerOrder] ([order_id], [Model_no], [Quantity], [product_name])
VALUES (N'Co_103', N'Model_101', 40, N'hand tool')
GO
INSERT [dbo].[CustomerOrder] ([order_id], [Model_no], [Quantity], [product_name])
VALUES (N'Co_105', N'Model_104', 10, N'cutting, diving, edgem, hand tool')
GO
INSERT [dbo].[Customers] ([Customer_id], [Sales_id], [Customer_name],
[Customer_phone_no], [Customer_email], [order_id], [product_id]) VALUES
(N'Cust_101', N'Sales_101', N'David', CAST(125544778 AS Numeric(18, 0)),
N'[e-mail]', N'Co_101', N'Product_101')
GO
INSERT [dbo].[Customers] ([Customer_id], [Sales_id], [Customer_name],
[Customer_phone_no], [Customer_email], [order_id], [product_id]) VALUES
16

(N'Cust_102', N'Sales_102', N'Thomas', CAST(125688965 AS Numeric(18, 0)),
N'[e-mail]', N'Co_103', N'Product_103')
GO
INSERT [dbo].[Customers] ([Customer_id], [Sales_id], [Customer_name],
[Customer_phone_no], [Customer_email], [order_id], [product_id]) VALUES
(N'Cust_103', N'Sales_103', N'James', CAST(315447856 AS Numeric(18, 0)),
N'[e-mail]', N'Co_102', N'Product_105')
GO
INSERT [dbo].[Fault_report] ([Fault_id], [Customer_id], [order_id], [Product_id],
[Fault]) VALUES (N'Fault_101', N'Cust_101', N'Co_101', N'Product_101',
N'defective')
GO
INSERT [dbo].[HR/Staff] ([Staff_id], [Staff_name], [Department], [Staff_phoneno],
[Line_number]) VALUES (N'Staff_101', N'Samuel', N'Manufacuting', CAST(213254878
AS Numeric(18, 0)), N'Line_101')
GO
INSERT [dbo].[HR/Staff] ([Staff_id], [Staff_name], [Department], [Staff_phoneno],
[Line_number]) VALUES (N'Staff_102', N'Theo', N'production', CAST(214587452 AS
Numeric(18, 0)), N'Line_102')
GO
INSERT [dbo].[HR/Staff] ([Staff_id], [Staff_name], [Department], [Staff_phoneno],
[Line_number]) VALUES (N'Staff_103', N'Dylan', N'sales', CAST(213266998 AS
Numeric(18, 0)), N'Line_103')
GO
INSERT [dbo].[HR/Staff] ([Staff_id], [Staff_name], [Department], [Staff_phoneno],
[Line_number]) VALUES (N'Staff_104', N'Luca', N'HR', CAST(213244875 AS
Numeric(18, 0)), N'Line_103')
GO
INSERT [dbo].[HR/Staff] ([Staff_id], [Staff_name], [Department], [Staff_phoneno],
[Line_number]) VALUES (N'Staff_105', N'Jaxon', N'customer_service', NULL,
N'Line_102')
GO
INSERT [dbo].[Inventory] ([Model_no], [Item_description], [item_name],
[Quantity], [finished_tool], [Raw_material_id]) VALUES (N'Model_101', N'Edge
cutting tools', N'Chisel', 10, N'cutter', N'Raw_101')
GO
INSERT [dbo].[Inventory] ([Model_no], [Item_description], [item_name],
[Quantity], [finished_tool], [Raw_material_id]) VALUES (N'Model_102', N'Cutting
tools', N'Cutter', 10, N'cutter', N'Raw_102')
GO
INSERT [dbo].[Inventory] ([Model_no], [Item_description], [item_name],
[Quantity], [finished_tool], [Raw_material_id]) VALUES (N'Model_103', N'Driving
tools', N'screwdriver', 20, N'cutter', N'Raw_103')
GO
INSERT [dbo].[Inventory] ([Model_no], [Item_description], [item_name],
[Quantity], [finished_tool], [Raw_material_id]) VALUES (N'Model_104', N'Cutting
tools', N'Scroll saw', 10, N'cutter', N'Raw_104')
GO
INSERT [dbo].[ManufacturingLine] ([Line_number], [Model_no],
[manufacturing_date], [Quantity_produced]) VALUES (N'Line_101', N'Model_101',
CAST(0x1E420B00 AS Date), 30)
GO
INSERT [dbo].[ManufacturingLine] ([Line_number], [Model_no],
[manufacturing_date], [Quantity_produced]) VALUES (N'Line_102', N'Model_102',
CAST(0x50420B00 AS Date), 10)
GO
17

INSERT [dbo].[ManufacturingLine] ([Line_number], [Model_no],
[manufacturing_date], [Quantity_produced]) VALUES (N'Line_103', N'Model_103',
CAST(0x53420B00 AS Date), 20)
GO
INSERT [dbo].[ManufacturingLine] ([Line_number], [Model_no],
[manufacturing_date], [Quantity_produced]) VALUES (N'Line_104', N'Model_104',
CAST(0x1A3B0B00 AS Date), 40)
GO
INSERT [dbo].[Product] ([product_id], [product_name], [product_category],
[product_size], [product_price], [order_id]) VALUES (N'Product_101', N'Cutter',
N'Cutting tool', 10, 3, N'Co_101')
GO
INSERT [dbo].[Product] ([product_id], [product_name], [product_category],
[product_size], [product_price], [order_id]) VALUES (N'Product_102', N'Pliers',
N'Mechanical hand tool', 15, 5, N'Co_102')
GO
INSERT [dbo].[Product] ([product_id], [product_name], [product_category],
[product_size], [product_price], [order_id]) VALUES (N'Product_103', N'Height
Gages', N'Measuring tool', 12, 4, N'Co_103')
GO
INSERT [dbo].[Product] ([product_id], [product_name], [product_category],
[product_size], [product_price], [order_id]) VALUES (N'Product_104',
N'screwdrivers, Pliers', N'Driving Tool', 20, 2, N'Co_106')
GO
INSERT [dbo].[Product] ([product_id], [product_name], [product_category],
[product_size], [product_price], [order_id]) VALUES (N'Product_105', N'Chisel,
Cutter', N'Edge cutting tool', 10, 8, N'Co_105')
GO
INSERT [dbo].[Production_line] ([Model_no], [line_number], [manufacture_date],
[quantity produced], [serial number]) VALUES (N'Model_101', N'Line_101',
CAST(0xCA410B00 AS Date), N'10', N'111')
GO
INSERT [dbo].[Production_line] ([Model_no], [line_number], [manufacture_date],
[quantity produced], [serial number]) VALUES (N'Model_102', N'Line_102',
CAST(0xD73B0B00 AS Date), N'20', N'222')
GO
INSERT [dbo].[Production_line] ([Model_no], [line_number], [manufacture_date],
[quantity produced], [serial number]) VALUES (N'Model_103', N'Line_103',
CAST(0x3D3D0B00 AS Date), N'30', N'333')
GO
INSERT [dbo].[Production_line] ([Model_no], [line_number], [manufacture_date],
[quantity produced], [serial number]) VALUES (N'Model_104', N'Line_104',
CAST(0xE23C0B00 AS Date), N'20', N'444')
GO
INSERT [dbo].[Raw_material_order] ([Rm_order_number], [RMrequirement_id],
[Line_number], [date_order], [Quantity_order], [Quantity_received], [delivery])
VALUES (N'10001', N'RM_101', N'Line_101', CAST(0x91400B00 AS Date), N'1', N'1',
N'2')
GO
INSERT [dbo].[Raw_material_order] ([Rm_order_number], [RMrequirement_id],
[Line_number], [date_order], [Quantity_order], [Quantity_received], [delivery])
VALUES (N'10002', N'RM_102', N'Line_102', CAST(0xA3410B00 AS Date), N'2', N'2',
N'2')
GO
INSERT [dbo].[Raw_material_order] ([Rm_order_number], [RMrequirement_id],
[Line_number], [date_order], [Quantity_order], [Quantity_received], [delivery])
VALUES (N'10003', N'RM_103', N'Line_103', CAST(0x90400B00 AS Date), N'3', N'3',
N'3')
18

GO
INSERT [dbo].[Raw_material_order] ([Rm_order_number], [RMrequirement_id],
[Line_number], [date_order], [Quantity_order], [Quantity_received], [delivery])
VALUES (N'10004', N'RM_104', N'Line_104', CAST(0xEE400B00 AS Date), N'4', N'4',
N'2')
GO
INSERT [dbo].[RM requirement] ([RMrequirement_id], [production_quantity],
[Line_number], [Raw_material_id]) VALUES (N'RM_101', N'10', N'Line_101',
N'Raw_101')
GO
INSERT [dbo].[RM requirement] ([RMrequirement_id], [production_quantity],
[Line_number], [Raw_material_id]) VALUES (N'RM_102', N'5', N'Line_102',
N'Raw_102')
GO
INSERT [dbo].[RM requirement] ([RMrequirement_id], [production_quantity],
[Line_number], [Raw_material_id]) VALUES (N'RM_103', N'10', N'Line_103',
N'Raw_103')
GO
INSERT [dbo].[RM requirement] ([RMrequirement_id], [production_quantity],
[Line_number], [Raw_material_id]) VALUES (N'RM_104', N'15', N'Line_101',
N'Raw_102')
GO
INSERT [dbo].[RM_Inventroy] ([Raw_material_id], [RM_name], [RM_quanity]) VALUES
(N'Raw_101', N'Wood', 20)
GO
INSERT [dbo].[RM_Inventroy] ([Raw_material_id], [RM_name], [RM_quanity]) VALUES
(N'Raw_102', N'Steel', 20)
GO
INSERT [dbo].[RM_Inventroy] ([Raw_material_id], [RM_name], [RM_quanity]) VALUES
(N'Raw_103', N'Stainless_Steel', 10)
GO
INSERT [dbo].[RM_Inventroy] ([Raw_material_id], [RM_name], [RM_quanity]) VALUES
(N'Raw_104', N'Rubber', 30)
GO
INSERT [dbo].[SalesPerson] ([Sales_id], [sales_name], [sales_email],
[sales_phoneno]) VALUES (N'Sales_101', N'Oliver', N'oliver_gmail.com',
CAST(123456789 AS Numeric(18, 0)))
GO
INSERT [dbo].[SalesPerson] ([Sales_id], [sales_name], [sales_email],
[sales_phoneno]) VALUES (N'Sales_102', N'Harry', N'[e-mail]',
CAST(123654789 AS Numeric(18, 0)))
GO
INSERT [dbo].[SalesPerson] ([Sales_id], [sales_name], [sales_email],
[sales_phoneno]) VALUES (N'Sales_103', N'George', N'george_gmail.com',
CAST(652314798 AS Numeric(18, 0)))
GO
INSERT [dbo].[SalesPerson] ([Sales_id], [sales_name], [sales_email],
[sales_phoneno]) VALUES (N'Sales_104', N'Noah', N'[e-mail]', CAST(125489643
AS Numeric(18, 0)))
GO
INSERT [dbo].[Shipping] ([Shipment_id], [Shipment_date], [Shipping_address],
[order_id]) VALUES (N'Ship_101', CAST(0x51420B00 AS Date), N'61 Wellfield Road',
N'Co_103')
GO
INSERT [dbo].[Shipping] ([Shipment_id], [Shipment_date], [Shipping_address],
[order_id]) VALUES (N'Ship_102', CAST(0x54420B00 AS Date), N'14 Tottenham Court
Road', N'Co_101')
GO
19

INSERT [dbo].[Shipping] ([Shipment_id], [Shipment_date], [Shipping_address],
[order_id]) VALUES (N'Ship_103', CAST(0x1F420B00 AS Date), N'Business Centre main
road', N'Co_102')
GO
INSERT [dbo].[Supplier] ([Supplier_id], [Supplier_name], [Supplier_phnoeno],
[Supplier_email], [Order_id]) VALUES (N'Sup_101', N'Leo', CAST(5566887985 AS
Numeric(18, 0)), N'[e-mail]', N'Co_101')
GO
INSERT [dbo].[Supplier] ([Supplier_id], [Supplier_name], [Supplier_phnoeno],
[Supplier_email], [Order_id]) VALUES (N'Sup_102', N'Oscar', CAST(3268554895 AS
Numeric(18, 0)), N'[e-mail]', N'Co_102')
GO
INSERT [dbo].[Supplier] ([Supplier_id], [Supplier_name], [Supplier_phnoeno],
[Supplier_email], [Order_id]) VALUES (N'Sup_103', N'Charlie', CAST(3255478991 AS
Numeric(18, 0)), N'[e-mail]', N'Co_103')
GO
INSERT [dbo].[Testing] ([test_id], [test_description], [test_results],
[line_number]) VALUES (N'Test_101', N'Unit_testing', N'pass', N'Line_101')
GO
INSERT [dbo].[Testing] ([test_id], [test_description], [test_results],
[line_number]) VALUES (N'Test_102', N'component_testing', N'pass', N'Line_102')
GO
INSERT [dbo].[Testing] ([test_id], [test_description], [test_results],
[line_number]) VALUES (N'Test_103', N'safety_testing', N'fail', N'Line_103')
GO
Sample data of tables
CustomerOrder table
Order_id Model_no Quantity
Co_101 Model_102 20
Co_102 Model_103 30
Co_103 Model_101 40
Customers table
Customer_id Sales_id Customer_name Customer_phoneno Customer_email Order_id Product_id
Cust_101 Sales_101 David 125544778 [e-mail] Co_101 Product_101
Cust_102 Sales_102 Thomas 125688965 [e-mail] Co_103 Product_103
Cust_103 Sales_103 James 315447856 [e-mail] Co_102 Product_105
HR/Staff table
Stadd_id Staff_name Department Staff_phoneno Line_number
Staff_101 Samuel Manufacuting 213254878 Line_101
Staff_102 Theo production 214587452 Line_102
20

Staff_103 Dylan sales 213266998 Line_103
Staff_104 Luca HR 213244875 Line_103
Staff_105 Jaxon customer_service NULL Line_102
ManufacturingLine table
Line_number Model_no Manufacturing_dat
e
Quantity produced
Staff_101 Samuel Manufacuting 213254878
Staff_102 Theo production 214587452
Staff_103 Dylan sales 213266998
21

Inventory table
Model_no Item_description Item_name Quantity
Model_101 Edge cutting tools Chisel 10
Model_102 Cutting tools Cutter 10
Model_103 Driving tools screwdriver 20
Product table
Product_id Product_name Product_category Product_size Product_price
Product_101 Cutter Cutting tool 10 3
Product_102 Pliers Mechanical hand
tool 15 5
Product_103 Height Gages Measuring tool 12 4
Product_104 screwdrivers Driving Tool 20 2
Product_105 Chisel Edge cutting tool 10 8
RM requirement table
RMrequirement_id Production_quantity Line_number Raw_material_id
RM_101 10 Line_101 Raw_101
RM_102 5 Line_102 Raw_102
RM_103 10 Line_103 Raw_103
RM_inventory table
RM_material_id RM_name RM_quanity
Raw_101 Wood 20
Raw_102 Steel 5
Raw_103 Stainless_Steel 10
Salespersons table
Sales_id Sales_name Sales_email Sales_phoneno
Sales_101 Oliver oliver_gmail.com 123456789
Sales_102 Harry [e-mail] 123654789
22

Sales_103 George george_gmail.com 652314798
Sales_104 Noah [e-mail] 125489643
Shipment table
Shipment_id Shaipment_date Shipping address Order_id
Ship_101 2021-03-25 61 Wellfield Road Co_103
Ship_102 2021-03-28 14 Tottenham Court
Road Co_101
Ship_103 2021-02-03 Business Centre
main road Co_102
Suppliers table
Supplier_id Supplier_name Supplier_phoneno Supplier_email Order_id
Sup_101 Leo 5566887985 [e-mail] Co_101
Sup_102 Oscar 3268554895 [e-mail] Co_102
Sup_103 Charlie 3255478991 [e-mail] Co_103
Testing table
Test_id Test_description Test_results Line_number
Test_101 Unit_testing pass Line_101
Test_102 component_testing pass Line_102
Test_103 safety_testing fail Line_103
Fault report table
Fault_id Customer_id Order_id Product_id Fault
Fault_101 Cust_101 Co_101 Product_101 defective
23

Raw_Material_order
Test_id Rm_oder_number Date_quantity Line_number
RM_101 10001 2020-01-02 Line_101
RM _102 10002 2020-10-02 Line_102
RM _103 10003 2020-01-01 Line_103
RM _104 10004 2020-04-04 Line_104
Production_line
Manufacture_date Line_number Model_no Quantity
produced
Serial
number
2020-11-10 Line_101 Model_101 10 111
2016-09-09 Line_102 Model_102 20 222
2017-09-02 Line_103 Model_103 30 333
Data Dictionary information (primary keys, foreign keys, constraints)
CustomerOrder table
Column Data type Primary/ Foreign Ky
Constraint
order_id varchar(50) Primary Key
Model_no varchar(50)
Quantity int
Customers table
Column Data type Primary/ Foreign Ky
Constraint
Customer_id varchar(50) Primary Key
Sales_id varchar(50)
Customer_name varchar(50)
Customer_phone_no numeric(18, 0)
24

Customer_email varchar(50)
order_id varchar(50) Foreign Key
product_id varchar(50) Foreign Key
HR/Staff table
Column Data type Primary/ Foreign Ky
Constraint
Staff_id varchar(50) Primary Key
Staff_name varchar(50)
Department varchar(50)
Staff_phoneno numeric(18, 0)
Line_number varchar(50) Foreign Key
Inventory table
Column Data type Primary/ Foreign Ky
Constraint
Model_no varchar(50) Primary Key
Item_description varchar(50)
item_name varchar(50)
Quantity int
ManufacturingLine table
Column Data type Primary/ Foreign Ky
Constraint
Line_number varchar(50) Primary Key
Model_no varchar(50)
manufacturing_date date
Quantity_produced int
Product table
Column Data type Primary/ Foreign Ky
25

Constraint
product_id varchar(50) Primary Key
product_name varchar(50)
product_category varchar(50)
product_size Int
product_price int
RM requirement table
Column Data type Primary/ Foreign Ky
Constraint
RMrequirement_id varchar(50) Primary Key
production_quantity varchar(50)
Line_number varchar(50) Foreign Key
Raw_material_id varchar(50) Foreign Key
RM_inventory table
Column Data type Primary/ Foreign Ky
Constraint
Raw_material_id varchar(50) Primary Key
RM_name varchar(50)
RM_quanity int
Salespersons table
Column Data type Primary/ Foreign Ky
Constraint
Sales_id varchar(50) Primary Key
sales_name varchar(50)
sales_email varchar(50)
sales_phoneno numeric(18, 0)
Shipment table
26

Column Data type Primary/ Foreign Ky
Constraint
Shipment_id varchar(50) Primary Key
Shipment_date date
Shipping_address varchar(50)
order_id varchar(50) Foreign Key
Suppliers table
Column Data type Primary/ Foreign Ky
Constraint
Supplier_id varchar(50) Primary Key
Supplier_name varchar(50)
Supplier_phnoeno numeric(18, 0)
Supplier_email varchar(50)
Order_id varchar(50) Foreign Key
Testing table
Column Data type Primary/ Foreign Ky
Constraint
test_id varchar(50) Primary Key
test_description varchar(50)
test_results varchar(50)
line_number varchar(50) Foreign Key
Fault_report table
Column Data type Primary/ Foreign Ky
Constraint
Fault_id varchar(50) Primary Key
Customer_id varchar(50) Foreign Key
27

order_id varchar(50) Foreign Key
Product_id varchar(50) Foreign Key
Fault varchar(50) Foreign Key
28

SQL code for querying tables
 Question 1: List of tools produced by the company
SQL Code:
Select product_id, product_name
from Product
Result:
 Question 2: Show the test results for one of the tools produced by the company.
SQL Code:
Select Testing.test_id, Testing.test_description, Testing.test_results,
ManufacturingLine.Line_number,
Inventory.Model_no, Inventory.item_name
from Testing
join ManufacturingLine on Testing.line_number = ManufacturingLine.Line_number
join Inventory on Inventory.Model_no = ManufacturingLine.Model_no
where Inventory.item_name = 'screwdriver'
Result:
29

 Question 3: List of suppliers.
SQL Code:
select Supplier_id, Supplier_name
from Supplier
Result:
 Question 4: List of customers
SQL Code:
select Customer_id, Customer_name
from Customers
Result:
 Question 5: Current inventory levels of finished tools.
SQL Code:
select Inventory.Model_no, Inventory.item_name, Inventory.Quantity
from Inventory
join ManufacturingLine on ManufacturingLine.Model_no = Inventory.Model_no
join Testing on Testing.line_number = ManufacturingLine.Line_number
where Testing.test_results = 'pass'
30

Result:
 Question 6: Current inventory levels of raw materials
SQL Code:
select Raw_material_id, RM_name, RM_quanity as 'current inventroy level'
from RM_Inventroy
Result:
 Question 7: List record of test(s) conducted on a given tool (Chisel)
SQL Code:
Select Inventory.Model_no, Inventory.item_name, Testing.test_id,
Testing.test_description,
Testing.test_results, Testing.line_number
from Testing
join ManufacturingLine on Testing.line_number = ManufacturingLine.Line_number
join Inventory on Inventory.Model_no = ManufacturingLine.Model_no
where Inventory.item_name = 'Chisel'
Result:
 Question 8: List of raw materials required to produce a given tool.
SQL Code:
Select [RM requirement].Raw_material_id, [RM requirement].RMrequirement_id,
RM_Inventroy.RM_name,
ManufacturingLine.Model_no, Inventory.item_name
from [RM requirement]
join ManufacturingLine on [RM requirement].Line_number =
ManufacturingLine.Line_number
join Inventory on Inventory.Model_no = ManufacturingLine.Model_no
Join RM_Inventroy on RM_Inventroy.Raw_material_id = [RM
requirement].Raw_material_id
31

where Inventory.item_name = 'Chisel'
Result:
9. Create a new order for an existing Customer.
insert into CustomerOrder (order_id)
values ('Co_105');
10. Add 4 different items to the order created
update CustomerOrder
set product_name = 'cutting, diving, edgem, hand tool'
where order_id = 'Co_105'
32

Result
11. Calculate and show the cost of each line in the order
SELECT CustomerOrder.order_id, Product.product_name,
ManufacturingLine.Line_number, Product.product_price
FROM CustomerOrder INNER JOIN
Product ON CustomerOrder.order_id = Product.order_id
INNER JOIN
ManufacturingLine ON CustomerOrder.Model_no =
ManufacturingLine.Model_no
WHERE (CustomerOrder.order_id = 'Co_105')
Result
33

12. Calculate and show the total cost of the order
SELECT CustomerOrder.order_id, Product.product_price
FROM CustomerOrder INNER JOIN
Product ON CustomerOrder.order_id = Product.order_id
WHERE (CustomerOrder.order_id = 'Co_105')
Result
13. List all the raw materials the company manages
SELECT [RM requirement].Raw_material_id,
ManufacturingLine.Quantity_produced
FROM [RM requirement] INNER JOIN
ManufacturingLine ON [RM requirement].Line_number =
ManufacturingLine.Line_number
Result
14. Schedule the production line for the tools requested in the order
SELECT CustomerOrder.order_id, Production_line.manufacture_date,
Inventory.item_name
FROM Production_line INNER JOIN
Inventory ON Production_line.Model_no =
Inventory.Model_no INNER JOIN
CustomerOrder ON Inventory.Model_no =
CustomerOrder.Model_no
WHERE (CustomerOrder.order_id = 'Co_105')
34

Result
15 show the list of raw materials and quantities required for its manufacturing.
SELECT RM_Inventroy.Raw_material_id, RM_Inventroy.RM_quanity,
Inventory.item_name
FROM RM_Inventroy INNER JOIN
Inventory ON RM_Inventroy.Raw_material_id =
Inventory.Raw_material_id
WHERE (Inventory.item_name = 'Screw saw')
 Question 16: create a new Delivery of raw materials order/note.
SQL Code:
Alter table Raw_material_order
ADD delivery varchar;
Result:
35

 Question 17: Update the inventory of raw materials by adding the raw materials delivered in
question 16.
SQL Code:
Update Raw_material_order
set delivery = 1
Result:
18. Add these tools to the inventory of finished tools.
update Inventory
set finished_tool= 'Screw saw'
Result
36

19 Create a shipment for the order created
SELECT Shipping.Shipment_id, Inventory.Model_no, Inventory.finished_tool,
Shipping.order_id
FROM Shipping INNER JOIN
CustomerOrder ON Shipping.order_id =
CustomerOrder.order_id INNER JOIN
Inventory ON CustomerOrder.Model_no = Inventory.Model_no
WHERE (Shipping.order_id = 'Co_105')
37

20 Create a new Problem Report for it.
insert into Fault_report
values ('Fault_102', 'Cust_103', 'Co_104', 'Product_106', ‘faulty’ )
38

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.
39

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).
Mukherjee, S., 2019. Indexes in Microsoft SQL Server. arXiv preprint arXiv:1903.08334.
Mukherjee, S., 2019. SQL Server Development Best Practices. International Journal of
Innovative Research in Computer and Communication Engineering, 10.
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.
40