CP2404 Assignment 1 Report: Entity-Relationship Diagram for Bikers

This report details the database design for the Mountain Bikers Alliance, a system managing customer information, vehicle registrations, and repair requests. The assignment begins by outlining business rules governing entities such as customers, registrations, vehicles, repairs, services, requirements, and parts. An entity-relationship diagram (ERD) is developed to visually represent the database schema, accurately identifying entities and their relationships. The report explains the conversion of the schema to 3NF and defines primary keys for each table, ensuring data integrity through foreign key constraints. The system stores customer details, vehicle information (including model and serial number), and repair requests. Many-to-many relationships between customers and vehicles are resolved using a registration entity. The report also addresses relationships between repairs and services, services and parts, and incorporates optionality using crow's foot notation. A data dictionary is provided with table and attribute descriptions. The report concludes that the ERD is optimal for data integrity, ensuring accurate data and handling potential errors, and includes an appendix with the ERD and data dictionary.

CP2404 Assignment – Part 1
Assignment 1 Report
Name :
Student Number :
Tutorial Group :
Page 1

CP2404 Assignment – Part 1
Section 1 – Business Rules
1) Entity: Customer
 One customer can have minimum one or many registered vehicles.
2) Entity: Register
 One registration can have one and only one customer registered.
 One registration can have one and only one vehicle registered.
 One registered customer and vehicle can have minimum one or many repair request at
different times.
3) Entity: Vehicle
 One vehicle is associated with minimum one or more than one registered customer.
4) Entity: Repair
 One customer can have multiple repair request. However, each bike and customer will
have individual request.
 Each repair can have one or multiple services to perform.
5) Entity: Perform
 Each perform is associated with one repair request only.
 Each perform is associated with zero or one services.
6) Entity: Service
 Each service is related with one or more than one perform.
 Each service requires one or many requirements.
7) Entity: Requirements
 Each requirement has only one service.
 Each requirement has zero or one parts.
8) Entity: Parts
 Each parts is associated with one or more requirements.
Page 2

CP2404 Assignment – Part 1
Section 2 – Data Dictionary
Page 3
Table
Name Attribute Name Description Type Format Range Req’d PK/
FK
FK
Reference
d Table
Customer
cid Customer’s unique id NUMBER Y PK
cname Customer name TEXT Y
address Customer’s address TEXT Y
contact Customer’s phone
number NUMBER Y
Register
Customer_cid Customer’s id NUMBER Y PK,
FK1 Customer
Vehivle_seria
lnumber
Vehicle’s serial
number NUMBER Y PK,
FK2 Vehicle
Vehicle
Serial_number Vehicle’s serial
number NUMBER Y PK
model Vehicle model TEXT Y
type Type of vehicle TEXT Y
Repair
Ref_num Repair request
reference number NUMBER Y PK
dor Date of request
repair DATE dd-mm-
yyyy Y
Prod_des Problem description TEXT Y
Register_cust
omer_cid Customer’s id NUMBER Y FK1 Register
Register_vehi
cle_serialnum
ber
Vehicle’s serial
number NUMBER Y FK2 Register

CP2404 Assignment – Part 1
Page 4
Table
Name Attribute Name Description Type Format Range Req’
d
PK/
FK
FK
Reference
d Table
Perform
Repair_ref_n
um
Repair request
reference number NUMBER Y PK,
FK1 Repair
Perform_serv
iceid Service id NUMBER PK,
FK2 Services
Services
Serviceid Service id NUMBER Y PK
description Service description TEXT Y
charge Service cost NUMBER Y
Requirem
ents
Parts_pnumbe
r Parts number NUMBER PK,
FK1 Parts
Require_serv
iceid Service id NUMBER Y PK,
FK2 Services
Parts
Pnumber Parts number NUMBER Y PK
Pdescription Part’s description TEXT Y
Quantity Quantity of parts in
stock NUMBER Y
cost Cost of each part NUMBER Y

CP2404 Assignment – Part 1
Section 3 – Report Summary
Entity-Relationship diagram is an essential step for creating a database management system where
the diagram helps in establishing the information requirements and understanding the use of the system.
In this case of Mountain Bikers Alliance, a system of customers, their bike repairs and services associated
with the repairs are modelled into entity and relationship between them. The entities are accurately
identified finally after converting the schema in 3NF. The primary keys are also defined in each table for
referred by the foreign key by contraints.
The system is consist of managing information regarding the customers, their registered vehicle and
repair request for each biking vehicles. The system stores the customers’ name, address and contact where
each customer have a unique identification number. Each customer is registered with one or more than
one vehicle depends on the time of the repair request. The vehicle can also be bought by other customers
as second hand. Hence one vehicle can also have more than one customer at different times. Here, many
to many relationship between customer and vehicle is resolved by introducing another entity called
Registration which holds the data about which customer and vehicle are registered with each other. Each
vehicle’s information is also stored in the system like its model number and vehicle type with a unique
serial number. The customer can make a repair request that records the description of the repair. Each
customer is allowed to make one request for one vehicle at a time. One repair can include multiple
services and one service can be obtained by multiple repairs. This relationship is also resolved by
assigning another entity named perform which stores the repair request is associated with which service.
It is possible that no service is required for any repairs as it can be done by only labor work. This is
represented by the optionality in using crow’s foot notation. The services is associated with the parts
required for the repair. One service can require multiple parts replacement as well as one part can be
related to multiple services. It is also possible that a service may not require any parts to be used which is
represented by the optionality. The parts entity holds the records of parts available in the stock. many to
many relationship between services and parts is deduced by bringing one more entity in the model named
requirements which stores the combination of services and parts required.
The unique constraints are used for primary key indexes that cannot be null also. The foreign key
constraints are required to handle the foreign key application on multiple places. Each foreign key
constraints which are related to same parent table are named differently for use in different places.
However, their data types are kept same. By doing this, the data integrity of the schema is preserved and
can be trustful to receive accurate data and errors on invalid data or data types. Since the data of services
and parts are not mandatory in some places the unique key constraints cannot be used in the performance
and requirements table for service id and parts number respectively. This can include duplicate and null
values in the schema. The duplicate values are not technically duplicate as the values are unique together
with other attribute. For example in performance table service id and repair reference number are
combined as unique for each performance. However, individually they are repeatable. On another hand,
the null values cannot be handled as it provides the valid data about the parts not being used in the service
and the service not being used in the repair. Hence it can be concluded that the Entity-Relationship
diagram developed for the Mountain Biker Association is best in terms of data integrity.
Page 5

CP2404 Assignment – Part 1
APPENDIX
Entity-Relationship Diagram
Page 6