Objective:
This assignment aims to enhance your understanding of database design through the practical application of Entity-Relationship Diagrams (ERD) and normalisation techniques. You will design a database system that is both efficient and scalable, addressing a real-world problem scenario provided below.
Learning Outcomes:
Students should be able to demonstrate their achievements in the following unit learning outcomes:
a. Adapt capacity in the definition, application, and evaluation of the fundamental concepts and theories of relational databases and database management system models.
b. Advocate high level of capacity in designing and implementing database systems using advanced modelling techniques, such as entity-relationship (ER) modelling and normalization, for business applications.
c. Exemplify high level of written and verbal communication skills relevant to the planning, design, and implementation of a technical solution.
Submission Guidelines:
The report is to be submitted in a word format. The assessment constitutes 40% of the total grade. The report should be between 2000 and 2200 words in length, excluding the title page and list of references. All pages must be numbered. The similarity index of your report should be less than 20%.
When uploading your assessment report, name your document using the following syntax:
[Student ID]_[unitcode]_[Class].doc (or ‘.docx’)
For example, 246514_ICT503_MEL.doc.
You must keep a backup copy of every assignment you submit until the marked assignment has been returned to you. In the unlikely event that one of your assignments is misplaced you will need to submit your backup copy.
Penalties for late submission:
Late submissions will incur a 10% deduction in marks for each day beyond the due date. No marks will be awarded if a student is absent or does not align with the given business scenario.
Assignment Description:
This assignment is designed to enhance students' skills in designing and implementing a relational database tailored to a specific business scenario.
Scenario Overview
TravelVista Tours – Online Travel Booking and Inquiry Management System
TravelVista Tours is a rapidly growing travel agency based in Melbourne that specialises in domestic and international holiday packages, guided tours, hotel bookings, and customised travel itineraries. As the business expands, TravelVista is facing challenges caused by managing bookings, customer enquiries, and package information through spreadsheets and manual email records. This fragmented approach has resulted in duplicate data, booking errors, inconsistent pricing, and delays in responding to customer requests. To improve efficiency and provide a smoother customer experience, TravelVista has decided to develop a centralised Travel Booking and Enquiry Management System (TBEMS) supported by a well-designed relational database.
The new system should maintain all essential travel information, including customer profiles, available tour packages, booking details, payment records, and customised travel enquiries. Customers often request tailored travel plans, so the database must also store enquiry information such as preferred destinations, travel dates, and special requirements.
This assignment requires students to design an Entity-Relationship Diagram (ERD) and apply normalisation techniques to ensure the database is structured efficiently. Students are expected to identify entities, attributes, primary keys, relationships, and constraints that will support TravelVista’s business operation.
Business Rules:
- A customer may make multiple bookings, but each booking must be linked to exactly one customer.
- Each booking must be associated with one travel package, but a travel package can appear in many bookings.
- A customer may submit multiple travel enquiries, but each enquiry belongs to only one customer.
- Each enquiry may request one or more destinations, but every destination can appear in many enquiries.
- A booking may have multiple payment transactions, but each payment relates to only one booking.
- Every travel package must include at least one destination, and destinations may appear in multiple packages.
- Each staff member may manage multiple bookings or enquiries, but each booking/enquiry is handled by only one staff member.
- A customer profile must exist before any booking or enquiry can be recorded in the system.
Main Deliverables:
The entire assignment should be discussed and presented as one coherent report and the presentation. The written report should include: 1) objectives of your database design, 2) benefits of your database design, 3) tables, data fields and commentary, etc. Feel free to make reasonable assumptions.
a. Entity-Relationship Diagram (ERD):
Construct a detailed ERD that represents the scenario. Your ERD should include all necessary entities, relationships, attributes, and primary keys. Use appropriate notation to indicate relationships, cardinality, and participation constraints clearly.
b. Normalisation:
Provide a detailed explanation of how you applied normalization techniques to ensure that your database design is free of redundancy and update anomalies. The process of normalizing your database design to at least 3NF (Third Normal Form). Identification of any potential issues you encountered during normalization and how you resolved them.
General Assessment Requirement
Incomprehensible submissions:
Assessments provide the opportunity for students to demonstrate their knowledge and skills to achieve the required standard. To do this, assessment responses need to be both clear and easy to understand. If not, SISTC cannot determine that students have demonstrated their knowledge and skills. Assessments will, therefore, be marked accordingly, including the potential for 0 (zero) marks where relevant.
Check with marking rubric:
Before submitting your assessment, you should check it against the assessment criteria and the marking rubric included in this specification to ensure that you have satisfactorily addressed all the criteria that will be used to mark your submission.
Referencing
This unit requires you to use APA 7th edition system of referencing. See the Sydney International School of Technology and Commerce’s quick reference guide – APA 7th Referencing. It can be used in conjunction with the online tool Academic Writer - https://extras.apa.org/apastyle/basics-7e/#/.
Extensions
Extensions can only be granted for exceptional and/or unavoidable circumstances outside of your control. Requests for extensions must be made by 12 noon on the submission date using the online extension request form. All requests for extensions should be supported by appropriate evidence (e.g., a medical certificate in the case of ill health)
https://forms.sistc.edu.au/232480027577862
Feedback
Feedback will be supplied through Moodle. Authoritative results will be published on Moodle.
Academic Misconduct
To submit your assessment task, you must indicate that you have read, understood, and complied with, the Sydney International School of Technology and Commerce Academic Integrity and Student Plagiarism policies and procedures.
You must also agree that your work has not been outsourced and is entirely your own except where the work quoted is duly acknowledged. Additionally, you must agree that your work has not been submitted for assessment in any other course or program.
Marking Rubric: Individual Project Report
Performance Levels
Very Poor FNE (0-14%)
Poor F (15-49%)
Satisfactory P (50-64%)
Good C (65-74%)
Very Good D (75-84%)
Excellent HD (85-100%)
Scenario Understanding (Total: 5 marks)
- Concise introduction. (2)
- Clear explanation of database objectives. (1)
- Benefits and Logical assumptions. (1)
- Business rules. (1)
There is little to no understanding of scenario needs, with content that is irrelevant or so poorly tailored that it fails to communicate.
Has limited understanding of the case study, resulting in significant omissions.
Understands the case study but may overlook some key elements or relationships.
Understands the case study well and identifies key entities, attributes, and relationships.
Understands the case study very clearly and identifies key entities, attributes, and relationships.
Demonstrates a thorough understanding of the case study, including its requirements and objectives.
Entity Relationship Diagram (ERD) Design (Total: 5 marks)
There is little to no understanding of ERD design, with content that is irrelevant or so poorly tailored that it fails to communicate.
Creates an unclear or incomplete ERD with significant inaccuracies.
Creates an ERD with some clarity but may miss some details or relationships.
Creates a clear ERD that accurately represents most aspects of the database schema.
Creates a clear ERD that accurately represents the database schema effectively.
Creates a clear, well structured, and comprehensive ERD that accurately represents the database schema.
Correctness of Entities and Relationships (Total: 5 marks)
There is little to no understanding of entities and relationships, with content that is irrelevant or so poorly tailored that it fails to communicate.
Entities and relationships are largely incorrect, missing, or poorly defined.
Many entities or relationships are missing or incorrectly identified. Cardinality is often incorrect or unclear.
Some entities or relationships are incorrectly identified or not clearly named. Cardinality may be inconsistent
Most entities and relationships are correctly identified with minor errors in naming or cardinality.
All entities and relationships are correctly identified and properly named. Relationships are clearly defined with appropriate cardinality and participation
Application of Normalization Principles (Total: 5 marks)
There is little to no understanding of Normalization Principles, with content that is irrelevant or so poorly tailored that it fails to communicate.
Fails to apply normalization principles, resulting in data redundancy and integrity issues.
Applies some aspects of normalization but may lack consistency or overlook key principles.
Applies normalization principles correctly but may have minor issues or omissions.
Applies normalization principles correctly, and deliverables meeting expectations effectively.
Applies normalization effectively, eliminating data redundancy and maintaining data integrity.
Commentary for database tables, primary keys and sample datasets (Total: 5 marks)
There is little to no understanding of database table, with content that is irrelevant or so poorly tailored that it fails to communicate.
Very little has been done or has been of extremely poor quality
The sample tables are ambiguous. At least 3 rows are provided for each field
The commentary of the tables are satisfactory. At least 3 rows are provided for each field.
The commentary of the tables are good. At least 5 rows are provided for each field
The commentary of the tables are exceptional. At least 5 rows are provided for each field
Data Integrity and Constraints. (Total: 5 marks)
There is little to no understanding of Data Integrity and Constraints, with content that is irrelevant or so poorly tailored that it fails to communicate.
Lacks proper implementation of data constraints, resulting in integrity issues.
Implements some data constraints but may lack consistency or overlook key constraints.
Implements data constraints effectively but may have minor issues or omissions.
Implements data constraints meeting expectations effectively.
Implements appropriate data integrity constraints, including primary keys, foreign keys, unique constraints, and check constraints.
Report Presentation (Total: 5 marks)
- Professional report presentation. (3)
- Use of APA referencing. (2)
There is little to no understanding of Report Presentation, with content that is irrelevant or so poorly tailored that it fails to communicate.
Very little has been done or has been of extremely poor quality.
Poor presentation. Inappropriate use of APA referencing.
Somewhat professional presentation. Appropriate use of APA referencing.
Appropriate presentation. Appropriate use of APA referencing.
Professional presentation. Appropriate use of APA referencing.
Self-Reflection (Total: 5 marks)
There is little to no understanding of Self-Reflection, with content that is irrelevant or so poorly tailored that it fails to communicate.
The self-evaluation does not provide a meaningful analysis of personal learning or the development process, with no insight into adaptations made or the effectiveness of the communication strategy.
The self-evaluation lacks depth and critical analysis of personal learning and the development process, providing insufficient detail on how the relational database was adapted to meet the scenario’s needs.
The self-evaluation provides a basic analysis of personal learning and the development process, with limited detail on the impact of adaptations and effectiveness of the communication strategy.
The self-evaluation offers a reasonable analysis of personal learning and the development process but could provide deeper insights into how changes improved the relational database used.
The self-evaluation provides a detailed analysis of personal learning and development, discussing key changes and the effectiveness of strategies used to enhance the relational database.