Business Data Management and Analytics

This document provides study material, tutorials, homework, and case studies on Business Data Management and Analytics. It covers topics like data modeling, translating ERD to relational, business case models, data definition, and more.

Running head: BUSINESS DATA MANAGEMENT AND ANALYTICS
BUSINESS DATA MANAGEMENT AND ANALYTICS
Name of the Student
Name of the University
Author Note

1
BUSINESS DATA MANAGEMENT AND ANALYTICS
Table of Contents
Tutorial 6 - Data Modelling.............................................................................................................2
Task 1: translating ERD to relational..........................................................................................2
Task 2 – Translating Relational to ERD......................................................................................2
Task 3 – Business Case Model 1.................................................................................................3
Homework 1 – Translating ERD to Relational............................................................................4
Homework 2 – translating Relational to ERD.............................................................................4
Tutorial 7 – Data Modelling – Anomalies.......................................................................................4
Task 2 – Business Case Model 2.................................................................................................5
Homework – Business Case Model 3..........................................................................................6
Tutorial 8 - Data Modelling Exercises.............................................................................................7
Task 2 – Case Study – Zero Normal Form..................................................................................7
Homework – Business Case Model 4..........................................................................................9
Tutorial 9 – Data Definition..........................................................................................................10
Task 2 – Business Case Model 5...............................................................................................10
Task 3 – Database Creation.......................................................................................................11
Business Modelling Case Study............................................................................................11
ERD.......................................................................................................................................12
Relational model....................................................................................................................12
SQL Create statement............................................................................................................14

2
BUSINESS DATA MANAGEMENT AND ANALYTICS
SQL Insert Statements...........................................................................................................15
Query 1..................................................................................................................................15
Query 2..................................................................................................................................16
Query 3..................................................................................................................................16
Task 4 – Functions.....................................................................................................................16
Homework – Business Case Model 6........................................................................................17

3
BUSINESS DATA MANAGEMENT AND ANALYTICS
Tutorial 6 - Data Modelling
Task 1: translating ERD to relational
Procedure Treatment Physician Patient Bed
Name Date discharged Doctor No name Shared
Description Date admitted Name address Floor
Survival rate Treatment no Qualifications DOB Bed No
Side effects Patient no
Procedure No
Scheduled Fee
Task 2 – Translating Relational to ERD

4
BUSINESS DATA MANAGEMENT AND ANALYTICS
Task 3 – Business Case Model 1
Business Rules and Assumptions
The major business rules and assumptions for the above ER Diagram are listed below:
 The information of the sellers is required to be stored in the system and identified by their
SerialNumber.
 The information of the buyer is required to be stored in the database and the identification
of the uniqueness of the buyers have been done with the help of the names of the buyers.
 The invoice details stores the purchase option in the database.

5
BUSINESS DATA MANAGEMENT AND ANALYTICS
Homework 1 – Translating ERD to Relational
Song Recording Artist Category Album
Song_no Recording_id Artist_no Category_no Album_no
Name Duration Name name label
Writer sequence Type (B or I)
Category_no Song_no Gender
Album_no Date_of_Birth
Artist_no
Homework 2 – translating Relational to ERD

6
BUSINESS DATA MANAGEMENT AND ANALYTICS
Tutorial 7 – Data Modelling – Anomalies
Task 2 – Business Case Model 2
Relational Model
Appliance Job Customer Technician
Serial Job_no Customer_no Technician_ID
Band Call_fee Name Rate

7
BUSINESS DATA MANAGEMENT AND ANALYTICS
Warranty Customer_no Phone_no
Model Start_time Address
End_time
Technician_id
Sundries
Total_parts
Serial
Date
Total_fare
Homework – Business Case Model 3
ER Diagram

8
BUSINESS DATA MANAGEMENT AND ANALYTICS
Relational Model
Member Booking Sessions
Member_ID BookingID Session_ID
Name Member_ID (fk) Session_name
Address Session_ID (fk)
No_of_Players
No_Of_Carts
Score

9
BUSINESS DATA MANAGEMENT AND ANALYTICS
Tutorial 8 - Data Modelling Exercises
Task 2 – Case Study – Zero Normal Form
Order Table
OrderID DateOrdered dateFulfilled
Payment1Amoun
t
Payment1Typ
e
Payment1Descriptio
n
Payment2Amoun
t
Payment2Typ
e
Payment2Descriptio
n
TaxFederal TaxState TaxLocal SubTotalTax
ShipToName ShipToStreet ShipToCountry ShipToPostcod
e
BillToName BillToStreet BillToCountry BillToPostcode
ShipToPhone BillToPhone
ItemName1 ItemNumber1 ItemQuantity1 ItemPrice1 ItemCategory1
ItemName2 ItemNumber2 ItemQuantity2 ItemPrice2 ItemCategory2
ItemName3 ItemNumber3 ItemQuantity3 ItemPrice3 ItemCategory3
ItemName4 ItemNumber4 ItemQuantity4 ItemPrice4 ItemCategory4
ItemName5 ItemNumber5 ItemQuantity5 ItemPrice5 ItemCategory5
ItemName6 ItemNumber6 ItemQuantity6 ItemPrice6 ItemCategory6
ItemName7 ItemNumber7 ItemQuantity7 ItemPrice7 ItemCategory7
ItemName8 ItemNumber8 ItemQuantity8 ItemPrice8 ItemCategory8

10
BUSINESS DATA MANAGEMENT AND ANALYTICS
ItemName9 ItemNumber9 ItemQuantity9 ItemPrice9 ItemCategory9
ItemName10 ItemNumber1
0
ItemQuantity10 ItemPrice10 ItemCategory1
0
Total Price
Third Normal Form
Order Billing Items Payment Shipping OrderLine
OrderID BillingID ItemNumbe
r
PaymentID ShippingID OrderLineI
D
DateOrdere
d
OrderID ItemName OrderID OrderID OrderID
dateFulfille
d
BillToName ItemCatego
ry
PaymentType ShipToName ItemID
BillToStreet ItemPrice PaymentAmout ShipToStreet ItemQuantit
y
BillToCountr
y
PaymentDescripti
on
ShipToCountr
y
BillToPostco
de
TaxFederal ShipToPostco
de
BillToPhone TaxState ShipToPhone
TaxLocal
SubTotalTax

11
BUSINESS DATA MANAGEMENT AND ANALYTICS
Total Price
Homework – Business Case Model 4
ER Diagram
Relational Model
Customer Product Order OrderLine
CustomerID ProductID OrderID OrderLineID
CustomerName Type CustomerID OrderID
CustomerDeatils Wood OrderDate ProductID
Price TotalPrice Qty
Tutorial 9 – Data Definition
Task 2 – Business Case Model 5
ER Diagram

12
BUSINESS DATA MANAGEMENT AND ANALYTICS
Relational model
Customers Staff Order Menu
CustomerID StaffID OrderID MenuID
Name StaffName MenuID Name
PhoneNumber Centre StaffID Details
DeliveryAddress CustomerID Type
Date Price
Time
Business Rules and Assumptions

13
BUSINESS DATA MANAGEMENT AND ANALYTICS
The major rules and assumptions of the design of the ER Diagram are:
 The customer details would be recorded in the customer tables in the database.
 The details of the staffs would be stored in the staff table identified by their StaffID.
 The order details would be stored in the order table which also includes the details of the
Staffs, Menu and Customers as well.
 The menu table would be storing the details of the menu in the system.
Task 3 – Database Creation
Business Modelling Case Study
The Business case study selected for this task is Task 3 – Business Case Model 1 from
tutorial 6.

14
BUSINESS DATA MANAGEMENT AND ANALYTICS
ERD
Relational model
Seller
Attributes Data Type Size Constraints
SerialNumber Int Primary key and
unique
Colour Varchar2 50
Make Varchar2 50

15
BUSINESS DATA MANAGEMENT AND ANALYTICS
Model Varchar2 50
Year Int
Buyer
Attributes Data Type Size Constraints
Name Varchar2 50 Primary key and
unique
Telephone Varchar2 50
Street Varchar2 50
Postcode Varchar2 50
Suburb Varchar2 50
State Varchar2 50
Invoice
Attributes Data Type Size Constraints
InvoiceNo Int Primary key and
unique
SerialNumber Int Foreign key and
unique
Name Varchar2 50 Foreign key and
unique
Date DATE

16
BUSINESS DATA MANAGEMENT AND ANALYTICS
TotalPrice Int
SQL Create statement
CREATE TABLE `buyer` (
`Name` varchar(50) NOT NULL,
`Telephone` varchar(50) NOT NULL,
`Street` varchar(50) NOT NULL,
`PostCode` varchar(50) NOT NULL,
`Suburb` varchar(50) NOT NULL,
`State` varchar(50) NOT NULL
);
CREATE TABLE `invoice` (
`InvoiceNo` int(11) NOT NULL,
`SerialNumber` int(11) NOT NULL,
`Name` varchar(30) NOT NULL,
`Date` date NOT NULL,
`TotalPrice` int(11) NOT NULL
);
CREATE TABLE `seller` (

17
BUSINESS DATA MANAGEMENT AND ANALYTICS
`SerialNumber` int(11) NOT NULL,
`Colour` varchar(50) NOT NULL,
`Make` varchar(50) NOT NULL,
`Model` varchar(50) NOT NULL,
`Year` int(11) NOT NULL
);
SQL Insert Statements
INSERT INTO `buyer` (`Name`, `Telephone`, `Street`, `PostCode`, `Suburb`, `State`) VALUES
('Andy', '033-2019430', 'Rowdon Street', '146963', 'Western Suburb', 'VIC'), ('Randy', '789-
2566783', 'Everton Road', '146990', 'Eastern Suburb', 'SA'), ('Sandy', '789-2566091', 'Queens
Street', '146001', 'Western Suburb', 'VIC');
INSERT INTO `seller` (`SerialNumber`, `Colour`, `Make`, `Model`, `Year`) VALUES ('1',
'Red', 'BMW', 'GR210', '2006'), ('2', 'Grey', 'Audi', 'R8', '2011'), ('3', 'Black', 'Audi', 'A4', '2008');
INSERT INTO `invoice` (`InvoiceNo`, `SerialNumber`, `Name`, `Date`, `TotalPrice`) VALUES
('1', '3', 'Andy', '2019-09-01', '34'), ('2', '1', 'Sandy', '2019-09-02', '56'), ('3', '2', 'Andy', '2019-09-
03', '31'), ('4', '1', 'Randy', '2019-09-04', '33');
Query 1
Description of the querying being created
The query provides the total spending of each buyer
SQL query
Select buyer.Name, SUM(invoice.TotalPrice)

18
BUSINESS DATA MANAGEMENT AND ANALYTICS
FROM invoice
INNER JOIN buyer ON invoice.Name = buyer.Name
GROUP BY buyer.Name;
Query 2
Description of the querying being created
The query provides the number of time each of the car has been booked
SQL query
SELECT seller.SerialNumber,seller.Make,seller.Model, COUNT(invoice.InvoiceNo)
From invoice
INNER JOIN seller ON seller.SerialNumber = invoice.SerialNumber
GROUP BY seller.SerialNumber, seller.Make, seller.Model;
Query 3
Description of the querying being created
Details of all the booking made is provided
SQL query
SELECT * FROM `invoice`,seller,buyer
WHERE seller.SerialNumber = invoice.SerialNumber and invoice.Name = buyer.Name;
Task 4 – Functions
Create View CallsCost As

19
BUSINESS DATA MANAGEMENT AND ANALYTICS
Select Mobile.MobileID, Plan.PlanName, Calls.CallDate, Calls.CallTime, Calls.CallDuration,
CallCost (Call.CallDate, Call.CallTime, Call.CallDuration, plan.PeakFee, plan.offPeakFess,
plan.weekendFee)
From Mobile
Inner join plan on Mobile.PlanName = Plan.PlanName
Inner Join Calls on Calls.MobileID = Mobile.MobileID;
Homework – Business Case Model 6
ER Diagram

20
BUSINESS DATA MANAGEMENT AND ANALYTICS
Relational Model
Dress Booking Customer DressBooking Alterations
DressID BookingID CustomerID DressBookingID AlterID
Colour CustomerID CustomerName BookingID DressBookingID
DressSize NoOfHours CustomerAddres
s
DressID Length
Brand BookingDate Measurements Shoulder
Chest
Waist
Business Rules and Assumptions
The Business rules and assumptions used for the development of the diagram is:
The details of the dresses are to be stored in the system.
The details of the customers and their measurements have been stored in the customer entity.
The details of the bookings and alteration have also been stored in the appropriate tables.
The dress bookings have been used to resolved the many to many relationships in between the
dresses and the bookings.