Assessment details Assignment Help

Assessment details for ALL students Assessment item 2—Assignment 2

Due date: Week 10 time) SMENT Weighting:

30%

Length:

Typically10-20 hours 2

Fridayof (Midnight AEST ASSES

Objectives This assessment itemrelates to all course learning outcomes in the course profile.

Introduction This is a highlyindustryrelevant assignment. It consists of multiple parts: 

Part A — Entity-Relationship Diagram(ERD)(40marks)



Part B — Table design (30marks)



Part C — Data Model Transformation (30marks) Students are expected to complete all parts.

Part A — Entity-Relationship Diagram (ERD)

(40 marks)

Draw an Entity-Relationship Diagram(ERD) for the data storage requirements of a business described in the case studybelow.

Notes: 

It is recommended that you follow the modelling standard used in the text book and lecture slides.



Use onlyattributes that uniquelyidentifyinstances of an entitythat are mentioned inside the case study. Any other attributes that also could uniquelyidentifyentityinstances beyond the case studyshould notbe introduced.



Some information in the case studyis ambiguous, vague, and/or m This is intentional to encourage students to think, explore, and search for answers. There are two options:

1.

Seek clarification about the case studybyposting a message on the course forums for this assignment (after searching thoroughly, you notice the same topic / question hasnot alreadybeen discussed). In an appendix named ‘Assumptions and Clarifications’, clearlydocument anyclarification received in your assignment submission. This appendix will be marked regarded as a 10-mark task in Part A as ‘Assumptions/ clarifications support all details provided in the ERD’.

2.

Make reasonable assumptions about missing or ambiguous information in the case study which are useful information that support the way you draw and present your developedERD. Your assumptions and clarifications need to adequately support why all details in the ERDare provided as such. However, marks may be lost forunreasonable assumptions. For example, the follow areunreasonable assumptions:



‘I assume that each invoice must be paid, so created a payment entity for this assignment’ (when the last line in the case study clearly states that payments are outside the scope) and



‘I could not find MS Visio in any CQU lab or any similar software, so hand drew my ERD for submission’.

The Case Study A

local

retailer

IT “IT

solutions” in Melbourneoff er both IT products( desktop PCs, tablet

PCs

and individual components) and servicest o

its

customers.

Due to new advances in technology and increased use of mobile devices, IT solutions has seen significant growth in IT product sales and demand for their services.

In order to meet the growing needs and to record product and servicerelated transactions, they hired a

consultant

to

designadatabase

to

storealloftherequireddata

about

their customers, offerings, products, services and billing. The consultant has been provided with data storage requirements which are described below.

An offering can be a product or a service.

When customers visits their store, IT solutions assigns

a unique customer ID to each customer and records their title, first name, last name, address (number, street, city, postcode), both home phone and mobile phone numbers and their email address.

If the customer wishes to purchase any of the offerings, then offeringID, description and type (product or service) are recorded. Purchase date is also recorded every time an offering is purchased. If the product is purchased then product name, price, and the product release date are noted.

If

the offering is a service then Name of the Employee responsible for that service, service conditions, service type are recorded. It is important to note that a service can be either repair or maintenance.

For repair cost will be recorded, if its maintenance, hourly rate will be noted.

Although it is not a common occurrence, sometimes stocked products may require repair. If this service is purchased by the customer, then a final Invoice will be prepared at the end of each service and given to the customer for payment. Invoice includes details relating to service date, due date and the amount due.

Employee (except employee name) and Payments are outside the scope for this case study.

How You Are Marked for the Entity-Relationship Diagram (ERD) You need to ensure you pay attention to the following aspects when developing your ERD: 

Correct entities are identified (5 marks)



Correctappropriate use of unique identifiers(5 marks)



Appropriateattributesand attribute types for each entity (5 marks)



Appropriateuse of symbols(5 marks)



Correctrelationships and cardinalities (min/ max cardinalities)(10 marks)



Assumptions/ clarifications support all details provided in the ERD (10 marks)

Part B — Table design(30 marks) “IT solutions” created a table to store details of different software products.Primary key for this table is a composite key made with Organisation and Software_name.

Unfortunately, some anomalies

arose while using this design.Below is a small sample of some of this data: Software_details Organisation

Country_of_origin

Software_name

Software_type

Version

Microsoft

USA

Office

Office application

2013

Microsoft

USA

Windows

Operating System

XP

Apple

USA

Mac

Operating System

7.6

Oracle

USA

Oracle

Database

11.0

Infosys Technologies Ltd.

India

BANCS 2000

Banking product

1.0

Ramco Systems

India

Marshal

ERP

1.0

Tata Consultancy Services

India

EX

Financial accounting

1.0

Eastern Software Systems

India

MakEss

ERP

1.0

Arrow

Australia

Tencia

Accounting

1.0

Pan Software

Australia

Riskware

Risk management

1.0

Questions Support your answers to questions below with anyassumptions that help to clarify your understanding of data in the Software_detailstable.

1.

Identifyone anomalythat might arise fromusing theabovetable. (6 marks)

2.

Identifyall functional dependencies in the above mentioned table. (7 marks)

3.

Explain whythis table does not satisfyBCNF. (7 marks)

4.

Convert this table into a set of BCNF tables that can hold the sample data shown in the above table.

(10 marks)

Note:Document

the

tables

in

your

design

using

the

notation

below. Customer(CID,Name,Street,Suburb,State,PostCode,DiscountPercent,Email) SalesOrder(SOID ,CID,Number,FullPrice,Discount,FinalPrice,TotalPaid,Status) foreignkey(CID)referencesCustomer(CID) Part C — Data Model Transformation(30 marks) Implement the ER model you developed in Part A as a relational database design

1.Transformyour ER model into a relational database design.

Note:Document

the

tables

in

your

design

using

the

(10 marks) notation

shown

in

the

example

below.Customer(CID,Name,Street,Suburb,State,PostCode,DiscountPercent,Email) SalesOrder(SOID,CID,Number,FullPrice,Discount,FinalPrice,TotalPaid,Status) foreignkey(CID)referencesCustomer(CID) 2.Implement sufficient tables to support the requirements below. You do notneed to implement (create) the entire database. You just need to implement enough of the database to demonstrate (and document) the following:

(20 marks)

1.

use of data types: provide examples of four different data types used in your design

2.

primary key constraints: provide two examples

3.

foreign key constraints: provide two examples

4.

a unique constraint: provide one example

5.

a cascading delete: provide one example

6.

column constraints (a constraint that affects onlyone column): provide two examples

7.

a table constraint (a constraint that affects more than one column): provide one example

8.

default values: provide two examples

9.

validation rule with a corresponding validation text: provide one each astwoexamples

10. input mask: provide one example 11. required: provide one example 12. format: provide one example Note: Each item has its mark as bolded and underlined above. You need to includea brief documentation (in a word document) that highlights where the above items are demonstrated in your database, e.g. where the primary key constraintsare. Marks will be lost if your documentation is missing, unclear or not concise.

Howandwhattosubmit You must make an electronic submission using submission link on the course web site.

Your

submission must consist of a single ZIP file which contains your assignment work in two files, as follows: 1.

A Microsoft Word document containing:



Your EntityRelationship Diagram(ERD) for Part A



Your reasonable assumptions/points of clarification for Part A (if you have any)



Your answers to questions in Part B



Documentation for Part C.

2.

Microsoft Access file containing:



The tables and features you have implemented to support the requirements for Part C.

Marking,modelsolutions,anddeadlines 

Marked assignments and marking feedback are made available to students approximately

2 weeks after the due date or 2 weeks after submission, whichever is later.AnyAssignment2 submitted 2 weeks after the due datewill NOT be accepted or marked.