data modeling

ITECH 2004 DATA MODELLING

CRICOS Provider No. 00103D iTECH 2004 ass 1 ER model 20 20 Page 1 of 4

Assignment 1: ER Model & Relational Schema Overview

The purpose of this task is to develop student’s skills in designing and implementing a relational

database for a given case study.

Timelines and Expectations

Percentage Value of Task: 20% (75 marks)

Due: Week 7 – Friday, May 1st, 2020 at 4:00 pm

Minimum time expectation: Preparation for this task will take approximately 20 hours

Learning Outcomes Assessed

The following course learning outcomes are assessed by completing this assessment:

K4. Design a relational database for a provided scenario utilising tools and techniques including ER

diagrams, relation models and normalisation.

K5. Describe relational algebra and its relationship to Structured Query Language (SQL). A1.

Design and implement a relational database using a database management system.

Assessment Details

Background

You have been commissioned to create a database for Alcool Holdings. Alcool Holdings is an online

retail beverages sales specialist and has two types of inventory, wine and beer. They would like a

database to manage their inventory, manufacturers, wholesalers, customers and sales.

Some of the reports that Alcool Holdings would like to run from your database design include a list of

customers by suburb and a list of inventory by price or other category. They would also like to apply

discounts to items by inventory type (i.e. wine or beer) by alcohol content or other category and apply

discounts to VIP customers and to bulk orders.

No normalisation has been undertaken on these entities, so there may be many to many relationships

that are not resolved. Your submission should have all many to many relationships resolved. You may

add entities or attributes as you see fit.

The minimum entities you are expected to have are listed below:

• Each inventory record will have a unique inventory ID, a title, a type (wine or beer), retail

price, number in stock, a wholesaler and alcohol content.

• Wine will have a year of production, one or more wineries, one or more wholesalers, body

*, style *, tannin *, a markup, and discount.

• Beer will have a year of production, one or more breweries, one or more wholesalers,

aroma **, appearance **, flavour **, markup, and discount.

ITECH 2004 DATA MODELLING

CRICOS Provider No. 00103D iTECH 2004 ass 1 ER model 20 20 Page 2 of 4

• Winery will have their name, address, contact, phone number, fax number, email address,

date of origin, specialisation and awards

• Brewery will have their name, address, contact, phone number, fax number, email address,

date of origin, specialisation and awards

• Awards will have type (wine or beer), name of award, year of award

• Wholesalers will have their name, address, contact, phone number, fax number, email

address

• Customers will have their name, address, phone number, email address and discount

* For a list of wine descriptions and what they mean see: https://winefolly.com/tutorial/wine-descriptions-

chartinfographic/

** For a list of beer descriptions and what they mean see: https://www.bjcp.org/docs/2015_Guidelines_Beer.pdf

Requirements

This assignment should be presented in a report format, including the following items:

• An ER Diagram with all entity names, attribute names, primary and foreign keys, relationships,

cardinality and participation indicated. All many to many relationships should be resolved.

• A discussion of normalisation, the normal form that each entity is in, how normalisation was

achieved, and why that is optimal. All entities to be in 3NF, unless there is strong justification

for leaving in 2NF.

• A list of relationships with all table names, attributes, primary and foreign keys indicated as per

the conventions given in the lecture slides (i.e. entity/table names in capitals, attributes as

proper nouns, primary key underlined and foreign keys in italics).

• A database schema indicating the type and purpose of all attributes.

Academic Presentation

Assignment should be presented in accordance with:

• General Guide to Referencing: https://federation.edu.au/__data/assets/pdf_file/0020/313328/FedUni-

GeneralGuide-to-Referencing-2016ed.pdf

• General Guide to Writing and Study Skills:

http://federation.edu.au/__data/assets/pdf_file/0018/190044/GeneralGuide-to-Writing-and-Study-Skills.pdf

• Guide to Layout and Appearance: https://federation.edu.au/__data/assets/pdf_file/0017/190043/General-

Guideto-Layout-and-Appearance.pdf

ITECH 2004 DATA MODELLING

CRICOS Provider No. 00103D iTECH 2004 ass 1 ER model 20 20 Page 3 of 4

Submission

The assignment is to be submitted via the Assignment 1 submission box in Moodle. This is to be found in the

Assessments section of the course Moodle shell.

Marking Criteria/Rubric

Assessment Criteria

Marking Scale

Poor Excellent

1 ....................... 5

Presentation and Referencing

• Overall presentation of the report

• Full APA referencing of all materials used and full

disclosure of assistance from all sources including tutors

and other students

0

0

ER Diagram

• Completeness of diagram

• Correct notation and convention used

• All assumptions clearly noted

• Primary and foreign keys

• Resolution of many to many relationships

0

0

0

0

0

Normalisation

• All entities and relationship in appropriate normal form

• Discussion of normalisation for all entities and relationships

• Appropriate interpretation of each normal form, arguments

for leaving the schema in the normal form you consider

optimal.

0

0

0

Relational Schema

• Primary keys used

• Foreign keys correctly identified including parent entity

• Schema is a correct translation of the E-R diagram

submitted with appropriate tables, columns, primary keys,

and foreign keys

• Types and restrictions on attributes given

0

0

0

0

0

Total Mark [75 marks] 0.0

Total Worth [20%] 0.0

Feedback

Feedback and marks will be provided in Moodle. Marks will also be available in FDL Marks.

Plagiarism:

Plagiarism is the presentation of the expressed thought or work of another person as though it is one's own without

properly acknowledging that person. You must not allow other students to copy your work and must take care to

ITECH 2004 DATA MODELLING

CRICOS Provider No. 00103D iTECH 2004 ass 1 ER model 20 20 Page 4 of 4

safeguard against this happening. More information about the plagiarism policy and procedure for the university

can be found at http://federation.edu.au/students/learning-and-study/online-help-with/plagiarism

Please refer to the Course Description for information regarding late assignments, extensions, and special

consideration. A reminder all academic regulations can be accessed via the university’s website, see:

http://federation.edu.au/staff/governance/legal/feduni-legislation