FIT2094 - Database Design BigRig Movers Assignment 1

Task Overview

This assignment is divided into three main tasks, each focusing on different aspects of database design:

• Task 1 – Conceptual Model (15 marks)

• Task 2 – Normalisation (15 marks)

• Task 3 – Logical Model (55 marks)

Additional marks are allocated for modelling standards, submission requirements, and Git usage (15 marks).

TASK 1: BigRig Movers (BRM) Conceptual Model

Based on the case scenario (pp. 2–3 of assignment brief), prepare a conceptual model using an appropriate diagramming package (e.g., Oracle Data Modeler, MS Visio).

Development Process:

1. Step 1: Identify entities and keys.

2. Step 2: Determine relationships.

3. Step 3: Assign non-key attributes.

Marking Guide (15 marks):

• Entities & Keys (5 marks):

  • Correct entity identification

  • Correct key selection

  • Penalty for unnecessary entities or surrogate/foreign keys at this stage

• Attributes (5 marks):

  • Correct placement of attributes in entities

  • Penalty for extra or misplaced attributes

• Relationships & Cardinality (5 marks):

  • Correct identification of relationships

  • Correct min/max cardinality

  • Penalty for redundant/unnecessary relationships

TASK 2: BigRig Movers (BRM) Normalisation

Using the supplied forms (dispatcher job details and hire rates), carry out normalisation:

• Ensure hire rates depend on truck/trailer combinations and purpose.

• Note: Registration (rego) numbers are not unique identifiers since they can be reassigned to different trucks.

Requirements:

1. Produce normalised relations in 3NF.

2. Provide dependency diagrams at each step.

3. Integrate normalised relations into logical model.

Marking Guide (15 marks):

• Normalisation Process (12 marks):

  • Correct steps (1NF → 2NF → 3NF)

  • Correct attribute synthesis

  • Penalty for unnecessary surrogate keys

• Dependency Diagrams (3 marks):

  • Show correct dependencies and transitions

TASK 3: BigRig Movers (BRM) Logical Model

Develop a logical level design based on:

• Task 1 conceptual model

• Task 2 normalised relations

• Further business rules provided

Business Rules / Additional Details:

1. All employees are assigned a role (no subclasses).

2. Salaries ≤ $999,999.00; must be assigned before adding employee.

3. Pickup/drop-off locations are not decomposed.

4. Quoted price ≠ final job cost; revised cost recorded separately (without altering original quote).

5. A mechanic must certify truck/trailer combinations before job departure.

6. BRM records decommission dates for sold/written-off trucks/trailers.

Logical Model Requirements:

• Use Oracle Data Modeler with Crow’s Foot notation.

• Do not show data types.

• Relations must be in 3NF.

• At least one surrogate key must be added, with justification in assumptions.

• Comment all attributes (in table structure).

• Apply check constraints / lookup tables where appropriate.

• Include legend (with name and ID).

• Minimum six Git pushes required for logical model development.

Schema File:

• Auto-generate from logical model (no manual edits except header and spool commands).

• Include statements.

• Must run error-free in Oracle.

• Include comments for all columns.

• Capture script output with .

Marking Guide (55 marks):

• Relations (10 marks): correct identification, no multivalued attributes.

• Primary Keys (10 marks): accurate assignment.

• Relationships (10 marks): correct identification and cardinalities.

• Surrogate Keys (4 marks): appropriate use and explanation.

• Data Types & Constraints (5 marks): correct Oracle types, null constraints.

• Business Rules (6 marks): integrity requirements implemented.

• Relational Model Generation (5 marks): matches logical model.

• Schema File (5 marks): error-free execution, correct comments.

Submission & Git Requirements

General [15 Marks]

• Modelling Standards (5 marks):

  • Use correct notation (no data types on logical model).

  • Include model legend with name & ID.

  • Follow submission format.

• Git Usage (10 marks):

  • Minimum 12 pushes showing development history:

    • 3 pushes for conceptual model

    • 3 pushes for normalisation

    • 6 pushes for logical model

  • Use meaningful commit messages.

  • Correct Git author details.

Academic Integrity

• Must comply with University Academic Integrity Policy.

• Generative AI use allowed only for design support (must be acknowledged).

• Plagiarism, falsification, contract cheating, or resubmission without approval are breaches.

• Students may be interviewed to defend their submission.

BigRig Movers Database Assignment

Task 1 – Conceptual Model [15 Marks]

1. Identification of Entities and Keys [5 Marks]

• Marks awarded for each correct entity identified

• Penalty for unnecessary entities included

• Marks awarded for each correct key selected

• Penalty for surrogate or foreign keys added

2. Attributes [5 Marks]

• Marks awarded for each necessary attribute identified

• Penalty for extra attributes included

• Penalty for placement of attribute in incorrect entity

3. Relationships and Cardinality [5 Marks]

• Marks awarded for each correct relationship identified

• Penalty for unnecessary relationships included

• Penalty for redundant relationships included

• Marks awarded for correct minimum and maximum cardinality for every correct relationship

Task 2 – Normalisation [15 Marks]

• Normalisation [12 Marks]

  • Marks awarded for each correct normalisation step

  • Marks awarded for correct attribute synthesis

  • Penalty for additional attributes or surrogate keys added during normalisation

• Dependency Diagrams [3 Marks]

  • Marks awarded for each correct dependency depicted within normalisation

• Mapping to Logical Model

  • Penalty for incorrect mapping of each relation to logical model

Task 3 – Logical Model [55 Marks]

1. Relations [10 Marks]

• Marks awarded for each required relation and its attributes identified

• Penalty for extra relations included

• Penalty for placement of attribute in incorrect relation

• Penalty for multivalued attributes included

2. Primary Keys [10 Marks]

• Marks awarded for each correct assignment of a primary key

3. Relationships [10 Marks]

• Marks awarded for each required relationship identified

• Penalty for each incorrect minimum and maximum cardinality

• Penalty for unnecessary or redundant relationships included

4. Surrogate Key [4 Marks]

• Marks awarded for creation of at least one appropriate surrogate key with explanation

• Marks awarded for creation of unique constraint(s) to protect natural keys

5. Attribute Data Types [5 Marks]

• Marks awarded for each correctly identified Oracle data type

• Marks awarded for correct null constraints based on business rules

6. Business Rules [6 Marks]

• Marks awarded for each correctly identified integrity requirement to implement business rules

7. Schema Generation [10 Marks]

• Relational Model Generation [5 Marks]

  • Correct generation of relational model from submitted logical model

  • Must match the logical model submitted

• SQL Schema File [5 Marks]

  • Correct generation of SQL schema file from logical model

  • Penalty for errors in Oracle execution

  • Penalty for missing column comments

Brief summary of assessment requirements 

Project: FIT2094 — BigRig Movers (BRM)
Deliverables:

• Task 1 (Conceptual model) — Conceptual E-R model (entities, keys, relationships, non-key attributes).

• Task 2 (Normalisation) — 1NF → 2NF → 3NF with dependency diagrams; integrate resulting relations.

• Task 3 (Logical model) — Oracle Data Modeler logical model (Crow’s Foot notation) in 3NF, with at least one surrogate key, attribute comments, check/look-up constraints, legend (name & ID), and generated SQL schema (brm_schema.sql) that runs in Oracle.

• Git history showing development (minimum pushes: Conceptual 3, Normalisation 3, Logical 6 — total 12), with meaningful commits and correct author details.

• Assumptions + justification (esp. surrogate key choice).

• Script execution log captured with SPOOL.

Key marking focus: correct entities/keys/attributes, correct relationship cardinalities, correct normalization & dependency diagrams, relations in 3NF, primary & surrogate keys, constraints and comments, schema generation without errors, modelling standards, and Git usage.

Key pointers to cover in your submission 

• Conceptual model (Task 1):

  • Identify only necessary entities (no unnecessary entities).

  • Select correct candidate keys (do not add surrogate/foreign keys at conceptual stage).

  • Place attributes in correct entities and avoid multivalued attributes.

  • Define correct relationships with accurate min/max cardinalities.

• Normalisation (Task 2):

  • Show functional dependencies and stepwise 1NF → 2NF → 3NF transformations.

  • Provide dependency diagrams for each step.

  • Ensure hire-rate dependency modelled on (truck, trailer, purpose).

  • Do not introduce unnecessary surrogate keys during normalisation.

• Logical model (Task 3):

  • Map normalized relations to logical tables consistent with conceptual model.

  • Enforce primary keys and foreign keys; add at least one well-justified surrogate key.

  • Apply check constraints / lookup tables (e.g., role codes, salary ≤ 999999).

  • Add attribute comments in table definitions.

  • Include legend with student name & ID; Crow’s Foot notation; do NOT show data types on logical diagram.

  • Generate brm_schema.sql with DROP statements, column comments, and spool the run output — script must execute cleanly in Oracle.

• General & Git:

  • Use industry notation and clear relationship labels.

  • Keep a clear commit history: descriptive messages, staged development steps.

  • Acknowledge (and document) any GenAI assistance if used.

Mentor-guided process

Below is the stepwise workflow a mentor would guide a student through, with the rationale for each step and brief action items.

1. Kickoff & brief analysis

• Goal: Ensure student fully understands scope, deliverables, marking rubric and constraints (e.g., rego not unique, salary ≤ $999,999).

• Action: Read pages 2–3 carefully; highlight business rules and special notes.

2. Research & data capture

• Goal: Extract all business entities and attributes from the brief and supplied forms.

• Action: Create a raw data inventory spreadsheet (candidate entities, attributes, sample records).

3. Conceptual modelling (3 incremental Git pushes)

Push 1 — Entities & keys: Mentor-run workshop to identify entities (e.g., Job, Customer, Truck, Trailer, Employee, Role, MechanicCertification, Quote, JobCosting, HireRate, Purpose). Commit message:

4. Normalisation (3 pushes)

Stepwise normalisation: Convert supplied dispatcher forms into relations. Demonstrate 1NF → 2NF → 3NF with dependency diagrams.

5. Logical modelling in Oracle Data Modeler (6 pushes)

Design decisions mentor suggests:

 

Create surrogate PK for entities with composite natural keys or long composite keys (e.g., ComboID for Truck-Trailer combo)  include an assumptions note explaining need and unique constraint to protect natural key.

6. Schema generation & testing

• Action: Export SQL (no extra Data Modeler export options), add header comments with student details and spool commands, run SQL in Oracle, fix errors, ensure comments present for each column, save execution log (spool output) and screenshot.

7. Final review, documentation & interview prep

• Action: Prepare a short assumptions document (surrogate key justification, design choices), create a checklist confirming all rubric items, assemble submission package and ensure Git history meets push counts. Mentor rehearses likely interview questions with the student (e.g., why chosen PKs, how hire rate relates to combo & purpose, why rego not PK, explain revised costing design).

Final outcome (what is submitted)

A complete, exam-ready package:

• Conceptual model diagram file (visio/ERD)

• Normalisation write-up + dependency diagrams (1NF→3NF)

• Oracle Data Modeler logical model file  (Crow’s Foot)

• Generated SQL schema file (with DROP statements, column comments, spool commands) and oracle run output log

• Assumptions & surrogate key justification document

• Git repository showing ≥12 pushes with meaningful commit messages and author info

• Submission-ready ZIP (if allowed) or direct files per brief

Learning objectives demonstrated

• Apply conceptual modelling to extract entities, keys and relationships from a case study.

• Perform correct normalization and dependency analysis to reach 3NF.

• Map normalized relations to a logical model using Oracle Data Modeler (Crow’s Foot).

• Implement business rules as constraints (check, unique, nullability) and add attribute comments.

• Generate and validate SQL schema and demonstrate working schema execution in Oracle.

• Maintain professional Git workflow documenting development history.

• Prepare to defend design choices and demonstrate academic integrity (documenting any GenAI assistance).

Get This Assignment Solution 

Looking for guidance on how to structure your assignment? You can download the available sample solution to understand the approach, formatting, and depth of analysis required. This sample is a great reference point, but it is strictly for educational and guidance purposes only.

Important Note: Submitting this sample as your own work can lead to plagiarism issues and academic penalties. Universities expect original submissions tailored to your specific instructions.

That’s why many students choose to order a fresh, plagiarism-free, and custom-written solution prepared by our team of professional academic writers. By doing so, you not only avoid plagiarism risks but also receive:

• 100% original content, tailored to your requirements

• Well-researched assignments from subject matter experts

• Guaranteed plagiarism report for peace of mind

• On-time delivery to meet your deadlines