Question
Database Schema: (10 Marks)
Create the necessary tables to store the following information:
A unique Contact_ID for each contact.
Customer Name, Address, Mobile Number, and Email Address.
A unique Vehicle_ID for each vehicle.
Vehicle Registration Number, Model, Manufacturer, Year of Manufacture, and Fuel Type.
Each customer may own multiple vehicles.
A unique Mechanic_ID for each mechanic.
Mechanic Name, Specialization, Experience, and Contact Number.
A unique Service_ID for each service transaction.
Service Date, Service Type (Periodic Service, General Repair, Engine Repair, Electrical Repair, Washing, etc.), Labour Charges, Spare Parts Cost, and Total Bill Amount.
Each service transaction must be assigned to one mechanic.
Multiple spare parts may be used during a service. Store the Part_ID, Part Name, Quantity, and Unit Price for each spare part. Your design should include:
An ER Diagram for the Vehicle Service Management System.
The final set of tables with all columns clearly defined.
Underline the Primary Key and specify all Foreign Keys.
Ensure that the database schema satisfies Third Normal Form (3NF).
Answer :
Word Count : 1240
The Vehicle Service Management System can be designed as a relational database in which customers, vehicles, mechanics, services, and spare parts are stored in separate but related tables. The main objective of the design is to avoid data redundancy, maintain data integrity, and represent the relationships between customers, their vehicles, mechanics, service transactions, and spare parts. The database can be implemented using SQL and can later be connected with a C++ program in the DBMS and C++ Lab for inserting, updating, searching, and displaying records. The main entities in the system are Customer, Vehicle, Mechanic, Service, Spare Part, and Service Part. A Customer can own many Vehicles, but every Vehicle belongs to one Customer. A Vehicle can have many Service transactions, while each Service transaction is related to exactly one Vehicle. A Mechanic can handle many Service transactions, but each Service transaction must be assigned to one Mechanic. A Service can use multiple Spare Parts, and the same Spare Part can be used in multiple service transactions. Therefore, a many-to-many relationship exists between Service and Spare Part, which is resolved using an intermediate table called Service_Part. The ER diagram can be represented as follows: ```text ┌──────────────────────┐ │ CUSTOMER │ ├──────────────────────┤ │ Contact_ID PK │ │ Customer_Name ________ ______ __________ ________ _____ __________ ________.
_____ ___ _____ ______ _______ _________.
__________ ___ ______ _______ _____ _____ _______ ___ _________ ______ ______.
____ __________ ______ ________ ___ ____ __________ _____ _____ _________ _________ _________.
______ ______ ________ ___ _____.
_______ _______ _________ _______ ____ ___ ___.
____ ________ ________ ______ _________ _________ _______ _________ ______.
_________ ____ __________ __________ ___ ______ ________ ______ _______ ________ ___ _____.
________ _______ _____ _________ _______.
____ __________ _________ ______ ___ ____ _________.
________ __________ ___ _____ ______ ____ _______.
______ ______ _____ _____ _______ ______ __________ _____ _________ __________.
_____ ____ _________ ________ ___ ________ _________.
_____ ______ ____ ____ ______ _______ ______.
_________ ____ ___ __________ ___ _________ __________ _______.
_________ _______ _____ _____ ______.
______ ______ ____ ______ ___ ______ ___ _________.
________ __________ _________ _____ ____ __________ _________.
___ ________ _______ ________ _____ __________ ____ ______.
______ _____ ________ ___ _____ __________.
_______ ________ ________ _______ ____ ______ _____.
______ ________ ___ _________ ______ _____ _______ ______ _____.
_____ _________ _________ _____ ___ _______ _________.
_______ ____ __________ ______ ________ ________ ___ _________ __________ ___ _________ ____.
__________ ____ __________ _______ ________ ______ ___.
_______ _______ ______ _________ ______ _____ ______ ______ ____ ___ ______ ________.
________ ____ _________ ___ __________ ___ ______ _________ ______ ______.
______ _________ ____ _______ __________ ______ __________ _______ ___ ____ ____.
______ _____ __________ _____ ______ _______ _______ _____ ___.
____ ___ ________ _______ ______ ___ ____ _________.
________ ________ ______ __________ _____ _______ __________ _______ _____.
____ ___ _______ ___ _______ __________ __________.
_____ _______ ________ __________ _________ __________ ______ ________ _______.
____ __________ _________ _____ _________ _____.
__________ ______ _______ _____ __________ ________ _____ _____ _____ __________ ________ _________.
_____ ________ __________ ________ _______ _____.
_______ ______ __________ ________ _____.
_______ __________ _______ _______ _________ ______ ______.
_____ _______ _____ ______ _____ ______ _________ _______ _______ __________ __________ _____.
___ _____ ________ __________ ________ ___ ___ ___ ____ _________ _____ __________.
________ ____ _________ ____ ______ ________ _________ __________ ___ _____ ____ _______.
____ ________ _______ ______ _________ ___ _____ ____.
___ __________ ____ ___ ___ ________ _____.
_____ ___ ____ ________ _________ ___ ________.
____ _____ ____ _________ ____ ___ ____ ___ ______ __________ ________.
_________ _____ _________ _____ _____ ________ _____.
______ ___ __________ ________ _____ _____ ___.
___ __________ _________ _______ _______ _________ ________.
_________ __________ ____ _________ ____ _____ _____ ____ ______ ____.
_______ _________ ________ ______ ______.
_____ ________ ______ ______ _________ __________ _______.
_____ _________ __________ __________ ____ ____ ________.
__________ _________ ___ __________ __________ ____ _________.
__________ ______ _________ ____ _________ ____.
____ _______ ____ _____ __________ _________ ________ _________ ___ __________ ___.
_________ ________ _______ _______ ____ _______ _______ ___ __________.
____ _____ _________ ____ ______ ____ ________ ________ _________ __________ _______ __________.
__________ ____ ________ _______ ____.
_______ ________ _____ ______ _______.
_______ _________ ______ ________ _________ _________ ____ _________ _____ ______ ________.
________ __________ ___ _____ __________ __________ _______ _____ ______ ___ _________.
_______ __________ ____ ________ _____ ________ _______ ________ ________.
_________ _________ _____ ______ ________ ___.
_____ __________ ______ _____ ___ __________ _________ __________ ____ __________.
_________ ________ _____ ______ ________ _________ _______ ___ ________ __________ __________ _________.
____ _________ ______ ________ ________ ___ ____ __________ ____ __________ _______.
____ ____ ____ _____ __________.
_______ ______ ________ _________ _________ _______ _____.
_______ ___ ____ ________ ________ _____.
___ __________ _______ ________ _____ _________ _________ _________ ___ ________ ______.
_____ _______ ________ _____ _________ ___.
________ __________ ________ _____ ________ __________ ________ ____.
________ _____ _________ ______ ______ ________ ____ ______ _____ __________.
_____ ___ ___ ___ ____ ____ ________ ___ ___ ________ ______.
________ ________ _________ _________ ______ ____ __________ ______ __________ ______.
______ ____ ______ ______ __________ __________ ___ ____ _____ _________ _________ ______.
______ ________ _____ ______ _________ ____.
______ ______ _______ __________ ________ ______ ___ ________ ___ ____.
__________ _____ _______ ______ _______ ________ _____ ______ _______.
________ __________ ___ _______ _______ __________ ______.
_____ _______ ______ ____ ________ _________ ____ ______ ______ ______.
_______ _________ ________ _______ __________ _____ ________ _____.
_________ ___ __________ _____ ______ ___ _________ ___ ________ ____ ______.
__________ ___ ___ ______ ____ ___ _____.
______ _________ __________ _____ _________ ________ ______ __________ ________ __________ _________.
_______ ____ ____ _______ ___ _______ __________ ______ _____ ________.
___ ___ ______ _____ __________.
_____ ______ __________ __________ ___ ________ ______ ____ ________ ________ __________ _____.
_________ ______ ______ _______ ________ _________ ______ ____ _____ ________.
_________ _________ _________ ___ ______ ________ ____ ______ _________ ______ _____.
_______ _______ ________ ___ _______ _______ ______ _____ ____ ____.
_____ ___ ___ __________ ________ ______ ___ _____ __________ _____.
____ _____ ______ _________ _________ _________ ______ _______ ____ ________.
_________ ___ ____ ______ _______ _________ _________.
__________ _____ _________ _________ __________ __________ _____ ____ ____ ______ ______.
________ __________ __________ __________ ________.
_________ ____ __________ _________ _________ ____ _________ __________ ____ ____ _____.
______ _______ _____ ___ _______ __________.
______ ______ ______ _________ _____ ________ ________ _____.
__________ ____ _________ ______ ________ __________ __________ ____ ______ _________ ___.
_____ _______ _____ _________ ___ ______ ________.
___ _________ _________ __________ __________ ____ ___ ________ _______ _____ ___.
___ ___ _________ ______ ______ ___ ___ ____ ________.
_________ _________ ___ _________ ____ ___ ____ __________ __________ ______.
____ ___ ____ ______ ____ _____ ___ ____.
________ __________ _____ _______ __________ ___ _________ ____ _______ _______ ______ ______.
____ ____ ____ ______ ___ _________ ___ ________ _____ ______ ______.
________ ______ _____ ________ _____ ___ ____ ____ __________ _________ ___ ____.
________ ___ ___ ___ _________ ____.
____ ____ ______ ______ _____ ______ ________ _____ __________ ____ __________ ____.
___ ______ ____ ________ ___ _________ __________ _____ ___ _____ __________ ____.
______ ___ _______ ____ ____ _____ __________ ___ _______ _______ _____.
_______ _________ __________ __________ ___.
___ ___ ____ ______ ____.
__________ _________ ________ ____ ________ _________ ________ ______ _____ _____ _________ _____.
__________ ____ _____ ____ _____ ______ __________ _______ ___ ______.
__________ _____ _________ ________ ________ _______ __________ _________ __________.
______ _________ ______ ________ ________ ___ ________ ___ ___ ____ _____ _______.
_____ __________ __________ ________ ____ ______ ________.
Get Full Answer on WhatsApp
The Vehicle Service Management System can be designed as a relational database in which customers, vehicles, mechanics, services, and spare parts are stored in separate but related tables. The main objective of the design is to avoid data redundancy, maintain data integrity, and represent the relationships between customers, their vehicles, mechanics, service transactions, and spare parts. The database can be implemented using SQL and can later be connected with a C++ program in the DBMS and C++ Lab for inserting, updating, searching, and displaying records. The main entities in the system are Customer, Vehicle, Mechanic, Service, Spare Part, and Service Part. A Customer can own many Vehicles, but every Vehicle belongs to one Customer. A Vehicle can have many Service transactions, while each Service transaction is related to exactly one Vehicle. A Mechanic can handle many Service transactions, but each Service transaction must be assigned to one Mechanic. A Service can use multiple Spare Parts, and the same Spare Part can be used in multiple service transactions. Therefore, a many-to-many relationship exists between Service and Spare Part, which is resolved using an intermediate table called Service_Part. The ER diagram can be represented as follows: ```text ┌──────────────────────┐ │ CUSTOMER │ ├──────────────────────┤ │ Contact_ID PK │ │ Customer_Name ________ ______ __________ ________ _____ __________ ________.
_____ ___ _____ ______ _______ _________.
__________ ___ ______ _______ _____ _____ _______ ___ _________ ______ ______.
____ __________ ______ ________ ___ ____ __________ _____ _____ _________ _________ _________.
______ ______ ________ ___ _____.
_______ _______ _________ _______ ____ ___ ___.
____ ________ ________ ______ _________ _________ _______ _________ ______.
_________ ____ __________ __________ ___ ______ ________ ______ _______ ________ ___ _____.
________ _______ _____ _________ _______.
____ __________ _________ ______ ___ ____ _________.
________ __________ ___ _____ ______ ____ _______.
______ ______ _____ _____ _______ ______ __________ _____ _________ __________.
_____ ____ _________ ________ ___ ________ _________.
_____ ______ ____ ____ ______ _______ ______.
_________ ____ ___ __________ ___ _________ __________ _______.
_________ _______ _____ _____ ______.
______ ______ ____ ______ ___ ______ ___ _________.
________ __________ _________ _____ ____ __________ _________.
___ ________ _______ ________ _____ __________ ____ ______.
______ _____ ________ ___ _____ __________.
_______ ________ ________ _______ ____ ______ _____.
______ ________ ___ _________ ______ _____ _______ ______ _____.
_____ _________ _________ _____ ___ _______ _________.
_______ ____ __________ ______ ________ ________ ___ _________ __________ ___ _________ ____.
__________ ____ __________ _______ ________ ______ ___.
_______ _______ ______ _________ ______ _____ ______ ______ ____ ___ ______ ________.
________ ____ _________ ___ __________ ___ ______ _________ ______ ______.
______ _________ ____ _______ __________ ______ __________ _______ ___ ____ ____.
______ _____ __________ _____ ______ _______ _______ _____ ___.
____ ___ ________ _______ ______ ___ ____ _________.
________ ________ ______ __________ _____ _______ __________ _______ _____.
____ ___ _______ ___ _______ __________ __________.
_____ _______ ________ __________ _________ __________ ______ ________ _______.
____ __________ _________ _____ _________ _____.
__________ ______ _______ _____ __________ ________ _____ _____ _____ __________ ________ _________.
_____ ________ __________ ________ _______ _____.
_______ ______ __________ ________ _____.
_______ __________ _______ _______ _________ ______ ______.
_____ _______ _____ ______ _____ ______ _________ _______ _______ __________ __________ _____.
___ _____ ________ __________ ________ ___ ___ ___ ____ _________ _____ __________.
________ ____ _________ ____ ______ ________ _________ __________ ___ _____ ____ _______.
____ ________ _______ ______ _________ ___ _____ ____.
___ __________ ____ ___ ___ ________ _____.
_____ ___ ____ ________ _________ ___ ________.
____ _____ ____ _________ ____ ___ ____ ___ ______ __________ ________.
_________ _____ _________ _____ _____ ________ _____.
______ ___ __________ ________ _____ _____ ___.
___ __________ _________ _______ _______ _________ ________.
_________ __________ ____ _________ ____ _____ _____ ____ ______ ____.
_______ _________ ________ ______ ______.
_____ ________ ______ ______ _________ __________ _______.
_____ _________ __________ __________ ____ ____ ________.
__________ _________ ___ __________ __________ ____ _________.
__________ ______ _________ ____ _________ ____.
____ _______ ____ _____ __________ _________ ________ _________ ___ __________ ___.
_________ ________ _______ _______ ____ _______ _______ ___ __________.
____ _____ _________ ____ ______ ____ ________ ________ _________ __________ _______ __________.
__________ ____ ________ _______ ____.
_______ ________ _____ ______ _______.
_______ _________ ______ ________ _________ _________ ____ _________ _____ ______ ________.
________ __________ ___ _____ __________ __________ _______ _____ ______ ___ _________.
_______ __________ ____ ________ _____ ________ _______ ________ ________.
_________ _________ _____ ______ ________ ___.
_____ __________ ______ _____ ___ __________ _________ __________ ____ __________.
_________ ________ _____ ______ ________ _________ _______ ___ ________ __________ __________ _________.
____ _________ ______ ________ ________ ___ ____ __________ ____ __________ _______.
____ ____ ____ _____ __________.
_______ ______ ________ _________ _________ _______ _____.
_______ ___ ____ ________ ________ _____.
___ __________ _______ ________ _____ _________ _________ _________ ___ ________ ______.
_____ _______ ________ _____ _________ ___.
________ __________ ________ _____ ________ __________ ________ ____.
________ _____ _________ ______ ______ ________ ____ ______ _____ __________.
_____ ___ ___ ___ ____ ____ ________ ___ ___ ________ ______.
________ ________ _________ _________ ______ ____ __________ ______ __________ ______.
______ ____ ______ ______ __________ __________ ___ ____ _____ _________ _________ ______.
______ ________ _____ ______ _________ ____.
______ ______ _______ __________ ________ ______ ___ ________ ___ ____.
__________ _____ _______ ______ _______ ________ _____ ______ _______.
________ __________ ___ _______ _______ __________ ______.
_____ _______ ______ ____ ________ _________ ____ ______ ______ ______.
_______ _________ ________ _______ __________ _____ ________ _____.
_________ ___ __________ _____ ______ ___ _________ ___ ________ ____ ______.
__________ ___ ___ ______ ____ ___ _____.
______ _________ __________ _____ _________ ________ ______ __________ ________ __________ _________.
_______ ____ ____ _______ ___ _______ __________ ______ _____ ________.
___ ___ ______ _____ __________.
_____ ______ __________ __________ ___ ________ ______ ____ ________ ________ __________ _____.
_________ ______ ______ _______ ________ _________ ______ ____ _____ ________.
_________ _________ _________ ___ ______ ________ ____ ______ _________ ______ _____.
_______ _______ ________ ___ _______ _______ ______ _____ ____ ____.
_____ ___ ___ __________ ________ ______ ___ _____ __________ _____.
____ _____ ______ _________ _________ _________ ______ _______ ____ ________.
_________ ___ ____ ______ _______ _________ _________.
__________ _____ _________ _________ __________ __________ _____ ____ ____ ______ ______.
________ __________ __________ __________ ________.
_________ ____ __________ _________ _________ ____ _________ __________ ____ ____ _____.
______ _______ _____ ___ _______ __________.
______ ______ ______ _________ _____ ________ ________ _____.
__________ ____ _________ ______ ________ __________ __________ ____ ______ _________ ___.
_____ _______ _____ _________ ___ ______ ________.
___ _________ _________ __________ __________ ____ ___ ________ _______ _____ ___.
___ ___ _________ ______ ______ ___ ___ ____ ________.
_________ _________ ___ _________ ____ ___ ____ __________ __________ ______.
____ ___ ____ ______ ____ _____ ___ ____.
________ __________ _____ _______ __________ ___ _________ ____ _______ _______ ______ ______.
____ ____ ____ ______ ___ _________ ___ ________ _____ ______ ______.
________ ______ _____ ________ _____ ___ ____ ____ __________ _________ ___ ____.
________ ___ ___ ___ _________ ____.
____ ____ ______ ______ _____ ______ ________ _____ __________ ____ __________ ____.
___ ______ ____ ________ ___ _________ __________ _____ ___ _____ __________ ____.
______ ___ _______ ____ ____ _____ __________ ___ _______ _______ _____.
_______ _________ __________ __________ ___.
___ ___ ____ ______ ____.
__________ _________ ________ ____ ________ _________ ________ ______ _____ _____ _________ _____.
__________ ____ _____ ____ _____ ______ __________ _______ ___ ______.
__________ _____ _________ ________ ________ _______ __________ _________ __________.
______ _________ ______ ________ ________ ___ ________ ___ ___ ____ _____ _______.
_____ __________ __________ ________ ____ ______ ________.
Get Full Answer on WhatsApp
IGNOU NEWS
Assignment Submission Last Date Extended Till 30 June 2026 Click Here★★★IGNOU June 2026 TEE Date Sheet Released Click Here★★★