Reference no: EM133034522

KL7011 Advanced Databases - Northumbria University

Learning Outcome 1: 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.

Learning Outcome 2: You will be able to demonstrate deep knowledge of key concepts of data warehousing, data analytics, data standards, and data quality.

Learning Outcome 3: 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.

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

Learning Outcome 5: 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

- You are expected to produce a word-processed answer to this assignment. Please use Arial font and a font size of 12 for text. For SQL code and output, you can use courier new font and a minimum size of 10, which preserves SQL format and layout. Where necessary, screenshots of SQL output may be used instead of plain text.

- You are required to use the Harvard Style of referencing and citation. The "Cite them right" guide is recommended for referencing and citation (Pears and Shields, 2008) which should be followed throughout your answer especially Part 3. Please do not include references to lecture notes.

You are advised to read the guidance for students regarding assessment policies (Northumbria, 2020).

Assessment Background and Scenario

This assessment is based on the scenario MOVEHOME of a fictitious real estate group of estate agents. The group 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 MOVEHOME scenario as described in the Appendix.

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

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

Notes for Part 1 (A & B)
• The ER/EER diagram should show all relevant entity types, relationship types, attributes, primary keys, and structural constraints. Note that not all keys are identified/mentioned in the scenario, so you are required to identify/devise appropriate primary keys for all the entity types. Your 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.
• 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 3rd normal form.

(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.

Part 2

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

(A) Populate the database with some sample data (e.g., you should generate your own dummy data and load it into the MOVIEHOME 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.

q1) Display details of semi-detached properties for sale having at least three bedrooms in the Jesmond area of Newcastle upon Tyne that were added to the system in the last 14 days.

q2) Display details of properties sold in Newcastle, Sunderland, Gateshead or Durham for £157,000 to £279,000 in the years 2019 or 2020.

Part 3

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

(A) Choose and justify what aspects of MOVEHOME conceptual design would be better off if implemented using object-relational database; then provide logical design and implementation of the subset of the MOVEHOME 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 MOVEHOME scenario in the Appendix and propose what aspects of the MOVEHOME database would benefit from incorporating NoSQL Database concepts. Illustrate your answer with code from a representative code from NoSQL Database implementation.

Part 4

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

The report should be concise and comprehensive and in the region of 800-900 words. You should use Harvard style of citation and referencing by following the guidelines in Pears and Shields (2008).

Attachment:- Advanced Databases.rar