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

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

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)

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.

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

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

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

Hand-in procedure

You should hand in your
answer for this assignment as a single word processed document electronically on
the ELP. A sample assignment template file will be uploaded on the ELP near the
submission deadline.

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: