Data Modeling and Analysis Project: Autosale Database

Desklib offers solved assignments and past papers to help students succeed.

Table of Contents
Introduction................................................................................................................................2
Task 1: Modelling and implementation.....................................................................................3
Task 2: Designing Star Schema.................................................................................................9
Task 3 – Data Analysis............................................................................................................10
Conclusion................................................................................................................................15
References................................................................................................................................16
List of Figures
Figure 1: ERD............................................................................................................................3
Figure 2: DDL command 1........................................................................................................4
Figure 3: DDL command 2........................................................................................................5
Figure 4: DDL command 3........................................................................................................5
Figure 5: DDL command 4........................................................................................................6
Figure 6: DDL command 5........................................................................................................6
Figure 7: Top-down v/s Bottom-up Approach...........................................................................8
Figure 8: Screenshot 1..............................................................................................................10
Figure 9: Screenshot 2..............................................................................................................11
Figure 10: Screenshot 3............................................................................................................11
Figure 11: Screenshot 4............................................................................................................12
Figure 12: Screenshot 5............................................................................................................12
Figure 13: Screenshot 6............................................................................................................13

Introduction
The below-made report contains the documentation of the three tasks provided. All the
necessary practicals are performed and the screenshots of each of the performed practicals are
pasted in this report. The task number 3 is properly performed using the Tableau software and
in the last part of Task 3, the better analytical tool is discussed.

Task 1: Modelling and implementation
Figure 1: ERD
The above created Entity Relationship Diagram is based on the database named Autosale.
The diagram is created using creately software. In the above-made diagram, all the entities
are properly defined with suitable attributes that are fulfilling all the requirements being
asked. There are 9 entities defined in the Diagram made above named as CarModels, Colour,
PostCode, City, Country, Buyer, BuyingSellingHistory, Cars, and Seller. Each of the entity is
properly defined with proper attributes mentioned (Goelman et al., 2018). Primary Keys, as
well as the Foreign Keys, are also defined wherever needed.
Primary Keys defined:
Cars table contains CarID as Primary key that uniquely identifies each of the Cars. Colour
table contains ColourID as Primary Key that uniquely identifies each of the Colours. Seller
table contains SellerID as Primary Key that uniquely identifies each of the Sellers.
CarModels table contains CarModelID as Primary Key that uniquely identifies each of the
CarModels.The BuyingSelling table contains BuyingSellingID as Primary Key that uniquely
identifies each of the BuyingSelling statuses. Buyer table contains BuyerID as Primary Key
that uniquely identifies each of the Buyers. The country table contains CountryID as Primary
Key that uniquely identifies each of the Country. City table contains CityID as Primary Key

that uniquely identifies each of the Cities. The PostCode table contains PostCodeID as
Primary Key that uniquely identifies each of the Postcodes.
Relationships are shown:
Each of the entities also contains some of the relationships mentioned among them. One to
many relationships is mentioned in entities named as Colour and CarModels where a single
can be contained by multiple Models of Car. Also, one to many is shown among Cars and
CarModels where a single car can be included in various models of car. Next is again the one
to many relationships among BuyingSellingHistory and CarModels, this also explains that a
single BuyingSellingHistory can include various models of car. The next relationship is
shown among Buyer and BuyingSellingHistory where a single Buyer can have multiple
BuyingSellingHistory. The next relationship is shown among PinCode and
BuyingSellingHistory where a single PinCode can have multiple BuyingSellingHistory. The
next relationship is shown among PostCode and Buyer where a single PostCode can be
contained by multiple Buyers. The next relationship is shown among City and PostCode
where a single City can have multiple Postcodes. The next relationship is shown among
Country and City where a single Country can have multiple Cities. The next relationship is
shown among PostCode and Seller where a single PostCodecan be contained by multiple
Sellers. The next relationship is shown among Seller and BuyingSellingHistory where a
single Seller can have multiple BuyingSellingHistory.
Foreign Keys defined:
A lot of foreign keys are also being defined in this Entity relationship diagram; foreign keys
are the keys that are actually considered as the Primary keys in other tables. As a lot of
relationships are being defined in the diagram, a number of foreign keys are made such as in
CarModels table CarID, ColourID. These Foreign keys are referenced from different tables.
In next tableCarModelsID, BuyerID, SellerID, PostCodeID are made as foreign keys.
Moving further, in Seller table, PostCodeID is referenced from the PostCode table and made
as Foreign Key. In City table, CountryID is referenced from the Country table and made as
Foreign Key. In PostCode table, CityID is referenced from City table and made as Foreign
Key. In BuyingSellingHistory table, PostCodeID is referenced from the PostCode table and
made as Foreign Key.
2. DDL Commands
All the tables mentioned in the ERD above are created using the DDL command named as
Create command. The whole database is created using the MySQL software. The created
tables are:

Figure 2: DDL command 1
Figure 3: DDL command 2
Figure 4: DDL command 3

Figure 5: DDL command 4
Figure 6: DDL command 5
3.
4. Difference between Top-down and Bottom-up
Top-Down Approach
The top-down approach is also known as design by analysis. The entities that are present can
be further split so as to achieve the design needs in order to cover the needs as well as the
requirements so as to accomplish the goals and aims that the top-down approach is needed.
Strengths:
There is some strength regarding the top-down approach that is listed as:

 This top-down approach has the strength that it consumes the least time for the
implementation and also maintains the quality of the material which is being used;
there is no jeopardizing of the quality in the material.
 Another point is the capability of the decisions as this process is quick as well as easy.
Weaknesses
As there are some strong points of this approach, there are some weak points also that are
listed as:
 The first point that is the weak point of this approach is that there are some
requirements of the high skills to use this approach.
 Another point is that participation is quite low in this approach.
Bottom-Up Approach
This bottom-up approach is also known as Design by synthesis approach. In order to build
entities, there are some requirements that are needed for this approach and this is the essential
requirement of this approach. Relationships that are defined will be discussed later when
entities will get build.
Strengths:
 The first point that states the strength of this bottom-up approach is to submit an ideal
solution which should be reliable and dependable.
 Another point is that there are fewer requirements of skills that need to implement on
this approach. It is easy to access and understandable.
Weaknesses:
Along with the strengths, there are some weaknesses as well:
 The first weak point that this approach has is that this is an expensive approach and
does not cost efficient. The cost of the implementation of the approach is high.
 Another weak point is that this approach does not provide the output of great
efficiency.
In order to perform this approach, the use of the top-down approach is used as this approach
has much strength and is suitable for this project as this approach provide great efficiency and
is an effective approach as it consumes the least time with no compromise in the quality with
the quick and easy decision making power.

Figure 7: Top-down v/s Bottom-up Approach
(Source: Smartsheet, 2019)

Task 2: Designing Star Schema

Task 3 – Data Analysis
1.
This part contains the information regarding the car manufacturers those who actually
contains the maximum amount of the car models.
Screenshot 1
The First image shows the names of the car manufacturers such as Aston Martin, BMW,
Chrysler Group LLC, and Ford Motor.
Figure 8: Screenshot 1
Screenshot 2
The Second image shows the names of the car manufacturers such as General Motors, Honda,
Hyundai and Ford Motor Company.

Figure 9: Screenshot 2
Screenshot 3
The Third image shows the names of the car manufacturers such as Jaguar Land Rover L,
Kia, Hyundai, and Mazda.
Figure 10: Screenshot 3

Screenshot 4
The Fourth image shows the names of the car manufacturers such as Mazda, Mercedes Benz,
Mitsubishi Motors Company and Nissan.
Figure 11: Screenshot 4
Screenshot 5
The Fifth image shows the names of car manufacturers such as Porsche, Subaru, Toyota, and
Volkswagen.
Figure 12: Screenshot 5