Database Design and Implementation Report Assignment
Introduction
The design, implementation and optimization of a relational database system for an online vehicle marketplace are explored in this research. The system leveraging inheritance modelling, selective de-normalization, indexing, stored procedures and triggers ensures data integrity, and query efficiency and ensures transactional security thereby reducing vehicle sales and inventory management to a whole clean check experience seamlessly.
Task 1
a. Database Design: Physical Model
Figure 1: Physical Model
(Source: Self-Created)
b. Physical Model Rationale
The used vehicle marketplace database physical model uses super/subtyping (inheritance), selective de-normalization, and non-key indexing to speed things up, maintainability, and performance. These choices are supported by accepted database design principles, along with considerations on the domain of online vehicle sales.
The implementation of super/subtyping (inheritance) in the VEHICLE entity allows systematic management of various types of vehicles (CAR, MOTORCYCLE, TRUCK). Therefore, the single table inheritance was chosen to store common attributes in the VEHICLE table, and specific attributes are stored in their respective subtype tables. This method is used to avoid the duplication of the Make, Model, Year, Mileage, and Price type of data in more than one table, which is useful in maintaining data integrity and eliminating redundancy. Without this approach, information might be spread across multiple tables for each separate vehicle category, causing data fragmentation through multiple complexities in finding all vehicles together. Also, foreign keys to VEHICLE enforce referential integrity, meaning that a CAR, MOTORCYCLE or TRUCK record cannot be inserted without a corresponding VEHICLE record. As noted by Alfarouq and Alnaser (2022), such a model is necessary to achieve hierarchical relationships in relational databases and to offer flexibility and scalability, where new vehicle types can be added without a substantial schema modification.
In the SALES table, selective de-normalization was used — we stored SalePrice instead of dynamically linking to VEHICLE.Price. It was done to maintain historical price consistency because vehicle prices valid in time are not constant, but the sale price is constant for historical financial auditing and reporting. However, this redundancy would be avoided by strict normalization, but in a real-world transactional system, performance criteria are taken into account. It will help avoid expensive joins and lookup operations to generate reports or track past sales when storing SalePrice in SALES. Miryala, (2024), mentions that in transactional systems that frequently allow for historical queries to be optimized, it can afford to sometimes have these reads on disk, at the expense of minimal redundancy.
VEHICLE.Price is applied to a non-key index for optimization of search and filtering performance. Since users filter vehicles by price most frequently, it is important to index this attribute to significantly reduce query response time. In the absence of an index, MySQL would have to carry out a full table scan, making it less efficient as the data set expands. Garcia-Molina et al. (2020) state that non-key attributes can be quickly indexed because of their benefits on range searches, especially in e-commerce platforms where filtering and sorting are common functionalities (Zhu et al. 2024). This decision ensures that customer queries do not become inefficient even as the number of vehicle listings increases.
To conclude, the design choices made in this database model handle this compromise to normalization principles with functional considerations while keeping practical performance in mind. Logical data structure is preserved in the inheritance model, and de-normalization with transactional consistency is in the inheritance model while non-key indexing brings search efficiency. Such choices make sure the database always stays scalable, performant and robust when the online vehicle marketplace grows.
Task 2
a. Database Implementation
Figure 2: All Tables in the Database
(Source: Workbench)
Figure 3: Customer Table
(Source: Workbench)
Figure 4: Vehicle Table
(Source: Workbench)
Figure 5: Car Table
(Source: Workbench)
Figure 6: Motorcycle Table
(Source: Workbench)
Figure 7: Truck Table
(Source: Workbench)
Figure 8: Sales Table
(Source: Workbench)
Figure 9: Order Table
(Source: Workbench)
Figure 10: Shipping Table
(Source: Workbench)
b. Stored Database Objects
i. Two Meaning Examples of Views
Figure 11: View 1
(Source: Workbench)
The view that the given SQL code provides is AvailableVehiclesForSale, a view of the list of unsold vehicles and the details of the seller for each of those vehicles. This is done by joining the VEHICLE table and CUSTOMER table and filtering out a subquery on the SALES table. The purpose of this view is to accomplish the most efficient and scalable way to obtain only available vehicles so as to optimize the search and filtering operations in the marketplace.
The JOIN VEHICLE and CUSTOMER will then ensure that a seller’s details such as the Name and Email are returned directly within the view without having to perform another query. This method is aligned with the principles of database optimization and reduces redundant queries with the speed of the query execution (Kossmann et al. 2022). To avoid including the Vehicles that are already present in the SALES table, the filter condition used is NOT IN (SELECT VehicleID FROM SALES) which only takes the Vehicles which are not already present in the SALES table. This helps prevent already sold vehicles from being presented to the user as available listings.
In addition, an index on VEHICLE.VehicleID allows faster filtering of records in a query. Because large datasets require less memory consumption and faster execution time, but likewise would increase complexity, the use of subqueries as in Uzzaman et al. 2024 falls into a happy median. Further supporting the customer’s decision-making, Condition and Price fields are included.
Overall, this view intends to improve performance, accuracy, and user experience while removing redundant queries, maintaining data integrity, and improving filtering performance and it can be a good construct for a platform of an online vehicle marketplace.
Figure 12: View 2
(Source: Workbench)
The view that is created by the SQL code is called SalesSummaryReport and compiles a list of all completed sales transactions that include vehicle details and buyer information. SALES’ JOIN operations with CUSTOMER and VEHICLE guarantee that the data in the report consists of comprehensive data on each sale and is linked to buyer data from CUSTOMER and the vehicle’s attributes from VEHICLE. This structure avoids the need for many queries and ultimately provides better efficiency in any query and readability.
ORDER BY S.SaleDate DESC is used to order sales in descending order which makes it more related to business monitoring at the expense of losing the actual sequence of sales. Sales transactions are sorted in descending order to facilitate the decision-making of stakeholders such as sales managers who must monitor recent trends in vehicle sales. Further, the sale price being placed in the SALES table meets good practices in data warehousing when old prices need to be separated from the new dynamic pricing of inventory.
The view helps optimize performance in reporting by pre-computing the needed joins and reducing the execution of complex queries for sales analysis. Structured storage of the buyer’s name, vehicle details, and sale price makes it easier for the customers to have service interactions or to do financial reconciliations. Additionally, query speed is further improved when indexing on SaleDate in large-scale transactional databases. In addition, this depiction promotes scalability ahead, enabling the addition of more attributes, like payment method or buyer location without any mess. Therefore, the database is more flexible to changes in business requirements and will allow the processing and analysis of larger growing sales volume over time.
Finally, the SalesSummaryReport allows data easier accessibility, improved operational efficiency, and more accurate reporting for tracking vehicle sales on the online marketplace.
ii. Two Meaning Examples of Stored Procedures
Figure 13: Procedure 1
(Source: Workbench)
The stored procedure GetVehiclesByType is a stored procedure which will retrieve vehicles, using their type, from the VEHICLE table so that efficient and structured data can be retrieved from the VEHICLE table. There is one single input parameter (vType), which is constrained by ENUM('Car', 'Motorcycle', 'Truck'). This constrains the data integrity to only allow certain types of valid vehicle types so the risk of invalid or malicious input is reduced. This constraint is then implemented at the procedural level to prevent the occurrence of possible errors stemming from inappropriately typed input parameter values which potentially reduce database reliability.
Within the procedure, the SELECT statement is written so that when users are provided with tools to view information about available vehicles, they receive complete information (VehicleID, Make, Model, Year, Mileage, Price, and VehicleType). This condition WHERE V. VehicleType = vType in the WHERE clause makes sure to get only vehicles of the required type. The filtering mechanism presented in this thesis improves query performance by reducing unnecessary retrieval of data, something that is most important when large datasets have to be searched in an online vehicle marketplace.
A great advantage of using a stored procedure instead of a normal query is that there is improved performance optimization and security enhancement. Precompiled stored procedures are faster than dynamic SQL queries because the code is precompiled. Stored procedures are also immune from SQL injection attacks because parameterized inputs remove the threat of malicious code execution.
Finally, what the GetVehiclesByType stored procedure does save is the database security, the database performance, and the database integrity. A perfect vehicle data query tool that can be of immense help in an online vehicle marketplace which provides an efficient method for querying vehicle data along with scalability for advanced operations.
Figure 14: Procedure 2
(Source: Workbench)
The recordSaleTransaction stored procedure is used to handle a vehicle sale transaction in a structured and efficient way. p_BuyerID, p_VehicleID and p_SalePrice (buyer, vehicle being sold, final sale price) are all input parameters, and it accepts three parameters. The procedure is made to execute two important steps, namely inserting a new record into the SALES table and deleting from the VEHICLE table as soon as the sold vehicle's status is switched to 'Sold' initially. It allows the vehicle data to be properly updated and gives the ability to sell a vehicle with no chance of duplicate sales.
Inserting the relevant details in the SALES table is the first part of the procedure. This ensures that all vehicle sales are accurately recorded in accounting systems, so the figures can be added and consolidated to track and report financial positions and be used for auditing. Vehicle price is aware that the price change over time but transaction price (SalePrice) need to be maintained to be used in the future. This is consistent with best practices in transactional database systems of denormalizing sales records to speed the performance at the cost of storage.
The second step is optional but is an important way to ensure inventory accuracy and is to delete the vehicle from VEHICLE. It can sometimes result in some data loss, but it will never show the sold vehicle as available. In an alternate approach, a status field in VEHICLE can be up to date instead of deletion, keeping historical inventory data while achieving the same functional advantage.
Handling sales transactions with a stored procedure provides a high performance and high-security benefit. The encapsulation of logic in a precompiled routine limits the risk of SQL injection attacks and strengthens data security (Garcia-Molina et al. 2020). Additionally, procedures enhance the performance by eliminating redundant query execution, making the system scalable and optimized for high arrivals environments.
iii. One Meaning Examples of Trigger
Figure 15: Trigger
(Source: Workbench)
The data integrity is enforced by preventing duplicate sales transactions using the PreventDuplicateSales trigger. This is a BEFORE INSERT trigger on the SALES table, such that it will prevent a vehicle from being sold more than one time by checking if the VehicleID exists in the SALES table. Then the transaction will be prevented from being executed if the condition is met, which the trigger will do by raising an error with SIGNAL SQLSTATE '45000' using a custom message. To maintain correct inventory management and eliminate discrepancies in sales records, this is a very important approach.
The use of the EXISTS condition helps the trigger very effectively check if the vehicle has not already been sold. As it stops searching as soon as it finds a match, the optimization of the SELECT 1 FROM SALES WHERE VehicleID = NEW.VehicleID query makes it more efficient since it cuts away the computational overhead created by wandering around aimlessly. In a relational database management system (RDBMS), these principles are to prevent redundancy and ensure transactional consistency; hence this trigger is an essential safeguard.
PreventDuplicateSales serves as a remote object database trigger ensuring data integrity, security, and reliability by enacting constraint enforcement at the database level instead of at the application level. It enables you to reduce the chance of application level bypasses or synchronization problems between the inventory and sales records. Also, triggers are a good way to ensure rules consistent with business within the database layer, keeping the application side validations as simple as possible.
Of course, triggers come with some overhead from a performance point of view, but this is a small price to pay for guaranteeing data consistency and stopping fraudulent transactions from occurring. These constraints are important in very high-volume transactional systems to avoid data corruption and record accurate history.
Therefore, the PreventDuplicateSales trigger enables the enforcement of inventory control, which prevents sales collection of the Same VehicleId only once through the vehicle marketplace system by validating integrity and security during transactions.
Conclusion
The research offers appropriate strategies in the field of database design that would maintain a good balance of normalization principles with database performance optimization. The system utilizes robust constraints, indexing, and procedural automation to make it scalable, secure, and operationally efficient while still being a trusted architecture for online vehicle transactions with data consistency across a large volume.
This advanced database assignment showcases practical implementation of physical modelling, indexing, views, stored procedures, and triggers within a real-world vehicle marketplace system. It serves as a strong reference for understanding academic structure, technical justification, and applied SQL logic. If you need tailored support with database design, SQL development, or report writing, assignment help online can deliver plagiarism-free, fully documented solutions aligned with your university marking criteria—saving time while improving grades.
References
Alfarouq, O. and Alnaser, A., 2022. Comparative Evaluation of Graph, Hierarchical, and Relational Structuring Paradigms in Large Commonsense Knowledge Bases. Journal of Computational Intelligence, Machine Reasoning, and Decision-Making, 7(9), pp.1-20.
Kossmann, J., Papenbrock, T. and Naumann, F., 2022. Data dependencies for query optimization: a survey. The VLDB Journal, 31(1), pp.1-22.
Miryala, N. K. (2024). Emerging Trends and Challenges in Modern Database Technologies: A Comprehensive Analysis. ResearchGate Publication, November.
Uzzaman, A., Jim, M.M.I., Nishat, N. and Nahar, J., 2024. Optimizing SQL databases for big data workloads: techniques and best practices. Academic Journal on Business Administration, Innovation & Sustainability, 4(3), pp.15-29.
Zhu, R., Wang, H., Xia, S., & Zheng, B. (2024). Learned index for non-key queries. Knowledge and Information Systems, 1-23.
