KL7011 - Advanced Databases Assignment

Assignment Task

Learning Outcomes

The learning outcomes (LOs) for this module are:-

LO1 You will be able to demonstrate critical understanding of the entire data life cycle and classical database engineering processes and approaches, and non-traditional database systems.

LO2 You will be able to demonstrate deep knowledge of key concepts of data warehousing, data analytics, data standards, and data quality.

LO3 You will be able to critically analyse, select, apply and evaluate advanced data modelling, database design, implementation and manipulation methods, techniques and tools to a complex data management problem.

LO4 You will be able to Appraise, analyse, design, develop and evaluate data warehousing and data analytics solutions using Oracle database system.

LO5 You will be able to develop critical awareness of the responsibilities of database developer with respect to professional, legal, security and ethical issues individually or as part of a team

Assessment Background and Scenario

This assessment is based on the scenario TRAVELNORTH of a fictitious transport company.  The company wants to develop an information system to support its activities. The details of the scenario are provided in the Appendix

Assignment Questions

Part 1

This part is based on the TRAVELNORTH scenario as described in the Appendix.

• Using entity-relationship (ER) OR enhanced entity-relationship (EER) modelling, produce a conceptual design for the database to support the TRAVELNORTH business activities.

• Convert the ER / EER diagram from Part 1(A) to produce a logical relational schema using ER / EER to relational mapping.

Points to consider while preparing your solution to this part follows.

Regarding Part 1 (A):

• The ER/EER diagram should show all relevant entity types, relationship types, key attributes, primary keys, and structural constraints.
• Not all keys are identified/mentioned in the scenario, so you are required to identify/devise appropriate primary keys for all the entity types.
• The ER/EER diagram must not show/include any foreign keys or any such attributes that represent foreign keys as these are logical and not conceptual concepts.

Regarding Part 1 (B):

• As part of the mapping process, for each relation, you should identify appropriate primary keys as well as foreign keys (if applicable). Furthermore, you need to make sure your relations obtained from mapping your ER/EER diagram are in the 3^rd normal form.
• You need to research, choose and justify an appropriate naming convention for relations, attributes, and keys. You need to use your chosen convention consistently throughout your assignment.
• You need to document all elements of your logical relational schema in a data dictionary, in a tabular form and must be presented as text rather than an image or picture. The data dictionary should include all relevant names, descriptions and constraints.

(C) Based on your logical design from Part 1 (B) and the information available in the scenario, produce an SQL script file using Oracle 11g/12c/higher.

Points to consider while preparing your solution to this part:

• Provide the SQL DDL code as text (e.g., CREATE TABLE, ALTER TABLE, etc.) statements for creating all the relations from Part 1 (B).
• Provide the output from running the script file in a live Oracle 11g/12c/higher session (e.g., using SPOOL, copy and paste, screenshots, etc.). If output is missing,  will be deducted from the above 10 marks. If output is incomplete or inadequate or misleading, then adequate marks up to a max of 2 will be deducted.
• You should use relational features from the SQL92 standard in Oracle 11g/12c/higher for constructing your data structures/tables, including appropriate primary and foreign keys.
• You should aim for a high degree of reliability in the data with use of as many constraints as possible, e.g., check constraints on various columns (e.g., the particular format of primary key values, positive physical values as described in the relevant lecture on integrity constraints)

Part 2 

This part is based on your answer/solution to Part 1, i.e., design and implementation of the database for the TRAVELNORTH scenario.

(A) Populate the database with some sample data (e.g., you should generate your own dummy data and load it into the TRAVELNORTH database, consider 5 to 10 rows for each table and enough data to see meaningful output for the queries below).

(B) Answer the following queries (retrievals) using Relational Algebra and SQL.

• Display details of schedules for travelling between Newcastle and Windermere with five or more available seats in the next ten days.

• Display details of the travel agent(s) with the most ticket sales in the month of October 2022.

Part 3 

This part is based on your answer/solution to Part 1 (A), i.e., conceptual design of the database for the TRAVELNORTH scenario.

(A)  Choose and justify what aspects of TRAVELNORTH conceptual design would be better off if implemented using object-relational database; then provide logical design and implementation of the subset of the TRAVELNORTH using ER/EER to object-relational mapping and object-relational features of Oracle Database System (Kannan); populate the object-tables with sample data and demonstrate your choice of design and implementation by running two complex queries on your object-tables.

(B) Analyse the conceptual database design from  Part 1 (A) and the TRAVELNORTH scenario in the Appendix and propose what aspects of the TRAVELNORTH database would benefit from incorporating NoSQL Database concepts. Illustrate your answer with code from a representative code from the NoSQL Database implementation.

Part 4

Consider the TRAVELNORTH scenario in the Appendix. Produce a report for the managing director of the TRAVELNORTH company – elaborating on professional, legal, ethical and security issues that need to be considered and make recommendations that you think are appropriate for TRAVELNORTH.

This IT and Computer Science has been solved by our PhD Experts at My Uni Paper. Our Assignment Writing Experts are efficient in providing a fresh solution to this question. We are serving more than 10000+ Students in Australia, the UK, and the US by helping them to score HD in their academics. Our Experts are well-trained to follow all marking rubrics and referencing styles.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.