Database Systems Assessment 2: Modeling - ITC556

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