Advanced Databases, the work was required SQL and Oracle coadding

Instructions : Before accepting the bid, please view all the requirements needed

1) w20048988 was my student ID, using this you have start doing answers in oracle and in question they gave sample example please this sample and answer the questions.

2) I will provide the templet whish contains some sample work, you have to use it as the reference so that we you understand who to write the answers for questions accordingly and delete the sample work which was already there in the templet and have to answer in that templet only.

3) before starting the work please read the MOVEHOME Scenario which was there in the Appendix below

4) This assignment has 4 parts each parts have sub parts and notes for that which clearly explains the work what to do

5) I will attach the assignment brief and templet documents please view the attachments

_________________________________________________________________________________________________

Instructions on Assessment:

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

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 (there are 4 parts, each part contains sub parts)

Part 1 (35 marks) (3 parts: A, B and C)

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.(15 marks)

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

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.(10 marks)

Notes for Part 1 (C): Your submission must include:

• An SQL script file containing appropriate SQL DDL (e.g., CREATE TABLE, ALTER TABLE, etc.) statements for creating all the relations from Part 1 (B).
• 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, 2 marks 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., particular format of primary key values, positive physical values as described in week 4 lecture slides on integrity constraints)

Part 2 (20 marks)

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)

->And Provide below output from running the above SQL code for populating your relational database (e.g., contents from Spool file or screenshots, etc.) (10 marks)

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

(10 marks)

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.

Notes for Part 2 (A & B): Your submission must include:

An SQL script file containing appropriate SQL DML (e.g., INSERT) statements for populating the tables you have created in Part 1 (C).

Relational Algebra expressions for Part 2 (B) (4 marks in total).

An SQL script file containing SQL retrieval (e.g., SELECT) statements for Part 2 (B) (6 marks in total)

An output file for running each of above the TWO script files in a live Oracle 11g/12c/higher session (e.g., using SPOOL, etc.). If any of the output is missing, 2 marks will be deducted from the above. If outputs are incomplete or inadequate or misleading, then adequate marks up to a max of 4 will be deducted.

Part 3 (35 marks)

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.(please follow the below order while answering this)

. Provide below your choice and justification of what aspects (subset) of the MOVEHOME conceptual design from Part 1.A you would like to implement using object relational databases (2 marks)

.Provide below the logical design for your chosen subset using ER/EER to object-relational mapping (2 marks).

.Provide below the SQL code and output for implementing your above logical object-relational design (8 marks).

.Provide below the SQL code and output for populating your above object-relational subset of the MOVEHOME database (4 marks)

.Provide below the SQL code and output for running two complex queries on the object-relational subset of the above MOVEHOME database (4 marks)

(B) Analyze 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. (15 marks)

Part 4 (10 marks)

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.

(10 marks)

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

APPENDIX: MOVEHOME Scenario

MOVEHOME is a group of independent estate agents in the UK, which operates as a single online portal for marketing / advertising properties for sale and renting. Its basic role is to bring owners/landlords and prospective buyers & tenants together.

Customers of MOVEHOME could be landlords, prospective buyers and tenants. Landlords register their properties for sale or renting at individual estate agents. When a customer wishes to view a property, their details are sent to the concerned estate agent, who arranges the viewing with the landlords. It is the job of the particular local branch of the concerned estate agent to communicate with the landlord and prospective buyers or tenant to confirm the viewing.

For each estate agent, the system knows their branch offices, staff at the branches, and which properties are marketed by each estate agent/local branch. The system records data about properties sold and rented out. Details of an estate agent includes name, headquarter, registered address, website, and contact details. For staff of an estate agent, the system needs to hold their id, name, address, contact as well as personal details. Each branch is given a unique code, and the system needs to know its address, contact details, and manager.

When a customer registers to MOVEHOME, the system allocates a unique code, and records their name, address, date of birth, gender, and contact details. When a property is taken up for marketing, the system allocates a unique id, and records its address, location, description, accommodation details (sizes and descriptions of rooms, etc), rental demand or asking price, type, number of rooms, etc.

When a view or visit is arranged for a property to be sold or rented out, the system records information about the customer will be viewing the property, the date and time of the view and what comments the customer made during or after the visit. For a property sale transaction by MOVEHOME, system records information about the customer who bought the property, and selling price, stamp duty and commission paid. Similarly, for a property rent transaction, the system records information about the property rented, rent per month, advance paid, commission, who is landlord and tenant, date tenancy commenced, date tenancy finished.