Computer Science Assignment - The Kangaroo Inn database

Charles Sturt University Subject Outline

ITC114 201390 S I-29 October 2013-Version 1 Page of 10 21

Sample exam paper A sample exam paper (with solutions) indicative of the structure for the final exam will be provided in the Resources section of this subject's Interact site toward the end of session.

Assessment Items Item number Title Type Value Due date* Return date**

1 Online Quiz 1 Assignment 5% 06-Dec-2013 27-Dec-2013

2 Modelling and Normalisation Assignment 20% 22-Dec-2013 12-Jan-2014

3 Online Quiz 2 Assignment 5% 17-Jan-2014 07-Feb-2014

4 Create and query databases using SQL Assignment 20% 02-Feb-2014 23-Feb-2014

5 Final Exam Exam 50% To be Advised. -

* due date is the last date for assessment items to be received at the University ** applies only to assessment items submitted by the due date

Assessment item 1 Online Quiz 1

Value: 5% Due date: 06-Dec-2013

Return date: 27-Dec-2013 Submission method options

Alternative submission method

Task

Complete the online quiz on Topics 1 and 2 in the ITC114 Interact Test Centre.

Rationale

This assignment will assess your knowledge of the fundamentals of database systems and data models.

Marking criteria

This assignment is a series of multiple choice questions. Each correct answer will score 1 mark. Marks will not be deducted for incorrect answers.

Assessment item 2 Modelling and Normalisation

Value: 20% Due date: 22-Dec-2013

Return date: 12-Jan-2014 Submission method options

Hand delivery (option applies to Internal only) Alternative submission method

Task

(50 marks): Part A. Create an ERD

Charles Sturt University Subject Outline

ITC114 201390 S I-29 October 2013-Version 1 Page of 11 21

Read the following problem and complete the tasks listed beneath it.

The Kangaroo Inn database The owners of the Kangaroo Inn, a Bed and Breakfast guest house in the Blue Mountains of NSW have approached you to build them a database to help them run their business.

Kangaroo Inn provides overnight accommodation and breakfast to couples and has six guest bedrooms and two detached private cottages in a private part of the garden. Each bedroom will accommodate a maximum of 2 guests only, and the cottages 4 guests.

In order to create a point of differentiation from other local bed and breakfast houses, Kangaroo Inn will allow guests to have their pet dogs stay in the attached kennels. The dog names are normally recorded so that the Inn can provide guests with a personalised dog collar for each dog.

A Continental breakfast is provided for all guests as part of their accommodation, but vegetarian and gluten-free alternatives are available. This information is normally recorded at the time of booking the accommodation.

Bookings are normally taken for up to 12 months in advance and guests who have stayed more than 3 times at the Inn receive a gift of wine and chocolates on arrival.

: Tasks

1. List all the business rules for the database for Kangaroo Inn. An example of a business rule is: "An invoice is generated by only one customer"; (10 marks) 2. Create an ERD that meets the Kangaroo Inn requirements. The ERD should show only Primary Key and Foreign Key attributes. (40 marks)

(15 marks):Part B. Create an RDM

: Tasks Using the ERD created in Part A: 1. Identify all attributes required for each table shown in the ERD; 2. Create an RDM for each table in the ERD;

(35 marks):Part C. Normalise the RDM to BCNF

: Tasks Using the RDM created in Part B: 1. Create a set of Dependency Diagrams for the Inn's database; 2. Normalise the tables to BCNF; 3. Create a new RDM for the normalised tables.

Rationale

This assignment has been designed to assess a student’s ability to: • Understand and interpret a set of business rules for a database; • Create an Entity Relationship Diagram that models the requirements accurately; • Create a Relational Data Model that accurately models the ERD and requirements; • Normalise the model to Boyce-Codd Normal Form;

Charles Sturt University Subject Outline

ITC114 201390 S I-29 October 2013-Version 1 Page of 12 21

Marking criteria

The following criteria will be used to evaluate assignment 2:

Criteria Marks Part A Appropriate business rules identified 10 Appropriate entities identified 10 Appropriate relationships identified 10 Primary keys identified 5 Foreign keys identified & correctly located 5 ERD displays correct entities and relationships 10 Part A Total 50 Part B Appropriate attributes identified for each table 5 Appropriate attributes located in appropriate tables 5 RDM is consistent with ERD 5 Part B Total 15 Part C: Appropriate Dependency Diagrams created 10 Tables normalised to BCNF 10 New RDM consistent with Dependency diagrams 10 Model eliminates redundancy, insertion & deletion anomalies 5 Part C Total 35 Assignment Total 100 Marks deducted for: ERD not matching business rules, each -2 Missing a table from an M:N relationship, per table -1 Wrong or missing Primary Key, each -1 Missing a dependency in a relationship, each -1 Wrong or missing Foreign Key, each -1 Relational tables not matching ERD, each -1 Relational Tables not matching Dependency Diagrams, each -1

Presentation

Assignments 2 and 4 must be written in a word processor like Microsoft Word, but saved as PDF (.pdf). Instructions will be provided on how to do this using free software. Each assignment must be submitted as a single file -- insert all graphics like screenshots into the document. Databases must not be included as part of a submission. Marks may be deducted for failing to follow instructions.

Any extensions required must be applied for before the due date of the assignment.

Assessment item 3 Online Quiz 2

Value: 5% Due date: 17-Jan-2014

Return date: 07-Feb-2014 Submission method options

Alternative submission method