ITC556 Database Systems: ER Diagram Database Modeling Report

This assignment solution for ITC556 Database Systems focuses on entity-relationship (ER) modeling. It details the creation of several entities including Manufacturer, Types, Manufacturer_type, Model, Warehouse, and Customer_booking, outlining the attributes associated with each. The solution also explains the business rules governing the relationships between these entities, such as a manufacturer producing multiple boat types, each model belonging to a specific type, warehouses storing single model types, and customers making bookings at warehouses. ER diagrams are used to visually represent these relationships, demonstrating how foreign keys are implemented to maintain data integrity and enforce business rules. Desklib provides this document and other resources for students.

ITC556
Database Systems
Assessment 2
Modeling
Student Name: Saif Ali Muhammed
Student ID- 11594027

Contents
TASK 1......................................................................................................................................3
Task 2.........................................................................................................................................4
Task 3.........................................................................................................................................5
Task 4.........................................................................................................................................6
References:.................................................................................................................................7
List of figures:
Figure 1: business rule 1............................................................................................................3
Figure 2: Business rule 2............................................................................................................4
Figure 3: Business rule3.............................................................................................................5
Figure 4: Business rule4.............................................................................................................6
1

TASK 1
There is a need for creation of three entities namely Manufacturer, Types and
Manufacturer_type.
Attributes related with entities are:
Manufacturer: code(PK), company_city, company_street, contact_number, company_state,
company_postcode, name_company, number_account.
type: type_boat (PK), boat_purpose.
manufacture_type: type_boat (FK), code(FK).
Business Rule1
Each manufacturer can produce many types of boats and each type is produced by only one
manufacturer
To satisfy this rule, manufacture table is created and code from manufacturer table is created
as a foreign key in manufacture_type having one to optional many relationships and
type_boat from types table is created as a foreign key in manufacture_type having one to the
mandatory relationship.
Figure 1: business rule 1
2

Task 2
There is a need for creation of one entity to previous structure namely Model.
Attributes related with entity are:
Model entity are: model_number (PK), model_engine, model_length, model_beam
model_suggested_retail_price, model_retail_price, model_dry_weight.
Business Rule2
Each model is created as a part of the type.
To satisfy this rule, Type_boat from Types table is created as a foreign key in Model table
one to many relationships exist between entities (1: M).
Figure 2: Business rule 2
3

Task 3
There is a need for creation of one more entity to the structure in Business rule2 namely
Warehouse.
Attributes related to the entity are:
Warehouse entity are: id_warehouse (PK), address, warehouse_postcode, contact_number,
model_number(FK).
Business Rule3
Each warehouse has a capacity for storing single type model.
To satisfy this rule, model_number from the Model table is created as a foreign key for
Warehouse table 1 to mandatory relationship exist between entities. Type_boat(FK) from
Types table is created as a foreign key in Warehouse table 1 to mandatory relationship exist
between entities.
4

Figure 3: Business rule3
Task 4
There is a need for creation of one more entity to the structure in Business rule3 namely
Customer_booking.
Attributes related with entity are:
Customer_booking entity are: D.O.B, email, phone_number, booking_id(PK),
id_warehouse(FK), full_name.
Business Rule4
Each warehouse is visited by the prospective customers with prior booking.
To satisfy this rule, id_warehouse from Warehouse table is created as a foreign key in table
Customer_booking table. There is one to many (1: M) relationships between warehouse and
customer_booking.
5

Figure 4: Business rule4
6

References:
 ER Diagrams Tutorial
, (2017).
Creately Blog. Retrieved from
https://creately.com/blog/diagrams/er-diagrams-tutorial/
 Gabry, O. (2016).
Database — Modeling: Entity Relationship Diagram. Retrieved
from https://medium.com/omarelgabrys-blog/database-modeling-entity-relationship-
diagram-part-5-352c5a8859e5
7