cr.1

Assessment # 1 Submission Template

Advanced Databases (KL7011)

Department of Computer & Information Sciences

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.
Student ID
Oracle Username
Student Name

Personalising your SQL output/prompt

Before executing any SQL code for this assignment, you should personalise your SQL output / prompt by running SET SQLPROMPT “UniversityUserName > ”, i.e., double-quote followed by your UniversityUserName followed by > and then a space and double-quote as shown in the screenshot below:

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)

Answer Part 1 A: Insert your ER or EER Diagram Below

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

(10 marks)

Answer Part 1 B: Provide your Logical Relational Design/Schema Below

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

Answer Part 1 C: Provide SQL DDL Script file contents (i.e., the SQL code for creating / altering your Tables / Constraints etc)

CREATE TABLE EMP

(EMP_NO CHAR(2)

CONSTRAINT PKEY_EMP PRIMARY KEY ,

EMP_NAME CHAR(10),

DEPT_NO CHAR(2),

SALARY NUMBER(6)

CONSTRAINT SAL_RANGE CHECK (SALARY >= 6000 AND SALARY <= 150000) ,

MARITAL_STATUS CHAR(1)

CONSTRAINT MAR_STATS CHECK (MARITAL_STATUS IN ('S', 'M', 'W', 'D'))

);

CREATE TABLE DEPT

(DEPT_NO CHAR(2)

CONSTRAINT PKEY_DEPT PRIMARY KEY ,

DEPT_NAME CHAR(10)

CONSTRAINT UNIQ_NAME UNIQUE ,

MANAGER_NO CHAR(2)

CONSTRAINT UNIQ_MANAGER UNIQUE,

BUDGET NUMBER(6)

CONSTRAINT MAX_BUDG CHECK (BUDGET <= 400000) );

CREATE TABLE PROJ

(PROJ_NO CHAR(2)

CONSTRAINT PKEY_PROJ PRIMARY KEY ,

DEPT_NO CHAR(2),

START_DATE DATE

CONSTRAINT UNIQ_START UNIQUE ,

DEADLINE DATE,

CONSTRAINT DURATION CHECK (TO_DATE (START_DATE, 'DD-MON-YYYY') <

TO_DATE (DEADLINE, 'DD-MON-YYYY')) );

CREATE TABLE ALLOC

(EMP_NO CHAR(2),

PROJ_NO CHAR(2),

CONSTRAINT PKEY_ALLOC PRIMARY KEY (EMP_NO, PROJ_NO) );

Answer Part 1 C: SQL DDL Output (e.g., SPOOL file contents or output you got when you executed your above SQL Table Creation code, this should show the SQL code as well as its output)

SQL> @D:\CompanyDB.sql

SQL>

SQL> Rem Everything must first be dropped from the data base. If an object doesn't

SQL> Rem exist, there will be an error message, but this can be ignored.

SQL>

SQL> DROP TABLE EMP CASCADE CONSTRAINTS purge;

Table dropped.

SQL> DROP TABLE DEPT CASCADE CONSTRAINTS purge;

Table dropped.

SQL> DROP TABLE PROJ CASCADE CONSTRAINTS purge;

Table dropped.

SQL> DROP TABLE ALLOC CASCADE CONSTRAINTS purge;

Table dropped.

SQL>

SQL> Rem CASCADE CONSTRAINTS is necessary to avoid violating any formal integrity

SQL> Rem constraints on the tables.

SQL>

SQL> Rem Now we can re-create the data base:

SQL>

SQL> CREATE TABLE EMP

2 (EMP_NO CHAR(2)

3 CONSTRAINT PKEY_EMP PRIMARY KEY ,

4 EMP_NAME CHAR(10),

5 DEPT_NO CHAR(2),

6 SALARY INTEGER

7 CONSTRAINT SAL_RANGE CHECK (SALARY BETWEEN 6000 AND 30000) ,

8 MARITAL_STATUS CHAR(1)

9 CONSTRAINT MAR_STATS CHECK (MARITAL_STATUS IN ('S', 'M', 'W', 'D'))

10 );

Table created.

SQL>

SQL> CREATE TABLE DEPT

2 (DEPT_NO CHAR(2)

3 CONSTRAINT PKEY_DEPT PRIMARY KEY ,

4 DEPT_NAME CHAR(10)

5 CONSTRAINT UNIQ_NAME UNIQUE ,

6 MANAGER_NO CHAR(2)

7 CONSTRAINT UNIQ_MANAGER UNIQUE,

8 BUDGET INTEGER

9 CONSTRAINT MAX_BUDG CHECK (BUDGET <= 400000) );

Table created.

SQL>

SQL> CREATE TABLE PROJ

2 (PROJ_NO CHAR(2)

3 CONSTRAINT PKEY_PROJ PRIMARY KEY ,

4 DEPT_NO CHAR(2),

5 START_DATE DATE

6 CONSTRAINT UNIQ_START UNIQUE ,

7 DEADLINE DATE,

8 CONSTRAINT DURATION CHECK (TO_DATE (START_DATE, 'DD-MON-YYYY') <

9 TO_DATE (DEADLINE, 'DD-MON-YYYY')) );

Table created.

SQL>

SQL> CREATE TABLE ALLOC

2 (EMP_NO CHAR(2),

3 PROJ_NO CHAR(2),

4 CONSTRAINT PKEY_ALLOC PRIMARY KEY (EMP_NO, PROJ_NO) );

Table created.

SQL>

SQL> ALTER TABLE EMP ADD CONSTRAINT FKEY_DEPT FOREIGN KEY (DEPT_NO) REFERENCES DEPT (DEPT_NO) DEFERRABLE;

Table altered.

SQL> ALTER TABLE DEPT ADD CONSTRAINT FKEY_EMP FOREIGN KEY (MANAGER_NO) REFERENCES EMP (EMP_NO) DEFERRABLE;

Table altered.

SQL> ALTER TABLE PROJ ADD CONSTRAINT FKEY_PROJ FOREIGN KEY (DEPT_NO) REFERENCES DEPT (DEPT_NO) DEFERRABLE;

Table altered.

SQL> ALTER TABLE ALLOC ADD CONSTRAINT ALLOC_EMP FOREIGN KEY (EMP_NO) REFERENCES EMP (EMP_NO) DEFERRABLE;

Table altered.

SQL> ALTER TABLE ALLOC ADD CONSTRAINT ALLOC_PROJ FOREIGN KEY (PROJ_NO) REFERENCES PROJ (PROJ_NO) DEFERRABLE;

Table altered.

SQL> spool off

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)

Answer Part 2 A: Provide SQL code below for populating the above relational database

INSERT INTO EMP

VALUES ('E1', 'Smith', 'D1', 9900, 'W');

INSERT INTO EMP

VALUES ('E2', 'Jones', 'D2', 13200, 'M');

INSERT INTO EMP

VALUES ('E3', 'Roberts', 'D2', 11000, 'M');

INSERT INTO EMP

VALUES ('E4', 'Evans', 'D3', 16500, 'S');

INSERT INTO EMP

VALUES ('E5', 'Brown', 'D3', 27500, 'S');

INSERT INTO EMP

VALUES ('E6', 'Green', 'D3', 13200, 'M');

INSERT INTO EMP

VALUES ('E7', 'McDougal', 'D4', 17600, 'D');

INSERT INTO EMP

VALUES ('E8', 'McNally', 'D5', 12100, 'M');

INSERT INTO EMP

VALUES ('E9', 'Fletcher', 'D5', 13200, 'S');

INSERT INTO DEPT

VALUES ('D1', 'Production', NULL, 100000);

INSERT INTO DEPT

VALUES ('D2', 'Sales', 'E5', 250000);

INSERT INTO DEPT

VALUES ('D3', 'Accounts', 'E9', 95000);

INSERT INTO DEPT

VALUES ('D4', 'Admin', 'E8', 156000);

INSERT INTO DEPT

VALUES ('D5', 'Personnel', 'E7', 196000);

INSERT INTO PROJ

VALUES ('P1', 'D1', '20-APR-2008', '23-FEB-2009');

INSERT INTO PROJ

VALUES ('P2', 'D1', '21-JAN-2009', '14-MAY-2009');

INSERT INTO PROJ

VALUES ('P3', 'D2', '02-FEB-2008', '03-MAR-2009');

INSERT INTO PROJ

VALUES ('P4', 'D3', '11-DEC-2007', '01-JAN-2009');

INSERT INTO PROJ

VALUES ('P5', 'D4', '08-OCT-2006', '31-DEC-2008');

INSERT INTO PROJ

VALUES ('P6', NULL, '09-OCT-2006', '30-DEC-2009');

INSERT INTO ALLOC

VALUES ('E1', 'P1');

INSERT INTO ALLOC

VALUES ('E1', 'P2');

INSERT INTO ALLOC

VALUES ('E2', 'P1');

INSERT INTO ALLOC

VALUES ('E2', 'P5');

INSERT INTO ALLOC

VALUES ('E4', 'P4');

INSERT INTO ALLOC

VALUES ('E5', 'P4');

INSERT INTO ALLOC

VALUES ('E6', 'P4');

INSERT INTO ALLOC

VALUES ('E9', 'P4');

INSERT INTO ALLOC

VALUES ('E5', 'P3');

INSERT INTO ALLOC

VALUES ('E7', 'P3');

Answer Part 2 A: Provide below output from running the above SQL code for populating your relational database (e.g., contents from Spool file or screenshots, etc)

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

Provide Relational Algebra expression below:

Provide SQL query code and output below:

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

Provide Relational Algebra expression below:

Provide SQL query code and output below:

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)

Answer Part 3 A

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

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

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

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

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

Answer Part 3 B

1) Provide below your choice and justification of what aspects (subset) of the MOVEHOME databases would benefit from incorporating NoSQL Database concepts (3 marks)

2) Provide below code and output for implementing your proposed NoSQL Database subset of the MOVEHOME database, populate it with some data, and example queries & outputs (12 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).

Answer Part 4: 10 Marks [8 for the quality of report covering all the above issues, 1 for the quality of referencing and citation and adhering to the Harvard style, 1 for presentation]

Page 14 of 14

Airport

acode

aname

City

ccode

cname

Flight

fnumber

weekdays

i

n

FlightLeg

part of

1

N

departure

arrival

1

1

N

N

legNo

1

N

Airline

h

a

s

1

N

alcode

alname

runs

1

N

b

a

s

e

o

f

N

1

o

f

LegInstance

1

N

Aeroplane

Type

Aeroplane

date

avail

seats

h

a

s

N

1

aid

total

seats

has

N

1

l

a

n

d

s

a

t

N

M

atype

company

max

seats

actual

depart time

schedule

time

actual

arrival time

schedule

time

Sname*Move_noLatitudeLongitudeMove_timeMove_date

SnameOwnerType*Home_port*

Ship_Movement

Ship

TypeTonnageHull

Ship_Type

PnameState_Country*Sea_Ocean_Lake*

Port

NameContinent

State_Country

Name

Sea_Ocean_Lake

Sname*Visit_noPname*Start_dateEnd_date

Ship_at_Port