Database and Analytics (Database and Analytics) for the Auto Sale's

DATABASE AND
ANALYTICS PRINCIPLE

TABLE OF CONTENTS
SECTION – 1 Database...................................................................................................................3
TASK 1............................................................................................................................................3
1. Construct a logical data model (ERD) for the Auto Sale’s by using the Lucid chart model
tools with represents Crow’s foot notation and Justify the design of Model. .......................3
In Supplier table- ...................................................................................................................7
2. SQL Commands (Data definition language) creates database tables by using MySQL
relational database..................................................................................................................8
3. Created and populate the entire table in the database which are created at least 10 records in
each tables. ..........................................................................................................................12
4. Critically analyse and discuss about the strength and weakness of top down and bottom up
approach to develop database and also justify the approach which used in the task. .........19
SECTION 2- DATA WAREHOUSING.......................................................................................20
TASK 2..........................................................................................................................................20
Designing Star Schema.........................................................................................................20
SECTION 3- DATA ANALYTICS...............................................................................................21
TASK 3..........................................................................................................................................21
Car manufacturer that contains most quantity of car models...............................................22
Average fuel economy for the city and highway driving.....................................................22
Good or Bad average fuel economy cars..............................................................................23
REFERENCES..............................................................................................................................29

INTRODUCTION
Database management is a process that can use to manipulate, retrieve, update, delete and
create information in the system. It is basically a collection of information that arrange in proper
format such as tables, columns etc. it provide an interface to the database for storage of
information and retrieval. This approach is applicable in the auto sale’s to manage and control
the information of car, supplier, buyer and seller etc. This report will discuss about the entity
relationship diagram where there are different type of entities and attributes represented through
ER Model. Furthermore, this assignment will discuss about the structural query language to
produce database tables and also create the table by using SQL command in DBMS. This report
will critically analyse the strength and weakness of bottom up or top down approach. It justifies
that which approach is applied in the database development. Lastly in this star schema of Auto
sales scenario will also be explained and data analysis of all the given excel sheet will be
explained. After this strength and weakness of the data analyses tool used will be explained and a
recommendation of another data analysis tool will also be given with the brief overview of it.
SECTION – 1 Database
TASK 1
1. Construct a logical data model (ERD) for the Auto Sale’s by using the Lucid chart model tools
with represents Crow’s foot notation and Justify the design of Model.
Entity relationship diagram is representing the relationship between the entities set that
store in database system (Cheng and Chu, 2018). An entity is a real world object or component
that has specific name in database system.

Figure 1: ER Diagram
According to figure, it can determine the different entities and their specific relationship
with another entity. Each entity has represented the attributes to establish a relationship between
the tables. Lucid chart modeling tool can use to develop the Entity relationship diagram or
Model.

Data Dictionary:
Entities Attributes
car_company  Company id (PK)
 company_name
Car  car_id (PK)
 car_name
 company id (FK)
Model  Model_id (PK)
 Model_name
year  year_id
 year
Car details  Detail_id
 engine_size
 co2 Emission
 price
 body type
 Mileage
 Model_id (FK)
 Year_id (FK)
 Fuel type_id (FK)
 Seller_id (FK)
 colour_id (FK)
Fuel type  Fuel_id
 Fuel_type
color  color_id

 color
buy_sell_history  buyer_id (FK)
 H_id
 car_id (FK)
 attributes
 purchase_date
 sold price
 sold_date
Buyer_info  Buyer_id (PK)
 B_L_Name
 B_contact
 postcode_id (FK)
 B_F_name
 B_M_name
postcode  Postcode id (PK)
 city id (FK)
 postcode
city  F5city_id
 city_name
 country_id (FK)
country  Country id (PK)
 country
Seller_info  seller_id (PK)
 S_H_id (FK)
 S_contact No
 postcode_id (FK)

 S_M_Name
 S_F_Name
Table -1
In car table
 Primary key - Car_id
In car_company
 primary- company_id
company_id is representing as a primary key in car_company table which contain unique
values in it. It contains a unique value to represent as a proper manner. It helps to create a
relationship with another table. company_id is represented as a foreign key in car table. In this
way, it has established a relationship between car and car_company tables.
This relationship shows between the car_company and car one or shows as a one to many
relationships of partial participation constraints because one car company contain information
about particular cars. In this way, it can be generating relationship between them.
In Buyer_info table
 Primary key – Buyer id
In Buyer sell history
 Foreign Key – buyer_id
Buyer id is representing as a primary key in buyer_info table which contain unique values in
it. It contains a unique value to represent as a proper manner. It helps to create a relationship with
another table. Buyer id is represented as a foreign key in buyer_sell_ history In this way, it has
established a relationship between buyer and car table.
This relationship shows between the buyer_info and buyer_sell history as a one to one
partial participation constraints because one buyer contain the information through history at
time. In this way, it can be generating relationship between them.

In Fuel type table
 Primary key – fuel_id
In car_details
 Foreign Key – fuel_id
fuel_id is representing as a primary key in fuel type table which contain unique values in it. It
contains a unique value to represent as a proper manner. It helps to create a relationship with
another table. It is also represented as a foreign key in car_detail table. In this way, it has
established a relationship between car and supplier table.
This relationship shows between the car_details and fuel type as a Many to one partial
participation constraints because Many fuel type supply by one car details. In this way, it has
been established a relationship between fuel_type and car_details.
In postcode table
 Primary key – postcode_id
In seller_info table
 Foreign Key – postcode_id
postcode_id is representing as a primary key in postcode table which contain unique values
in it. It contains a unique value to represent as a proper manner. It helps to create a relationship
with another table. Postcode_id is represented as a foreign key in seller_info table. . In this way,
it has established a relationship between car and supplier table.
This relationship shows between the postcode and seller_info tab;e of Many to one partial
participation constraints because car supply by one supplier. In this way, it has been established a
relationship between postcode and seller.
Justification of design:
It has justified that Entity relationship model is the advancement representation of
different entities and their specific attributes. In this way, it is representing the relationship in the
database tables. Primary key plays important role in DBMS that help to establish connection
between the tables in proper manner. It is dynamic ways to describe the flow of information in
different entities. To fully utilized the ER model in database engineering guarantee to produce
the high quality of design for purpose of creation, maintenance and management. ER model help
to maintain the flow of communication between entities.