Database System: A&H Boating - ERD Design and Implementation Project

This project presents a case study of a database system designed for A&H Boating. The assignment begins with an overview of the system's requirements, focusing on showcasing boats online and managing related data. The core of the project involves the creation of Entity-Relationship Diagrams (ERDs) to model the database structure. Task 1 details the manufacturer entity, including attributes like manufacturer code, address, and contact information. Task 2 expands on this, introducing boat models and their relationships to boat types. Task 3 incorporates warehouse data, illustrating the storage and management of boats across multiple locations. Finally, Task 4 addresses customer bookings and visits, including the use of a bridge table to handle the many-to-many relationship between customers and warehouses. The project concludes with a bibliography of relevant research papers. The database design includes manufacturers, boat types, models, and warehouses, and how they are related to each other. The ERDs visually represent these relationships and the attributes associated with each entity. The project also considers customer bookings and how to manage these bookings in the database, with the database being implemented using SQL.

Running head: DATASET SYSTEM
Dataset System: Case Study of A&H Boating
Name of the System
Name of the University

1DATASET SYSTEM
Table of Contents
Task 1:.............................................................................................................................................2
Task 2:.............................................................................................................................................3
Task 3:.............................................................................................................................................4
Task 4:.............................................................................................................................................5
Bibliography:...................................................................................................................................6

2DATASET SYSTEM
Task 1:
Figure 1: First ERD of A&H Boating System
(Source: Created by Author)
The organization, A&H Boating, is intending to create a system that will assist the
organization to showcase their boats online. The system will be consisting a database where all
the organizational process related data will be stored. The database will store the details of the
manufacturer such as manufacturer code, street, city, state and postcode. For communication
purpose, the database will store the phone number. The account number will be stored for
account related information purpose. The database will also store the boat type data. The purpose
of the type is also stored. The relation between these two entities are one to many. The relation
states that one manufacture may produce one or many types of boats.

3DATASET SYSTEM
Task 2:
Figure 2: Second ERD of A&H Boating System
(Source: Created by Author)
Each of the model is associated with a single type. The database will hold the model data
such as model_numer, engine, length, beam, suggested retail price, retail price and dry weight.
The type attribute in the model entity is the reference of the boat_type attribute in the boat entity.
The relation suggests that one boat must have at least one model.

4DATASET SYSTEM
Task 3:
Figure 3: Third ERD of A&H Boating System
(Source: Created by Author)
The warehouse of A&H Boating will also be stored in the database. The warehouse entity
will have warehouse_id attribute as the primary key. The address and postcode are for the storing
the address of individual warehouses. The phone_number attribute is for storing the contact
details of different warehouses. The warehouse entity will be associated with the boat entity. The
relation between the boat and warehouse will be one to many. This illustrates that a single
warehouse will store only one type of boat but many boat types will be represented through
many warehouses.

5DATASET SYSTEM
Task 4:
Figure 4: Fourth ERD of A&H Boating System
(Source: Created by Author)
The customer can book a visit to a specific warehouse with a prior booking. Many
customer can visit ay warehouses. This implies many to many relationship between the customer
and warehouse. As real word database implementation does not support many to many relation,
the booking_visit entity has been created as a bridge table. This entity stores the
booking_number attribute as primary key. It has references to both the tables customer and
warehouse respectively.

6DATASET SYSTEM
Bibliography:
Ahadi, A., Prior, J., Behbood, V. and Lister, R., 2015, June. A quantitative study of the relative
difficulty for novices of writing seven different types of SQL queries. In Proceedings of the 2015
ACM Conference on Innovation and Technology in Computer Science Education (pp. 201-206).
ACM.
Gaikwad, A.S., Kadri, F.A., Khandagle, S.S. and Tava, N.I., 2017. Review on Automation Tool
for ERD Normalization. International Research Journal of Engineering and Technology (IRJET)
[Online], 4(2), pp.1323-1325.
Letkowski, J., 2015. Doing database design with MySQL. Journal of Technology Research, 6,
p.1.
Roy, S., Orr, L. and Suciu, D., 2015. Explaining query answers with explanation-ready
databases. Proceedings of the VLDB Endowment, 9(4), pp.348-359.