1. Homepage
  2. Programming
  3. CS 6400: Database Systems Concepts and Design - Team Project: BuzzCars

CS 6400: Database Systems Concepts and Design - Team Project: BuzzCars

Engage in a Conversation
GaTechCS 6400Database Systems Concepts and DesignBuzzCarsJavaPythonPHPJSP

CS 6400 – Team Project Fall 2023 CourseNana.COM

BuzzCars CourseNana.COM

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. CourseNana.COM

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. CourseNana.COM

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. CourseNana.COM

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! CourseNana.COM

Functionality and Users CourseNana.COM

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: CourseNana.COM

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 CourseNana.COM

  • Salespeople, who will only have access to searching available inventory, entering customer information, and entering sales transactions CourseNana.COM

  • Managers, who can view inventory, purchase history, sales transactions, parts order history, and reports CourseNana.COM

  • And the owner, who has access to everything and can perform any activity in the system (a combination of all permissions) CourseNana.COM

    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. CourseNana.COM

    Operational Details CourseNana.COM

    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. CourseNana.COM

    Vehicles CourseNana.COM

    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. CourseNana.COM

    Customers CourseNana.COM

    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 CourseNana.COM

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 CourseNana.COM

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. CourseNana.COM

Parts CourseNana.COM

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). CourseNana.COM

Buyers CourseNana.COM

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. CourseNana.COM

Application Functionality CourseNana.COM

Public Access CourseNana.COM

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. CourseNana.COM

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: CourseNana.COM

  • Vehicle type CourseNana.COM

  • Manufacturer CourseNana.COM

  • Model year CourseNana.COM

  • Fuel type CourseNana.COM

  • Color CourseNana.COM

  • Keyword, which searches the manufacturer, model year, model name and description CourseNana.COM

    fields. Anything that matches the entered keyword (either entirely or as a substring) for CourseNana.COM

    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!” CourseNana.COM

    If there are vehicles that match the search criteria, you should return the following attributes for each vehicle in the search results: CourseNana.COM

Privileged Access CourseNana.COM

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. CourseNana.COM

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. CourseNana.COM

In addition, all privileged users will have an additional search option added to the search page which allows for searching by VIN. CourseNana.COM

Inventory Clerks CourseNana.COM

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.) CourseNana.COM

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. CourseNana.COM

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. CourseNana.COM

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. CourseNana.COM

Salespeople CourseNana.COM

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. CourseNana.COM

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. CourseNana.COM

Managers CourseNana.COM

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. CourseNana.COM

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. CourseNana.COM

Owner CourseNana.COM

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!) CourseNana.COM

Reports CourseNana.COM

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. CourseNana.COM

Seller History CourseNana.COM

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. CourseNana.COM

Average Time In Inventory CourseNana.COM

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. CourseNana.COM

Price Per Condition CourseNana.COM

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. CourseNana.COM

Parts Statistics CourseNana.COM

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. CourseNana.COM

Monthly Sales CourseNana.COM

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. CourseNana.COM

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.) CourseNana.COM

Get in Touch with Our Experts

Wechat WeChat
Whatsapp Whatsapp
GaTech代写,CS 6400代写,Database Systems Concepts and Design代写,BuzzCars代写,Java代写,Python代写,PHP代写,JSP代写,GaTech代编,CS 6400代编,Database Systems Concepts and Design代编,BuzzCars代编,Java代编,Python代编,PHP代编,JSP代编,GaTech代考,CS 6400代考,Database Systems Concepts and Design代考,BuzzCars代考,Java代考,Python代考,PHP代考,JSP代考,GaTechhelp,CS 6400help,Database Systems Concepts and Designhelp,BuzzCarshelp,Javahelp,Pythonhelp,PHPhelp,JSPhelp,GaTech作业代写,CS 6400作业代写,Database Systems Concepts and Design作业代写,BuzzCars作业代写,Java作业代写,Python作业代写,PHP作业代写,JSP作业代写,GaTech编程代写,CS 6400编程代写,Database Systems Concepts and Design编程代写,BuzzCars编程代写,Java编程代写,Python编程代写,PHP编程代写,JSP编程代写,GaTechprogramming help,CS 6400programming help,Database Systems Concepts and Designprogramming help,BuzzCarsprogramming help,Javaprogramming help,Pythonprogramming help,PHPprogramming help,JSPprogramming help,GaTechassignment help,CS 6400assignment help,Database Systems Concepts and Designassignment help,BuzzCarsassignment help,Javaassignment help,Pythonassignment help,PHPassignment help,JSPassignment help,GaTechsolution,CS 6400solution,Database Systems Concepts and Designsolution,BuzzCarssolution,Javasolution,Pythonsolution,PHPsolution,JSPsolution,