cr.1

Assessment # 1 Brief Advanced Databases (KL7011)

Page 1 of 10

Department of Computer & Information Sciences

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

This assessment addresses learning outcomes LO1, LO3 and LO5.

ASSESSMENT BRIEF

Module Title: Advanced Databases

Module Code: KL7011

Academic Year / Semester: 2021-22 / Semester 1

Module Tutor / Email (all queries): Akhtar Ali [email protected]

% Weighting (to overall module): 60%

Assessment Title: Assignment 1: individual work

Date of Handout to Students: 15th October 2021

Mechanism for Handout: Module Blackboard Site & Live Session in Week 3

Deadline for Submission Attempt by Students:

25th November 2021 @ 23:59 GMT

Mechanism for Submission: Document upload to Module Blackboard Site

Submission Format / Word Count Please upload your written report as a single PDF document (approximately 3,000 words).

Date by which Work, Feedback and Marks will be returned:

23rd December 2021

Mechanism for return of Feedback and Marks:

Mark and individual written feedback will be uploaded to the Module Site on Blackboard. For further queries please email module tutor.

Assessment # 1 Brief Advanced Databases (KL7011)

Page 2 of 10

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 Regulations

You are advised to read the guidance for students regarding assessment policies

(Northumbria, 2020). They are available online here.

Late submission of work

Where coursework is submitted late without approval, after the published hand-in

deadline, the following penalties will apply.

For coursework submitted up to 1 working day (24 hours) after the published hand-in

deadline without approval, 10% of the total marks available for the assessment

(i.e.100%) shall be deducted from the assessment mark.

Coursework submitted more than 1 working day (24 hours) after the published hand-

in deadline without approval will be regarded as not having been completed. A mark

of zero will be awarded for the assessment and the module will be failed,

irrespective of the overall module mark.

Assessment # 1 Brief Advanced Databases (KL7011)

Page 3 of 10

These provisions apply to all assessments, including those assessed on a Pass/Fail

basis.

The full policy can be found here.

Students must retain an electronic copy of this assignment (including ALL

appendices) and it must be made available within 24hours of them requesting it

be re-submitted.

Academic Misconduct

The Assessment Regulations for Taught Awards (ARTA) contain the Regulations

and procedures applying to cheating, plagiarism and other forms of academic

misconduct.

The full policy is available at here

You are reminded that plagiarism, collusion and other forms of academic misconduct

as referred to in the Academic Misconduct procedure of the assessment regulations,

which are taken very seriously. Assignments in which evidence of plagiarism or other

forms of academic misconduct is found may receive a mark of zero.

Criteria for success:

For textual components :

80-100% - The description will excellently cover all the specific topics requested. The

written work will be fluent, clearly presented and of out-standing quality.

70-79% - The description will comprehensively cover all the specific topics requested.

The written work will be fluent and clearly presented and of distinctive quality.

60-69% - The student will show a very good knowledge of the specific topics, with very

good presentation skills and quality.

50-59% - The student will show an above average knowledge of the specific topics,

with above average presentation skills and quality.

Assessment # 1 Brief Advanced Databases (KL7011)

Page 4 of 10

40-49% - There will be an inadequate description of a significant proportion of the

topics requested. There will be no major failures in presentation clarity though partly

inadequate.

Less than 40% - There will be little or no information conveyed in an intelligible

manner on the specific topics requested.

For ERD/EERD, SQL and other database technical components:

80-100% - The students will produce exceptional models (conceptual/logical/physical),

and will demonstrate the use of notation/language, which have outstanding syntactic

accuracy (e.g., adhering to ER/EER modelling standards, error free SQL code), with

exceptional semantic relevance (e.g., are relevant to the requirements of the particular

scenario).

70-79% - The students will produce fully complete models (conceptual/ logical/

physical), and will demonstrate the use of notation/language, which have high

syntactic accuracy, with high semantic relevance.

60-69% - The students will produce almost complete models (conceptual/ logical/

physical), and will demonstrate the use of notation/language, which have appropriate

syntactic accuracy with reasonably well semantic relevance.

50-59% - The students will produce fairly complete models (conceptual/ logical/

physical), and will demonstrate the use of notation/language, which have adequate

syntactic accuracy with reasonable semantic relevance.

40-49% - The students will produce models, and will demonstrate the use of

notation/language, which have some syntactic accuracy and semantic relevance but

on balance inadequate as a whole.

Less than 40% - The students will not produce sufficient models, and/or will be unable

to demonstrate the use of notation/language with significant syntactic accuracy and/or

significant semantic relevance.

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.

Assessment # 1 Brief Advanced Databases (KL7011)

Page 5 of 10

Assignment Questions

Part 1 (35 marks)

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)

Assessment # 1 Brief Advanced Databases (KL7011)

Page 6 of 10

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

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

Assessment # 1 Brief Advanced Databases (KL7011)

Page 7 of 10

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.

(20 marks)

Assessment # 1 Brief Advanced Databases (KL7011)

Page 8 of 10

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

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

Hand-in procedure: You should hand in your answer for this assignment as a single

word-processed document electronically on the Blackboard. A sample assignment

template file will be uploaded on the Blackboard near the submission deadline.

Assessment # 1 Brief Advanced Databases (KL7011)

Page 9 of 10

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.

Assessment # 1 Brief Advanced Databases (KL7011)

Page 10 of 10

References & Bibliography

Northumbria (2020) Academic Regulations for Taught Awards. Available at:

https://www.northumbria.ac.uk/about-us/university-services/student-library-and-

academic-services/quality-and-teaching-excellence/assessment/guidance-for-

students/ (Accessed: 4 October 2021).

Kannan, P.K. (2019) Oracle Database Object-Relational Developer's Guide -19c. Part Number E96436-01. Available at: https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/index.html (Accessed: 08 October 2021).

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

Newcastle upon Tyne: Pear Tree Books. Available at:

https://www.citethemrightonline.com/ (Accessed: 4 October 2021).