ACSCars, Inc.: Design a Normalized Database

ACSCars, Inc., a car manufacturing firm, wants to launch a web-based database application for its vendors and dealers. It wants to develop the application by using MySQL as the database.

Assume that you are working as a senior software developer for ACSCars, Inc. and have been assigned to develop the required application. After several rounds of discussion, you decide that a database with the following tables and fields should be created:

Tcars with car details such as: car_id, car_name, car_type, car_model, car_color, and car_price.

Tdealers with dealers details such as: dealer_id, dealer_name, dealer_location, dealer_state, and dealer_phno.

Tvendors with vendors details such as: vendor_id, vendor_name, vendor_location, vendor_state, vendor_phno, and vendor_items.

Transactions_master with transactions that took place daily such as: trans_id, dealer_id, car_id, car_amt, car_discount, and trans_date.

Transactions_detailsif you want to extend it to your trans_details, reduce the duplicity in trans_master and put the same in trans_details. For the given scenario, complete the following tasks:

Write SQL statements to create these tables. (Make sure to include primary and foreign keys when linking the tables.)

Use the tables created above to develop a web-based database. You will not be creating the entire application, just the database.