Comprehensive Database Design Project: A&H Boating - Semester 2

This document presents a comprehensive database design for A&H Boating, starting with an Entity-Relationship Diagram (ERD) and progressing through logical design, including entities, attributes, and data types. The project details the entities such as A&HBoating, Manufacturer_Details, BoatDetails, ModelDetails, Warehouse_Details, and Customer_Details, along with their respective attributes and data types. Business rules governing the relationships between entities are also outlined, clarifying the one-to-many and one-to-one relationships within the database. The document provides a detailed breakdown of each entity's attributes, data types, and constraints, ensuring data integrity and efficient database management. Finally, a bibliography of relevant resources is included, offering further context and support for the database design principles applied. This project is designed to provide a robust and scalable database solution for A&H Boating's operational needs.

Running head: DATABASE DESIGN OF A&H BOATING
Database Design of A&H Boating
Name of Student-
Name of University-
Author’s Note-

1DATABASE DESIGN OF A&H BOATING
1. The ERD of A&H Boating
Figure 1: ERD of A&H Boating
2. ERD with Model Description
Figure 2: ERD after task 2 Completion

2DATABASE DESIGN OF A&H BOATING
3. ERD with Warehouse Details
Figure 3: ERD of A&H Boating after task 3

3DATABASE DESIGN OF A&H BOATING
4. Final ERD Model of A&H Boating
Figure 4: ERD of A&H Boating
Logical Design
Entities Attributes
A&HBoating Branch_Code, Branch_Name, B_Street, B_City,
B_State, B_PinCode, MftCode, Model_Name
Foreign key (MftCode) references
Manufacturer_Details (MftCode)
Foreign key (Model_Name) references ModelDetails
(Model_Name)

4DATABASE DESIGN OF A&H BOATING
Manufacturer_Details MftCode, MftName, MftStreet, MftCity, MftState,
MftPostCode, MftPhoneNumber, MftAccountNumber
BoatDetails Model_Code, MftCode, Model_Name, Boat_Purpose,
Description
Foreign key (Model_Code) references ModelDetails
(Model_Number)
Foreign key (MftCode) references
Manufacturer_Details (MftCode)
ModelDetails Model_Number, Model_Name, Manufacturing_Date,
BEngine, BLength, BBeamSize, BWeight, BPrice
Warehouse_Details Warehouse_ID, Model_Number, WName, WStreet,
WCity, WState, WPostCode, WPhoneNumber
Foreign key (Model_Number) references ModelDetails
(Model_Number)
Customer_Details C_Email_ID, Warehouse_ID, C_FName, C_LName,
D_O_B, C_Street, C_City, C_State, C_PostCode,
C_PhoneNumber
Foreign key (Warehouse_ID) references
Warehouse_Details (Warehouse_ID)
Table 1: Logical Diagram of A&H Boating
Business Rules
Entities Relationships

5DATABASE DESIGN OF A&H BOATING
A&HBoating - Manufacturer_Details One to many relationship
Manufacturer_Details - BoatDetails One to many relationship
A&HBoating - ModelDetails One to many relationship
BoatDetails - ModelDetails One to one relationship
BoatDetails - Warehouse_Details One to one relationship
Customer_Details - Warehouse_Details One to many relationship
ModelDetails - Warehouse_Details One to one relationship
Table 2: Business Rules taken in ERD of A&H Boating
Data Description
Attribute Data type Type Value
Branch_Code int Not Null 10
Branch_Name varchar Not Null 25
B_Street varchar Not Null 30
B_City varchar Not Null 15
B_State varchar Not Null 15
B_PinCode int Not Null 6
MftCode int Not Null 10
MftName varchar Not Null 20

6DATABASE DESIGN OF A&H BOATING
MftStreet varchar Not Null 20
MftCity varchar Not Null 15
MftState varchar Not Null 15
MftPostCode int Not Null 6
MftPhoneNumber int Not Null 10
MftAccountNumber int Not Null 14
MftCode int Not Null 10
Model_Name varchar Not Null 15
Boat_Purpose varchar Not Null 10
Manufacturing_Date date
CostOfManufacturing int 10
Description varchar Not Null 50
Model_Number int Not Null 10
Model_Name varchar Not Null 15
Manufacturing_Date date
BEngine varchar 20
BLength int 10
BBeamSize int 20
BWeight int 20
BPrice int 20
C_Email_ID varchar Not Null 15
Warehouse_ID int Not Null 10
C_FName varchar Not Null 15

7DATABASE DESIGN OF A&H BOATING
C_LName varchar Not Null 15
D_O_B date
C_Street varchar Not Null 20
C_City varchar Not Null 20
C_State varchar Not Null 20
C_PhoneNumber int Not Null 10
Warehouse_ID int Not Null 10
Model_Number int Not Null 10
WName varchar Not Null 20
WStreet varchar Not Null 20
WCity varchar Not Null 20
WState varchar Not Null 20
WPostCode int Not Null 06
WPhoneNumber int Not Null 10
Table 3: Data Description in ERD of A&H Boating

8DATABASE DESIGN OF A&H BOATING
Bibliography
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Barokati, N., Wajdi, N., & Barid, M. (2017). Application Design Library With gamification
concept. Jurnal Lentera: Kajian Keagamaan, Keilmuan dan Teknologi, 3(1), 93-102.
Btoush, E. S., & Hammad, M. M. (2015). Generating ER diagrams from requirement
specifications based on natural language processing. International Journal of Database
Theory and Application, 8(2), 61-70.
El‐Assady, M., Sevastjanova, R., Gipp, B., Keim, D., & Collins, C. (2017, June). NEREx:
Named‐Entity Relationship Exploration in Multi‐Party Conversations. In Computer
Graphics Forum (Vol. 36, No. 3, pp. 213-225).
Gupta, S. K., Atkinson, S., O’Boyle, I., Drogo, J., Kar, K., Mishra, S., & Wen, J. T. (2016).
BEES: Real-time occupant feedback and environmental learning framework for
collaborative thermal management in multi-zone, multi-occupant buildings. Energy and
Buildings, 125, 142-152.
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and
data warehouses. Prospect Press.
Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology Research, 6,
1.