Automating Administrative Duties for a High-End Car Dealership

Car and Customer Details

Archie‟s Luxury Motors is a car dealership that specializes in high-end luxury vehicles in addition to offering the biggest selection of local and international vehicles from the world‟s most popular manufacturers in all price brackets. Rather than being situated in the middle of the hustle and bustle of a large metropolis, Archie's Luxury Motors enjoys an idyllic rural setting in the beautiful open countryside. The dealership currently records its data manually, keeping records in hardcopy, with individual files being held for different car sale transactions. This process is slow, laborious and not very secure. As repeating fields and human error are being attributed to most of the errors occurring, this reflects badly on the company‟s image. The dealership would therefore like to turn its manually kept information into a computerized database to help automate processes. In addition, at this point in time due to the amount of manual transactions, there is a large cost associated with keeping track of the cars currently in the dealership‟s possession. The owner, Archie, would like to reduce the losses and cut down unwanted costs and expenses by keeping track of all car sale transactions and information regarding the dealership‟s customers and sales agents. To do this effectively, Archie has decided to automate all administrative duties in relation to the process of recording car sale transactions and car display shows. When a customer is first interested in purchasing a car from the dealership, they complete a form that contains their personal details along with their desired car features (see Appendix 1 – “New Customer Questionnaire Form"). The dealership‟s business premise is to match cars with customers based on the features that a car has, and the features that a customer desires. The form lists the most basic categories of common car features (i.e. Comfort and Convenience, Safety and Security, etc.) but the complete list is far more extensive. Alternatively, some customers are motoring enthusiasts and leave the features section blank on the form. Instead, they inquire directly to the sales agent about which car they are after. Furthermore, the dealership not only records which features a car contains, but also its model and the car manufacturer. In the car industry, many manufacturers follow what is known as a „product line architecture‟. This means that rather than wasting time and money on a brand new model design, the manufacturer would rather make small changes to an already markettested model. For each model, its type must be recorded (e.g. sedan, 4WD, etc.) and its previous model, if applicable. For each manufacturer, its name and region (i.e. Europe, Asia, Oceania, etc.) must be recorded. Archie offers a VIP program where customers can subscribe to become VIPs of the dealership. This program is designed to entice customers to have a long-term relationship with the company. In addition, the dealership organizes contests every month where VIPs can win monetary prizes and other spontaneous gifts. If a customer decides to become a VIP, he/she has an option to create a user account on the company‟s website in order to participate in these contests. Information that must be recorded about each contest includes the contest number, the starting and ending dates, along with the prize description. Participation in contests is not compulsory; therefore the user accounts participating in a contest and the winner must also be recorded. CSE4DBF (2015) – Assignment 1, Part 1 Page 3/6 Once the customer has decided to purchase a specific vehicle after conversing with one of the dealership‟s sales agents, a Bill Of Sale is completed to follow regulations (see Appendix 2 – “Vehicle Bill Of Sale Form"). The car‟s 17 digit VIN number is recorded, along with the customer‟s and sales agent‟s details. In addition to the fields contained in Appendix 2, the dealership also keeps track of the date that the car entered the dealership, the price that the dealership paid for the car, and the price that the car is on sale for. At the dealership, there is a hierarchy amongst the sales agents. Senior agents are the most experienced and best negotiators. Each senior agent is assigned to supervise at least one junior agent. Each junior agent has only one supervisor. When a junior agent is promoted to senior, the date of promotion must be recorded. This is due to the commission policy at Archie‟s Luxury Motors. Senior agents receive a commission for each sale where the agreed price with the customer is greater than the “for sale” price in the database (the asking price). The actual amount of commission depends on how many years that the agent has been a senior for. For example, for an agent who has been a senior for 5 years, the commission for a sale would be calculated as: (AgreedPrice – AskingPrice) * 5%, where the agreed price is greater than the asking price. Note that the percentage amount is always calculated in full years. For all sales agents, their ID, name and date of birth is also recorded. Given that the dealership is situated on an extensive block of land, Archie decided to construct a multi-million dollar car showroom and test track in order to boost revenue. The dealership offers the chance for a group of people (known as a Viewing Party) to come and view/test drive exotic cars on premise (see Appendix 3 – “Viewing Party Form"). To boost market research, Archie offers special deals to viewing parties who are part of an organization or those who are international guests. The success of Archie‟s viewing parties has recently led to many international businesses hosting special work functions at the dealership. It is important to note that some cars are so breathtaking that it is common for the same viewing party to view a particular car more than once. Discounts are offered to viewing parties who make more than three visits in a calendar year. TASKS: 1. You are required to develop an EER model for the above problem description. The ER should contain all necessary information such as entities, attributes, primary keys, relationships (including specialization/generalization if any), cardinalities, and participation (including (min, max)). You must have at least one specialization/generalization or union type in your EER diagram. Any assumptions should also be stated clearly. If assumptions are made, it is important to make sure that the assumptions reflect possible real practice for a car dealership and do not contradict with the problem description above. Assignment should be typed, not written/drawn by hand. Use CASE tool or any software to draw figures in your assignment. [75%] 2. Apply Normalization Techniques (Bottom-Up approach) on Appendices 1, 2 and 3 to come up with BCNF tables.