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