Database and Analytical Principles Report: Auto Sales Analysis

This report provides a comprehensive analysis of database and analytical principles applied to an auto sales scenario. It begins with the design of a logical model using an Entity Relationship (ER) diagram, detailing entities such as cars, sellers, and buyers, along with their attributes and relationships. The report then delves into the use of SQL commands to create database tables and populate them with sample records, covering Data Definition Language (DDL) and Data Manipulation Language (DML) commands. Furthermore, it critically evaluates the strengths and weaknesses of top-down and bottom-up approaches in database design. The report also includes the design of a star schema for data warehousing, followed by a discussion of data analytics techniques applied to car sales data, such as identifying car models with the highest quantity, analyzing fuel economy, and comparing 2-wheel and 4-wheel drive vehicles. The report concludes with recommendations and a critical reflection on the methodologies used.

Database and analytical principles

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
SESSION -1 DATABASE...............................................................................................................3
TASK 1............................................................................................................................................3
Design of logical model of Entity relationship of Auto sales database ......................................3
2. Produce a scripts by using SQL command to create set of database tables.............................9
3. By using SQL statements, populate the tables in the database that create 10 records in each
table............................................................................................................................................14
Data Manipulation Language (DML)........................................................................................21
4. Discuss about the strength and weakness of top down and bottom up approach in design. . 24
SECTION 2- DATA WAREHOUSING.......................................................................................25
TASK 2..........................................................................................................................................25
Designing star schema................................................................................................................25
SECTION 3- DATA ANALYTICS...............................................................................................27
TASK 3..........................................................................................................................................27
Car Manufacturer which contains most quantity of car models................................................27
Average fuel economy for the city and highway driving...........................................................27
Good and bad average fuel economy cars..................................................................................28
Car manufacturer which have 4-wheel drive and 2 wheel drive with engine power more than
3.5...............................................................................................................................................29
Critical reflection.......................................................................................................................31
RECOMMENDATION.................................................................................................................34
CONCLUSION..............................................................................................................................34
REFERENCES..............................................................................................................................35

INTRODUCTION
Database is a collection of element that lives for a long terms. It is a type of software that
provide an interface to the database for storage of information and retrieval. This report will
discuss about the Entity relationship diagram on the basis of their modelling tools and also
representing the specific entities, attributes and their relationship between them. Furthermore,
this assignment will discuss about the SQL command to create table by using My SQL relational
database and it should represent the different level of table. It will critical analyse the strength
and weakness of top down or bottom up approach to design, develop the database within proper
justifications. Afterwards, this assignment will describe to design a star schema of auto sale and
also make a reasonable assumption that support the design. At last, this report will describe the
data analytics tool to finding the results and outcomes.
SESSION -1 DATABASE
TASK 1
Design of logical model of Entity relationship of Auto sales database
Illustration 1: ER Diagram

Entity relationship diagram is a basically a graphical representation of information system that
depict the relationship among the entities, objects, concepts and events within system (Ghosh
and Bashar, 2018). It is based on the data modelling technique that can help to define the
enterprise process for designing a relational database. It involves ERD to provide the visual
starting point of database design that support to determine the information system requirement
through an organization.
In Above diagram, there are different type of entities represented such as car, seller and
buyer etc. Each entities has been shown their attributes in the form of tables.
For example- In car table, there are such attributes Mileage, year, body, type and engine etc.
Data Dictionary -
Entities attributes
Buyer_info  Buyer_id (PK)
 B_L_name
 B_contact
 B_F_name
 B_M_name
 Postcode_id (FK)
Buy_sell_history  H_id (PK)
 attributes
 Purchase_date
 sold_date
 sold_price
 car_id (FK)
 buyer_id (FK)
Car  Car_id (PK)
 car_name
 company_id (FK)
car_company  company_id

 company_name
 car_detail  Detail_id (PK)
 engine_size
 Co2 Emission
 price
 body_type
 Mileage
 color_id (FK)
 model_id (FK)
 seller_id (FK)
 fuel_id (FK)
 year_id (FK)
City  city_id (PK)
 city_name
 country_id (FK)
color  color_id (PK)
 color
country  country_id (PK)
 country
Fuel_type  Fuel_id
 Fuel_type
Model  Model_id
 Model_name
Postcode  postcode_id (PK)
 postcode
 city_id (FK)
Seller_info  seller_id (PK)
 S_contact

 S_M_name
 S_F_name
 postcode_id (FK)
Year  Year_id (PK)
 year
Table -1
This above table is representing all the entities that has some specific attributes and also create a
relationship between them. According to scenario, each table contain a unique key that help to
create a relationship between two or more tables.
In buyer_info table:
Buyer_id is a primary key that help to create a relationship with postcode table because
buyer store the postcode details in it. it has created a relational between buyer table and postcode
table. It is representing as a Foreign key in postcode_id . buyer_info and postcode has to create
one to one cardinality participation. It occurs the total participation between the postcode and
buyer tables.
Buyer_info table:
 Primary key - Buyer_ id
 Foreign key- postcode_id
In car table:
car_id is a primary key that help to create a relationship with car_company table because
company has stored the information of car so that it has created a relational between car and
car_company table. company_id is representing as a foreign key. Buyer and car has to create one
to one cardinality participation. It occurs the total participation between the entities.
car table:
 Primary key – car_id
 Foreign key – company_id

In seller_info table:
Seller id is a primary key that help to create a relationship with seller table and postcode
because seller store the information of postcode, they want to sell the car so that it has created a
relational between seller and postcode. On the other hand, Postcode_id is representing as a
foreign key seller_info table. Seller and buyer has to create one to one cardinality participation. It
occurs the total participation between the seller and buyer.
Seller_info table:
 Primary key – Seller_id
 Foreign key – Postcode_id
In car_details table:
detail_id is a primary key that help to create a relationship with car_detail table and
colour table because it store the information about the car colour in database system. colour_id is
representing as a foreign key in car_detail table. Car_detail and color has to create one to one
cardinality participation. It occurs the total participation between the car_detail and color table.
Car_details table:
 Primary key - detail_id
 Foreign key- color_id
In car_details table contain the information about the model, year, fuel type and other
information so that it has required to use the foreign key in car_detail table to create a
relationship between them.
Foreign key : model_id , fuel_id , year_id etc.
Each key contain the specific values in the database system in proper manner.

Justification for using ER Diagram-
Designer choose the Entity relationship design because it helps to analyse and understand
every attribute in each table. It supports to identify that primary key has performed significant
role in the database system. It is also creating a relationship between the different tables. In my
opinion, it allows for designer to show the relationship between the group of data and gives a
simple yet an effective overview of different entities in the system. It also uses ER model to
summarised that what entities are needed in the relational database system. In this way, it can be
representing the relationship among the records in the tables (Roy-Hubara, Rokach and Shoval,
2018). It can use in the both as a reference for the designer when it has setting the foreign key
and as a means of determine the many to one, many to many relationships, which can resolve in
proper manner.
ER model is produced because it records the design of database that need to part of
technical manner. In the future, it will require the maintenance of database, which necessary. At
time, ER model plays important role to easily find the entities and attributes. It also helps to the
designer to check the overall working system of relational DBMS. Entity relationship diagram is
clearly representing an appropriate flow of information which required for any type of database
development. It has to be provide the proper guidance to understand the concept of development.
It can store all information or data in the form of tables to generate a specific ouput in proper
manner.

2. Produce a scripts by using SQL command to create set of database tables.
SQL stands for structure query language which is mainly used to create, retrieve,
maintain the relational database system in proper manner. It is particularly applied in the work
with structure data where it has created relations associated with the data itself. A database
system is mainly used the SQL query language to perform different operations and functions.
Data definition language (DDL):
Data definition language is a type of standard for the commands that define the different
structure in the database. DDL statements modify, create and remove object in the tables or
indexes.
Create