CS 6400: Database Systems Concepts and Design - Team Project: BuzzCars
CS 6400 – Team Project Fall 2023
BuzzCars
An anonymous Georgia Tech alumnus has decided to go into the business of selling used cars. While she’d like to eventually have a full website like most car dealerships, she’s decided for now that she’d just like a simple application so that she can update her inventory when buying and selling, keep track of financial information, and allow customers visiting her showroom to search her inventory. Right now, she doesn’t have enough staff to take pictures of the cars, so only the details will be stored in the database. Along with the functions to support her operations, she wants a couple of reports so she can track how well her new business is doing.
When reading through this project description, please make the following assumptions: unless otherwise specified as optional, all attributes are required; unless otherwise specified, if given a list of potential values, choices should be limited to that list; If a set of values is listed with "and/or”, combinations of those values are possible, while “or” indicates only a single value is possible; that you should create normalized schemas, and minimize the use of NULL. You also do not need to be concerned about handling concurrent operations that could conflict and introduce inconsistencies in your database.
You may implement the project UI as a traditional standalone application (e.g., Java GUIs or Python’s TkInter) or as a web application (e.g., web scripting languages like PHP or JSP). Remember that your project will not be graded on its aesthetic appeal, but on its functionality.
Do not create any additional functionality that is not mentioned in this specification (such as email notifications, etc.) or attempt to enhance your final product beyond what the specification requires. Adding unwanted functionality can and will impact your grade!
Functionality and Users
BuzzCars will have a public-facing interface for searching vehicles, and additional features that can be accessed by logging in as a user. These features will be described in further detail later, but it is important to remember to distinguish between public users and logged-in users and what they may/may not access. Furthermore, there are distinct categories of users that will login, with various permissions:
• Inventory clerks, who buy vehicles and add them to inventory, along with information about the car’s previous owner (also considered customer information), and enter parts orders
-
Salespeople, who will only have access to searching available inventory, entering customer information, and entering sales transactions
-
Managers, who can view inventory, purchase history, sales transactions, parts order history, and reports
-
And the owner, who has access to everything and can perform any activity in the system (a combination of all permissions)
Since this is a prototype system, it will not be necessary to have an interface for adding/registering users and granting them appropriate permissions. The database administrator will manually add users and set permissions as needed. All users will be identified using a unique username determined by the database administrator and will login with their username and a password assigned by the DBA. (It is acceptable to store passwords in the database as plaintext in the initial version of this system.) You should also store the first and last name of the user to further identify them in other areas of the system.
Operational Details
There are a variety of people and things involved in the day-to-day operations of BuzzCars. Unless otherwise specified, any properties mentioned here are required. You should build a database schema that facilitates storing the information needed for these processes.
Vehicles
Vehicles are tracked on a variety of characteristics. First, each vehicle has a unique alphanumeric Vehicle Identification Number (VIN). Next, the type of vehicle is stored. The list of vehicle types is in the appendix and should be updatable by the database administrator in case new types of vehicles are invented. The manufacturer name is also stored, and a list of valid manufacturer names is provided in the appendix of this specification. Of course, the color of the car is also an important detail, and a list of generic color names that can be chosen for a vehicle is also in the appendix. A car may have multiple colors, for example, silver and red. The list of colors is not expected to change. The mileage (odometer reading) is also stored for each vehicle. Finally, an optional description can be entered that contains additional information such as what accessories or equipment the car has or any other information.
Customers
Sellers and buyers (combined, referred to as “customers”, because they are capable of buying and selling) can be either an individual person or a business. For all customers, their address (street/city/state/postal code) and their phone number are collected. Customers also have the
option of providing an email address so that BuzzCars can stay in touch with them
electronically. If the customer is an individual, their first and last names, along with their
driver’s license number (which can be assumed to be unique), will be recorded. If the customer
is a business, the business’ tax identification number (similar to a Social Security number) and
business name, along with the name of a primary contact and their title (such as owner, fleet
manager, etc.), are recorded.
Sellers
Vehicles are sold to BuzzCars by sellers, and the vehicle should link back to the seller that sold the vehicle. It is safe to assume that a car will only be sold to BuzzCars once, but sellers may sell multiple vehicles. Purchase prices are entered for each sale manually by the inventory clerk handling the transaction, who will determine the condition of the vehicle (Excellent, Very Good, Good, Fair) which corresponds to a certain value determined by the inventory clerk. The purchase date should be tracked to determine how long the car is in inventory.
Parts
Some cars are purchased and need new parts before they can be sold. An inventory clerk will determine what parts are necessary, lookup where to purchase them (and for how much) and input the order information into the system. Clerks may order one or more parts in a parts order. Each part order is associated with a certain vendor, so you will need to keep track of the name of the vendor, their address (street/city/state/postal code) and phone number, in addition to BuzzCars’ purchase order number (which is generated by combining the VIN with the ordinal of the order – so the first parts order for (ordered/received/installed), a description of the part, the part number (which may be alphanumeric), and the cost of the part. If multiple quantities of a part are needed, the quantity will be tracked as part of the parts order. The total cost of a parts order is the sum of all parts’ costs. Parts status will be manually updated by clerks; however, they cannot revert a part to a previous status (such as installed to ordered). The status for each part must be tracked individually as all parts on an order are rarely received altogether. It’s also possible for a vehicle to have multiple parts orders from the same vendor for a vehicle. Because part prices may change, you should not worry about maintaining a static list of part numbers and prices. A vehicle cannot be returned for any public search results or be sold if it has any parts pending or not installed (in other words, all parts must be installed).
Buyers
Vehicles are bought by buyers in a sale transaction with a salesperson. The sales price is calculated as 125% of the original purchase price (the price BuzzCars paid to buy the car) combined with 110% of any parts costs also associated with the vehicle. Just as with selling, the vehicle should have a link to the customer who purchased it, and it’s possible (and good for business) that a buyer can purchase several vehicles. Should a buyer purchase several vehicles at the same time, they would still be handled as separate sales transactions. The purchase date should be tracked in order to determine when a car leaves inventory.
Application Functionality
Public Access
The only feature accessible to the public is searching for vehicles. Because of this the initial state of the application should be to open the “public” search page, with an option to login provided somewhere on that page.
The public search page should initially display somewhere prominent, the total number of cars available for purchase in the system, that is, cars without any pending parts orders. Searching can be done on the following criteria:
-
Vehicle type
-
Manufacturer
-
Model year
-
Fuel type
-
Color
-
Keyword, which searches the manufacturer, model year, model name and description
fields. Anything that matches the entered keyword (either entirely or as a substring) for
any of those fields, case insensitive, should be returned.
For fields other than keyword, it may be appropriate to use drop-downs to provide choices to the user. You do not need to allow making multiple selections for these fields, selecting a single value is acceptable. Results must match all search options that are entered.If no vehicles meet the search criteria, a message should be displayed: “Sorry, it looks like we don’t have that in stock!”
If there are vehicles that match the search criteria, you should return the following attributes for each vehicle in the search results:
-
VIN
-
Vehicle type
-
Model Year
-
Manufacturer
-
Model
-
Fuel type
-
Color(s) – be sure if a car has multiple colors, that it only returns a single result row and
that all colors are listed
-
Mileage
-
Sales Price
These results should be sorted by VIN in ascending order, no other sorting options are necessary. Users can select an individual result, which will open a detail page that includes the VIN, vehicle type, Model Year, Model Name, Manufacturer, fuel type, color(s), mileage, sales price, and the description of the car.
Privileged Access
As noted previously, users who are employees of BuzzCars will have access to additional features to perform their job duties. Remember that you do not need to provide any interface for creating or registering users and granting them privileges. Ideally, all users will start on the public-facing search screen, which provides a login option, and after logging in, will update to include access to the appropriate functionality.
One area of common functionality is the ability to look up and add customers to the system. However, this is only available when performing a purchase or sales transaction and is not something that needs to be independently accessible. and based on the customer type, the appropriate fields (as described earlier in this specification) should be input into the system.
In addition, all privileged users will have an additional search option added to the search page which allows for searching by VIN.
Inventory Clerks
After an inventory clerk logs in, he/she will be given access to an “Add Vehicle” button or link, that will allow them to add new vehicles that have been purchased. On the add vehicle form, the clerk will need to search or add a customer to link the purchase to a seller. along with the date of purchase. After submitting the data and successfully adding the vehicle to the database, the clerk will be taken to the detail page for the vehicle. (What the detail page looks like for clerks will be described further on.)
Inventory clerks will need to also pull up previously purchased vehicles and will do that using the search screen, which should show them somewhere on the search screen the number of vehicles currently. Clerks can search on the same criteria as public users, along with the option to search by VIN. Unlike public search, however, the results for a clerk should include any vehicle that has not been sold even those with parts pending. Selecting a result will load that vehicle’s detail page.
The clerk’s view of the detail page is similar to the detail page shown to public users and should show the same information but include fields for the original purchase price and the total of all parts costs. A newly added car will show $0 total for parts because it has no parts ordered yet. There should also be a mechanism for updating the status of an individual part from ordered, to received, to installed. A part’s status cannot be changed to a previous status, and once it is installed, its status can no longer be updated. An “add part order” link or button should also be provided to access the add parts order form.
If a part needs to be entered, the clerk will access the parts order form and enter the information for that part (these elements were described previously in the “part” section). Similar to how customers are handled, you should have a mechanism on the parts order form for searching and adding vendors to link them to a parts order. Since multiple parts may be part of the same order, you will need to provide a mechanism for entering multiple parts into an order.
Salespeople
Salespeople will start, after logging in, on the search page. Upon loading the detail page for a vehicle, the sales person will see the same detail page that customers do, with an added button or link to sell the car. This will load the sales order form.
On the sales order form, salespeople can look up a customer (or add them if a customer is not found) and confirm the sale by entering the sales date. The sales price of the car cannot be changed – BuzzCars’ philosophy doesn’t believe in bargaining over car prices and customers will enjoy knowing that the price listed for a car is the price they will pay without any added hassle.
Managers
Managers have view-only access to all information along with reports (which will be described in their own section). Like inventory clerks and salespeople, after logging in, managers start on the search screen, can also search by VIN. They additionally have the option to filter by sold vehicles, unsold vehicles, or all vehicles. When filtering by unsold or all vehicles, all unsold vehicles will be returned regardless of parts status.
When viewing a vehicle detail page, managers will see all information for the car – including all of the seller’s contact information (everything except their driver’s license or tax ID number), the name (first and last)ike would be shown on an inventory clerk’s view. In addition, if the car has been sold, the buyer’s contact information (everything except their driver’s license or tax ID number), sales date, and the salesperson’s name (first and last) will be displayed.
Owner
As stated previously, the owner has access to the complete functionality of the system, must be able to view all information and reports, and should be able to do any activity described previously in this specification. Essentially, the owner’s login will allow her to do anything a manager, inventory clerk, or salesperson can do, keeping in mind any context for business processes. (For example, the vehicle detail page will show all information like it does for managers, and the owner will also be able to sell a car or add parts to it, but the system should not allow her to add parts to or sell a car that has been sold. This is just an example of one natural limitation – you may need to determine if there are others!)
Reports
Reports will be visible to the owner and managers. Access to these reports should be via a link, button, or dropdown menu that can be displayed on the initial search page for users that are allowed access to reports.
Seller History
This report will show detail about all vehicles purchased by BuzzCars and their sellers. It will include the following elements: the name of the seller (either first name and last name or company name, which should be displayed as a single column, not two different columns for each seller type), the total number of vehicles they have sold to BuzzCars, the average purchase price for the vehicles they have sold to BuzzCars, the average number of parts ordered per vehicle, and the average cost of parts per vehicle. The report should be sorted by total number of vehicles sold descending, followed by average purchase price ascending. In addition, any seller who has sold vehicles and shows an average of five or more parts on this report, or where the average cost of parts is $500 or more, should have their resulting row highlighted with a red background to indicate that they may be selling lower quality vehicles and that BuzzCars may want to avoid buying from them in the future.
Average Time In Inventory
This report, based on the difference between vehicle sales dates and the vehicle purchase dates, will display, by vehicle type, the average amount of time a vehicle remains in inventory, in days. When counting days spent in the inventory, the first and last day should be counted as a day, and if a vehicle was added and sold on the same day, it will be counted as one day. If a vehicle type has no sales history, the report should display “N/A” for that vehicle type.
Price Per Condition
This report will display, by vehicle type, and for each condition (Excellent, Very Good, Good, Fair), the average price paid for cars that BuzzCars has purchased. If a vehicle type or condition has never been purchased, the report should display “$0” for that result. This should be a pivoted report (there should not be a row for each combination of vehicle type and condition), so vehicle type could be displayed as rows, with condition for the columns, or vice versa – your team can decide which form “looks” better.
Parts Statistics
This report will be used to negotiate better prices with parts vendors. In this report, you should list: the vendor name, the number of parts supplied by that vendor, and the total dollar amount spent on parts.
Monthly Sales
This report will be the most frequently used report and has two parts. First, a summary page, which lists for all sales transactions, by year and month, the total number of vehicles sold, the total sales income, and the total net income (which is sales price less purchase price and any parts costs). If a year or month does not have sales data, it can be excluded from this report. The results will be ordered by year and month descending, with the most recent year and month as the first result.
From each year/month result, a drilldown report for that year and month must be accessible. Based on the sales data for that year and month, the drilldown will display the top performing salespeople, by showing the salesperson’s first and last name, the number of vehicles they sold in that year and month and their total sales for that year and month. To determine who is the top sales person for the month, the drilldown will be sorted by total vehicles descending followed by total sales descending. (In other words, in the event of a tie where two or more salespeople have sold the same number of vehicles, the salesperson who has sold the highest dollar value will be considered the top salesperson.)
