Database Systems Assignment: ERD Design for Business Rules and Data

This assignment solution focuses on designing an Entity-Relationship Diagram (ERD) for a database system. The solution begins by defining business rules related to manufacturers, types, and models, and then constructs ERDs to represent these rules. The assignment progresses through several iterations, adding complexity with each step. Initially, the ERD covers manufacturers and types. Subsequently, the ERD is expanded to include models and their relationship to types. Further development incorporates warehouses and their association with models. Finally, the solution incorporates customer booking details, resulting in a comprehensive ERD that includes manufacturers, types, models, warehouses, and customers. The solution includes detailed attributes for each entity, such as manufacturer details, model specifications, warehouse information, and customer data. The document concludes with a bibliography of relevant resources.

Running head: DATABASE SYSTEM
Database Systems
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM
Task 1
For the first task the business rules that are to be considered are:
 Manufacture Code is used for the identification of the manufacturer
 Each and every type is produced by only a single manufacturer
The following ER Diagram has been constructed using the provided business rules.
The main entity is the Manufacturer, Type
The attributes within the entity are:
Manufacturer (Manufacturer_No (primary key), Company_Name, City, Street, PostCode,
Phone_number, Account_number)
Type (TypeNo (primary key), Type, Purpose, manufacturerNo (foreign key))
Task 2
For Task 2 an additional business rule provided by the company is:

2
DATABASE SYSTEM
Each every model that is produced should be a part of the Type.
From the above consideration the following ER diagram has been developed.
The main entities of this ERD are Model, Type and manufacturer
The main attributes of the entity are:
Manufacturer (Manufacturer_No (primary key), Company_Name, City, Street, PostCode,
Phone_number, Account_number)
Type (TypeNo (primary key), Type, Purpose, manufacturerNo (foreign key))
Model (ModelNo (primary key), length, engine, Beam, Recommended_Retail_Price,
Retail_Price, Dry_Weight, TypeNo (forgien key))
Task 3
The business rule described for the next composition of diagram are:
 Only a single type of model should be produced by a single warehouse.

3
DATABASE SYSTEM
 The warehouse should be identified with a unique ID
 The database should be storing each and every information about the warehouse
The ERD composed with these business rules would look like the following ER Diagram
The main entities of the system are Manufacturer, Type Warehouse and Model.
The main entities of the system are:
Manufacturer (Manufacturer_No (primary key), Company_Name, City, Street, PostCode,
Phone_number, Account_number)

4
DATABASE SYSTEM
Type (TypeNo (primary key), Type, Purpose, manufacturerNo (foreign key))
Model (ModelNo (primary key), length, engine, Beam, Recommended_Retail_Price,
Retail_Price, Dry_Weight, TypeNo (forgien key))
Warehouse (WarehouseNo (primary key), Address, Postcode, Contact, ModelNo (forgien key))
Task 4
The new business rules described for the fourth task are:
 The details of the customer booking should be stored in the database of the system
The composition of the ERD after

5
DATABASE SYSTEM
The main entities of the system are Manufacturer, Type, Warehouse, Customer and Model
The main attributes present in this entities are:
Manufacturer (Manufacturer_No (primary key), Company_Name, City, Street, PostCode,
Phone_number, Account_number)
Type (TypeNo (primary key), Type, Purpose, manufacturerNo (foreign key))
Model (ModelNo (primary key), length, engine, Beam, Recommended_Retail_Price,
Retail_Price, Dry_Weight, TypeNo (forgien key))

6
DATABASE SYSTEM
Warehouse (WarehouseNo (primary key), Address, Postcode, Contact, ModelNo (forgien key))
Customer (CustomerNo (primary key), Name, DateofBirth, CustomerAddress, Email,
ContactNumber, WarehouseNo (forgien key))
Final ERD design
All the business rules of the company has been summarized below:
 Manufacture Code is used for the identification of the manufacturer
 Each and every type is produced by only a single manufacturer
 Each every model that is produced should be a part of the Type.
 Only a single type of model should be produced by a single warehouse.
 The warehouse should be identified with a unique ID
 The database should be storing each and every information about the warehouse
 The details of the customer booking should be stored in the database of the system

7
DATABASE SYSTEM
From the ERD the derived entities are Manufacturer, Warehouse, Customer, Type and
Model
The attributes of the system are in the entities are:
Manufacturer (Manufacturer_No (primary key), Company_Name, City, Street, PostCode,
Phone_number, Account_number)
Type (TypeNo (primary key), Type, Purpose, manufacturerNo (foreign key))

8
DATABASE SYSTEM
Model (ModelNo (primary key), length, engine, Beam, Recommended_Retail_Price,
Retail_Price, Dry_Weight, TypeNo (forgien key))
Warehouse (WarehouseNo (primary key), Address, Postcode, Contact, ModelNo (forgien key))
Customer (CustomerNo (primary key), Name, DateofBirth, CustomerAddress, Email,
ContactNumber, WarehouseNo (forgien key))

9
DATABASE SYSTEM
Bibliography
Ford, A. R. (2015). JMU Dining to Go.
Hingorani, K., Gittens, D., & Edwards, N. (2017). REINFORCING DATABASE CONCEPTS
BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND NORMALIZATION
TOGETHER FOR DESIGNING ROBUST DATABASES. Issues in Information Systems, 18(1).