StudyHub

INF10004

Case Study Project

This is a group project with a maximum of 3 students per Group.

This assignment contributes 20% of your total marks for this subject.

Due date/ time: Please see Canvas.

This project requires you to construct a database for a given case study and develop some queries using SQL to find answers for given problems. There may be specific business rules that you need to reflect on your database.

Submission Requirements

• All submissions must be made by a team (even if your team size is one person)

Files required:

• A single MS WORD document named INF10004_Case study.DOCX that contains: o A fully expanded and complete ERD diagram.

The diagram must be an image that has been generated in www.diagrams.net.

The diagram must follow the ERD symbol conventions used in lectures and labs.

15. The relational schema.

o Network diagram (follow the same drawing guidelines from lectures).

• A text file named INF1004_Case study_SQL.TXT that contains all of the SQL statements required to create, populate and query tables used in this assignment.

• Your script must work with Oracle iSQL Junior

• A WORD Document named INF10004_Case Study_OUTPUT.DOCX that contains the output generated by ISQL JR after all of the statements in your script file (INF1004_Case study_SQL.TXT) been executed.

Home Depot Handyman Warehouse

Business Narrative

The Home Depot Handyman Warehouse in Hawthorn is the largest home handyman store in Melbourne. The store runs training courses every Saturday. The courses cover topics such as Home Appliance, House Safety and Security, Hardware and Tools etc.

Home Depot record the course code, the name of the course, course description and other related details of the course. Courses may be run simultaneously in different parts of the premises on the same day. However, same course is never run twice on the same date. Each Training Course has an ID, a name. E.g. HAT_INT002, How to Install a Dryer Vent. Each course areas has different levels of course categories to reflect difficulty levels (Beginner, Intermediate and Advance).

Customers who are interested in attending a training course often ring the store and request a brochure. The customer is given a customer ID (if they don’t already have one). Their name and address is recorded. The date of the request is also stored. At the end of each day, one of the administrative staff, sends an email with a .pdf brochure document that outlines the training course to the customer.

Each training course is offered a number of times a year. E.g. How to Install Dryer Vent will be run on June 25 and Auguest 1 this year.

Customers must make a booking to attend a training course on one of the days offered. The date of the booking is recorded.

In an attempt to get more attendees to courses, Home Depot Handyman Warehouse offer cheap Promotional Products to customers who have booked on courses. The Promotional Products on offer change from date to date. The customers who book on a course may select any or all of the Promotional Products on offer. The Promotional Products selected by each customer is recorded. Some customers choose all the Promotional Products. Some customers don’t choose any of them.

After the customer has attended a training course offering, he/she supplies a rating for the course they attended. The rating is a number of the range 1-5. (5 is good, 1 is poor). Where a customer

does not supply a rating, a zero is recorded.

Each event has one or more teachers. All teachers have a teacher ID and name. Where more than one teacher is involved in an event, one of the teachers is considered to be the leader.

There is no need to record payments made by customers. That task is handled by another of the Home Depots’ systems.

Home Depot wanted to record all information reflected in sample information provided in Appendix.

Requirements:

1. Create a fully expanded ERD based on the above information.

2. Create a Relational Schema based on the ERD.

3. Create a Network Diagram based on your relation schema.

4. Implement your design in Oracle (only after your ERD is complete!) All tables must enforce referential integrity where necessary.

Surrogate Keys are NOT allowed to be introduced into your solution. All constraints must be named using the conventions used in INF10004 subject.

5. Populate your tables with the data provided. In some instances, you may need to provide your own data.

6. Create Queries to solve the queries listed below.

(Names & dates etc below indicate columns required. The data shown here is irrelevant and do not reflect actual data in the case)

a. List all booking made.

CourseCode	Description	Date	Customer Name
CV07	Advanced Stonework	13 Feb	Jimmy
CV07	Advanced Stonework	13 Feb	Freddy
SD05	Routers	25 Nov	Hans
SD05	Routers	25 Nov	Jimmy

b. List each promotional product selected by a customer.

CourseCode	Date	Customer Name	Product Selected
CV07	13 Feb	Jimmy	X03
CV07	13 Feb	Jimmy	X04

SD05	25 Nov	Hans	X01
SD05	25 Nov	Hans	X09

3. List the customer name and training course of any customer who requested a mail out for a training course and then booked for the same training course.

7. Create Queries to solve the queries listed below.

(Names & dates etc below indicate columns required. The data shown here is irrelevant and do not reflect actual data)

a. List the number of times each training course has been booked by customers.

CourseCode	Description	Count
SD05	Routers	17
CV07	Advanced Stonework	22

b. For each training course run on a specific date, list the Average of scores.

Teacher	CourseCode	Date	Avg
Edna	SD05	25 Nov	4.8
Hilary	CV07	13 Feb	4.25
Dave	CV07	13 Feb	3.6

3. List the number of times every promotional product has been chosen by a customer. Include products that have never been selected by a customer.

ProductCode	Total
X01	3
X02	5
X03	0
X04	10
X05	4
X07	11
X08	0 …

4. Calculate the average rating of all evaluations.

4. List the course code, description for all courses that have a better average than calculated by the question above. This query must work correctly, even when more evaluations are added in the future. (i.e. do not hard-code numeric values within the query)

Appendix: Home Depot Handyman Warehouse information derived from existing documents

Below are copies of various word-processed reports / documents that are manually prepared by staff.

Upcoming Training Courses		(L) indicates Leader
	HAT_ADV000	2 January
	Instructors: Allison Cameron (L) , James Wilson
	HTT_ADV002	22 June
	Instructors: James Wilson (L) , Allison Cameron
	HTT_INT005	13 January
	Instructors: Allison Cameron (L)
	LLT_INT005	21 September
	Instructors: James Wilson (L)
	HSS_INT000	25 May
	Instructors: Eric Foreman (L) , Amber Volakis, Chris Taub
	LLT_INT005	1 June
	Instructors: Eric Foreman (L) , Amber Volakis, Chris Taub
	HSS_BEG001	6 July
	Instructors: Lisa Cuddy (L) , Eric Foreman
	HAT_INT003	13 July
	Instructors: Amber Volakis (L) , Lisa Cuddy
	LLT_BEG001	1 June
	Instructors: Robert Chase (L), Chris Taub
	LLT_ADV005	15 June
	Instructors: Robert Chase (L), Chris Taub
	LLT_BEG002	27 January
	Instructors: Lisa Cuddy (L)

April 2: Mail Out Reminder

Please mail the HAT_ADV000 info to the following people:

Lance Sweets, 1 John St, Hawthorn 3122

Angela Montenegro, 2 Red St, Box Hill 3128 Donald Trump, 10 United St, Kew 3101

Joseph Bidden, 11 United St, Kew 3101

May 9: Mail Out Reminder

Please mail the HTT_INT005 info to the following people:

Angela Montenegro, 2 Red St, Box Hill 3128 Zack Addy, 2 Green Way, Kew 3101

Sample Training Course categories, course codes & other details

Home Appliance – Course Code: HAT

Home Appliance Training provides basic training to help you find the problem with your broken appliances, and how to repair them yourself! Use professional repair techniques used to fix all types of appliances.

HAT_BEG000 HAT_ INT001

Page 7 of 14

Prepared by Ravinda Wijesinghe Version 1

HAT_BEG001 HAT_ INT002

HAT_INT003 HAT_ADV000

Page 8 of 14

Prepared by Ravinda Wijesinghe Version 1

Home Safety & Security – Course Code: HSS

Home is the place we should always feel the most peaceful and secure. Get real world advice and tech tips from leading trainers about smart technology. Learn about everything from wireless hub solutions to touchless tech ideas.

HSS_BEG000 HSS_BEG001

HSS_INT000 HSS_ADV001

Page 9 of 14

Prepared by Ravinda Wijesinghe Version 1

Hardware & Tools – Course Code: HTT

Learn how to use a wide range of tools and improve your D.I.Y. skills.

HTT_BEG000 HTT_BEG002

HTT_ADV002 HTT_INT005

Page 10 of 14

Prepared by Ravinda Wijesinghe Version 1

Lawn & Landscaping – LLT

Give your garden the best start when you install hardscape elements like pathways and retaining walls early in the season. These manmade elements form the infrastructure to your garden, directing foot traffic and water flow, while providing focal points in the overall design. Check out our inspirational training guides that break down projects and make your garden come alive this year.

LLT_BEG002 LLT_INT005

LLT_BEG001 LLT_ADV005

Page 11 of 14

Prepared by Ravinda Wijesinghe Version 1

Course Booking details
Booking Sheet 01
Course ID: HAT_ADV000		Date: 2 Jan
Attendee ID		Name	Products Selected
C0102		Angela Montenegro
C1231		Zack Addy
C0405		Max Keenan
C2020		Camille Saroyan
Booking Sheet 02
Course ID: HTT_INT005		Date: 13 Jan
Attendee ID		Name	Products Selected
C0102		Angela Montenegro	X02, X04, X06
C2020		Camille Saroyan	X04, X06
C0149		Jack Hodgins
Booking Sheet 03
Course ID: LLT_ BEG002		Date: 27 Jan
Attendee ID		Name	Products Chosen
C0405		Max Keenan	X07, X08, X09
C0149		Jack Hodgins	X07
Booking Sheet 04
Course ID: HSS_INT000		Date: 25 May
Attendee ID		Name	Products Chosen
C0102		Angela Montenegro
C1231		Zack Addy
C0405		Max Keenan
Promotional Product List (for Training Courses)
Code	Description	Price	Code	Description Price
X01	Garden Hand Tool	$2	X06	Combination Padlock $2
X02	10-piece spanner set	$3	X07	Glue & Repair Kit	$1
X03	Hobby Hammer	$2	X08	Surprise Pack	$4
X04	Mini Pliers	$3	X09	Build-a-fence DVD	$3
X05	BBQ Lighter	$3	X10	Pocket Torch.	$2

Page 12 of 14

Prepared by Ravinda Wijesinghe Version 1

Requirements Sheet 01

Course ID: HSS_INT000 Date: 25 May

Promotional Products on Offer:

X01 X03

Requirements Sheet 02

Course ID: LLT_BEG002 Date: 27 January

Promotional Products on Offer:

X07 X08 X09

Requirements Sheet 03

Course ID: HTT_INT005 Date: 13 Jan

Promotional Products on Offer:

X02 X04 X06

Page 13 of 14

Prepared by Ravinda Wijesinghe Version 1

Teacher List

Teacher ID & Name & Phone:
T01	Robert Chase	0401 112233
T05	Lisa Cuddy	0402 223344
T12	Allison Cameron	0403 334455
T36	James Wilson	0404 445566
T41	Eric Foreman	0405 556677
T43	Amber Volakis	0406 667788

Customer List

Cust ID, Name & Address

C0102	Angela Montenegro	2 Red St, Box Hill 3128
C0149	Jack Hodgins	10 Black Rd Kew, 3103
C0405	Max Keenan	1 Linda Cres, Hawthorn 3122
C1231	Zack Addy	2 Green Way, Kew 3103
C2020	Camille Saroyan	7 Kelly St, Hawthorn 3122
C3301	Lance Sweets	1 John St, Hawthorn 3122

Customer evaluations

Cust ID	Course ID	Course Date	Rating	Comment
C0102	HAT_ADV000/	2 Jan/	4/	Well run. Afternoon tea was
	HSS_INT000	25 May	3	substandard/ Good Course.
				Well Run. Very Noisy
C1231	HAT_ADV000/	2 Jan/	4/3	Good/
	HSS_INT000	25 May		Ok
C0405	HAT_ADV000/	2 Jan/	1/1/1	Ok, but toilet is smelly/
	LLT_BEG002/	27 Jan/		Today toilet cleaned/
	HSS_INT000	25 May		Trainer dress not
				appropriate. Awful. Learnt
				nothing. Waste of time.
C2020	HAT_ADV000/	2 Jan/	3/ 4	Great/ Nice
	HTT_INT005	25 May
C0149	HTT_INT0005/	25 May/	5/5	None/ Best one so far
	LLT_BEG002	27 Jan