Database Systems (MITS4003) Assignment Help

Overview

You are required to investigate and identify data requirements in the given case study. In doing so, you are required to 

(1) develop an Entity Relationship Diagram, 

(2) design relational schema (including Unique identifier, relationships and all attributes), and 

(3) Derive a physical design from the logical design. 

Note: This is an individual assignment carrying 10% of the total mark in this unit. The submission deadline is end of the week of session 5. 

Objectives 

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students’ skills to analyze organization database requirements, develop a data model to reflect the organization’s business rules. This assessment covers the following LOs. 

1. Synthesize userrequirements/inputs and analyse the matching data processing needs, demonstrating adaptability to changing circumstances; 

2. Develop an enterprise data model that reflects the organization’s fundamental business rules; refine the conceptual data model, including all entities, relationships, attributes, and business rules. 

3. Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; further use of data manipulation language to query, update, and manage a database 

Assessment Criteria 

This assessment is designed to assess student’s knowledge, skill and application of knowledge and skill in relation to 

1. Understand and investigate requirements. 

2. Identify entities and required attributes for each entity. 

3. Appropriately select any Unique identifier for each entity. 

4. Identify relationship among entities. 

5. Develop entity relationship diagram. 

6. Derive a physical design and provide enough description of each table, primary keys, and attributes of each entity.

CASE STUDY 

Study the following scenario and attempt all tasks that follow: – 

Great Ocean Road Rental Company (GORRC) 

GORRC is one of the rental companies in Victoria and offers house or apartment rental to tourists who want to reside near the Great Ocean Road. 

The Company is using a manual method for keeping track of tourists and their rentals. However, they are interested in going online and allowing tourists to search available houses / apartments, do a reservation based on the advertised rents. 

When any tourists rent a house / apartment with the Company for the first time, their details must be recorded in a system. There are certain types of villas that are classified for senior aged people. It is a policy of GORRC that these villas are only rented to customers over 70 years old. Houses/apartments details must be recorded with information such house id, classification (normal vs senior), kitchen (Yes / No), living room (Yes / No), number of bedrooms, number of washrooms, BBQ (Yes / No), number of onsite car parks, daily rent, etc. 

All booking information such as booking id, customer id, house id, from_date, to_date, total cost must also be stored. Each house/apartment has a unique id. A customer can search their desired house/apartment online to see if it is available on their preferred dates. 

All rentals are for minimum 1 day. Rental charges may vary based on house/apartment types and dates. Total rental payment will be deducted automatically from customer’s credit/debit card at the time of registration. Each booking id must be unique. (The booking id can be a number automatically generated by the system).

Task 1 (Weighting 20%) 

• Investigate and identify data requirements for the given case study. • Provide (i) a list of required entities and (ii) a list of attributes for each entity identified in (i). 

• Identify the Unique identifier for each entity. 

Task 2 (Weighting 60%) 

Develop an Entity-Relationship (ER) data model for the given scenario showing as many as possible the following: 

• Entities and unique identifier for each entity. 

• Relationships between entity types, including relationship constraints. For each relationship, indicate the degree of constraint such as ‘One to Many’, ‘Many to Many’ or ‘One to One’. 

• Attributes: You are required to identify all necessary attributes. Assign all attributes to entity types and underline all unique identifier attributes. 

You may choose any modelling notation. State any necessary assumptions you have made, with the understanding that they do not contradict the given scenario. 

Task 3 (Weighting 20%) In this task, you are required to 

• Derive a physical design having details of tables, primary key, foreign keys, and brief details about all essential columns of tables. 

o Provide detail information like requisite, atomic, type of value, key or non-key and constraint for all data columns of each table.

General Instructions 

1. Yoursubmission should be clear and concise and in your own words. 

2. One submission per student. 

3. Use headings to guide the marker and include tables or diagrams where required. 4. The referencing style must follow the IEEE referencing style. 

Submission Guidelines 

1. Follow the link in Moodle to upload your submission on or before the deadline. All submissions must be done in LMS using the respective link provided. 

2. Submission must be made by the due date and time as determined by the unit facilitator. Submissions made after the due date/time will be penalized at the rate of 20% per day (including Saturday, Sunday and public holidays). 

3. Incidence of plagiarism will be penalized based on VIT Academic Integrity Policy and Procedure. 

4. Submit your work in a word document file named ‘Student_Id_MITS4003_A1’.

Marking Guide:

Marking Guide: 

Unacceptable 

Acceptable 

Good 

Excellent

1. Task 1 

(Weighting 20%)

a. Entities 

(Weighting 10%)

Entities not identified or incorrectly 

identified.

A few of the required entities are identified.

Most of the required entities are identified.

All of the required 

entities are identified.

b. Attributes 

(Weighting 5%)

Attributes not 

identified or 

incorrectly identified.

A few of the required attributes for each of the required entities are identified.

Most of the required attributes for each of the required entities are identified.

All of the required 

attributes for each of the required entities are identified.

c. Unique 

identifier 

(Weighting 5%)

Unique identifier not identified or 

incorrectly identified.

Unique identifier for a few of the required entities is identified.

Unique identifier for most of the required entities is identified.

Unique identifier for all of the required 

entities is identified.

2. Task 2 

(Weighting 60%)

a. Entities 

(Weighting 10%)

Does not adequately demonstrate 

required entities in an ER diagram.

A few of the required entities are 

demonstrated 

correctly in an ER 

diagram.

Most of the required entities are 

demonstrated 

correctly in an ER 

diagram.

All of the required 

entities are 

demonstrated 

correctly in an ER 

diagram.

b. Attributes 

(Weighting 10%)

Does not adequately demonstrate 

required attributesin an ER diagram.

A few of the required attributes for each of the required entities are demonstrated 

correctly in an ER 

diagram.

Most of the required attributes for each of the required entities are demonstrated 

correctly in an ER 

diagram.

All of the required 

attributes for each of the required entities are demonstrated 

correctly in an ER 

diagram.

c. Unique 

identifier 

(Weighting 10%)

Does not adequately demonstrate 

required Unique 

identifier in an ER 

diagram.

Unique identifier for a few of the required entities is 

demonstrated 

correctly in an ER 

diagram.

Unique identifier for most of the required entities is 

demonstrated 

correctly in an ER 

diagram.

Unique identifier for all of the required 

entities is 

demonstrated 

correctly in an ER 

diagram.

2.4 Relationships 

(Weighting 30%)

Does not adequately identify and 

demonstrate 

relationships among entities in an ER 

diagram.

A few of the required relationships among entities are identified and correctly 

demonstrated in an ER diagram.

Most of the required relationships among entities are identified and correctly 

demonstrated in an ER diagram.

All of the required 

relationships among entities are identified and correctly 

demonstrated in an ER diagram.

3. Task 3 

(Weighting 20%)

a. Tables 

(Weighting 5%)

Relational schema 

does not adequately cover the required 

tables, and key and non-key attributes.

Relational schema 

covers a few of the required tables, and key and non-key 

attributes for those tables.

Relational schema 

covers most of the 

required tables, and key and non-key 

attributes for those tables.

Relational schema 

covers all required 

tables, and key and non-key attributes for those tables.

b. Foreign 

Keys 

(Weighting 10%)

Relational schema 

does not adequately cover foreign keys.

Relational schema 

covers required 

foreign keys in a few of the required 

tables.

Relational schema 

covers required 

foreign keys in most of the required 

tables.

Relational schema 

covers required 

foreign keys in all 

required tables.

c. Informatio 

n related to 

columns. 

(Weighting 5%)

Does not adequately cover information 

about data columns.

Provide required 

detail information for all data columns of a few of the tables.

Provide required 

detail information for all data columns of 

most of the tables.

Provide required 

detail information for all data columns of all