RMIT University Business Data Management and Analytics Homework

This document presents a comprehensive solution to a series of data modeling assignments, covering various aspects of database design and implementation. The assignment begins with translating Entity-Relationship Diagrams (ERDs) to relational models and vice versa, followed by modeling business cases. Several business scenarios are explored, including a hospital system, a used car dealership, and a fast-food online business, requiring the creation of ERDs, relational models, and SQL queries. The solution includes the identification of anomalies, normalization techniques, and the creation of database schemas, including SQL create statements, insert statements, and query examples. The assignments also encompass the development of views and functions within the database context. The document is a complete data modeling homework solution covering different aspects of database design, data definition, and business modeling.

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