ITO4132 : Introduction to Databases Assignment 2

Assignment Tasks

Task 1: Relational Database Queries Relational Algebra (6 Marks)

• Submission File: T1-cr-ra.pdf

• Format: Answers must be written in MS Word or Google Docs, then exported as PDF.

• Requirement: Write relational algebra operations for each query, ensuring efficiency. Copy and paste each question into your answer document before solving.

Relational Algebra Symbols for Reference:

• Project: π

• Select: σ

• Join: ⨝

• Intersect: ⋂

• Union: ⋃

• Minus: –

Example
Question: List the Team ID and Team name for all teams involved with the carnival held on 1st Feb 2023.
Answer:
R = π team_id, team_name (σ carn_date = "01-Feb-2023" TEAM)
OR
R = π team_id, team_name (R1)

Questions:

(a) List the carnival date, carnival name, and location for all carnivals which have an event type code of "10K" .
Marks: 1

(b) List competitor number, first and last name, and date of birth for any competitor who is registered by City Run but has not yet registered for any events.
Marks: 2

(c) List competitor number, first and last name, and their emergency contact’s first name, last name, and phone number for all competitors who registered in the carnival named "CR Summer Series Sydney 2023" .
Marks: 3

Task 2: Data Insert (10 Marks)

• Script File: T2-cr-insert.sql

• Instruction: Load selected tables with your own test data using the provided script.

Minimum Data Requirements:

• 20 ENTRIES

• 5 TEAMS

Rules:

1. Treat all inserted data as a single transaction.

2. Use hardcoded primary key values (below 100, no sequences).

3. Entries and teams must be spread across at least 3 completed carnivals , each with at least 2 different events .

4. Do not modify or overwrite schema-provided data.

Marks: 10

Task 3: Data Manipulation (10 Marks)

• Script File: T3-cr-dml.sql

• Requirement: Manage transactions properly and use sequences for primary keys. No hardcoding allowed.

Subtasks:

(a) Create sequences for the following tables:

• COMPETITOR

• ENTRY

• TEAM
  Sequences must begin at 100 and increment by 1.
  Marks: 1

(b) Register Brigid Radcliffe for the "CR Summer Series Melbourne 2024" in the "21.1 Km Half Marathon". Treat this as a single transaction.
Marks: 3

(c) Update Brigid Radcliffe’s entry: downgrade to "10 Km Run" and create a new team "Kenya Speedstars" supporting Beyond Blue . Single transaction.
Marks: 3

(d) Withdraw Brigid Radcliffe and disband "Kenya Speedstars". Treat as a single transaction.
Marks: 3

Task 4: SQL Queries (46 Marks)

• Script File: T4-cr-queries.sql

• Note: Test data must produce output. Queries showing "no rows selected" lose 50% marks.

Subtasks:

(a) List runners registered with a Gmail email in carnivals organised by City Run. Output must include: carnival date, carnival name, event description, and fullname (first + last). Order by date and fullname.
Marks: 5

(b) List runners who supported a charity as an individual in the "42.2 km Marathon". Output must include: carnival date, runner name, charity details, and event description. Ordered by date → charity → runner.
Marks: 5

(c) List the number of events completed by each competitor over the last two calendar years . Output includes competitor details, events per year, and total. Show “Completed No Runs” where applicable.
Marks: 8

(d) List total participants in the "5 Km Run" for each 2023 carnival. Columns: carnival date, carnival name, total_entries5Km. Order by entries (desc) → date (asc).
Marks: 8

(e) List events with no entries for carnivals run by City Run. Columns: carnival date, carnival name, event description. Order by event description.
Marks: 8

(f) List team details for carnivals where the most popular team name(s) have been used. Output includes team name, carnival date, team leader details, and team size. Order by team name → carnival date.
Marks: 12

Task 5: Design Modifications (13 Marks)

• Script File: T5-cr-mods.sql

• Database: Live database after Tasks 2–4

Subtasks:

(a) Modify schema to prevent a competitor from enrolling in two events in the same carnival .
Marks: 2

(b) Add an attribute for elapsed time (finish - start) for runners, stored as minutes (to 2 decimal places). Initialise with existing data where possible.
Marks: 4

(c) Modify schema to allow competitors to nominate multiple emergency contacts .
Marks: 7

Task 6: Non-Relational Database Queries – MongoDB (10 Marks)

Subtasks:

(a) Write SQL in T6a-cr-json.sql to generate JSON documents for each carnival event. Document ID = ddmmyyyy_eventcode.
Marks: 4

(b) Insert documents into MongoDB collection events .
Marks: 1

(c) List all inserted events.
Marks: 1

(d) List all 10K events with >2 participants. Show carnival date, location, competitor count, and competitor details.
Marks: 2

(e) Correct the location for the carnival on 01-Feb-2023 to Lake Gillawarna, Georges Hill, 2198 . Verify with db.find
Marks: 2

Submission Requirements

You must submit seven independent files (not zipped):

• T1-cr-ra.pdf
• T2-cr-insert.sql
• T3-cr-dml.sql
• T4-cr-queries.sql
• T5-cr-mods.sql
• T6a-cr-json.sql
• T6b-cr-mongo.mongodb.js

Important Notes:

• Add comments for the marker at the top of each script.

• Push all files to FIT GitLab with minimum two commits per file.

• Submit correctly via Moodle (mandatory student declaration).

• Late Penalty: –10 marks per 24 hours.

• Double-check uploaded files before final submission.

Brief summary of the assessment (key pointers)

• Purpose: Build, test and modify a relational race-management database (City Run) and produce a set of SQL / JSON deliverables that demonstrate relational algebra, DML/DDL, transactions, schema design changes and a migration/export to MongoDB.

• Deliverables (7 files): T1-cr-ra.pdf , T2-cr-insert.sql , T3-cr-dml.sql , T4-cr-queries.sql , T5-cr-mods.sql , T6a-cr-json.sql , T6b-cr-mongo.mongodb.js .

• Core tasks:

  • Task 1: express queries in relational algebra (show efficiencies).

  • Task 2: insert test data (min. 20 ENTRIES, 5 TEAMS) — single transaction, hardcoded PKs <100>

  • Task 3: transactional DML using sequences (start at 100) + competitor lifecycle (register, downgrade, withdraw).

  • Task 4: SQL reporting queries (aggregation, joins, date logic, special outputs like “Completed No Runs”) — must produce rows using your test data.

  • Task 5: schema modifications (prevent duplicate event enrolment in same carnival; add elapsed_time column and initialize it; allow multiple emergency contacts).

  • Task 6: JSON export + MongoDB : create event-based JSON docs, insert into events collection, query and update documents.

• Grading highlights: queries must return results with your test data; follow transaction/sequence rules; add comments for markers; push to GitLab (≥2 commits/file); submit via Moodle; late penalty applies.

How the Academic mentor guided the student — step-by-step process (brief)

1. Kick-off & scoping

   • Reviewed the brief together and identified must-have outputs and common pitfalls (e.g., hardcoding vs sequences, requirement that Task 2 keys <100>

   • Created a short checklist of the seven deliverables and grading risks (no rows selected, missing comments, wrong PK usage).

2. Designing test data (Task 2)

   • Mentor helped design a minimal but robust test dataset: 20 ENTRY rows and 5 TEAM rows distributed across ≥3 completed carnivals and at least two events per carnival.

   • Advised using realistic but consistent values (unique phone for Brigid Radcliffe), and documented the transaction block in T2-cr-insert.sql .

   • Emphasized that Task 2 is the only place to hardcode PKs under 100 — all other inserts must use sequences.

3. Relational algebra answers (Task 1)

   • Walked through how to convert each question into concise relational algebra expressions; stressed clarity and efficiency (push σ before π where selective, avoid unnecessary joins).

   • Reviewed symbol use (π, σ, ⨝, ⋂, ⋃, −) and asked student to paste the question text above each answer in T1-cr-ra.pdf .

4. Sequences & transaction patterns (Task 3)

   • Mentor demonstrated creating sequences that start at 100 and increment by 1 (one per table), and wrapping multi-step changes in explicit transactions with rollback on error.

   • For Brigid’s lifecycle (register → downgrade/create team → withdraw/disband team): modeled each step as a single transaction and taught use of RETURNING / currval to link generated keys.

5. Developing and testing SQL queries (Task 4)

• Broke Task 4 into small sub-problems and sketched sample query patterns (e.g., LIKE '%@gmail.com' joins to event/carnival tables; use CASE WHEN or COALESCE to print “Completed No Runs”).
• Emphasized testing iteratively: run each query against the test data, then expand or modify test data if a correct query returned zero rows.
• Taught best practices for date-aware logic (compute previous two calendar years dynamically using date functions rather than hardcoding).

1. Schema change design & implementation (Task 5)

• For “prevent enrolling in two events in same carnival”: mentor recommended adding a unique composite constraint on (competitor_id, carnival_id) in the ENTRY/registration table (or a unique index on (competitor_id, carnival_id) ), and explained tradeoffs.
• For elapsed_time : guided adding a elapsed_minutes DECIMAL(7,2) column, populated via an UPDATE that computes (finish_time - start_time) expressed in minutes (NULL where times missing), and adding a COMMENT on the column.
• For multiple emergency contacts: designed a normalized approach — create an EMER_CONTACT table and a join table COMP_ETC (or convert single emergency columns into a separate relation) and migrate existing contact values into the new structure inside a transaction.

1. Generating JSON and MongoDB tasks (Task 6)

• Showed how to use SQL JSON functions or string-aggregation to produce documents where _id = ddmmyyyy_eventcode .
• Mentored on exporting SQL output to a JSON file and on mongoimport / JS insertion commands for T6b-cr-mongo.mongodb.js .
• For the correction step: used a single updateMany command to set the location for the specified carnival and then find() to verify.

1. Verification, edge-cases and academic integrity

   • Ran all queries to confirm they return the intended output and adjusted test data where necessary.

   • Inserted comments at the top of each script describing assumptions and test-data provenance for marker transparency.

   • Reinforced plagiarism rules: samples for reference only, and the student’s SQL is original.

2. Packaging, GitLab and Moodle submission

   • Prepared the final checklist: 7 files present, each with marker comments, appropriate commits on GitLab, exported , and final Moodle upload.

   • Advised to test re-download of submitted files to ensure nothing corrupted.

• Outcome & learning objectives covered (brief)
• Outcome achieved

• Student produced the seven required deliverables, each with marker comments and transaction-safe SQL.

• Test data ensured all Task 4 queries returned results (no “no rows selected”).

• Schema changes implemented and populated (elapsed time added and initialized; emergency contacts normalized).

• JSON documents created and inserted into MongoDB; location correction applied and verified.

• Key learning objectives covered

• Translate business questions into relational algebra and efficient SQL.

• Design and insert test data respecting constraints and grading rules.

• Proper use of transactions , sequences , and PK/ FK management.

• Advanced SQL: joins, aggregates, window functions, conditional outputs, and date arithmetic.

• Schema evolution: adding columns, constraints, and normalizing relationships safely.

• Exporting/transforming relational data into JSON and working with MongoDB 

• Best practices for reproducible submissions: comments, Git history and validated Moodle upload.

Assessment Requirements – Brief Summary

This assessment focused on designing, testing, and modifying a City Run race-management database using relational and non-relational database techniques. Students were required to demonstrate proficiency in relational algebra, SQL DML/DDL, schema evolution, and MongoDB integration.

• Core Tasks :

  1. Relational algebra queries with efficiency.

  2. Test data insertions (≥20 entries, ≥5 teams across ≥3 carnivals).

  3. Transactional DML with sequences (competitor lifecycle).

  4. SQL reporting queries (joins, aggregation, conditional outputs).

  5. Schema modifications (constraints, new attributes, normalization).

  6. JSON generation, MongoDB insertions, queries, and updates.

• Grading highlights: Queries must return data, schema rules must be followed, files must include marker comments, GitLab commits, and Moodle submission.

Mentor-Guided Step-by-Step Approach

Step 1: Kick-off and Scoping

• The mentor began by carefully reviewing the assessment brief with the student.

• A checklist of deliverables and risks (e.g., no “no rows selected” outputs, correct sequence usage, comments for marker) was created.

• Clarified the difference between hardcoded keys (Task 2) and sequence-generated keys (Task 3) to avoid common errors.

Step 2: Relational Algebra Queries (Task 1)

• Mentor explained how to map English-language questions into relational algebra operations.

• Showed how to optimize expressions by applying σ (select) before π (project) to reduce data early.

• Student pasted each question into the answer sheet and then wrote corresponding algebra queries.

• Example reviewed: carnival events filtered by event type code “10K” using σ followed by π.

Step 3: Designing Test Data (Task 2)

• Mentor guided the creation of robust but minimal test data:

  • 20 entries, 5 teams, spread across at least 3 carnivals.

  • At least 2 events per carnival.

• Data chosen to ensure Task 4 queries would produce results (e.g., Gmail users, charities, different event distances).

• Insert script was wrapped in a single transaction, with hardcoded PKs <100>

Step 4: Transactional DML with Sequences (Task 3)

• Mentor introduced sequence creation for COMPETITOR, ENTRY, and TEAM starting from 100.

• Guided the student to structure Brigid Radcliffe’s full lifecycle:

  1. Register → 21.1 Km Half Marathon (transaction 1).

  2. Downgrade entry + create team “Kenya Speedstars” (transaction 2).

  3. Withdraw Brigid + disband team (transaction 3).

• Emphasis was on atomic transactions, ensuring rollback if any step failed.

Step 5: Complex SQL Queries (Task 4)

• Mentor helped decompose each query into sub-steps (filters, joins, groupings).

• Examples covered:

  • CASE/COALESCE for “Completed No Runs”.

  • LIKE '%@gmail.com' for email-based filtering.

  • Dynamic year functions to capture “last two calendar years” without hardcoding.

• Student tested queries iteratively with the test data, modifying it where queries initially returned empty sets.

Step 6: Schema Modifications (Task 5)

• Mentor explained schema evolution principles:

  • Unique constraint on (competitor_id, carnival_id) to prevent multiple enrollments.

  • Elapsed time column (DECIMAL(7,2)) with initialization using finish–start times.

  • Emergency contacts normalization: create a new table for multiple contacts, migrate existing values, and enforce referential integrity.

• Scripts were wrapped in transactions and annotated with comments.

Step 7: MongoDB Integration (Task 6)

• Mentor showed how to generate JSON documents from SQL output with 

• Student inserted JSON documents into MongoDB and verified with .

• Queries for filtering 10K events with >2 participants and correcting carnival location were demonstrated.

• Mentor emphasized documenting each step in the file.

Step 8: Packaging and Submission

• Mentor reviewed all deliverables against the submission checklist:

  • Comments added for markers.

  • ≥2 GitLab commits per file.

  • Correct Moodle upload with declaration.

  • Verified downloads to avoid submission corruption.

Final Outcome

• The student successfully submitted all seven deliverables, each meeting format and content requirements.

• Queries produced valid results due to carefully designed test data.

• Schema modifications were implemented and tested.

• JSON export and MongoDB integration were completed with updates verified.

• Academic integrity maintained — student’s work was original, guided but not copied.

Learning Objectives Covered

1. Relational algebra application for query representation.

2. Transaction and sequence management in SQL.

3. Test data design to validate database queries.

4. Advanced SQL skills : joins, aggregates, constraints, conditional outputs, date logic.

5. Schema evolution techniques and normalization.

6. Cross-platform database handling : relational to NoSQL (MongoDB).

7. Professional academic practice : proper documentation, version control, and submission protocol.

Get the Right Guidance for Your Assignment

Looking for reliable help with your academic tasks? You can download the sample solution provided on this page to understand how to structure and approach your assignment effectively. This file is a valuable reference to guide your own writing.

Important Note: The sample solution is strictly for reference purposes. Submitting it as your own work may result in plagiarism issues and academic penalties.

If you want a 100% original, plagiarism-free solution, our team of professional academic writers is here to help. We deliver custom-written assignments tailored to your requirements, ensuring accuracy, proper formatting, and timely delivery.

Why Order a Fresh Assignment?

• Written from scratch, based on your instructions

• Guaranteed plagiarism-free with Turnitin report

• Well-structured, properly cited, and formatted

• Saves time while improving academic confidence

• Delivered before your deadline

Take control of your grades by choosing the option that best suits your needs.

Download Sample Solution
Order Fresh Assignment