Learning Outcome Details

Assignment Learning Objectives and Graduate Learning Outcomes

This is an individual assignment. The aim of the assignment is for you to develop understanding of information     management and database concepts, modelling approaches and applying them to a realistic business context by :

•     Designing SQL queries capable of producing specific database reports relating to information management (ULO4, and GL01/GLO3/GL08).

•     Design a simple database using ERR modelling, illustrating how the business can use and manage information (ULO3, and GLO1/GLO3/GL05); and

•    Critically evaluating the business case and reporting a solution accordingly (ULO3 and GLO3/GL05).

Starting the Assignment

You are strongly encouraged to make a start on this Assignment by the end of the 4th Week of          Trimester, and you should progress thoughtfully through the steps. Multiple iterations are necessary even for experienced data modellers to perfect a design. An early start will get you a long way ahead!

Remember, hasty decisions made in the design process may result in much more work later.       Feel free to discuss concepts and ideas with peers but remember your submission must be your own work. Be careful not to allow others to copy your work.

(EER diagrams should use crow's foot notation as per lecture notes. Under no circumstances will the ‘quaint’ diagrams produced from Microsoft Access be acceptable, nor will its curious and unique form of SQL be acceptable. Such submissions will attract zero marks.)

Task 1: SQL Queries

Include the following SQL queries that were completed as part of the weekly workshops. These were labelled as Assignment Question within each of the workshops.

a)   Workshop Week 2 -  Question 3

b)   Workshop Week 3 -  Question 6

c)    Workshop Week 4 -  Question 8

d)   Workshop Week 5 -  Question 4

e)   Workshop Week 5 -  Question 5

For each SQL query include the following:

i.    Actual textual SQL query   (e.g. SELECT column FROM table;)

ii.   A screenshot of the SQL query being executed on your university database account and the resulting table.

Make sure the SQL query and the resulting table are captured in full in the screenshot. The table should be formatted with no lines wrapping around. Use set linesize command (see Workshop   Week 2) to increase line length if necessary.

iii.   An explanation of each of the SQL queries in your own words (1 or 2 sentences max).

Task 2: Business Case

Graeme’s Pet Accessories is a small, but rapidly growing business operating out of Geelong. The in- store business began as a means for Graeme to follow his passion for pet cats and dogs and selling accessories and toys. As much as he enjoys selling pet accessories, it is not a very profitable undertaking. So, Graeme began conducting obedience training group classes for Puppies (less than 1yr old), young dogs (1-2yr olds), adult dogs (3yrs and older) and their owners to supplement the business bottom-line.

Currently, the dog obedience training business aspect is expanding, and he now has five Trainers who are teaching classes at four levels (i.e., Introductory, Intermediate, Advanced, Tricks). Introduction and Intermediate level obedience are group classes for Puppies, Young Dogs and their owners only and Advanced group training classes are for adult dogs and their owners only. The one- on-one Trick class (i.e., one Trainer, one dog and owner) is strictly reserved for individual private training class upon request and is strictly reserved for competent dogs and owners ready for advance Tricks level training.

Approximately two thirds of the training classes consist of school age children owners, and one third of classes is for adult owners. The business also keeps track of which Dog Trainers have a “Working with Children” card and its expiry date, to ensure that only approved Dog Trainers can teach classes with children (i.e., under 18yrs of age) participating.

Additionally, Graeme has now just opened online store presence to sell pet accessories via the Internet and is busy integrating the online business processes and offering five aspects of the pet accessory business for online sales as follows:

• Aquarium Shop (aquariums, decorative coral and plants, tank ornaments and decorations, water filters and pumps, tank cleaning gear, fish food)

• Bird Shop (cages, toys, feeders, drinker bottles, perches and ladders, swings and mirrors, bird seed and food)

• Cat Shop (scratching posts, cat playgrounds and towers, cat beds, grooming gear, toys, leads and harnesses, treats and food)

• Dog Shop (kennels, collars, leads and harnesses, dog beds, toys, grooming gear, chew treats and food, poop bags)

• Rabbit Shop (hutches, drinker bottles, grooming gear, rabbit runs)

Graeme’s Pet Accessories is now carrying out the following core business operations:

• Acting as a retail outlet agent for “Western Cats and Dogs Accessories” and “Animal Health and Food Products” and managing in-store and online order processing.

• Online only and off-the-shelf sales of regular cat and dog toys and accessories

• Booking puppies and dogs into group classes that teach obedience.

• Booking dog trainers to teach one-on-one private dog Trick training class based on the competence of the individual dog and owner.

• Allocating dog owners and their dogs to specific groups or individual classes depending on training levels competence and requests.

So far, his business has been operating manually with temporary notes scribbled on notebooks in combination with a series of disorganised manual spreadsheets. These tools are becoming inefficient and increasingly inappropriate for managing the growing sales and training demands and the business efficiency. A transition to a database supported information system is urgently needed.

Task 2: EER Model

You are employed as a business analyst at Deakin Innovative Solutions, a business consulting firm.  You task is to investigate and develop a data model (an EER model) for the back-end database in     the newly proposed system. The back-end database will store all of the critical data and support all core business operations mentioned above.

Note: The design of any payments system is outside the scope of this project.

EER Diagram

EER diagram should contain the following components: Entities, named relationships, attributes (with Primary keys denoted) and cardinalities for all relationships.

A faster way of doing the EER diagram is to sketch it up on a blackboard, take a digital           photograph and paste it into your report. Alternatively, you may use any software tools at   your disposal (Lucid chart for example). If hand-drawn make sure your EER diagram is neat and legible.

Submission Instructions

The assignment must be submitted as soft copy in the form of a PDF document via CloudDeakin by the due date. File should be named your firstname_lastname (e.g., John_smith.pdf). You do not     have to submit an actual database!

You must keep a backup copy of every assignment you submit, until the marked assignment has been returned to you.  In the unlikely event that one of your assignments is misplaced, you will  need to submit your backup copy.

Any work you submit may be checked by electronic or other means for the purposes of detecting collusion and/or plagiarism.

When you are required to submit an assignment through your CloudDeakin unit site, you will          receive an email to your Deakin email address confirming that it has been submitted. You should   check that you can see your assignment in the Submissions view of the Assignment dropbox folder after upload, and check for, and keep, the email receipt of the submission.