Database Design: A&H Boating ERD, Business Rules, and Data Description

This assignment presents a comprehensive Entity-Relationship Diagram (ERD) for A&H Boating, detailing the logical design of the database. It includes multiple figures illustrating the ERD's evolution through different stages, from the initial design to the final model incorporating warehouse details. The solution provides a thorough description of entities and their attributes, such as branch, manufacturer, boat type, model, warehouse, and customer details. Business rules governing the relationships between entities are clearly outlined in a table format, specifying cardinality and constraints. Furthermore, the assignment includes a detailed data description, specifying attribute data types and constraints for each entity. The assignment is supported by a bibliography of relevant sources.

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

3. ERD with Warehouse Details
Figure 3: ERD of A&H Boating after task 3
4. Final ERD Model of A&H Boating
Figure 4: ERD of A&H Boating

Logical Design
Entities Attributes
A&HBoating BranchCode, BranchName, BStreet, BCity, BState,
BPinCode, MCode, ModelName
Foreign key (MCode) references ManufacturerDetails
(MCode)
Foreign key (ModelName) references
ModelDescription (ModelName)
ManufacturerDetails MCode, MName, MStreet, MCity, MState, MPostCode,
MPhoneNumber, MAccountNumber
BoatType ModelCode, MCode, ModelName, BoatPurpose,
Description
Foreign key (ModelCode) references ModelDescription
(ModelNumber)
Foreign key (MCode) references ManufacturerDetails
(MCode)
ModelDescription ModelNumber, ModelName, DateOfManufacturing,
Engine, Length, BeamSize, Weight, Price
WarehouseDetails WarehouseID, ModelNumber, MName, MStreet, MCity,
MState, MPostCode, MPhoneNumber
Foreign key (ModelNumber) references
ModelDescription (ModelNumber)
CustomerDetails CEmailID, WarehouseID, CFName, CLName, DOB,

CStreet, CCity, CState, CPostCode, CPhoneNumber
Foreign key (WarehouseID) references
WarehouseDetails (WarehouseID)
Table 1: Logical Diagram of A&H Boating
Business Rules
Entities Relationships
A&HBoating - ManufacturerDetails One to many relationship
ManufacturerDetails - BoatType One to many relationship
A&HBoating - ModelDescription One to many relationship
BoatType - ModelDescription One to one relationship
BoatType - WarehouseDetails One to one relationship
CustomerDetails - WarehouseDetails One to many relationship
ModelDescription - WarehouseDetails One to one relationship
Table 2: Business Rules taken in ERD of A&H Boating
Data Description
Attribute Data type Type Value
BranchCode int Not Null 10
BranchName varchar Not Null 25
BStreet varchar Not Null 30

BCity varchar Not Null 15
BState varchar Not Null 15
BPinCode int Not Null 6
MCode int Not Null 10
MName varchar Not Null 20
MStreet varchar Not Null 20
MCity varchar Not Null 15
MState varchar Not Null 15
MPostCode int Not Null 6
MPhoneNumber int Not Null 10
MAccountNumber int Not Null 14
MCode int Not Null 10
ModelName varchar Not Null 15
BoatPurpose varchar Not Null 10
DateOfManufacturing date
CostOfManufacturing int 10
Description varchar Not Null 50
ModelNumber int Not Null 10
ModelName varchar Not Null 15
DateOfManufacturing date
Engine varchar 20
Length int 10
BeamSize int 20

Weight int 20
Price int 20
CEmailID varchar Not Null 15
WarehouseID int Not Null 10
CFName varchar Not Null 15
CLName varchar Not Null 15
DOB date
CStreet varchar Not Null 20
CCity varchar Not Null 20
CState varchar Not Null 20
CPhoneNumber int Not Null 10
WarehouseID int Not Null 10
ModelNumber int Not Null 10
MName varchar Not Null 20
MStreet varchar Not Null 20
MCity varchar Not Null 20
MState varchar Not Null 20
MPostCode int Not Null 06
MPhoneNumber int Not Null 10
Table 3: Data Description in ERD of A&H Boating