BIS1002 Data and Information Management
Trimester 1, 2025
Assessment 2: Applied Project – 1

Assessment Overview

Weighting

30% of unit total

Due Date

Week 7, Sunday, 30 March, by 23:55 AEST/AEDT

Assessment Type

Individual assessment

Word Count / Length

2500 words +/- 10%

Unit Learning Outcomes

In this assessment, you will be tested on whether you have successfully met the  following Unit Learning Outcomes (ULOs):

• ULO-1: Understand and evaluate the role of database management  systems in information technology applications within organisations. • ULO-2: Identify organisational requirements for data and develop data  models using conceptual data modelling techniques.

• ULO-3: Explain and apply database design techniques for relational  database systems.

• ULO-4: Derive a physical design for a database from its logical design.

Submission Type and  Required Format

The type of assessment you will be completing is an Applied Project.

It should include a complete database design and implementation based on  business requirements provided in the assignment requirements.

Assessment Details

Assessment Purpose

The purpose of this assessment is to provide a structured framework for creating  efficient and reliable relational databases through planning, requirement analysis,  design, implementation and testing. It provides opportunity for students to learn,

practice and demonstrate fundamental skills relevant to the business world in  developing information systems, analysing data and making informed decisions.

Assessment Instructions

In your assignment you will need to analyse the following scenario, create ER  diagram, build a database and populate it with sample data.

You need to design a Hotel Reservation System satisfying the following conditions:

• Many hotels can be registered in the system

• Customers reserve rooms in their preferred hotels

• Each hotel has many rooms (the room types can be a single room, a double  room, a queen room, a studio, a suite etc.)

• A customer can make many reservations

• A reservation should be made by only one customer

• During the stay, a customer can get many services, which will be added to the  reservation (breakfast, parking, shuttle, flowers, restaurant etc.)

• When the customers check out, the final bills are prepared based on their  reservation records

Hotel

name, phone number, address

Room

room number, room type, area, facilities

Reservation

date, rate, number of nights

Service

type, price

Customer

name, address, phone number

Bill

date, balance

You need to design and implement the hospital database. There are three parts in  the assignment: 

1. Conceptual Model design

2. Relational Model design

3. Physical Model design. Database population using SQL programming. Your assignment consists of 3 parts:

Part 1. Conceptual Mode

You need to describe the Conceptual Model, which must include the following  elements:

• Entities and their attributes. For each entity you must also introduce unique  identifier attribute for each entity.

• Identify multi-valued and composite attributes (if any). In this case, they need  to be shown in the ER diagram with the corresponding notations. • Describe relationships between entities and their cardinalities. Remember  that relationships can be one-to-one, one-to-many, many-to-many, and also  optional or mandatory.

• Draw the Conceptual Model Enhanced Entity-Relationship diagram using  app.diagrams.net tool, save it as .png or .jpg file and insert it in the Word  document.

Part 2. Logical (Relational) Model

Describe the algorithm of conversion of the Conceptual Model into the Logical  Model. Remember that your description should include the following elements:

• Entities in the Conceptual Model become relations (tables) in the Logical  Model, and unique identifiers become primary keys.

• For one-to-one or one-to-many relationship between two entities you will  need to create a foreign key in one of the relations (tables).

• If there is a many-to-many relationship between two entities, then you will  need to create a new relation (table) with a composite primary key.

• For each field (column) in a relation, describe a data type (integer, float,  character, text etc.).

• If an entity contains a multi-valued attribute, then you will need to create a  new relation in the Logical Model corresponding to this attribute.

• Draw the Logical Model diagram using app.diagrams.net tool, save it as .png  or .jpg file and insert it in the assignment Word document.

Part 3. Create and populate the database.

The SQL queries below can be written in XAMPP MySQL.

• Write SQL queries to create each table. The queries should contain creation  of primary and foreign (if applicable) keys.

• Write SQL queries to populate the tables. Each table should contain several  records to test the referential integrity.

• One of the records describing customers should have your name as a  customer name.

• One of the records describing hotels should have an address as your campus  address (Sydney, Melbourne, or Brisbane campus address).

• One of the records describing bills should have your student id as a primary  key.

• One of the records describing rooms should have your ICT5151 classroom  number in your campus.

• Make screenshots of the development environment you worked with and  insert the images in the assignment file.

• Copy SQL queries in the assignment document.

• Export your project database in SQL script containing queries to create tables  and populate data.

• Write SELECT SQL queries to test the database.

What to submit:

 

1. A MS Word document containing database design and diagram images of  Conceptual and Logical Models.

2. Your project database SQL script generated by XAMPP.

Artificial Intelligence (AI)  Use

In this assessment, the use of generative artificial intelligence (AI) tools is NOT  PERMITTED. In this assessment, you must NOT use generative artificial  intelligence (AI) to generate any materials or content in relation to the assessment  task.

This means you are NOT allowed to use any AI tools such as chatbots, text  generators, paraphrasers, summariser, or solvers to complete your assessment.  You are expected to produce your own work and cite any sources properly.

Engaging with AI tools for this assessment will be treated as a breach of academic  integrity. Refer to the Academic Integrity Policy and Procedure for more details.

Other Important Information

Assessment Resources

It is strongly recommended that you use the following to find academic peer reviewed sources of information.

• ECA Library catalogue and databases

• Unit Readings (Course Reserves) Subject Guide: Information Technology

For in-text citation and referencing, follow the ECA HE Student Guide to APA 7th Referencing.

Submission

Requirements

On completion of your assessment:

• Follow the Assessment Presentation Guidelines

• Submit your document as a PDF or MS Word document via the assessment  submission link in Canvas.

• Include a completed Assessment Cover Sheet, including the AI Declaration,  and attach it to the assessment.

• Save drafts of your work.

[Include more information or remove not applicable information as required for  your assessment]

Assessment Support

For academic support or feedback on a draft of your assessment, please email  [email protected]

For assistance with finding resources, such as books and journal articles, please  email [email protected]

For information and guides on tackling assessments and developing your academic  skills, please visit in the ECA Library and Learning Support website:

For queries about this specific assessment task, please contact the Unit  Coordinator.

Assessment 2 Rubric

Marking Criteria

High

Distinction (HD) 85-100)

Distinction (D)

75-84

Credit (C)

65-74

Pass (P)

50-64

Fail (F)

0-49

Criteria 1 – 20%

Demonstrate an  understanding  and design the  Conceptual

Model design

processes

Displays

exceptional

understanding of  concepts and

their practical

application in

Conceptual

Model design

process

Exhibits accurate  and detailed

breadth and

depth of

understanding

and describing

Conceptual

Model design

process

Exhibits breadth  and depth of

understanding

and describing  Conceptual

Model design

process

Basic knowledge  only of

understanding

and describing  Conceptual

Model design

process

Inadequate

understanding

and describing

Conceptual

Model design

process

Criteria 2 – 20%

Demonstrate an  understanding  and design the  Logical Model

design processes

Displays

exceptional

understanding of  concepts and

their practical

application in

Conceptual

Model design

process

Exhibits accurate  and detailed

breadth and

depth of

understanding

and describing

Conceptual

Model design

process

Exhibits breadth  and depth of

understanding

and describing  Conceptual

Model design

process

Basic knowledge  only of

understanding

and describing  Conceptual

Model design

process

Inadequate

understanding

and describing

Logical Model

design process

Criteria 3 – 20%

Demonstrate

understanding  and skills of

creating the

Conceptual

Model diagram

Displays

exceptional

understanding

and skills of

creating the

Conceptual

Model diagram

Exhibits accurate  and detailed

breadth and

depth of

understanding

and skills of

creating the

Conceptual

Model diagram

Exhibits breadth  and depth of

understanding

and skills of

creating the

Conceptual

Model diagram

Basic

understanding

and skills of

creating the

Conceptual

Model diagram

Inadequate

understanding

and skills of

creating the

Conceptual

Model diagram

Criteria 4 – 20%

Demonstrate

understanding  and skills of

creating the

Logical model

diagram

Displays

exceptional

understanding

and skills of

creating the

Logical Model

diagram

Exhibits accurate  and detailed

breadth and

depth of

understanding

and skills of

creating the

Logical Model

diagram

Exhibits breadth  and depth of

understanding

and skills of

creating the

Logical Model

diagram

Basic

understanding

and skills of

creating the

Logical Model

diagram

Inadequate

understanding

and skills of

creating the

Logical Model

diagram

Criteria 5 – 5%

Demonstrate

understanding  of database

primary and

foreign keys,

Displays

exceptional

understanding of  database

primary and

foreign keys, and

Exhibits accurate  and detailed

breadth and

depth of

understanding of  database

Exhibits breadth  and depth of

understanding of  database

primary and

foreign keys, and

Basic knowledge  only of

understanding of  database

primary and

foreign keys, and

Inadequate

understanding of  database

primary and

foreign keys, and  database

 

and database

referential

integrity.

database

referential

integrity.

primary and

foreign keys, and  database

referential

integrity.

database

referential

integrity.

database

referential

integrity.

referential

integrity.

Criteria 6 – 15%

Demonstrate an  understanding  and ability to

write and run

simple SQL DDL  and DML queries

Displays

exceptional

understanding

and ability to

write and run

simple SQL DDL  and DML queries

Exhibits accurate  and detailed

breadth and

depth of and

ability to write

and run simple  SQL DDL and

DML queries

Exhibits breadth  and depth of

understanding

and ability to

write and run

simple SQL DDL  and DML queries

Basic knowledge  only of

understanding

and ability to

write and run

simple SQL DDL  and DML queries

Inadequate

understanding

and ability to

write and run

simple SQL DDL  and DML queries