Advanced Databases (KL7011) — NORTHERNTOURS Coach Travel Database: EER Design, Oracle Implementation, Object-Relational and NoSQL Extensions
This piece of work addresses a four-part Masters-level assessment in Advanced Databases built around NORTHERNTOURS, a fictitious coach travel operator running services across cities, towns and tourist sites in the North East of England. The company sells tickets through a network of independent travel agents, each currently working from a paper-based sales book, while NORTHERNTOURS itself maintains separate paper records for routes, schedules, seat availability, vehicles and drivers. The brief asks for a single computer-based system capable of replacing both, tracking every agent transaction while giving the company control over ticket issue and seat allocation. Part one covers the conceptual and logical design. An enhanced entity-relationship model was produced covering agents, agent employees, customers, tickets, routes, stops, schedules, vehicles, drivers and the meal provision recorded at each stop, with key attributes, primary keys and full structural constraints shown. Because the scenario does not name identifiers for most entity types, appropriate surrogate and natural keys were devised and justified. The diagram was then mapped to a logical relational schema, normalised to third normal form, with a documented naming convention applied consistently across relations, attributes and keys, and every element recorded in a text-based data dictionary giving names, data types, descriptions and constraints. Part two moves to implementation in Oracle. A full DDL script creates the relations with primary and foreign keys and a substantial set of check constraints — key format patterns, positive seat counts and fare values, date ordering on schedules. Sample data populates the relevant tables, and two retrieval problems are answered twice over, once in relational algebra and once in SQL: schedules between Newcastle and Berwick-upon-Tweed with seven or more seats free in the coming fortnight, and the agent with the highest ticket sales across a defined month. Spooled session output evidences each script running. Part three revisits the conceptual design to argue where object-relational features earn their place — nested route-and-stop structures and composite address and contact types being the clearest candidates — implemented using Oracle object types, VARRAYs and nested tables, and demonstrated through two multi-join aggregate queries. A parallel discussion identifies the schedule and availability workload as a fit for document-oriented NoSQL storage, with representative code and a reasoned account of the denormalisation trade-offs involved. Part four is a report to the managing director covering sustainability, professional, legal, ethical and security obligations, alongside diversity, inclusion, cultural and environmental matters, commercial risk evaluation and mitigation, supported throughout by current literature and published standards.
Read Model Answer →