Certificate Course in
Business Analytics & Consulting
in association with PwC India
Course : SQL
Lecture On : Database
EditEditData Modelling and
Design,
MasterMaster
Introduction to MySQL 1
texttext
stylesstyles
2
Session Agenda
Data Warehouse
OLAP, OLTP, ERD
Schemas
Constraints
Data Modelling
Database DCM Cycle
RDBMS
DDL and DML Statements
3
4
Data
●
Warehouse
A data warehouse is the central repository of data of the entire
enterprise.
● A data warehouse is conceptually similar to a warehouse.
Warehouse Data Warehouse
1. It is used by a retail company to store 1. It is used by a company to store all
all its goods. its data in databases.
2. Goods are stored such that their 2. Data is stored such that its retrieval
retrieval and management is easy. and management is easy.
● A data warehouse is characterised by the
following:
- Subject oriented: Built for a specific purpose
- Integrated: Collated from multiple sources into
one form
- Non-volatile: Data does not change over time
- Time variant: Capable of capturing information [Link]
over time
5
Flow of Data in an
Organisation
[Link]
6
1. Data is captured from multiple sources and stored in Excel files or in
databases, such as Oracle.
2. The Extract, Transform, Load (ETL) process is performed on the
captured data in the following manner:
● Extract: Extracting the information from the data sources
● Transform: Transforming the values to the required format, such as
converting rupees to dollars
● Load: Loading the data into a central repository, which is none other than a
data warehouse
3. The stored data is either connected to Online Analytical Processing
(OLAP) for further processing or used for creating reports in tools, such as
Tableau or PowerBI.
[Link]
7
Dimensional
Model
A dimension model is a database structure technique and is used to optimize the
database for the fast retrieval of data.
The dimensional model contains two entities:
1. Facts
2. Dimensions
Facts are the numerical data and dimensions are the metadata (that is, data
explaining some other data) attached to the fact variables. Both facts and
dimensions are equally important for generating actionable insights from a data
set.
[Link]
8
Entity Relationship
Diagram
● An Entity Relationship Diagram (ERD) is a representation of the tables in
a database.
● It gives us an idea of how different entities are connected.
● ERDs are extremely useful to get an overall idea of a database in
very less time. Example:
[Link]
9
Notation in an
ERD:
● Fields marked with a star are the primary keys for each table.
● A line connecting two tables indicates that a relationship exists
between them.
● A line connecting a table to itself indicates that the table
references itself.
Types of cardinalities that can exist in a relationship:
[Link]
10
There are four types of relationships that
exist in an ERD:
● One to One: One to one type of relationship exists, when a single instance
of an entity is related to only a single instance of another entity.
● One to Many: One to many type of relationship exists, when a single
instance of an entity is related to more than one instance of another entity.
● Many to One: Many to one type of relationship exists, when more than one
instance of an entity is related to only one instance of another entity.
● Many to Many: Many to many type of relationship exists, when more than
one instance of an entity is related to more than one instance of another
entity.
[Link]
11
Schem
as
A schema is an outline of the entire data model which shows how different data
sets are connected and how the different attributes of each data set are used
for the database design.
STAR SCHEMA:
A star schema is a type of schema design that has one fact table connected to
multiple dimension tables.
Some of the advantages of star schemas are as follows:
● Easy to understand
● Easy to implement
● Efficient in terms of querying because a maximum of only one join is required
[Link]
12
Star Schema
Example
[Link]
13
SNOWFLAKE
SCHEMA:
● A snowflake schema is called so because the dimension tables surrounding
the fact table can branch off into more dimension tables.
● Snowflake schemas are particularly useful when it becomes difficult to
manage the size of the dimensional tables.
● Snowflake schemas are efficient in terms of data storage.
[Link]
14
OLAP vs
OLTP
[Link]
15
Entity
Constraints
Constraints are rules used to restrict the values that can be stored in the columns
of a database. This ensures data integrity, which is nothing but the accuracy and
consistency of the data stored in the database.
Entity constraints are of the following different types:
● Unique: Used for columns that need
unique values. Eg: Employee IDs
● Null: Determines the columns that can have null values.
Eg: An employee may not need to specify their location and therefore the
location column can have a null value.
● Primary Key: Determines the column that uniquely identifies a table.
[Link]
16
Referential
●
Constraints
These are used to restrict the values that are taken by a column in one
table based on the values that exist in another table.
● According to this rule, the value that appears as a foreign key in a table is
valid only if it also appears as a primary key in the table to which it refers.
● A given table has only one primary key but it can have multiple foreign
keys. Before you assign a column as a foreign key, you need to ensure
that the primary key column of the table that it refers to is present and it
does not have null or duplicate values.
Eg:
[Link]
17
Semantic
Constraints
Semantic constraints impose additional restrictions on the values in a column.
Using a semantic constraint ensures that we do not get incorrect data for any
row in the database.
Eg: A row with an Indian phone number not having 10 digits after the country
code would not be allowed to enter the database.
[Link]
18
Data
Modelling
● Data modelling is the first step in the analytics journey of an organisation.
Before you begin with the analysis of data, you need to ensure that it is
stored in a data warehouse in the correct structure, format and shape.
● It can be compared to making a floor plan for your house i.e., laying
foundation.
● You need to first finalise the different tables and their relationships before
you actually start creating the model. Once created, it is difficult to alter
the structure of the model, as the relationships have already been defined.
In essence, it is advised to finalise the design before implementation.
[Link]
21
Database Design-Creation-
Manipulation Cycle
[Link]
22
● Design: Create an ERD for the given business requirement.
● Development/Implementation: Create the required structure –
tables and their relationships – in a database design tool, such as
MySQL Workbench.
● Manipulation: Insert the records into the tables created.
● Revision: Test and refine the structure according to any changes in
the business requirement.
● Production: Deploy the database into a production environment and
automate the entire cycle so that the entire data gets updated on a day-to-
day basis.
● Maintenance: Update the created schema according to the changes in the
structure (if any). [Link]
23
Relational
● Schemas
The major aspect of a relational schema is relationships.
● All the tables within it are linked to each other with one or more
relationships.
● We need such a schema to optimise storage space or querying or both.
● It helps us understand how to combine data from multiple tables and get
the required output.
Advantages Disadvantages
1. Easy to structure data 1. Rigid, restricts flexibility
2. Efficient querying as optimisations, 2. Not horizontally scalable
such as indexing are possible
3. Easy to navigate and explore data 3. Minimal/No support on semi-structured
data, such as JSON objects and XML data
types
4. Easy to define relationships between data NA
points
[Link]
24
Relational vs Non-Relational
Schemas
Non-relational schemas have certain advantages over relational
schemas such as:
● No requirement for setting up relationships between multiple
tables
● Highly flexible
● Highly
ACID scalable
VS BASE
Note: In essence,
TEST: non-relational
schemas are built
for huge data-
capturing
requirements,
whereas
relational
schemas are
preferred for
capturing
transactions.
[Link]
25
SQL - Structured Query
Language
SQL stands for Structured Query Language. In some applications, it is also
referred to as DSL, or Domain Specific Language. SQL is used for managing and
manipulating data held in a relational database management system (RDBMS).
SQL helps you to:
● Create data,
● Manipulate data,
● Delete data, and
● Share data
Why should you learn SQL?
● SQL is language-agnostic, which makes it a language that can be
easily learnt and comprehended.
● It is also supported by all the modern database systems such as Oracle,
MySQL, SQL Server and so on.
[Link]
26
RDBMS - Relational Database
● Management
RDBMS helps to store your data while preserving the relationships within the
data.
● It is a Database Management System that stores data in the form of
tables, where each table is related to the other tables in the database.
● This kind of model follows a relational model and hence, it is known as a
relational database management system.
● Most popular RDBMS systems: MS SQL, MySQL, Oracle Database, IBM Db2,
PostgreSQL
● Common industrial applications of RDBMS : Banking sector, E-commerce
websites, Social networking sites
● Advantages of SQL: open-source, supports all the features of an RDBMS;
has a wide user community [Link]
27
SQL
Commands
SQL commands are mainly divided into two
subcategories:
● DDL (Data Definition Language)
● DML (Data Manipulation Language)
[Link]
28
DDL
Statements
[Link]
29
DML
Statements
[Link]
37
Any
Queries?
Thank
You!
[Link]
19
Applications of Data
Modelling
Data modelling is essential in almost every industry. A thorough analysis of well-
organised data can lead you to identify trends that you never knew existed, and
act on them.
1. Social media:
● Your user profile
● The pages/accounts that you visit
● The kind of interactions that you have with your friends
● The similarities and differences between what you like and what your friends
like
2. Streaming platforms:
● The categories/types of videos that you watch
● Whether you discontinue watching particular videos and if so, at what points
of time
[Link]
● Whether you skip ads or watch certain ads
20
3. Airline
industry:
● Ticket-booking information of travellers, including their places of arrival and
departure
● Time periods during which customers do more bookings
● Time periods during which certain routes have a higher demand
4. Mobile applications:
● Your navigation in the app: The pages that you visit, the time you spend on
those pages and your interaction with them
● Frequently updated data: Saved addresses
5. Banking sector:
● Your transaction history
● Your user profile
● Preferred products: Netbanking services, branch visits or phone banking
services
[Link]