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

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 SupplierName table- 7 2. 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 SupplierName table- 7 2.

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.

2. SQL Commands (Data definition language) creates database tables by using MySQL
relational database.
Data definition language: It simply deals with the overall descriptions of data schema and
creates or modifies the structure according to the requirement (de Sousa and Cura, 2018). There
is predefined command useful for create database objects.
 Create – It is mainly used to create database or its objects such as views, functions, store
procedures, index and triggers etc.
 Drop – It can be used to delete the object from the database table which created by
Commands (Mior and Salem, 2018).
 Alter – It is mainly used to alter the structure in the database system.
 Comment – It is applicable to add some comment in the data dictionary and perform
specific task in the system. Rename – It help to rename the object which already existing in the data dictionary.
SQL Command:

3. Created and populate the entire table in the database which are created at least 10 records in
each tables.
Buyer_info table:
Buy_sell_history table:

Color table:
Year table:

Car table:
City table

Country table:
Fuel table:

Model table
postcode table

Seller table:

Data Manipulation language
A data manipulation language is basically programming language that can use for adding,
deleting and inserting the data in the database system.
INSERT COMMAND

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.
A top down approach is a type of process that easily breaks down the system and also help
in the decompositions. It is an essential process that start with top to bottom process where
handle every task in proper manner (Sadasivam and Arumugam, 2018). It deals with the sub
system in terms of reverse engineering. Top down approach is a coherent with less redundancy
and fewer to do things. It establishes a connection from top level and after bottom level.
Strength-
 It is based on the high level design and development.
 It supports the strong management system. Minimize the complexity in the system (Narang, 2018).
Weakness-
 Limited creativity Shows the challenges
Bottom up approach-
Bottoms up approach mainly start with the simplified version of task that gets it working.
It starts with the bottom level to collect all the information which required increasing the system
and also maintaining the services in proper manner. It is based on the incoming data from the
environment. It can be implemented the decision in quickly manner. It has breaks down the
structure in step by step (Narang, 2018). It is mainly deals with the specific information and
move to the generals. It identifying the data elements and then grouping them together in the
form of data set.
Strength-
 High visibility
 It easily demonstrates work in quickly manner.
 It has highly capable
 High deployment
Weakness-
 Lack of cohesion.
 Lowest level of integration process.

 It is very tough to implement in the system
In present DBMS Design, it is used the top down approach that starts with the top level to
follows the different task in proper manner. It helps to create a new database because the objects
at logical level. It can apply object to the physical database design. In this way, designer will
assess one of the interface that database has checking table, views and their relationship in proper
ways.
SECTION 2- DATA WAREHOUSING
TASK 2
Designing Star Schema
Dimensional modelling can be defined as database structure that is normally used to
optimize data warehousing tools and online quarries. It normally consists of facts and
dimensional tables where fact tables are the numeric values and dimensional table is the entity
points of getting facts. It was developed by Kimball and is a part of Business development life
cycle. It is normally used to identify key business processes within a business (Valladares and
et.al., 2017). It is also used in designing of data warehouses by using different concepts, methods
and techniques. Kimball has tried to extend the portfolio of best practices. Kimball's dimensional
model consist of four key steps decisions that are made during designing of dimensional model
(Ristoski, Bizer and Paulheim, 2015). It makes construction of schemas much easier and helps in
designing detailed activities. It also helps in defining tables, columns etc. with proper business
rules defined.
First step is to select the business process. In this, first of all the main business process is
defined and then it is segregated in such a way that each business process is modelled separately.
This is done after complete construction of Entity relationship diagram. Next step is to declare
the grains. It is one of the main crucial step in a dimensional design. It helps in establishing exact
data that a single fact table row represents. Grain must be declared initially only before declaring
or choosing dimension or fact table as all the fact and dimensions must be consist with the grain.
Next step is to identify the dimensions. In this all the many to many relationships in ER diagram
are selected where all the business events are gathered and analysed in the fact table. It helps in
answering all the context related to business process events with the help of identified
dimensions. All the dimensions should be associated with a given fact row. Lastly facts are
identified (Yessad and Labiod, 2016). In this, facts are identified with the help of business

processes and events and it should always be numeric. In this all the flat tables are denormalized
into flat tables with single key which connects fact tables.
With the help of this Kimball's four step dimensional model a star schema has been
designed of the Auto Sale's Scenario. This schema will help in analysing all the good and bad
sales of all the cities. After following all the four steps of Kimball Star schema of Auto sales is
designed which consist of four dimensional tables and one fact table. Dimensional tables of this
star schema are: car, seller, buyer, supplier dimensional tables whereas fact table in this consist
of all the main numeric keys that connect dimensional tables to fact table together. Above Auto
sales scenario star schema is shown below:
Illustration 1: Star Schema

SECTION 3- DATA ANALYTICS
TASK 3
Data analytics is used to examine and analyse data sets which are further used to draw
conclusions about any kind of informations that is present in the dataset. These kinds of tools
help in analysing required information, arranging information in a proper manner and visualizing
it. Here, data analytics tool that is used for analysis is Microsoft Excel (Winston, 2016Turner and
et.al., 2015). In Excel all the required information is analysed and represented in the form of
tables, graphs, charts etc. Here it is used as a tool because it is quite efficient to be used for
analysing, all kinds of information effectively.
Car manufacturer that contains most quantity of car models
General motors is the car manufacturing company which consist of most quantity of car
models which is 84. Second highest quantity of car models manufactured is by Volkswagen
groups which is 33.
General motors 84
Volkswagen groups 33
Above graph clearly shows the highest quantity of car model manufacturer.
0
10
20
30
40
50
60
70
80
90
General motors
Volkswagen groups
Illustration 2: Car manufacturer with highest quantity of car models

Steps:
 First of all, it can be started the general motors to contain the specific 84 model of
particular car in different age group.
 It is collected the data set that provided to save within the system, as per requirement to
use the formula that apply on the table to generate proper result and outcome.
 At last, it should obtain the chart or graph that represent the proper data by using Excel.

Average fuel economy for the city and highway driving
City driving average fuel economy is 20.1 whereas Highway average fuel economy is 28.2.
0
5
10
15
20
25
30
City FE
Highway FE
Illustration 3: City and highway driving fuel economy
Above graph clearly shows the average fuel economy of highway and city driving.
Steps
 First of all, it shows the average fuel economy of highway and city driving.
 It is collected the data set that provided to save within the system, as per requirement to
use the formula that apply on the table to generate proper result and outcome.
 At last, it should obtain the chart or graph that represent the proper data by using Excel.

Good or Bad average fuel economy cars
Manual and automatic cars both have different good fuel economy whose difference can
be seen in the below graph where automatic cars good fuel economy is 23 whereas manual cars
good fuel economy is 25.
Automatic cars 23
Manual Cars 25
Steps
 First of all, it Manual and automatic cars both have different good fuel economy whose
difference can be seen in the below graph where automatic cars good fuel economy is 23
whereas manual cars good fuel economy is 25.
 It is collected the data set that provided to save within the system, as per requirement to
use the formula that apply on the table to generate proper result and outcome.
 At last, it should obtain the chart or graph that represent the proper data by using Excel.

Car manufacturer which have 4 wheel drive and 2 wheel drive with engine power more than 3.5
2 wheel drive car manufacturers with engine power more than 3.5 are: Mercedes-Benz,
Austin Martin, Jaguar, Land Rover, Porsche, General Motors, BMW, Hyundai, Nissan,
Volkswagen, Toyota, kia and Chrysler Group LLC.
4 wheel drive car manufacturers with engine power more than 3.5 are: Chrysler Group LLC,
Porsche and Mercedes-Benz.
2-d 12
4-d 3
22
22.5
23
23.5
24
24.5
25
25.5
Automatic cars
Manual Cars

Steps
 First of all, it 4 wheel drive car manufacturers with engine power more than 3.5.
 It is collected the data set that provided to save within the system, as per requirement to
use the formula that apply on the table to generate proper result and outcome. At last, it should obtain the chart or graph that represent the proper data by using Excel.
0
2
4
6
8
10
12
14
2-d
4-d

Critical reflection
This assignment has helped me to gather a clarity of Entity relationship diagram. With
the help of this diagram I have also learnt crow's foot notations and how it helps in drawing ER
diagram. This understanding and knowledge which I have gathered using this crow foot notation
help me in drawing the ER diagram with more clarity as it helped me in understanding the
relations between entities and the way it should be represented. This understanding helped me in
defining attributes, entities in a better way (Friese, 2019). With the help of this ER diagram I was
able to define tables, columns for creation of the database. I have also learnt different SQL
commands which helped me in creation and modification of database tables. Database tables
were created with the help of proper data definition languages and values or data in those tables
were filled with the help of data modelling language. These SQL commands also helps me in
modifying and updating database tables. After creation of database tables and ER diagram Star
Schema of Auto sales Scenario that was explained above was made.
After this completion of diagram, tables and schema data analytics part came into picture.
This helped me to understand the meaning of data analytics and how it can be done in different
tools. For this data analytics part, I have learned Microsoft Excel which helped me to extract all
the required information and was also able to develop a graph for the same. All the required
questions were easily answered with the help of this data analytics tool (Ristoski, Bizer and
Paulheim, 2015). In other words it can be said that I gathered a better understanding of all those
topics in a better way.
Strength of the data analytics tool that has been used
Microsoft Excel is one of the oldest data analytics tool that is used bot analysing the data
that has been gathered in databases. One of the major strength of Excel is that is extremely easy
to use and easy to store any kind of data. Another biggest strength of Microsoft Excel is that in
this user can open as many spreadsheets as they want and as many columns and rows as they
want. There is no limitation of number of spreadsheets and number of column and rows. In
Microsoft Excel Easy and effective comparisons can be done with large amount of data. It is well
structured and data in it can be stored in whichever way user likes (Ristoski, Bizer and Paulheim,
2015). Microsoft Excel has in built formulas which can be used for analytics part or for any kind

of calculations which eventually also helps in summarizing as well as organizing any kind of
data.
It consists of various kinds of inbuilt tools that can be used to sort, filter, search and
quickly narrow down any kind of data which can be used for analysing. Microsoft Excel also
consist of some inbuilt templates which can be used according to the business ideas, uses and the
type of work which is to be carried out and analysed in it. It also helps in keeping complete data
together. All the files that is saved in excel can be opened on any kind of device such as mobiles,
laptops etc. in fact can also be opened on any kind of server as well.
Weakness of data analytics tool that has been used
Although Microsoft Excel has many strength or advantages but it has many
disadvantages or weaknesses as well. Many times it happens that strengths of Excel becomes its
weakness many times. One of the most common weakness of Excel is that due to its large
storage capacity it becomes extremely difficult to analyse any kind of data as analytical part in
Excel is to be done manually only which also increases the chances of error to occur. Due to its
manual operation sometimes serious human errors can occur. Nowadays many companies uses
databases and as a result it becomes difficult to use excels for storing data which eventually
makes it difficult to analyse the required data. Sometimes it becomes extremely difficult to find
the required data using which analysis is to be done and if any human error occurs during the
insertion of data then it will eventually affect the overall analysis of the data. Sometimes doe to
its large storage of data it forces the computer systems to slow down which affects the efficiency
of the system. There is no doubt that Excel provides good variety of graphs but it also has a
limitation which is that customization of graphs is not possible in Excel. There are some standard
formats of graphs available which makes it difficult to make changes in the graphs.
Another data analytics that can be used for this analytics
Rapid miner is another data analytics tool that is commonly used for data analytics (Sharma and
Ramani, 2018). it is a data science analytics software that is used for predictive analysis, text
mining, data preparation, deep learning etc. this software can help in removing human errors in
an effective manner. Analytics done by this is efficient enough and can easily be done with the
help of its analytics tools and components that are available in it. Learning, development and
usage of this tool is quite easy and can be done effectively. Exploration of data in this can be

done easily, interactively and effectively. It is exactly like Microsoft Excel but less complex and
easy to use. It also overcomes all the limitations and weakness of Excel in an effective manner.
Rapid miner also helps in creating any kind of predictive data analysis model in just few clicks
and cab be refined according to the data modification that is done. Rapid miner is the best data
analytics tool as compared to Microsoft Excel (Sharma and Ramani, 2018). One of the main
weakness of Excel which is human errors cannot be updated automatically can be removed with
the help of this software. Rapid miner has five sub products which are: Rapid miner studio, auto
model, turbo prep, server and lastly Rapid miner Radoop.
CONCLUSION
As per discussion, it concluded that Database management is a type of method that can use
for managing the information and data in proper manner. It involves manipulate, retrieve, update,
delete and create information in the system. It provides an interface to the database for storage of
information and retrieval. It summarized the auto sale’s to manage and control the information of
car, supplier, buyer and seller etc. This report discusses about the entity relationship diagram
where there are different type of entities and attributes represented through ER Model. It
describes the structural query language to produce database tables and also create the table by
using SQL command in DBMS. It also critically analyses the strength and weakness of bottom
up or top down approach. In this data modelling of the given scenario is also done and with the
help of Kimball's data modelling steps star schema of the Auto Sales scenario has also been
done. After data modelling, data analytics of the provided excel sheet is also done where all the
questions that were asked for analysis are done using Microsoft Excel as a data analytics tool. At
last strength and weakness of Microsoft Excel is explained and also Rapid miner data analytics
tool has also been explained in brief.

REFERENCES
Books and Journals
Cheng, L.C. and Chu, H.C., 2019. An innovative consensus map-embedded collaborative
learning system for ER diagram learning: sequential analysis of students’ learning
achievements. Interactive Learning Environments. 27(3). pp.410-425.
de Sousa, V.M. and Cura, L.M.D.V., 2018, November. Logical Design of Graph Databases from
an Entity-Relationship Conceptual Model. In Proceedings of the 20th International
Conference on Information Integration and Web-based Applications & Services (pp. 183-
189). ACM.
Friese, S., 2019. Qualitative data analysis with ATLAS. ti. SAGE Publications Limited.
Mior, M.J. and Salem, K., 2018, October. Renormalization of NoSQL database schemas.
In International Conference on Conceptual Modeling (pp. 479-487). Springer, Cham.
Narang, R., 2018. Database management systems. PHI Learning Pvt. Ltd.
Ristoski, P., Bizer, C. and Paulheim, H., 2015. Mining the web of linked data with
rapidminer. Web Semantics: Science, Services and Agents on the World Wide Web, 35,
pp.142-151.
Sadasivam, U.M. and Arumugam, C., 2018, May. Teaching Database Design and Analysis in an
Effective Way on Digital Platform and Its Effect on Society. In International Conference
on Computational Science and Its Applications (pp. 481-491). Springer, Cham.
Sharma, A.K. and Ramani, P., 2018. Rigorous Data Analysis and Performance Evaluation of
Indian Classical Raga Using RapidMiner. In Soft Computing: Theories and
Applications (pp. 97-106). Springer, Singapore.
Turner, A., and et.al., 2015. Data analysis for strength and conditioning coaches: Using excel to
analyze reliability, differences, and relationships. Strength & Conditioning
Journal, 37(1), pp.76-83.
Valladares, P., and et.al., 2017, July. Dimensional data model for early alerts of malicious
activities in a CSIRT. In 2017 International Symposium on Performance Evaluation of
Computer and Telecommunication Systems (SPECTS) (pp. 1-8). IEEE.
Winston, W., 2016. Microsoft Excel data analysis and business modeling. Microsoft press.
Yessad, L. and Labiod, A., 2016, November. Comparative study of data warehouses modeling
approaches: Inmon, Kimball and Data Vault. In 2016 International Conference on
System Reliability and Science (ICSRS) (pp. 95-99). IEEE.