An Entity-Relationship (ER) Diagram Study Assignment

Description/Paper Instructions

An Entity-Relationship (ER) Diagram Study Assignment

Module Learning Outcomes (MLOs) assessed:

Knowledge & Understanding:

1. Key data modelling concepts, application of database theory, principles for supporting business and information systems.

Intellectual / Professional skills & abilities:

3. Conceptual data modelling, relational database design and implementation in SQL & PL/SQL, and object-based databases.

Personal Values Attributes (Global / Cultural awareness, Ethics, Curiosity) (PVA):

5. Realise the responsibilities of database designers with respect to professional, legal, security and ethical issues.

Tasks of the Assignment

Part 1 (50 marks)

This part is based on the EasyMove scenario in the Appendix

(A) Using a database design approach of your choice, produce a logical design for the database to support the EasyMove online property marketing system.

(30 marks)

Your answer must consist of ONE of the following:

• An entity-relationship (ER) diagram (20 marks) and its mapping into a set of relations (10 marks). The ER diagram should show all relevant entity types, relationship types, attributes, keys, and structural constraints. Note that not all keys are identified/mentioned in the scenario/partial data dictionary, so you are required to identify/devise appropriate keys for all the entity types. Your ER diagram must not show/include any foreign keys/attributes. 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 diagram are in 3rd normal form.

(OR)

• A set of normalised relations (10 marks) obtained through normalisation process (20 marks) instead of ER modelling. You should make clear how the normalisation process has been carried out, and the reasoning employed, in particular quoting/providing evidence (series of steps) to support the decisions made and how your relations have been derived. Each relation in your answer should be in 3rd normal form.

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

(20 marks)

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 (A). 16 marks
• The output from running the script file in a live Oracle 11g/12c session (e.g., using SPOOL, copy and paste, screenshots, etc.). 4 marks
• You should use relational features from the SQL92 standard in Oracle 11g/12c 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 (30 marks)

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

(A) Populate the database with some data (by generating your own data like 5 to 10 rows for each table and enough data to see meaningful output for the queries below).

(10 marks)

(B) Answer the following queries (retrievals) using SQL and relational algebra.

(20 marks)

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

2) Display details of properties sold in Newcastle, Sunderland, Gateshead or Durham for £157,000 to £279,000 in the years 2017 or 2018.

Your submission must include:

• Relational Algebra expressions (6 marks in total)
• An SQL script file containing appropriate SQL DML (e.g., INSERT) statements for populating the tables you have created in Part 1 (B). 8 marks
• An SQL script file containing SQL retrieval (e.g., SELECT) statements for Part 2 (B). 10 marks
• An output file for running each of above the script files in a live Oracle 11g/12c session (e.g., using SPOOL, etc.). 2 marks for the insertions, 2 marks for each of the SQL retrieval output, hence a total of 6 marks for the output.
• A total of 30 marks (6 + 8 + 10 + 6 = 30)

Part 3 (20 marks)

(A) Consider the EasyMove scenario in the Appendix. Produce a report for the managing director of the company elaborating on the professional, legal and ethical issues that need to be considered and make any recommendations that you think are appropriate for development and management of the database for EasyMove.

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

(B) Compare and contrast different approaches to database design (e.g., entity relationship modelling, normalisation, etc.) and briefly justify the approach you have used for answering Part 1 (A).

(8 marks)

The report should be concise and comprehensive and in the region of 600-700 words. You should consult a range of literature (e.g., database text books, journal and conference articles, and quality websites). Again you should use Harvard style of citation and referencing by following the guidelines in Pears and Shields (2008).

References

Pears, R. and  Shields, G. (2008) Cite them right: the essential referencing guide. (Accessed: 19 September 2018).

APPENDIX

EasyMove Scenario

EasyMove is a consortium of independent nation-wide estate agents, which operates in a virtual manner and provides a single online portal for marketing / advertising properties for sale and renting. Its basic role is to bring landlords and prospective buyers & tenants together.

Clients could be landlords, prospective buyers and tenants. Landlords register their properties for sale or renting at individual estate agents. When a client 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 and local branch. The system records data about properties sold and rented out. The details of the data requirements are as follows:

• Client (client code, name, address, date of birth, gender, contactNumber, email)
• EstateAgent (agent code, name, headquarter, registeredAddress, contact number, website, email)
• Branch (branch code, branch address, contact number, email, manager)
• AgentsStaff (staffid, name, address, personal & contact details)
• Property (property id, address, location, property description, accommodation details (sizes and descriptions of rooms, etc), rental demand or asking price, type, number of rooms, etc)
• Viewing (property to view, client, date and time, comments)
• PropertySale (transaction with Client / Buyer in Property, selling price, stamp duty, commission)
• PropertyRental (property rented, rent per month, advance paid, commission, who is landlord and tenant, date tenancy commenced, date tenancy finished)

RUBRIC QUALITY OF RESPONSE NO RESPONSE POOR / UNSATISFACTORY SATISFACTORY GOOD EXCELLENT Content (worth a maximum of 50% of the total points) Zero points:  Student failed to submit the final paper. 20 points out of 50:  The essay illustrates poor understanding of the relevant material by failing to address or incorrectly addressing the relevant content; failing to identify or inaccurately explaining/defining key concepts/ideas; ignoring or incorrectly explaining key points/claims and the reasoning behind them; and/or incorrectly or inappropriately using terminology; and elements of the response are lacking. 30 points out of 50:  The essay illustrates a rudimentary understanding of the relevant material by mentioning but not full explaining the relevant content; identifying some of the key concepts/ideas though failing to fully or accurately explain many of them; using terminology, though sometimes inaccurately or inappropriately; and/or incorporating some key claims/points but failing to explain the reasoning behind them or doing so inaccurately.  Elements of the required response may also be lacking. 40 points out of 50:  The essay illustrates solid understanding of the relevant material by correctly addressing most of the relevant content; identifying and explaining most of the key concepts/ideas; using correct terminology; explaining the reasoning behind most of the key points/claims; and/or where necessary or useful, substantiating some points with accurate examples.  The answer is complete. 50 points:  The essay illustrates exemplary understanding of the relevant material by thoroughly and correctly addressing the relevant content; identifying and explaining all of the key concepts/ideas; using correct terminology explaining the reasoning behind key points/claims and substantiating, as necessary/useful, points with several accurate and illuminating examples.  No aspects of the required answer are missing. Use of Sources (worth a maximum of 20% of the total points). Zero points:  Student failed to include citations and/or references. Or the student failed to submit a final paper. 5 out 20 points:  Sources are seldom cited to support statements and/or format of citations are not recognizable as APA 6th Edition format. There are major errors in the formation of the references and citations. And/or there is a major reliance on highly questionable. The Student fails to provide an adequate synthesis of research collected for the paper. 10 out 20 points:  References to scholarly sources are occasionally given; many statements seem unsubstantiated.  Frequent errors in APA 6th Edition format, leaving the reader confused about the source of the information. There are significant errors of the formation in the references and citations. And/or there is a significant use of highly questionable sources. 15 out 20 points:  Credible Scholarly sources are used effectively support claims and are, for the most part, clear and fairly represented.  APA 6th Edition is used with only a few minor errors.  There are minor errors in reference and/or citations. And/or there is some use of questionable sources. 20 points:  Credible scholarly sources are used to give compelling evidence to support claims and are clearly and fairly represented.  APA 6th Edition format is used accurately and consistently. The student uses above the maximum required references in the development of the assignment. Grammar (worth maximum of 20% of total points) Zero points:  Student failed to submit the final paper. 5 points out of 20:  The paper does not communicate ideas/points clearly due to inappropriate use of terminology and vague language; thoughts and sentences are disjointed or incomprehensible; organization lacking; and/or numerous grammatical, spelling/punctuation errors  10 points out 20:  The paper is often unclear and difficult to follow due to some inappropriate terminology and/or vague language; ideas may be fragmented, wandering and/or repetitive; poor organization; and/or some grammatical, spelling, punctuation errors 15 points out of 20:  The paper is mostly clear as a result of appropriate use of terminology and minimal vagueness; no tangents and no repetition; fairly good organization; almost perfect grammar, spelling, punctuation, and word usage. 20 points:  The paper is clear, concise, and a pleasure to read as a result of appropriate and precise use of terminology; total coherence of thoughts and presentation and logical organization; and the essay is error free. Structure of the Paper (worth 10% of total points) Zero points:  Student failed to submit the final paper. 3 points out of 10: Student needs to develop better formatting skills. The paper omits significant structural elements required for and APA 6th edition paper. Formatting of the paper has major flaws. The paper does not conform to APA 6th edition requirements whatsoever. 5 points out of 10: Appearance of final paper demonstrates the student’s limited ability to format the paper. There are significant errors in formatting and/or the total omission of major components of an APA 6th edition paper. They can include the omission of the cover page, abstract, and page numbers. Additionally the page has major formatting issues with spacing or paragraph formation. Font size might not conform to size requirements.  The student also significantly writes too large or too short of and paper 7 points out of 10: Research paper presents an above-average use of formatting skills. The paper has slight errors within the paper. This can include small errors or omissions with the cover page, abstract, page number, and headers. There could be also slight formatting issues with the document spacing or the font Additionally the paper might slightly exceed or undershoot the specific number of required written pages for the assignment. 10 points: Student provides a high-caliber, formatted paper. This includes an APA 6th edition cover page, abstract, page number, headers and is double spaced in 12’ Times Roman Font. Additionally, the paper conforms to the specific number of required written pages and neither goes over or under the specified length of the paper.
GET THIS PROJECT NOW BY CLICKING ON THIS LINK TO PLACE THE ORDER CLICK ON THE LINK HERE:  https://www.perfectacademic.com/orders/ordernow Also, you can place the order at www.collegepaper.us/orders/ordernow / www.phdwriters.us/orders/ordernow
Do You Have Any Other Essay/Assignment/Class Project/Homework Related to this? Click Here Now [CLICK ME]and Have It Done by Our PhD Qualified Writers!!

PLACE THE ORDER WITH US TODAY AND GET A PERFECT SCORE!!!