Designing Relational Database for a Used Car Buying and Selling Website
1.Project Objective
The aim of this project is to develop a relational database for a website that facilitates the buying and selling of used cars. The website will allow sellers to advertise their cars and potential buyers to search for used cars based on various categories. However, this project does not include purchase transactions since they are carried out separately outside the website.
2.Database Design Phases
The following phases will be involved in designing the database:
2.1.Defining a Mission Statement
The designed database must be able to store the required data and have the following features:
- Website user data. To advertise their used cars, sellers must register as users. Similarly, buyers must also register as users to be able negotiating the product prices.
- Used cars data offered by sellers. Each user can offer more than one used car.
- Product advertisement data. Products are offered by used car sellers through advertisements displayed on the website.
- Product search data. Website visitors can search for cars offered based on the seller’s location, car brand, and car body type.
- Price negotiation history. Price negotiation can only be carried out if the seller activates the bid feature for each product offered.
- City data. Buyers and sellers of used cars on this website come from all regions of Indonesia, so there must be data about cities in Indonesia
2.2 Creating Table Structure
Tables required in the database:
Required fields, data types, primary key and foreign keys for each table
Notes:
PK = Primary Key, a column that contains unique values to distinguish each record in a table
FK = Foreign Key, a column used to connect one table to another table
2.3 Defining Table Relationships
Note:
1:N = One-to-many relationship, a type of association between two tables in a relational database where one record in the first table (the parent table) can be associated with multiple records in the second table (the child table). This means that each record in the child table must have a corresponding record in the parent table, but each record in the parent table can have zero or more corresponding records in the child table.
2.4 Determining Business Rules and Constraints
Business rules are the guidelines that govern how data is used and managed within a database. Constraints are specific limitations or restrictions that are applied to data to enforce business rules.
2.5 Implementing the Relational Database
The result of this process is an Entity Relationship Diagram (ERD), which is a graphical representation of the relationships between entities (tables) in a database. It is a useful tool for visualizing the overall structure of the database and identifying potential data redundancies or anomalies.
The following ERD represents the relationships between the six tables described in the previous section:
The hierarchical model of the database is characterized by a parent-child relationship between tables. In this model, each child table is dependent on its parent table, and each parent table can have multiple child tables.
The order in which the tables are created is important in a hierarchical database. The parent tables should be created first, followed by their child tables. This ensures that the child tables have the necessary data from the parent tables to function properly. The sequence for creating tables in this project is:
The queries used to create each table in PostgreSQL are presented in the image below.
Indexes also created on frequently used columns so the database can quickly locate the relevant data without having to scan the entire table. Because users usually search for used car offers data based on seller location, car brand, and car body type, indexes are created on three columns using these queries
3. Generating Dummy Data Using Python Faker Library
The next step in the database designing process is to populate the database with data. Since the client (Pacmann) only provided city and product data in CSV format, dummy data needs to be generated for the remaining four tables: users, advertisement, product_search, and bid. Dummy data is randomly generated data that is used for testing purposes. The dummy data will be generated using the Python Faker library and produce CSV files as output.
The steps of dummy data generation should follow the same steps as table creation, starting with the parent tables or the tables with the least dependency on other tables. The following sections provide the syntax for generating dummy data for each table:
3.1 Generating Dummy Data for Users Table
Since the users table contains city_id field whose value must match the city_id field in the city table, the city table data needs to be imported in Python before generating dummy data. The following function can be used for that purpose:
#Create function to load file csv and save into list of dictionary
def csv_to_dict(filename):
#open file csv
with open(f'{filename}', mode='r', encoding='utf-8-sig') as file:
csv_reader = csv.DictReader(file)
#save data as list of dictionary
data = {}
for row in csv_reader:
for key, value in row.items():
data.setdefault(key, []).append(value)
return data
#Create function to show table
def show_data(table):
tab = tabulate(tabular_data = table,
headers = table.keys(),
tablefmt = "psql",
numalign = "center")
print(tab)
#Extract file city.csv into list of dictionary
city = csv_to_dict('city.csv')
#Show city data as a table
show_data(city)Then create dummy data for the user table with the following function:
def tabel_users (n_data, is_print):
#Define start date
start_date = datetime(2023, 6, 1)
#Define end date
end_date = datetime(2023, 12, 31, 23, 59, 59)
#Make the table outline
table = {}
table["user_id"] = [i+1 for i in range(n_data)]
table["first_name"] = [FAKER.first_name() for i in range(n_data)]
table["last_name"] = [FAKER.last_name() for i in range(n_data)]
table["phone"] = [FAKER.phone_number() for i in range(n_data)]
#Create username based on a combination of first name and last name
table["username"] = []
for i in range(n_data):
first_name = table["first_name"][i]
last_name = table["last_name"][i]
email = f"{first_name}{last_name}_{FAKER.pyint()}"
table["username"].append(email)
#Create email based on a combination of first name and last name
table["email"] = []
for i in range(n_data):
first_name = table["first_name"][i].lower()
last_name = table["last_name"][i].lower()
email = f"{first_name}_{last_name}{FAKER.pyint()}@{FAKER.free_email_domain()}"
table["email"].append(email)
#Choose random city_id from city table
table["city_id"] = [random.choice(city['city_id']) for i in range(n_data)]
#Create random password
table["password"] = [FAKER.password() for i in range(n_data)]
#Create random joined_date
table["joined_date"] = [FAKER.date_time_between(start_date=start_date, end_date=end_date) for i in range(n_data)]
#Print table
if is_print:
show_data(table)
return table
#Create 100 rows data of users table
users = tabel_users(n_data = 100, is_print = True)The Users table consists of 9 fields. These are rules for generating dummy data for each field.
The dummy data generated for the users table consists of 100 data rows with the following appearance:
3.2 Generating Dummy Data for Advertisement Table
Since the advertisement table contains a product_id field whose value must match the value of the product_id field in the product table, before generating dummy data, the product table data must be read in Python Programming Language using the same functions as before, csv_to_dictionary and show_data, as follows:
#Extract file car_product.csv into list of dictionary
product = csv_to_dict('car_product.csv')
#Show product data as a table
show_data(product)Then dummy data is generated for the advertising table using the following function:
def tabel_advertisement(n_data, is_print):
#Define start date, at least the date when the user was registered
start_date = min(users["joined_date"])
#Define end date akhir, with the maximum date is today
end_date = datetime.now()
#Create table
table = {}
table["adv_id"] = [i + 1 for i in range(n_data)]
table["date_created"] = [FAKER.date_time_between(start_date=start_date, end_date=end_date) for i in range(n_data)]
table["user_id_seller"] = [random.choice(users["user_id"]) for i in range(n_data)]
table["product_id"] = [random.choice(product["product_id"]) for i in range(n_data)]
table["title"] = [f"Dijual murah {product['model'][product['product_id'].index(i)]}
{product['year'][product['product_id'].index(i)]}" for i in table["product_id"]]
table["description"] = [f"Minat hubungi {users['phone'][users['user_id'].index(i)]}" for i in table["user_id_seller"]]
table["sell_price"] = []
for product_id in table["product_id"]: #Menetapkan harga maksimum dan minimum sesuai model mobil
if product['model'][product['product_id'].index(product_id)] == "Toyota Yaris":
sell_price = FAKER.random_int(124_000_000, 240_000_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Toyota Agya":
sell_price = FAKER.random_int(97_000_000, 155_000_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Toyota Calya":
sell_price = FAKER.random_int(104_000_000, 137_000_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Daihatsu Ayla":
sell_price = FAKER.random_int(83_000_000, 120_000_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Daihatsu Terios":
sell_price = FAKER.random_int(166_000_000, 223_000_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Daihatsu Xenia":
sell_price = FAKER.random_int(100_000_000, 220_500_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Honda Jazz":
sell_price = FAKER.random_int(178_000_000, 250_000_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Honda CR-V":
sell_price = FAKER.random_int(116_000_000, 415_000_000, 10_000_000)
elif product['model'][product['product_id'].index(product_id)] == "Honda Civic":
sell_price = FAKER.random_int(186_000_000, 397_500_000, 10_000_000)
else:
sell_price = FAKER.random_int(100_000_000, 178_000_000, 10_000_000)
table["sell_price"].append(sell_price)
#Define bid features with 95% of the chance data enables bid features
table["can_bid"] = [FAKER.boolean(chance_of_getting_true = 95) for i in range(n_data)]
#Print table
if is_print:
show_data(table)
return table
#Create 400 rows data of advertisement table
advertisement = tabel_advertisement(n_data = 400, is_print = True)Advertisement table consists of 8 fields. These are rules for generating dummy data for each field.
The dummy data generated for the advertisement table consists of 400 data rows with the following appearance:
3.3 Generating Dummy Data for Product_search Table
Dummy data for the product_search table is created after creating the advertisement table because the product_search table has one field that comes from the advertisement table, namely the advertisement ID number or adv_id. Dummy data for the product_search table is created with the following function:
def tabel_search(n_data, is_print):
#Define start date, at least the date when the advertisement was created
start_date = min(advertisement["date_created"])
#Define end date, with the maximum date is today
end_date = datetime.now()
#Create table
table = {}
table["search_id"] = [i + 1 for i in range(n_data)]
table["date_created"] = [FAKER.date_time_between(start_date=start_date, end_date=end_date) for i in range(n_data)]
table["adv_id"] = [random.choice(advertisement["adv_id"]) for i in range(n_data)]
#Print table
if is_print:
show_data(table)
return table
#Create 700 rows data of product_search table
product_search = tabel_search(n_data = 700, is_print = True)Product_search table consists of 3 fields. These are rules for generating dummy data for each field.
The dummy data generated for the product_search table consists of 700 data rows with the following appearance:
3.4 Generating Dummy Data for Bid Table
Dummy data for the bid table is created after creating the advertisement and users tables because the bid table has one field that comes from the advertisement table, namely the advertisement ID number or adv_id, and one field that comes from the users table, namely the user ID number or user_id of the potential buyer. Bargaining process with the seller or the advertiser can only be done by website visitors who have registered as users. Dummy data for the bid table is generated with the following function:
def tabel_bid(n_data, is_print):
#Define start date
start_date = min(advertisement["date_created"])
#Define end date
end_date = datetime.now()
#Create
table = {}
table["bid_id"] = [i + 1 for i in range(n_data)]
table["date_created"] = [FAKER.date_time_between(start_date=start_date, end_date=end_date) for i in range(n_data)]
adv_ids = [random.choice(advertisement["adv_id"]) for i in range(n_data)]
table["adv_id"] = adv_ids
table["user_id_buyer"] = []
for adv_id in adv_ids:
#Ambil data user_id_seller
used_user_ids = {advertisement["user_id_seller"][i] for i, aid in enumerate(advertisement["adv_id"]) if aid == adv_id}
#Daftar user_id tersedia karena user_id_buyer tidak boleh sama dengan user_id_seller
available_user_ids = set(users["user_id"]) - used_user_ids
#Pilih random user_id_buyer
table["user_id_buyer"].append(random.choice(list(available_user_ids)))
#Define bid price
min_prices = [0.5 * advertisement["sell_price"][advertisement["adv_id"].index(adv_id)] for adv_id in adv_ids]
max_prices = [advertisement["sell_price"][advertisement["adv_id"].index(adv_id)] for adv_id in adv_ids]
#Looping the bid price
table["bid_price"] = []
for adv_id in adv_ids:
bid_price = None
if advertisement['can_bid'][advertisement['adv_id'].index(adv_id)] == False:
bid_price = max_prices[advertisement["adv_id"].index(adv_id)]
#jika tidak ada fitur can_bid, harga yang bisa dimasukkan = sell_price
else:
bid_price = FAKER.random_int(min=min_prices[advertisement["adv_id"].index(adv_id)],
max=max_prices[advertisement["adv_id"].index(adv_id)],
step=500_000)
table["bid_price"].append(bid_price) # Append the generated bid_price
# Print table
if is_print:
show_data(table)
return table
#Create 550 rows data of bid table
bid = tabel_bid(n_data = 550, is_print = True)Bid table consists of 5 fields. These are rules for generating dummy data for each field.
The dummy data generated for the bid table consists of 550 data rows with the following appearance:
4. Inserting Data into Database
The city and product tables provided by the client (Pacmann) and four tables generated with Python Faker Library (users, advertisement, product_search, and bid tables) then imported into the database using the import data function from pgAdmin as in the image below
After the import process is successful and before performing transactional queries, it is necessary to check whether the data has been entered as desired. This can be done using the SELECT * query to display the contents of each table, as shown in the following examples.
After importing the dataset, backup is performed to facilitate the restore process of the database. The backup process is carried out using the backup function in pgAdmin. The database backup file can be downloaded in this link.
5. Creating Transactional Queries
Transactional queries are queries used to perform a specific action or task, such as inserting, updating, or deleting data stored in a database. Here are some common transactional queries performed on a used car sales website database:
5.1 Finding cars manufactured in specific year
There is a data type error in the year field of the product table. Therefore, to perform a transactional query to find cars manufactured in year ≥ 2015, the data type of the year field needs to be changed to integer using the following query:
The following is the query that was run to find used car products manufactured in ≥ 2015 and the results.
5.2 Adding a Bid Record
Since only registered users can submit bids, first I will register as a user before adding a bid. The query carried out to register as a user is as shown below:
The following query is used to input a new bid data, namely bid_id = 551
Results after inputting one new bid data with bid_id = 551 by user_id_buyer= 101
5.3 Looking for a seller that sells the most products
5.4 Viewing All Cars Offered by a Seller and Sorting the Displayed Data by the Most Recently Advertised Product
Based on the previous query, we have identified the seller with the most products, namely Mahmud Hariyah (user_id = 96) who offers 13 products. In this section we will display all the used cars that Mahmud Hariyah has offered and sort the products based on the latest advertised date.
5.5 Searching for the Cheapest Used Car Based on a Keyword
For example, we want to search the cheapest Toyota Yaris available for sale. The query used is as follows:
5.6 Searching for Used Cars Closest to the Buyer’s Location
The distance is calculated by creating a haversine distance function based on the latitude and longitude difference between the seller’s and buyer’s locations.
--Create function measuring distance between two cities
CREATE FUNCTION haversine_distance (point1 POINT, point2 POINT)
RETURNS float AS $$
DECLARE
lon1 float := radians(point1[0]);
lat1 float := radians(point1[1]);
lon2 float := radians(point2[0]);
lat2 float := radians(point2[1]);
dlon float := lon2 - lon1;
dlat float := lat2 - lat1;
a float;
c float;
r float := 6371;
jarak float;
BEGIN
--haversine formula
a := sin(dlat/2)^2+cos(lat1)*cos(lat2)*sin(dlon/2)^2;
c := 2*asin(sqrt(a));
distance := r*c;
RETURN distance;
END;
$$ LANGUAGE plpgsql;For example, a prospective buyer in the city of Surabaya is looking for a Suzuki Ertiga closest to his location. So, the ads displayed in the filter only for Suzuki Ertiga model products and sorted based on the kilometer distance between the seller’s location and the city of Surabaya.
--Create temporary table seller detail location
CREATE TEMPORARY TABLE detail_seller AS
WITH adv_user AS (SELECT adv_id, user_id_seller, product_id, sell_price, city_id FROM advertisement AS x
LEFT JOIN users as y ON user_id_seller = user_id),
adv_user_city AS (SELECT * FROM adv_user LEFT JOIN city USING (city_id))
SELECT adv_id, user_id_seller, city_name, brand, model, body_type, year, sell_price, location
FROM adv_user_city LEFT JOIN product USING (product_id)
--Search for the nearest used Suzuki Ertiga car based on the distance between the buyer and seller
SELECT adv_id, user_id_seller, city_name AS seller_location, brand, model, body_type, year, sell_price,
haversine_distance ((location),
(SELECT location FROM city WHERE city_name = 'Kota Surabaya')) AS km_distance FROM detail_seller
WHERE model = 'Suzuki Ertiga'
ORDER BY km_distanceand we will get the output as follows:
Based on the results presented above, it can be seen that if a buyer is looking for a Suzuki Ertiga car whose location is closest to him, for example in Surabaya, there is one Suzuki Ertiga advertisement with the seller’s location in Surabaya, 7 Suzuki Ertiga advertisements with the seller’s location in Malang and 6 advertisements Suzuki Ertiga with seller location in Yogyakarta.
6. Creating Analytical Queries
Analytical queries are used to gain insights from data such as identify trends and patterns in data, answer complex questions about data and make predictions about future trends. Here are some common analytical queries performed on a used car sales website database:
6.1 Car Model Popularity Ranking based on number of bids
Based on the image above, it can be concluded that the most popular car model is the Toyota Calya with the highest number of offers, namely 64 offers.
6.2 Comparing car prices with average prices per city
--Create CTE joining users and city table
WITH users_city AS (SELECT a.user_id, b.city_name FROM users as a LEFT JOIN city as b ON a.city_id = b.city_id),
--Create CTE joining users_city and advertisement table
adv_uc AS (SELECT x.adv_id,x.product_id,x.sell_price,y.city_name
FROM advertisement as x LEFT JOIN users_city as y ON x.user_id_seller = y.user_id)
--Joining adv_uc and product table
SELECT city_name, brand, model, year, sell_price,
AVG (sell_price) OVER (PARTITION BY city_name ORDER BY sell_price DESC RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS avg_carprice_city
FROM adv_uc as c LEFT JOIN product as d ON c.product_id = d.product_id
ORDER BY avg_carprice_city DESC
Based on the results displayed above, Balikpapan has the highest average selling price for used cars, namely IDR 202,500,000.
6.3 Comparing the bid price given when user place a bid with subsequent bids
--Joining bid and advertisement table
WITH adv_bid AS (SELECT a.bid_id, a.date_created AS bid_date, a.adv_id, a.bid_price, a.user_id_buyer,
b.product_id, b.sell_price
FROM bid as a LEFT JOIN advertisement as b ON a.adv_id = b.adv_id),
--Joining adv_bid and product table
rawdata AS (SELECT y.model, x.* FROM adv_bid AS x LEFT JOIN product AS y USING (product_id)),
--Create data partition based on adv_id and user_id_buyer
rawdata2 AS (SELECT model, adv_id, user_id_buyer, sell_price, bid_date AS first_bid_date,
LEAD(bid_date,1) OVER (PARTITION BY adv_id, user_id_buyer ORDER BY bid_date) AS next_bid_date,
bid_price AS first_bid_price,
LEAD(bid_price,1) OVER (PARTITION BY adv_id, user_id_buyer ORDER BY bid_date) AS next_bid_price FROM rawdata)
--Filtering product only for model Honda Civic and user_id_buyer bid more than 1 time
SELECT * FROM rawdata2
WHERE next_bid_date IS NOT null AND model = 'Honda Civic'
For example, for the Honda Civic model, there are three potential buyers who submitted bid more than once for the same adv_id (advertisement), namely user_id_buyer 67, 10 and 69.
User_id_buyer = 67 submitted 2 bids for the Honda Civic product in adv_id=24 on November,29 2023 at a price of 72.500.000 and December,24 2023 at a price of 98.000.000. User_id_buyer = 10 submitted 2 bids for the Honda Civic product in adv_id=181 on July,10 2023 at a price of 149.500.000 and October,24 2023 at a price of 109.000.000. User_id_buyer = 69 submitted 2 bids for the Honda Civic product in adv_id=207 on September,17 2023 at a price of 186.500.000 and then on January,1 2024 at a price of 186.000.000.
6.4 Comparing the percentage difference in the average price set by the seller (sell_price) with the average price proposed by potential buyers (bid_price) based on the car model in a certain time period
--Create a temporary table to calculate average sell price for the last 6 months
CREATE TEMPORARY TABLE avg_sell_price AS
SELECT model, AVG(sell_price) AS avg_sell_price FROM advertisement as a LEFT JOIN product as B USING (product_id)
WHERE date_created BETWEEN '2023-07-05' AND '2024-01-05'
GROUP BY 1
--Create a temporary table to calculate average bid price for the last 6 months
CREATE TEMPORARY TABLE avg_bid_price AS
WITH rawdata AS (SELECT * FROM advertisement as a LEFT JOIN product as B USING (product_id))
SELECT model, AVG(bid_price) AS avg_bid_price FROM bid as y LEFT JOIN rawdata as x USING (adv_id)
WHERE y.date_created BETWEEN '2023-07-05' AND '2024-01-05'
GROUP BY 1;
--Calculate difference between avg_sell_price and avg_bid_price
SELECT *, (avg_sell_price-avg_bid_price) AS difference,
(avg_sell_price-avg_bid_price)/avg_sell_price*100 AS difference_percent
FROM avg_sell_price LEFT JOIN avg_bid_price USING (model)
For example, we want to compare the percentage difference between the average car price set by the seller (selling_price) and the average price proposed by potential buyers (bid_price) based on the car model for the last 6 months, from July, 5 2023 to January, 5 2024.
*Difference is the difference between the average car price set by the seller (avg_sell_price) and the average price proposed by potential buyers (avg_bid_price). A negative difference means that the average price proposed by potential buyers > the average car price set by sellers and vice versa
**Difference_percent is the percentage of the difference that has been calculated or the result of dividing the contents of the difference column by the contents of the avg_sell_price column multiplied by 100%
Image above shows that there are several product models with negative differences, namely Suzuki Ertiga, Toyota Agya and Daihatsu Ayla because the average price proposed by prospective buyers (avg_bid_price) > the average car price set by sellers (avg_sell_price).
6.5 Creating a window function to find out the average bid price for each brand and model car in the last 6 months
--Create temporary table contains bid price for each car model
Buat temporary table bid price untuk setiap model mobil
CREATE TEMPORARY TABLE bidprice_bymodel AS
WITH rawdata AS (SELECT * FROM advertisement as a LEFT JOIN product as B USING (product_id))
SELECT brand, model, bid_price, y.date_created FROM bid as y LEFT JOIN rawdata as x USING (adv_id)
--Create a temporary table to calculate the average bid price for the last 6 months
CREATE TEMPORARY TABLE bid_price_6m AS
SELECT brand, model, AVG(bid_price) AS m_mobil_6 FROM bidprice_bymodel
WHERE date_created BETWEEN '2023-07-06' AND '2024-01-06'
GROUP BY 1,2
--Create a temporary table to calculate the average bid price for the last 5 months
CREATE TEMPORARY TABLE bid_price_5m AS
SELECT brand, model, AVG(bid_price) AS m_mobil_5 FROM bidprice_bymodel
WHERE date_created BETWEEN '2023-08-06' AND '2024-01-06'
GROUP BY 1,2
--Create a temporary table to calculate the average bid price for the last 4 months
CREATE TEMPORARY TABLE bid_price_4m AS
SELECT brand, model, AVG(bid_price) AS m_mobil_4 FROM bidprice_bymodel
WHERE date_created BETWEEN '2023-09-06' AND '2024-01-06'
GROUP BY 1,2
--Create a temporary table to calculate the average bid price for the last 3 months
CREATE TEMPORARY TABLE bid_price_3m AS
SELECT brand, model, AVG(bid_price) AS m_mobil_3 FROM bidprice_bymodel
WHERE date_created BETWEEN '2023-10-06' AND '2024-01-06'
GROUP BY 1,2
--Create a temporary table to calculate the average bid price for the last 2 months
CREATE TEMPORARY TABLE bid_price_2m AS
SELECT brand, model, AVG(bid_price) AS m_mobil_2 FROM bidprice_bymodel
WHERE date_created BETWEEN '2023-11-06' AND '2024-01-06'
GROUP BY 1,2
--Create a temporary table to calculate the average bid price for last month
CREATE TEMPORARY TABLE bid_price_1m AS
SELECT brand, model, AVG(bid_price) AS m_mobil_1 FROM bidprice_bymodel
WHERE date_created BETWEEN '2023-12-06' AND '2024-01-06'
GROUP BY 1,2
--Merges all temporary tables with CTE
WITH raw1 AS (SELECT * FROM bid_price_6m LEFT JOIN bid_price_5m USING (brand,model)),
raw2 AS (SELECT * FROM raw1 LEFT JOIN bid_price_4m USING (brand,model)),
raw3 AS (SELECT * FROM raw2 LEFT JOIN bid_price_3m USING (brand,model)),
raw4 AS (SELECT * FROM raw3 LEFT JOIN bid_price_2m USING (brand,model))
SELECT * FROM raw4 LEFT JOIN bid_price_1m USING (brand,model)
From the image above, we can conclude that average car bid price has been on an upward trend from month 1 to month 5. This could be due to a number of factors, such as increased demand for cars, rising fuel prices, and inflation. The most significant increase in average bid price occurred in month 5.
There are some cars that experienced a decrease in average bid price in month 5. This could be due to a number of factors, such as a decline in the popularity of the model, the introduction of new models, and the condition of the car not being in good condition.
Honda has the highest average bid price. This suggests that Honda cars are still favorite for many buyers in Indonesia. Honda CR-V has the highest average bid price for all models. This suggests that the Honda CR-V is still the most popular SUV in Indonesia.
Toyota has the second highest average bid price. This suggests that Toyota cars are also still a popular choice for many buyers in Indonesia. Toyota Yaris has the highest average bid price of all Toyota models. This suggests that Toyota Yaris is still the most popular hatchback in Indonesia.
Suzuki has the third highest average bid price. This suggests that Suzuki cars are also still an attractive choice for many buyers in Indonesia. Suzuki Ertiga has the highest average bid price of all Suzuki models. This suggests that the Suzuki Ertiga is still the most popular MPV in Indonesia.
REFERENCES
Bagui, S. S., Earp, R. (2022). Database Design Using Entity-Relationship Diagrams. Britania Raya: CRC Press.
Barrows, A., Levine Young, M., Stockman, J. C. (2010). Access 2010 All-in-One For Dummies. Jerman: Wiley.
Powell, G. (2006). Beginning Database Design. Britania Raya: Wiley.
analystanswers.com/dummy-data-definition-example-how-to-generate-it/
link-assistant.com/seo-wiki/transactional-query/
muhammadniko.web.id/pengertian-primary-key-beserta-fungsi-dan-contohnya/
