KL7011: Advanced Databases Assessment

Assignment Overview

Part 1 (A) – Conceptual Design 

Task:

Using ER or Enhanced ER (EER) modelling, produce a conceptual design of the database for supporting the information system of the NORTHERNTOURS company.

Assumptions:

Provide below any assumptions made while selecting entity and relationship types.

Discussion on Conceptual Design:

Provide below a brief justification explaining why your ERD/EERD is suitable for the NORTHERNTOURS scenario.
(Up to 3 marks deducted if inadequate.)

Part 1 (B) – Logical Relational Schema (12 marks)

Task:

Convert the ER/EER diagram from Part 1(A) into a logical relational design using ER/EER to relational mapping.

Naming Conventions Statement:

Provide a short explanation of the naming conventions used and justify your choices.

Normalisation Statement (3NF):

Confirm whether or not all relations in the logical schema are in 3rd Normal Form (3NF).

Part 1 (C) – SQL DDL Implementation (8 marks)

Task:

Based on your logical design, create an SQL script using Oracle 11g/12c/higher.

Part 2 (A) – Data Population (7 marks)

Task:

Populate a subset of relations with 5–10 rows of sample data for each table to support the queries in Part 2(B).

Part 2 (B) – Relational Algebra + SQL Queries (8 marks)

Query 1:

Display details of schedules travelling between Newcastle and Berwick-upon-Tweed with seven or more available seats in the next 14 days.

Query 2:

Display details of the travel agent(s) with the most tickets sold between 15 September and 15 October 2025.

Part 3 (A) – Object-Relational Implementation (20 marks)

Task:

Choose a subset of the NORTHERNTOURS conceptual design that benefits from object-relational features, justify your choice, map it using object-relational mapping, implement it, populate it, and run two complex queries.

1. Choice and Justification (2 marks):

Provide justification for selecting the subset for object-relational implementation.

2. Logical Design – Object-Relational Mapping (2 marks):

Provide the logical design for your chosen subset.

3. SQL Code for Object-Relational Implementation (8 marks):

Provide SQL code as TEXT ONLY, with screenshots of output.

4. SQL Code for Populating Object Tables (4 marks):

Provide SQL INSERT code as TEXT, with screenshots of results.

5. Two Complex Object-Relational Queries (4 marks):

Provide SQL code (TEXT) + screenshots of results.
Complex queries should include joins and aggregations.

Part 3 (B) – NoSQL Component (15 marks)

Task:

Analyse Part 1(A) and propose which aspects of NORTHERNTOURS would benefit from NoSQL. Discuss design choices, provide representative NoSQL code, populate it, and demonstrate sample queries.

1. Subset Choice & Justification (2 marks):

Provide justification of which part of the database benefits from NoSQL.

2. NoSQL Design Choices Discussion (3 marks):

Discuss modelling choices (document model, key-value, graph, etc.)

3. NoSQL Code + Output (10 marks):

Provide:

• NoSQL implementation code (TEXT)

• Data population

• Sample queries

• Outputs (screenshots/images)

Part 4 (10 marks)

Task:

Write a 900–1000 word report covering:

• Sustainability issues

• Professional, legal, ethical concerns

• Security issues

• Diversity, inclusion, cultural and societal considerations

• Environmental impacts

• Risk management and commercial risks

• Recommendations

Brief Summary of Assessment Requirements

The assessment for the NORTHERNTOURS database project is divided into four major parts, each evaluating a different component of advanced database design and implementation.

Part 1 – Database Design & SQL Implementation (40 marks)

Part 1(A): Conceptual Design

• Create an ER/EER diagram to model the NORTHERNTOURS information system.

• State all assumptions made during modelling.

• Provide a discussion/justification explaining why the conceptual model is suitable.

Part 1(B): Logical Relational Schema

• Convert the ER/EER diagram into a logical relational schema.

• Provide a Data Dictionary.

• Explain naming conventions.

• Confirm whether the schema is in Third Normal Form (3NF).

Part 1(C): SQL DDL

• Create an SQL DDL script in Oracle to implement the logical design.

• Provide execution output via screenshots.

Part 2 – Data Population & Querying (15 marks)

Part 2(A): Populate Tables

• Insert 5–10 rows per table to support query execution.

Part 2(B): Queries

• Write relational algebra expressions and SQL queries for:

  1. Schedules between Newcastle and Berwick-upon-Tweed with seat requirements.

  2. Travel agents with the highest ticket sales within a defined date range.

• Provide screenshots of SQL output.

Part 3 – Object-Relational & NoSQL Design 

Part 3(A): Object-Relational Implementation

• Select a subset of the model suitable for object-relational features.

• Provide:

  • Justification

  • Object-relational mapping

  • SQL object types & tables

  • Sample data

  • Two complex queries (joins + aggregations)

Part 3(B): NoSQL Implementation

• Identify which parts of the system benefit from NoSQL.

• Discuss design choices (document, key-value, or graph).

• Provide:

  • NoSQL implementation code

  • Data population

  • Example queries with screenshots

Part 4 – Professional, Ethical & Sustainability Report 

Write a 900–1000 word academic report analysing:

• Sustainability & environmental concerns

• Professional, legal, ethical issues

• Security risks

• Diversity, inclusion & cultural considerations

• Risk management & commercial risks

• Recommendations for NORTHERNTOURS

Harvard referencing is required.

How the Academic Mentor Guided the Student 

The academic mentor used a structured, scaffolded approach to help the student understand and complete each section effectively. The guidance process focused on breaking complex tasks into manageable steps, ensuring clarity and accuracy.

1. Understanding the Scenario and Requirements

The mentor first helped the student analyse the NORTHERNTOURS scenario to:

• Identify key business processes

• Recognise entities such as customers, schedules, tickets, travel agents, buses, locations

• Understand business rules such as seat availability, booking periods, and ticket sales

This foundational understanding enabled the student to design meaningful models.

2. Part 1(A): Conceptual Design (ER/EER)

Mentor Guidance:

• Identify all entities and their attributes.

• Determine relationships, cardinalities, and constraints.

• Use EER features only when justified (e.g., subclasses or multivalued attributes).

• Document assumptions to clarify modelling decisions.

• Write a clear justification demonstrating the model’s fit.

Outcome:

The student produced a complete ER/EER diagram along with well-stated assumptions and a clear rationale, demonstrating conceptual clarity.

3. Part 1(B): Logical Schema + Data Dictionary + Normalisation

Mentor Guidance:

• Convert each entity and relationship into tables, identifying:

  • Primary keys

  • Foreign keys

  • Composite keys (if any)

• Prepare a Data Dictionary listing fields, data types, keys and constraints.

• Follow consistent naming conventions (e.g., snake_case or PascalCase).

• Verify 3NF compliance through dependency checks.

Outcome:

The student delivered a structured relational schema, ensuring correctness and normalisation compliance.

4. Part 1(C): SQL DDL Implementation

Mentor Guidance:

• Convert schema into CREATE TABLE statements with proper constraint syntax.

• Use Oracle-specific data types (VARCHAR2, NUMBER, DATE).

• Test commands and troubleshoot errors (missing commas, incorrect FK references).

• Capture screenshots of successful execution.

Outcome:

The student produced a clean, error-free SQL DDL script and execution logs.

5. Part 2(A): Data Population

Mentor Guidance:

• Create realistic dummy data (5–10 rows per table).

• Maintain referential integrity (e.g., valid foreign keys).

• Insert in logical order (parent tables → child tables).

Outcome:

The student populated each table with valid data and provided screenshots of successful insertions.

6. Part 2(B): Relational Algebra & SQL Queries

Mentor Guidance:

For each query:

• Interpret requirements (date ranges, seat counts, destinations).

• Build relational algebra expressions first to clarify logic.

• Translate into SQL using:

  • WHERE clauses

  • JOINS

  • GROUP BY and ORDER BY (for ticket counts)

Outcome:

The student produced correct relational algebra expressions, SQL queries, and output screenshots.

7. Part 3(A): Object-Relational Implementation

Mentor Guidance:

• Identify data suitable for object types (e.g., customer addresses, location objects, complex ticket objects).

• Map these into Oracle object-relational structures:

  • Object types

  • Nested tables

  • VARRAYs

• Insert sample data into object tables.

• Construct two complex queries involving joins + aggregations.

Outcome:

The student successfully demonstrated object-relational modelling and executed advanced queries.

8. Part 3(B): NoSQL Design and Implementation

Mentor Guidance:

• Identify parts where NoSQL provides advantages (e.g., flexible schedules, unstructured feedback).

• Select a suitable NoSQL model (document store recommended).

• Demonstrate sample NoSQL code (MongoDB-like structure).

• Provide screenshots of inserted documents and query results.

Outcome:

The student implemented a NoSQL subset and justified the design choices effectively.

9. Part 4: Professional, Ethical & Sustainability Report

Mentor Guidance:

• Structure the report into clear sections aligned with the marking rubric.

• Incorporate:

  • Ethical guidelines

  • GDPR/legal compliance

  • Environmental and sustainability considerations

  • Security best practices

  • Inclusion and diversity policies

  • Commercial and operational risk factors

• Use Harvard referencing correctly.

Outcome:

The student produced a well-referenced, academically structured 900–1000 word report.

Final Outcome & Learning Objectives Achieved

By completing the assessment with step-by-step mentoring, the student achieved the following key learning outcomes:

• Understanding and applying ER/EER modelling
• Converting conceptual models into logical schemas
• Ensuring data integrity through normalisation
• Implementing SQL DDL using industry-standard Oracle syntax
• Populating databases and writing both relational algebra & SQL queries
• Practising advanced object-relational database features
• Designing and implementing NoSQL structures
• Analysing ethical, legal, sustainability, and security factors in database systems
• Producing an industry-aligned professional report

Boost Your Grades with Expert-Crafted Academic Support

Looking to better understand how to approach your assignment? Our sample solution is a valuable reference designed to guide your learning, clarify complex concepts, and help you structure your own work with confidence. You can download the sample to see the formatting, flow, and level of detail expected in high-quality academic submissions.

However, it’s important to use the sample strictly for reference purposes only. Submitting any part of it as your own work may lead to plagiarism penalties from your institution. Every university has strict academic integrity rules, and your submission must always be original.

If you need a fully customised, plagiarism-free assignment tailored to your topic and requirements, our team of professional academic writers is here to help. We deliver fresh solutions written from scratch, ensuring:

• 100% original content with plagiarism report

• Accurate, well-researched answers prepared by subject experts

• Proper formatting, citations, and adherence to assessment criteria

• On-time delivery with unlimited revisions

• Complete confidentiality and student-friendly pricing

Get the support you need without risking your academic integrity.

Download the Sample or Order a Fresh Solution

Download Sample Solution      Order Fresh Assignment