ITC556 Database Systems: ER Diagram for Boat Manufacturing System

This document provides a complete solution for an ER modeling assignment focused on a boat manufacturing database. The solution details the creation of an Entity-Relationship (ER) diagram incorporating entities such as Manufacturer_table, Manufacturer_type, Boat_type_detail, Boat_model, Warehouse_details, and Customer_booking_details. Each entity's attributes are defined, and the relationships between them are established based on specific business rules. The ER diagram illustrates how manufacturers produce various boat types, how each boat type has multiple models, how boat models are stored in warehouses, and how customers can book viewings at these warehouses. The solution covers the creation of tables, primary keys, and foreign key relationships to satisfy the business rules outlined in the assignment, providing a comprehensive database design for boat manufacturing operations. Desklib offers a wealth of similar solved assignments and past papers to aid students in their studies.

ITC556
Database Systems
Assessment 2
Modeling
Student Name: -Jerome Ankit Dubey
Student ID- 11614386

Contents
Task 1.........................................................................................................................................3
Task 2.........................................................................................................................................5
Task 3.........................................................................................................................................6
Task 4.........................................................................................................................................7
List of Figures:
Figure 1 Business rule 1.............................................................................................................4
Figure 2 Business rule 2.............................................................................................................5
Figure 3 Business rule 3.............................................................................................................6
Figure 4 Business rule 4.............................................................................................................7
1

Task 1
The three entities are required for business rule 1. The entities are Manufacturer_table,
Manufacturer_type, Boat_type_detail
For every entity there are associateattributes:
Manufacturer_table: manufacturer_code(PK), company_name,street, city,state, postcode,
phone_number, account_number.
Manufacturer_type: manufacturer_code (FK), boat_type (FK).
Boat_type_detail:boat_purpose, boat_type(PK).
Business Rule1
Each and every manufacturer can produce one or more than one type of boats and each boat
can be produced by one manufacturer only.
For satisfying this business rule a Manufacturer_table is created with
manufacturer_codeas primary key and act as a foreign key in the Manufacturer_type
table. These two tables have one to many (optional) relationship. Another table is created
Boat_detail having a boat_type as primary key which act as a foreign key in the
Manufacturer_type table. The relationship among these two table is one to the mandatory
relationship(Roe, 2018).
2

Figure 1 Business rule 1
3

Task 2
Now a new entity is created and added to ER diagram. The entity added is Boat_model.
Entity Boat_modelhas following attributes:
Boat_model attributes are: model_number (PK), engine, length,beamsuggested_retail_price,
retail_price,dry_weight, boat_type(FK).
Business Rule2
Every type has one or more than one model. And every model has its type.
For satisfying this business rule, in the Manufacturer_type table, Boat_type_detail attribute
is made as a foreign key in the Boat_model table having one to many relationships among
tables.
Figure 2 Business rule 2
4

Task 3
One more entity is added to ER diagram. The entity is added is Warehouse_details.
For entity Warehouse_details attributes are:
Warehouse_detailsattributsare:warehouse_id (PK), warehouse_address, postcode,
phone_number, model_number(FK).
Business Rule3
Each model of boat is kept in a particular warehouse. And every warehouse can store model
of one type of warehouse.
For satisfying the third business rule, the attribute model_number in Boat_model entity has
been declared as a foreign key in Warehouse_detailsentityhaving one to the mandatory
relationship among tables.
Figure 3 Business rule 3
5

Task 4
A new entity is added to ER diagram. The entity added is Customer_booking_details.
Attributes related withCustomer_booking_details entity are:
Customer_booking_detailsattributesare:booking_id(PK),customer_fullname, customer_dob,
customer_address, customer_email, customer_contactno, warehouse_id(FK).
Business Rule4
Every warehouse can be visited by customers and can-do booking.
For satisfying this rule, the attribute warehouse_id in Warehouse_details table is created as
a foreign key in Customer_booking_detailstable having one to many relationships among
tables(Conceptdraw, 2017).
Figure 4 Business rule 4
6

References
 Conceptdraw, (2017). What is Entity-Relationship Diagram. Retrieved from
http://www.conceptdraw.com/How-To-Guide/erd-what-is-entity-relationship-
diagram.
 Roe, C. (2018). What is Data Modeling?. Retrieved
fromhttp://www.dataversity.net/what-is-data-modeling/.
7