Section A Relational Model [Total: 15 Marks]

Q1 [5 Marks]

A  company  wishes  to  record  the  following  attributes  about  their  employees:  employee  ID, department number, name, home address, education qualifications and skills which the employee has.

A small sample of data is show below:

Employee ID	Department Number	Employee Name	Home Address	Qualification	Skill
101	21	Given name: Joe Family name: Bloggs	Street: 12 Wide Rd Town: Mytown Postcode: 1234	Bachelor of Commerce MBA	Project Management Hadoop R
102	13	Given name: Wendy Family name: Xiu	Street: 55 Narrow St Town: Mytown Postcode: 1234	Bachelor of Computer Science Master of IT Doctor of Philosophy	SQL PL/SQL
103	13	Given name: Sarah Family name: Green	Street: 25 High St Rd Town: Mytown Postcode: 1234	Certificate IV in Business Administration	SQL Java Phyton

Use this data to explain the difference between a simple attribute, a composite attribute and a multivalued attribute. Your answer must include examples drawn from this data.

Simple - an attribute which cannot be subdivided eg. employeeid, department number

Composite - an attribute which can be subdivided into additional attributes eg. employee name, home address

Multivalued - an attribute which has many potential values eg. qualification, skill

Q2 [10 Marks]

The following relations represent a publications database:

AUTHOR (author_id , author_name)

AUTHOR_PAPER (author_id, paper_id, authorpaper_position)

PAPER (paper_id , paper_title, journal_id)

JOURNAL (journal_id, journal_title, journal_month, journal_year, journal_editor)

* editor in journal references author(author_id) – this is an author acting as the journal editor

Authors write papers which are published in an edition of a journal. Each edition of a journal is       assigned a journal id and appoints an editor. A given paper may be authored by several authors, in such cases each author is assigned a position representing their contribution to the paper:

Write the relational algebra for the following queries (your answer must show an understanding of query efficiency):

List of symbols:

project: π , select: σ , join: ⨝ , intersect n, union U, minus -

(a) Show the paper title, journal title, and month and year of journal publication for all papers published before 2012. (3 marks)

R1 = π journal_id, journal_title, journal_month, journal_year (σ journal_year < 2012 (JOURNAL)) R2 = π journal_id, paper_title(PAPER)

R3 = R1 ⨝ R2

R = π paper_title, journal_title, journal_month, journal_year (R3)

Here R1 could be done in two steps, a select and then a project.

(b) Show the names of all authors who have never been listed as first author (authorpaper_position = 1) in any paper. (3 marks)

R1 = π author_id (σ authorpaper_position = 1 (AUTHOR_PAPER))

R2 = π author_id (AUTHOR) - R1

R3 = AUTHOR ⨝ R2

R4 = π author_name(R3)

(c) Show the paper title and author names of all papers published in a journal titled 'Big Data Research' in May 2022. (4 marks)

R1 = π journal_id (σ journal_tile = ‘Big Data Research’ and journal_month = ‘May’ and journal_year = 2022 (JOURNAL))

R2 = π paper_id, paper_title (PAPER ⨝ R1)

R3 = π author_id, paper_title (π author_id, paper_id(AUTHOR_PAPER) ⨝ R2)

R4 = π paper_title, author_name (AUTHOR ⨝ R3)

Section B SQL [Total: 55 Marks]

Employee System Model for Section C and Section D

The following data model depicts an employee system:

Your answers for this section should be developed and tested using a connection to the Oracle     database (ORDS, MoVE or local SQL Developer). When connected to the database you must use the tables owned by the user PAYROLL on the Oracle database. After you are happy with an         answer, format it and copy and paste it to the answer space for the appropriate question.

Q3 [8 marks]

List the employee number, the employee name, the employee job and the yearly salary of all       employees that belong to the ‘Sales’ department. The name of the employee must be shown in a column called “Employee Name” and the yearly salary must be shown in the form of $34,200 in a column called “Yearly Salary” . Show the employee with the highest salary first, if two employees  have the same salary, order it based on the employee number.

Code the SQL SELECT statement.

SELECT

e.empno,

empname

empjob,

to_char(empmsal * 12, '$99,990')

FROM

payroll.employee e

JOIN payroll.department d

ON e.deptno = d.deptno

WHERE

upper(deptname) = upper('Sales')

ORDER BY

empmsal DESC,

e.empno;

AS "Employee Name",

AS "Yearly Salary"

Q4 [14 marks]

For each course which has been completed by at least 5 employees, list the course code, the         course description and the course duration. The course duration must be shown in a column called “Course Duration” and include the word ‘days’ (e.g. 4 days). Order the output by the course code.  Code the SQL SELECT statement.

SELECT

c.crscode,

crsdesc,

crsduration | | ' days' as "Course Duration"

FROM

payroll.registration r

JOIN payroll.course c

ON r.crscode = c.crscode

WHERE

r.regevaluation IS NOT NULL

GROUP BY

c.crscode,

crsdesc,

crsduration

HAVING

COUNT(empno) >= 5

ORDER BY

c.crscode;

Q5 [14 marks]

List ALL employees whose total course registrations are less than the average number of                 registrations for employees who have registered for a course. Note that some employees may         repeat a course, this repeat does not count as a different course. In the list, include the employee   number, name, date of birth and the number of different courses they have registered for. Order the output by employee number.

Code the SQL SELECT statement.

SELECT

e.empno,

empname,

to_char (empbdate, 'dd-Mon-yyyy ') AS dob,

COUNT (DISTINCT crscode) AS crscount

FROM

payroll .employee e

LEFT JOIN payroll .registration r ON e .empno = r .empno

GROUP BY

e.empno,

empname,

to_char (empbdate, 'dd-Mon-yyyy')

HAVING

COUNT (DISTINCT crscode) < (

SELECT

AVG (COUNT (DISTINCT crscode))

FROM

payroll.registration

GROUP BY

empno

)

ORDER BY

e.empno;

Q6 [19 marks]

List, for all employees who have had more than four appointments within the company, the         employee number, their name and how many appointments they have had - note that one row in the history table represents an appointment.

Also list, as part of the output, the percentage of the employees appointments which contain a history comment, the yearly salary they started with for their first appointment and their current yearly salary. You may assume that all salaries in the system are rounded to the nearest whole number. Order the output by employee number.

Your output MUST have the form as shown below:

SELECT

h.empno,

e.empname,

COUNT(*) AS numb_appoints,

lpad(to_char(COUNT(h.histcomments) * 100 / COUNT(*), '990.9')

| | '%', 20) AS percent_with_comment,

(

SELECT

histmsal * 12

FROM

payroll.history

WHERE

histbegindate = (

SELECT

MIN(histbegindate)

FROM

payroll.history

WHERE

empno = h.empno

)

AND empno = h.empno

) AS starting_yearly_salary,

e.empmsal * 12 AS current_yearly_salary

FROM

payroll.history h

JOIN payroll.employee e

ON h.empno = e.empno

GROUP BY

h.empno,

e.empname,

e.empmsal

HAVING

COUNT(*) > 4

ORDER BY

empno;

Section C NoSQL [Total: 15 Marks]

Q7 [8 Marks]

Below  is  a  snippet  of JSON formatted text data for ‘Employee System’ employees who have registered in offered courses details (note that the snippet only includes partial data):

[

{

"_id" : 7566,

"name" : {

"initial" : "JM",

"familyName" : "JONES"

},

"department" : "TRAINING",

"job" : "MANAGER",

"birthdate" : "02-04-1984",

"courseInfo" : [

{

"code" : "JAV",

"description" : "Java for Oracle developers",

"date" : "01-02-2017",

"evaluation" : 3

},

{

"code" : "PLS",

"description" : "Introduction to PL/SQL",

"date" : "11-09-2017",

"evaluation" : null

}

]

},

{

"_id" : 7698,

"name" : {

"initial" : "R",

"familyName" : "BLAKE"

},

"department" : "SALES",

"job" : "MANAGER",

"birthdate" : "01-11-1980",

"courseInfo" : [

{

"code" : "JAV",

"description" : "Java for Oracle developers",

"date" : "01-02-2017",

"evaluation" : 5

},

{

"code" : "SQL",

"description" : "Introduction to SQL",

"date" : "13-12-2016",

"evaluation" : null

},

{

"code" : "SQL",

"description" : "Introduction to SQL",

"date" : "12-04-2016",

"evaluation" : 4

}

]

},

{

"_id" : 7782,

"name" : {

"initial" : "AB",

"familyName" : "CLARK"

},

"department" : "ACCOUNTING",

"job" : "MANAGER",

"birthdate" : "09-06-1982",

"courseInfo" : [

{

"code" : "JAV",

"description" : "Java for Oracle developers",

"date" : "13-12-2016",

"evaluation" : 5

}

]

}

]

Write an SQL statement that generates the above json formatted data from the tables owned by the user PAYROLL on the Oracle database.

SELECT

JSON_OBJECT( '_id' VALUE e.empno,

'name' VALUE JSON_OBJECT(

'initial' VALUE empinit,

'familyName' VALUE empname

),

'department' VALUE deptname,

'job' VALUE empjob,

'birthdate' VALUE to_char(empbdate,'dd-mm-yyyy'),

'courseInfo' VALUE JSON_ARRAYAGG(

JSON_OBJECT(

'code' VALUE r.crscode,

'description' VALUE crsdesc,

'date' VALUE to_char(offbegindate,'dd-mm-yyyy'),

'evaluation' VALUE regevaluation

)

)

FORMAT JSON )

| | ','

FROM

PAYROLL.DEPARTMENT d

JOIN PAYROLL.employee e

ON d.deptno = e.deptno

JOIN payroll.registration r

ON e.empno = r.empno

JOIN payroll.course c

ON r.crscode = c.crscode

GROUP BY

e.empno,

empinit,

empname,

deptname,

empjob,

empbdate

ORDER BY

e.empno;

Q8 [7 Marks]

Below is a snippet of JSON formatted text data for ‘Employee System’ employees who have registered in offered courses details:

[

{

"_id" : 7566,

"name" : {

"initial" : "JM",

"familyName" : "JONES"

},

"department" : "TRAINING",

"job" : "MANAGER",

"birthdate" : "02-04-1984",

"courseInfo" : [

{

"code" : "JAV",

"description" : "Java for Oracle developers",

"date" : "01-02-2017",

"evaluation" : 3

},

{

"code" : "PLS",

"description" : "Introduction to PL/SQL",

"date" : "11-09-2017",

"evaluation" : null

}

]

},

{

"_id" : 7698,

"name" : {

"initial" : "R",

"familyName" : "BLAKE"

},

"department" : "SALES",

"job" : "MANAGER",

"birthdate" : "01-11-1980",

"courseInfo" : [

{

"code" : "JAV",

"description" : "Java for Oracle developers",

"date" : "01-02-2017",

"evaluation" : 5

},

{

"code" : "SQL",

"description" : "Introduction to SQL",

"date" : "13-12-2016",

"evaluation" : null

},

{

"code" : "SQL",

"description" : "Introduction to SQL",

"date" : "12-04-2016",

"evaluation" : 4

}

]

},

{

"_id" : 7782,

"name" : {

"initial" : "AB",

"familyName" : "CLARK"

},

"department" : "ACCOUNTING",

"job" : "MANAGER",

"birthdate" : "09-06-1982",

"courseInfo" : [

{

"code" : "JAV",

"description" : "Java for Oracle developers",

"date" : "13-12-2016",

"evaluation" : 5

}

]

}

]

Create a collection using the JSON formatted text generated in Q7 OR the snippet data above in a MongoDB database, name the collection as EMPLOYEE and write the MongoDB command:

(a) to show the details of all employees in the ‘SALES’ department. [1 marks]

db.employee.find({"department":"SALES"});

(b) to show the initial and family name of all employees who had registered for the "Java for Oracle developers" course.  [2 marks]

db.employee.find({"courseInfo.description":"Java for Oracle

developers"},{"_id":0,"name.initial":1,"name.familyName":1});