Database and Analytics Principles

Database and Analytics Principles
1

Contents
INTRODUCTION...........................................................................................................................3
TASK 1 Modelling and implementation.........................................................................................3
1 Using Crow’s foot notation, design the logical data model of Auto sale’s database...............3
3. Uses SQL command or statements, populate the table within database and also creating 10
records in each DB table............................................................................................................12
4 Critically evaluate the data analytics technologies /tool, explain how BuyUS can utilise to
derive value................................................................................................................................21
TASK 2 Designing star Schema....................................................................................................23
CONCLUSION..............................................................................................................................26
REFERENCES..............................................................................................................................27
2

INTRODUCTION
Database is based on the element that can live for long terms. It is consider as a type of
software that provide the significant interface to database for purpose of storage of data or
information. The documentation will describe about the entity relationship diagram through data
modelling tool. It is mainly representing the various entities, attributes and relationship with the
help of ER model. Therefore, it is to be consider as important thing which needs to create
database table by using SQL command. MySQL is based on the relational database that should
be acquired a lot of information related product, employee and so on.
The report will describe about the weakness and strength of design approach for developing
database with full of justification. Furthermore, it will describe the star schema of auto sales
which make an assumption for designing.
TASK 1 Modelling and implementation
1 Using Crow’s foot notation, design the logical data model of Auto sale’s database.
Figure 1 Auto Sale's ER Model
3

Entity relationship diagram is consider as graphical way to represent information or data which
depict the relationship among different entities, concepts, objects and events within system. This
type of ER model is consider as data modelling technique which always helping for define entire
process through relational database. ERD mainly involves for visualizing from start point to end
point. In order to identify the system requirement of BuyUs company.
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)
Customer  customer_id (FK)
 customer_name
 customer_address
 customer_phoneNo
Employee  employee_id (PK)
 Employee_name
4

 Employee_address
 Employee salary
 Employee job title
 Employee qualification
City  city_id (PK)
 city_name
 country_id (FK)
Order  order_id (PK)
 order_date
 quantity
 product _order
Fuel_type  Fuel_id
 Fuel_type
Model  Model_id
 Model_name
Postcode  postcode_id (PK)
 postcode
 city_id (FK)
Table:1
Justification through ER model
As per designer may choose the perfect ER model which help for analysing and
identifying the attributes in every database table. Generally it always support for identifying
primary key that can performed significant role within database system. In order to create the
relationship between one or more table by using foreign key (Rashid, 2019). It allows for
developer to represent as a relationship between large number of data or information which give
the simplest way to overcome multiple entities within Auto sale’s data system. Afterwards, it can
be understand that summarise entities and their attributes.
5

Furthermore, it will be used the reference key such as foreign which establish the
relationships such as many to many, one to one, one to many and many to one. It can be
identified the accurate solution or result.
ER model has produced that record the information or data within database system. In
future, it is an essential thing to maintain the database and plays important role to find out the
attributes of particular entities (ivshitsIlyas and Roy, 2019). In order to help developer to check
the working condition of relational database system. ER model is clearly showing the flow of
information, data which require to store important detail and generate relevant output.
2. Produce the script by using SQL command to create or develop the database tables.
SQL stands for structural query language that help for creating, maintaining and
retrieving the relational database system. In particularly, it can be applied in the overall work
through structured data where it help for creating relationship, associated with data. Structured
query language is based on the computer language for storing, manipulating and retrieving the
data or information stored in relational database (Tomar, Bhati and Kaur, 2019). SQL is consider
as standard which consists of various relational management system such as oracle, MySQL and
SQL server use SQOI as their standard database design.
When developers are executing the SQL command for relational database, system
identify the best way to carry out the request. SQL engine give special preference to figure out
how it can be interpreted the task.
Data definition Language
It is based on the standardization that mainly define as commands for various structure in
database system. It became useful for modifying, creating and removing particular object within
database tables (Chai and et.al., 2019). A data manipulation language is based on the computer
programming language that mainly used for inserting, modifying and deleting data in database. It
often a sub language which can easily distinguish as being part of separate data query language.
DML is closely related to the other components.
On the other hand, Data manipulation language comprises the data change statement
which can easily modify stored data but not schema. Manipulation may persistent database
objective such as stored procedures.
Create
6