The following question is based upon a Car-Service relation which records the details of transactions occurring in an Automotive Mechanic and Repair business. You may assume the data are representative.

CustName CustDOB ServiceDATE CarRego CarMade CarModel Service FEE Staff NAME Staff speciality Lois Price 8/09/1998 19/5/2023 1y568 Toyota Camry 199 Ali Service tech Justin Thyme 10/10/2000 8/4/2022 1y576 Honda CRV 400 Chip Auto body Stan Dupp 10/10/2000 9/5/2023 1t567 Toyota Camry 199 Ali General Jack Pott 29/03/1981 2/10/2022 1t158 Ford Focus Fifty Holly Service tech Lois Price 8/09/1998 1/8/2022 1y458 Tesla Model X 199 Ali Auto body Ina Minat 03/01/1991 15/9/2022 1i132 Holden Astra 190 Chip Auto body Mike Rafone 30/01/2004 15/9/2022 1l884 BMW Z4 One twenty Ali 99999 Ina Minat 03/01/1991 10/8/2019 1i123 Black car Astra 269 Ali General

You have been asked to design a relational database for this system. You know that there are problems with the current design and that it will need to be modified in order to work effectively. You need to write a 1–2-page report that addresses the following:

1. What are the specific problems associated with the current design and why do they arise?
2. How would you change the current design and how does your new design address the problems you have identified with the current design. In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3, how they apply to this problem, and justify the changes you are making to the system. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question. Give technical answers as well

Report on the Current Design of the Car-Service Relation and Proposed Modifications

Introduction

The current design of the Car-Service relation presents several issues that can lead to data redundancy, inconsistency, and inefficiencies in data management. This report identifies the specific problems associated with the current design and proposes a new relational database schema that addresses these issues.

Problems with the Current Design

1. Data Redundancy

The current design contains repeated information across multiple records. For instance, the customer "Lois Price" appears multiple times with different service transactions. This redundancy can lead to increased storage requirements and complicates data management, as any updates to customer information must be made in multiple places.

2. Lack of Normalization

The current design does not adhere to the principles of normalization, particularly the First Normal Form (1NF) and Second Normal Form (2NF). For example, the "Staff speciality" field contains multiple specialties for the same staff member, which violates 1NF. Additionally, the "Service FEE" is directly associated with the service transaction but does not account for the relationship between services and staff specialties, leading to potential anomalies.

3. Inconsistent Data Entry

The data entry for "Service FEE" shows inconsistencies in formatting (e.g., "Fifty" vs. "One twenty"). This inconsistency can lead to difficulties in data processing and analysis, as numerical values should be stored in a consistent format for calculations.

4. Poor Referential Integrity

The current design lacks foreign key relationships that would enforce referential integrity between entities such as customers, vehicles, services, and staff. This can lead to orphaned records and inconsistencies in the database.

Proposed Changes to the Design

To address the identified problems, I propose a new relational database schema that includes the following tables:

1. Customers Table

• CustomerID (Primary Key)
• CustName
• CustDOB

2. Vehicles Table

• VehicleID (Primary Key)
• CarRego
• CarMade
• CarModel
• CustomerID (Foreign Key referencing Customers)

3. Staff Table

• StaffID (Primary Key)
• StaffName
• StaffSpeciality

4. Services Table

• ServiceID (Primary Key)
• ServiceDate
• ServiceFee
• VehicleID (Foreign Key referencing Vehicles)
• StaffID (Foreign Key referencing Staff)

5. ServiceTypes Table (Optional for further normalization)

• ServiceTypeID (Primary Key)
• ServiceTypeName

6. ServiceDetails Table (Optional for further normalization)

• ServiceDetailID (Primary Key)
• ServiceID (Foreign Key referencing Services)
• ServiceTypeID (Foreign Key referencing ServiceTypes)

Justification for the New Design

1. Elimination of Redundancy

By separating customers, vehicles, staff, and services into distinct tables, we eliminate redundancy. Each customer, vehicle, and staff member is stored only once, reducing storage requirements and simplifying updates.

2. Improved Normalization

The proposed design adheres to normalization principles, ensuring that each table contains data relevant to a single entity. This reduces the risk of anomalies during data insertion, deletion, or updates.

3. Consistent Data Entry

By defining specific data types for each field (e.g., numeric for ServiceFee), we ensure consistent data entry. This facilitates accurate calculations and reporting.

4. Enhanced Referential Integrity

The use of foreign keys establishes relationships between tables, enforcing referential integrity. This ensures that every service transaction is linked to a valid customer, vehicle, and staff member, preventing orphaned records.

5. Scalability and Flexibility

The new design allows for easy expansion. For instance, if new service types are introduced, they can be added to the ServiceTypes table without altering the existing structure.

Conclusion

The current design of the Car-Service relation presents several issues that can hinder effective data management. By implementing a new relational database schema that emphasizes normalization, reduces redundancy, and enforces referential integrity, we can create a more efficient and reliable system for managing automotive service transactions. This new design not only addresses the current problems but also provides a scalable framework for future growth.