1 of 231

CROWN DATA ANALYSIS AND CONSULTANCY LIMITED

FINANCE SYSTEM DATABASE

By: CODA ANALYTICS

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

2 of 231

Agenda

1	DSU
2	Overview
3	Business Use case
4	Quick Data Overview
5	Database Requirements
6	Normalization
7.	Database Creation
8.	Testing

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

3 of 231

Business Use Case

What's the use case?	A storage platform that tracks the CODA Cash inflow,Cash Outflow which will be used to develop reports in Tableau.
Who has interest in this information	This platform and it’s data will be of importance to CODA management
How should the information be presented to users	An SQL database will be developed which a user can interact with
Why should this use case be of interest to users	This will enable identification and remedy of expenditure on non performing areas

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

4 of 231

Transaction Data Overview

Dimensions	Facts
Sender	Phone
category	transaction_date
sub_category	amount
receiver	qty
Department

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

5 of 231

Transaction Data Sample

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

6 of 231

PAYMENT_HISTORY Data Overview

Dimensions	Facts
Customer	payment_fees
email	fee balance
payment_method	contract_submitted_date
	phone

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

7 of 231

PAYMENT_HISTORY Data Sample

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

8 of 231

DATABASE

Transaction DATABASE DEVELOPMENT

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

9 of 231

Requirements

As a…......	I Want to…....	So I can….....
Business User	normalize finance transaction dataset into the following tables: Senders_tbl, Receiver_tbl,Department_tbl,categories_tbl,Subcategories_tbl,TransactionsFCT_tbl	Optimize the database to eliminate data redundancy
Business user	I want to create a data dictionary with the following columns:Database,Tables,Column Name,Example,Data types,Character length or size,Null or Not Null,Description(Primary or foreign key)	So that we can store data and will also guide during creation of tables in sql
	Detailed Requirements

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

10 of 231

Data Normalization

Data Normalization is the process of organizing data to achieve the following goals

Eliminate data Redundancy

Efficient use of disk space

To easily update data in the database

Ensure a scalable database

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

11 of 231

Normalization Process

1	Separate facts from dimensions in the dataset facts – These are quantitative values that can be subjected to mathematical operations Dimensions – These are qualitative values and they are just identifiers
2	There should be no repetition in the table If there is repetition, you must move the column(s) to a new table without repetition
3	Values in a column must be singular/atomic if the values are not singular, then you must create another column within the same table to separate those values

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

12 of 231

Normalization Process

4	Introduce unique identifiers (Primary Key) for each row if a table does not have a primary key column, give it one Primary Keys should never contain sensitive information e.g. bank account number All the columns must not depend on each other but the primary key of the table
5	Introduce foreign keys when you have more than one table Foreign Key is a column that is a primary key in another table and is used to link the two tables We use the hierarchical (child, mother, …) concept in applying the foreign keys i.e. the primary key in the child table becomes the foreign key in the mother table

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

13 of 231

Sample Normalized Table

Normalization

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

14 of 231

Sample Data Dictionary

Data Dictionary

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

15 of 231

Transaction DATABASE

DEMONSTRATION IN SQL SERVER

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

16 of 231

Transaction Database Testing

Test Scripts

Test Description	Test Steps
To validate that the Transaction data has been organized into the following tables, Employee_tbl,Platform_tbl,Fct_tbl,	1.Open the Normalized Data for whatsapp 2. Check the available various sheets names 3. Identify that there exists dimensions and facts sheets 4. Check that there exists Fct_Tbl, Dim_Employee_Tbl and Dim_Platform_Tbl

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

17 of 231

Transation DB Test validations

Test Validation

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

18 of 231

DATABASE

PAYMENT_HISTORY DATABASE EVELOPMENT

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

19 of 231

As a…......	I Want to…....	So I can….....
Business User	normalize finance_payment_history dataset into the following tables: fct_tbl, officelocation_tbl,	Optimize the database and eliminate data redundancy
Business user	I want to create a data dictionary with the following columns:Database,Tables,Column Name,Example,Data types,Character length or size,Null or Not Null,Description(Primary or foreign key)	use it as a reference while developing the tables in SQL server
	Detailed Requirements

Requirements

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

20 of 231

DEMONSTRATION IN SQL SERVER

PAYMENT_HISTORY DATABASE

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

21 of 231

PAYMENT_HISTORY DBA Testing

Test Scripts

Test Description	Test Steps
To validate that the meetings data has been organized into fct_tbl, Particpant_position_tbl, meeting type_tbl, officelocation_tbl, meeting topic_tbl	1.Open the Normalized Data for meetings 2. Check the available various sheets names 3. Identify that there exists dimensions and facts sheets 4. Check that there exists fct_tbl, Particpant_position_tbl, meeting type_tbl, officelocation_tbl, meeting topic_tbl

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

22 of 231

PAYMENT_HISTORY DBA Test validations

Test Validation

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

23 of 231

DATABASE

CUSTOMERUSER DATABASE DEVELOPMENT

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

24 of 231

CUSTOMERUSER Data Overview

Dimensions	Facts
firstname	id
lastname	phone
is_staff
is_client
gender

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

25 of 231

CUSTOMERUSER Data Sample

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

26 of 231

DEMONSTRATION IN SQL SERVER

CUSTOMERUSER DATABASE

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

27 of 231

CUSTOMERUSER DBA Testing

Test Scripts

Test Description	Test Steps
To validate that DAF dataset have been organized in the following tables Fct_tbl, Department_tbl,Employee_tbl,Task_tbl,Subtask_task	1. Open the DAF records normalization file 2. Identify the sheets denoted by various table names 3. On the sheets identified are Fct_tbl

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

28 of 231

CUSTOMERUSER DBA Test validations

Test Validation

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

29 of 231

RESEARCH

Category	Description	Link
First Normal form	Describes how to eliminate repetition	https://www.studytonight.com/dbms/database-normalization.php
First Normal form	Atomicity	https://www.youtube.com/watch?v=jgUeOjImOOw
2ND Normal Form	Eliminates partial dependencies in the tables	https://www.youtube.com/watch?v=9L10Q1nAfyg
Third Normal Form	getting rid of transitive dependency	https://www.youtube.com/watch?v=aAx_JoEDXQA

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

30 of 231

Key

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

31 of 231

Repetiton	Explain why repetition is allowed in some cases where you cant get rid of them since they are meaning full	https://stackoverflow.com/questions/43202875/when-does-repeating-data-get-removed-before-after-normalization

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

32 of 231

AGENDA

1	DSU
2.	Basic queries For Retrieving Data
3.	Joins
4.	Store procedure and its importance
5.	Views and its importance

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

33 of 231

Business Use Case

What's the use case?	A storage platform that tracks the CODA Cash inflow,Cash Outflow which will be used to develop reports in Tableau.
Who has interest in this information	This platform and it’s data will be of importance to CODA management
How should the information be presented to users	An SQL database will be developed which a user can interact with
Why should this use case be of interest to users	This will enable identification and remedy of expenditure on non performing areas

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

34 of 231

Basic querries

Learning objectives

Write and build queries.
Filter data given various criteria.
Sort the results of a query.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

35 of 231

Select statement

Use SELECT Command for Retrieving data from a table

SELECT [Date]

FROM TransactionFCT_tbl;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

36 of 231

If we want more information, we can just add a new column to the list of fields, right after SELECT:

SELECT [Date],Quantity,[amount(ksh)]

FROM TransactionFCT_tbl;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

37 of 231

SELECT DISTINCT

If we want only the unique values so that we can quickly see what species have been sampled we use DISTINCT

SELECT DISTINCT department_ID, [Date],Quantity,[amount]

FROM TransactionFCT_tbl;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

38 of 231

FILTERING

Databases can also filter data – selecting only the data meeting certain criteria

We need to add a WHERE clause to our query:

SELECT*

FROM TransactionFCT_tbl

WHERE Department_ID='2001';

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

39 of 231

Use of WHERE & AND clause

We can use more sophisticated conditions by combining tests with AND and OR.

AND is an operator that combines two conditions. Both conditions must be true for the row to be included in the result set

SELECT*

FROM [PaymenthistFCT_tbl]

WHERE (Department_ID='2001') AND Balance>'600');

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

40 of 231

Filter

We can use more sophisticated conditions by combining tests with AND and OR.

SELECT*

FROM [PaymenthistFCT_tbl]

WHERE (Department_ID='2001') OR ([Balance]>'600');

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

41 of 231

SORTING

We can also sort the results of our queries by using ORDER BY

The keyword ASC tells us to order it in Ascending order. We could alternately use DESC to get descending order.

SELECT *

FROM Fct_Cost_tbl

ORDER BY [Amount(Ksh)] ASC;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

42 of 231

sorting cont’d

We can also sort on several fields at once.

SELECT [Date],Quantity,[amount]

FROM TransactionFCT_tbl

WHERE (amount]>'600')

ORDER BY department_IDASC;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

43 of 231

SELECT column_name

FROM table_name

ORDER BY column_name ASC | DESC;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

44 of 231

The computer is basically doing this:

Filtering rows according to WHERE
Sorting results according to ORDER BY
Displaying requested columns or expressions.

Clauses are written in a fixed order: SELECT, FROM, WHERE, then ORDER BY. It is possible to write a query as a single line, but for readability, we recommend to put each clause on its own line.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

45 of 231

ALTERING TABLE

Once a table is created in the database, there are many occasions where one may wish to change the structure of the table. In general, the SQL syntax for ALTER TABLE is,

ALTER TABLE TransactionFCT_tbl ADD ‘USD’ MONEY

SELECT * FROM TransactionFCT_tbl

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

46 of 231

UPDATING TABLE

UPDATE TransactionFCT_tbl

SET [Date] = '6/7/2024'

WHERE department_ID= 2001

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

47 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

48 of 231

1. WHERE

Purpose: Filter records based on conditions.
When to Use: To retrieve only rows that meet specific criteria.

Example: To find transactions where the amount is greater than 1000:�sql�CopyEdit�SELECT * FROM cashoutflow_tbl WHERE amount > 1000;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

49 of 231

. BETWEEN

Purpose: Retrieve rows within a range of values (inclusive).
When to Use: When you need data that falls within a specific range.

Example: To find cash inflow payments made between two dates:�sql�CopyEdit�SELECT * FROM cashinflow_tbl

WHERE rep_date BETWEEN '2023-01-01' AND '2023-12-31';

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

50 of 231

3. AND

Purpose: Combine multiple conditions; all must be true.
When to Use: When filtering rows based on multiple conditions.

Example: To find transactions sent by a specific sender (sender_id = 1) and for a specific department (department_id = 2):�sql�CopyEdit�SELECT * FROM cashoutflow_tbl

WHERE sender_id = 1 AND department_id = 2;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

51 of 231

4. OR

Purpose: Combine multiple conditions; at least one must be true.
When to Use: When filtering rows where at least one condition is true.

Example: To find transactions where the department is 1 or 2:�sql�CopyEdit�SELECT * FROM cashoutflow_tbl

WHERE department_id = 1 OR department_id = 2;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

52 of 231

GROUP BY

Purpose: Group rows that share the same values in specified columns and apply aggregate functions.
When to Use: When performing aggregation across groups of rows.

Example: To calculate the total transaction cost for each department:�sql�CopyEdit�SELECT department_id, SUM(transaction_cost) AS total_cost

FROM cashoutflow_tbl

GROUP BY department_id;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

53 of 231

6. SUM

Purpose: Calculate the total sum of a numeric column.
When to Use: When aggregating numerical data.

Example: To calculate the total amount of all cash inflow payments:�sql�CopyEdit�SELECT SUM(amount) AS total_amount FROM cashoutflow_tbl;

To retrieve the total amount, average transaction cost, and count of transactions for each department, ordered by the total amount (highest to lowest):

sql

CopyEdit

SELECT department_id,

SUM(amount) AS total_amount,

AVG(transaction_cost) AS avg_cost,

COUNT(*) AS transaction_count

FROM cashoutflow_tbl

GROUP BY department_id

ORDER BY total_amount DESC;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

54 of 231

7. MAX

Purpose: Retrieve the maximum value in a column.
When to Use: When you need to find the highest value in a column.

Example: To find the maximum payment fees:�sql�CopyEdit�SELECT MAX(payment_fees) AS max_fees FROM cashinflow_tbl;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

55 of 231

8. COUNT

Purpose: Count the number of rows that match a condition.
When to Use: When determining the number of rows in a table or group.

Example: To count the number of transactions in a specific department:�sql�CopyEdit�SELECT COUNT(*) AS transaction_count

FROM cashoutflow_tbl

WHERE department_id = 3;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

56 of 231

9. AVG

Purpose: Calculate the average value of a numeric column.
When to Use: When finding the mean of values in a column.

Example: To find the average amount of cash outflow transactions:�sql�CopyEdit�SELECT AVG(amount) AS avg_amount FROM cashoutflow_tbl;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

57 of 231

10. ORDER BY

Purpose: Sort the result set by one or more columns (ascending by default, descending with DESC).
When to Use: When results need to be displayed in a specific order.

Example: To retrieve transactions ordered by transaction date in descending order:�sql�CopyEdit�SELECT * FROM cashoutflow_tbl

ORDER BY transaction_date DESC;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

58 of 231

Combined Example Query

To retrieve the total amount, average transaction cost, and count of transactions for each department, ordered by the total amount (highest to lowest):

sql

CopyEdit

SELECT department_id,

SUM(amount) AS total_amount,

AVG(transaction_cost) AS avg_cost,

COUNT(*) AS transaction_count

FROM cashoutflow_tbl

GROUP BY department_id

ORDER BY total_amount DESC;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

59 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

60 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

61 of 231

The GROUP BY clause is used to tell SQL what level of granularity the aggregate function should be calculated in. The level of granularity is represented by the columns in the SELECT statement that are not aggregate functions.

below is the syntax for group by;

SELECT "column_name1", "function type" ("column_name2")

FROM "table_name"

GROUP BY "column_name1";

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

62 of 231

SQL FUNCTIONS

Since we have started dealing with numbers, the next natural question to ask is if it is possible to do math on those numbers, such as summing them up or taking their average. The answer is yes! SQL has several arithematic functions, and they are:

Average, sum,maximum,minimum.count,round

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

63 of 231

FINCTIONS

AVG	Average of the column.
COUNT	Number of records/rows
MAX	Maximum of the column
MIN	Minimum of the column.
SUM	Sum of the column
ROUND	Round a number to s specified precision

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

64 of 231

SUM

select OfficelocationID, [Date],sum ([Amount(Ksh)]) as amount

from TransactionFCT_tbl

group by deparment_ID, [Date]

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

65 of 231

AVG

select avg ([Amount(Ksh)]) as amount

from TransactionFCT_tbl

COUNT

select count ([Amount(Ksh)]) as amount

from TransactionFCT_tbl

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

66 of 231

MAX

select MAX ([Amount(Ksh)]) as amount

from TransactionFCT_tbl

MIN

select MIN ([Amount(Ksh)]) as amount

from TransactionFCT_tbl

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

67 of 231

ROUND

SELECT ROUND ([Amount(Ksh)], 2) Amount FROM TransactionFCT_tbl

WHERE department_ID= 2001

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

68 of 231

Joins

Joins in SQL server are used to query (retrieve) data from 2 or more related tables. In general tables are related to each other using foreign key constraints

In SQL server, there are different types of JOINS.

1. CROSS JOIN

2. INNER JOIN

3. OUTER JOIN

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

69 of 231

General Formula for Joins

SELECT ColumnList

FROM LeftTableName

JOIN_TYPE RightTableName

ON JoinCondition

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

70 of 231

JOINS

Inner Join	Returns only the matching row from two tables
left join	Returns only the matching row from two tables and non matching rows from left table
right join	Returns only the matching row from two tables and non matching rows from right table
full join	returns all the rows from both tables including the non matching
Cross join	Returns the cartesian product from the tables involved

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

71 of 231

SELECT category,DepartmentID, Department

Department

FROM Dim_Category_tbl

JOIN Dim_Department_tbl

ON Dim_Category_tbl. CategoryID=Dim_Department_tbl.CategoryID

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

72 of 231

SELECT

Sender_ID AS [Sender],

Subcategory AS Subcategory,

Category,

Department AS Officelocation,

[Date],

[quantity],

[Amount] AS [Unit price(ksh)],

[Amount] AS [Amount(Ksh)]

FROM

dbo.TransactionFCT_tbl AS Trans

RIGHT JOIN

dbo.Subcategory_tbl AS Subcat

ON Trans.Sender_ID = Subcat.subcategory_ID

RIGHT JOIN

dbo.Categories_tbl AS Cat

ON Cat.category_ID = Subcat.category_ID

RIGHT JOIN

dbo.Department_tbl AS Dept

ON Dept.department_ID = Trans.department_ID

RIGHT JOIN

dbo.PaymenthistFCT_tbl AS PayHist

ON PayHist.department_ID = Dept.department_ID;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

73 of 231

SELECT

TransactionFCT_tbl.transaction_ID AS [Transaction ID],

TransactionFCT_tbl.Date AS [Transaction Date],

TransactionFCT_tbl.quantity AS [Quantity],

TransactionFCT_tbl.amount AS [Amount],

TransactionFCT_tbl.total AS [Total Amount],

CategoriesDim_tbl.category AS [Category],

SubcategoryDim_tbl.subcategory AS [Subcategory]

FROM

FINANCE_SYSTEM_DBA.TransactionFCT_tbl AS TransactionFCT_tbl

INNER JOIN

FINANCE_SYSTEM_DBA.CategoriesDim_tbl AS CategoriesDim_tbl

ON TransactionFCT_tbl.category_ID = CategoriesDim_tbl.category_ID

INNER JOIN

FINANCE_SYSTEM_DBA.SubcategoryDim_tbl AS SubcategoryDim_tbl

ON TransactionFCT_tbl.subcategory_ID = SubcategoryDim_tbl.subcategory_ID;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

74 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

75 of 231

Procedure

selecting the relevant columns from the data

Eliminating repetition from the data using unique tool

Introducing an ID column to populate the ID COLUMN with values.

Joining more than one table to create the foreign key relationship among them.

Separating the tables by selecting specific columns for specific tables

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

76 of 231

Preview

1	DSU
2.	Views and its importance
3.	Review of Stored procedure
4.	using database power the tableau

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

77 of 231

VIEWS

A view is nothing more than a saved SQL query. A view can also be considered as a virtual table

CREATE VIEW vw_DepartmentTransactionCost AS

SELECT COF.department_id, DEP.department_name, SUM(COF.transaction_cost) AS total_cost

FROM finance_db.fct_cashoutflow_tbl COF

JOIN finance_db.dm_department_tbl DEP ON COF.department_id = DEP.department_id

GROUP BY COF.department_id, DEP.department_name;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

78 of 231

SELECT * from vw_DepartmentTransactionCost;

DELETE vw_DepartmentTransactionCost;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

79 of 231

Example

CREATE VIEW vwPaymentDetailsWithUser AS

SELECT CIF.payment_id, CIF.payment_fees, CIF.down_payment, USR.first_name, USR.last_name, USR.user_category

FROM finance_db.fct_cashinflow_tbl CIF

JOIN finance_db.dm_user_tbl USR ON CIF.user_id = USR.user_id;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

80 of 231

Advanatages of views

Advantages of using views:

1. Views can be used to reduce the complexity of the database schema,

2. Views can be used as a mechanism to implement row and column level security.

Row Level Security:

3. Views can be used to present aggregated data and hide detailed data.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

81 of 231

Example with column level security

CREATE VIEW vw_UserTransactionInfo AS

SELECT

USR.first_name,

USR.last_name,

USR.user_category,

COF.transaction_id,

COF.receiver_name,

COF.amount,

COF.transaction_date

FROM

finance_db.dm_user_tbl USR

JOIN

finance_db.fct_cashoutflow_tbl COF

USR.user_id = COF.sender_id;

select* from vWcategorybydepartmentoperation

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

82 of 231

Example with Row level security

CREATE VIEW RecentTransactions AS

SELECT transaction_id, sender_id, receiver_name, amount

FROM finance_db.fct_cashoutflow_tbl

WHERE transaction_date > '2023-01-01';

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

83 of 231

SAMPLE TABLEAU REPORT

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

84 of 231

Views can be used to reduce the complexity of the database schema,Creating tables with specific columns to be used in making the reports in tableau

Sections from the Report:

Costs by Location: Aggregates costs by geographical location.
Costs by Department: Aggregates costs by department.
Costs by Category: Aggregates costs by different cost categories.
Monthly Costs: Aggregates costs by month.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

85 of 231

CREATE VIEW vw_FinancialDashboard AS

SELECT

L.location,

D.department_name,

C.type AS CategoryType,

MONTH(CF.transaction_date) AS MonthNumber,

DATENAME(MONTH, CF.transaction_date) AS MonthName,

CF.amount,

CF.transaction_cost

FROM

finance_db.dbo.fct_cashoutflow_tbl CF

JOIN

finance_db.dbo.dm_location_tbl L ON CF.location_id = L.location_id

JOIN

finance_db.dbo.dm_department_tbl D ON CF.department_id = D.department_id

JOIN

finance_db.dbo.dm_cashoutflow_category_tbl C ON CF.category_id = C.category_id;

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

86 of 231

STORE PROCEDURE

A stored procedure is group of T-SQL (Transact SQL) statements. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it's name

We will learn how to create, execute, change and delete stored procedures.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

87 of 231

Creating store proc

Create Procedure spName of procedure

Begin

Select column list from tblName

End

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

88 of 231

Example

CREATE PROC spcategory

BEGIN

SELECT CategoryID,category FROM Dim_Category_tbl

END

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

89 of 231

Executing SP

EXEC spName of procedure

EXECUTE spName of procedure

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

90 of 231

Modifying SP

Alter spName of procedure

Begin

Select column list from tblName

End

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

91 of 231

Deleting SP

To delete the SP,

use DROP PROC 'SPName' or DROP PROCEDURE 'SPName'

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

92 of 231

: All parameter and variable names in SQL server, need to have the @symbol

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

93 of 231

store proc with input parameter

CREATE PROC spcategorybycategoryandcategoryID

@CategoryID INT,

@category VARCHAR(100)

BEGIN

SELECT CategoryID,category FROM Dim_Category_tbl WHERE CategoryID=@CategoryID and category=@category

END

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

94 of 231

Creating store proc with multuple tables

create procedure spalltables

begin

select [Description],Subcategory,category,Officelocation, [Date] ,Quantity,[Unit price(ksh)],[Amount(Ksh)]

from Dim_Description_tbl

right join Dim_Subcategory_tbl

ON Dim_Description_tbl.DescriptionID=Dim_Subcategory_tbl .DescriptionID

right join Dim_Category_tbl

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

95 of 231

Dim_Category_tbl.SubcategoryID=Dim_Subcategory_tbl.SubcategoryID

right join Dim_Department_tbl

on Dim_Department_tbl.CategoryID=Dim_Category_tbl.CategoryID

right join Dim_OfficeLocation_tbl

on Dim_OfficeLocation_tbl.DepartmentID=Dim_Department_tbl.DepartmentID

right join Fct_Cost_tbl

on Fct_Cost_tbl.OfficelocationID=Dim_OfficeLocation_tbl.OfficelocationID

end

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

96 of 231

Executing store procedure

spcategorybycategoryandcategoryID 3001,'transport'

How to view the text of your store procedure

sp_helptext spcategorybycategoryandcategoryID

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

97 of 231

Advantages of store procedure

The following advantages of using Stored Procedures over adhoc queries (inline SQL)

1. Execution plan retention and reusability - Stored Procedures are compiled and their execution plan is cached and used again, when the same SP is executed again.

2. Reduces network traffic - You only need to send, EXECUTE SP_Name statement, over the network, instead of the entire batch of adhoc SQL code.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

98 of 231

3. Code reusability and better maintainability - A stored procedure can be reused with multiple applications. If the logic has to change, we only have one place to change,

4. Better Security - A database user can be granted access to an SP and prevent them from executing direct "select" statements against a table. This is fine grain access control which will help control what data a user has access to.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

99 of 231

GOAL OF THE PROJECT

It's a project where CODA was able to employ a team of experts to interpret finance information where they were required to develop reports,secure information and optimize performance.
Develop a CODA FINANCE REPORT in tableau that will be used to show the status and performance of the company enabling make day to day decisions of whether to expand or cut the costs. Also outline the revenue the company is generating each year.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

100 of 231

After creating CODA FINANCE REPORT we were able to note that the finance information was not secure and the existing excel format of information was not fully optimized to achieve optimal performance of the report. We developed CODA FINANCE DATABASE where we can store and retrieve the finance information. Information was now secure in the database, went ahead and used it to power our report to achieve the optimal performance.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

101 of 231

Requirement

As a CODA management i want to have CODA FINANCE DATABASE tables combined into one table with the following fields,Description from Dim_Description_tbl ,[Office Location]from Dim_officelocation_tbl , [Department] from Dim_Department_tbl,[Cost Category]from Dim_Cost category_tbl,[Cost Subcategory] from Dim_Cost Subcategory_tbl,[Quantity], [Unit Price(Ksh)],[Amount(Ksh)] [Date] from fct_Cost_tbl So that we can power the CODA FINANCE REPORT

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

102 of 231

Creating views with multiple tables

create view vWalltables

select [Description],Subcategory,category,Officelocation, [Date] ,Quantity,[Unit price(ksh)],[Amount(Ksh)]

from Dim_Description_tbl

right join Dim_Subcategory_tbl

ON Dim_Description_tbl.DescriptionID=Dim_Subcategory_tbl .DescriptionID

right join Dim_Category_tbl

ON Dim_Category_tbl.SubcategoryID=Dim_Subcategory_tbl.SubcategoryID

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

103 of 231

right join Dim_Department_tbl

on Dim_Department_tbl.CategoryID=Dim_Category_tbl.CategoryID

right join Dim_OfficeLocation_tbl

on Dim_OfficeLocation_tbl.DepartmentID=Dim_Department_tbl.DepartmentID

right join Fct_Cost_tbl

on Fct_Cost_tbl.OfficelocationID=Dim_OfficeLocation_tbl.OfficelocationID

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

104 of 231

splitting dating column to month and year

select datepart(Month,[Date]) Mnth,datename(Year,[Date]) [Year] from [dbo].[Fct_Cost_tbl]

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

105 of 231

Requirement As a

As a business user i would like to denomalize finance information from separate tables into one combined table with the following columns : [Description],[Cost Subcategory],[Cost Category],[Department],[Office Location],[Month],[Year] , [Date] ,[Quantity],[Unit Price(Ksh)],[Amount(Ksh)] so that i can connect the data to the tableau report.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

106 of 231

Now, we will start by joining all the columns that we want to retrieve in our original dataset.

We will use full join to join and retrieve all the information as our dataset is.

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

107 of 231

join

select [Description],[Cost Subcategory],[Cost Category],[Department],[Office Location], datename(Month,[Date]) [Month],datename(Year,[Date]) [Year] , [Date] ,[Quantity],[Unit Price(Ksh)],[Amount(Ksh)]

from [Dim_Description_tbl]

left join [Dim_Subcategory_tbl]

ON [Dim_Description_tbl] .[DescriptionID]=[Dim_Subcategory_tbl].[DescriptionID]

left join[Dim_Category_tbl]

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

108 of 231

join

ON [Dim_Category_tbl].[SubcategoryID]=[Dim_Subcategory_tbl].[SubcategoryID]

left join [Dim_Department_tbl]

on [Dim_Department_tbl].[CategoryID]=[Dim_Category_tbl].[CategoryID]

left join [dbo].[Dim_OfficeLocation_tbl]

on [Dim_OfficeLocation_tbl].[DepartmentID]=[Dim_Department_tbl].[DepartmentID]

left join [dbo].[Fct_Cost_tbl]

on [Dim_OfficeLocation_tbl].[OfficeLocationID]=[Fct_Cost_tbl].[OfficeLocationID] and [Dim_Description_tbl].[DescriptionID] =[Fct_Cost_tbl].[DescriptionID]

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

109 of 231

Store procedure

After joining all our data to the desired columns, we will store the information using the store procedure

What is a store procedure?

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

110 of 231

W e will use the store procedure created to connect our data to tableau since it is carrying the same information as our Coda Finance Report

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

111 of 231

Work completed so far

Normalization

Creation of tables and inserting of records

Basic queries

Function

Joins

Views and Importance of views

Prcedures and its advantages

Connecting the database to tableau

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

112 of 231

Work completed so far

Below is the Link to the Query for Creation, Insertion of Records, Joints & Store Procedure for denormalizing tables and views for retrieving records using where and order by statements.

Click Here

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

113 of 231

Creation Of Tables

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

114 of 231

Creation Of Tables Cont’d

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

115 of 231

Creation Of Tables Cont’d

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

116 of 231

Insertion

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

117 of 231

Insertion Cond’t

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

118 of 231

DENORMALIZING [CODA_FINANCEDB]

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

119 of 231

DENORMALIZING [CODA_FINANCEDB]

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

120 of 231

CREATING VIEW

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

121 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

122 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

123 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

124 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

125 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

126 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

127 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

128 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

129 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

130 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

131 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

132 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

133 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

134 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

135 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

136 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

137 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

138 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

139 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

140 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

141 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

142 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

143 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

144 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

145 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

146 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

147 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

148 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

149 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

150 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

151 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

152 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

153 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

154 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

155 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

156 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

157 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

158 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

159 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

160 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

161 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

162 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

163 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

164 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

165 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

166 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

167 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

168 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

169 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

170 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

171 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

172 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

173 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

174 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

175 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

176 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

177 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

178 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

179 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

180 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

181 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

182 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

183 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

184 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

185 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

186 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

187 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

188 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

189 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

190 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

191 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

192 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

193 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

194 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

195 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

196 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

197 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

198 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

199 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

200 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

201 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

202 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

203 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

204 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

205 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

206 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

207 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

208 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

209 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

210 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

211 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

212 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

213 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

214 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

215 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

216 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

217 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

218 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

219 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

220 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

221 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

222 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

223 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

224 of 231

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

225 of 231

DEMOSTRATION OF THIRD NORMAL FORM

(How to rule out transient dependencies)

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

226 of 231

DEMOSTRATION OF THE NORMALISATION ABOVE

Bellow is a demonstration of the normalization reflected above:

Demonstration of normalization

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

227 of 231

TABLE RELATIONSHIPS

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

228 of 231

DIAGRAM

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

229 of 231

PROTOTYPE(TABLES & DATA DICTIONARY)

NOTE:

The database contains six dimension tables and one fact table
Two of the dimension tables are also fact tables to other dimension tables as seen in the ERD diagram above.
Bellow is a detailed view of the database tables and data dictionary

Click here for

Detailed view of tables and data dictionary

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

230 of 231

REASECH

NOTE:

REASERCH ON RELATIONSHIP DIAGRAMS (Click on the links bellow to go to page for further study.
STAR SCHEMA
SNOW FLAKE
DIFFERENCES BETWEEN A STAR SCHEMA AND A SNOW FLAKE

DIFFERENCES BETWEEN A DIMENSION TABLE AND A FACT TABLE
Above is a detailed view of the research

Click here for

Detailed view of the research

This presentation uses a free template provided by FPPT.com

www.free-power-point-templates.com

231 of 231

DESIGN

Click on the icons bellow to view the database code both in text and sql formats:

TEXT FORMART

SQL FORMART

This presentation uses a free template provided by FPPT.com