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.

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 ⋂ , union ⋃ , minus -

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

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

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

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.

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.

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.

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:

 

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.

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]

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

(c) to update the evaluation for R. Blake (id: 7698) registration to "Introduction to SQL" (code: “SQL”) course on the "13-12-2016". The new value for the evaluation is 3.  [4 marks]

Section D Transaction [Total: 15 Marks]

Q9. [5 marks]

Given the following transaction sequence, complete the table by clearly indicating what locks are present at each of the indicated times (Time 0 to Time 9).

Cell entries must have the form:

●    S(Tn) - for a shared lock by Tn,

●    X(Tn) - for an exclusive lock by Tn or

●    Tn wait Tm - for a wait of Tn due to Tm (where n and m are transaction numbers).

 

(a) Complete the table by clearly indicating what locks are present at each of the indicated times (Time 0 to Time 9).

Cell entries must have the form:

●    S(Tn) - for a shared lock by Tn,

●    X(Tn) - for an exclusive lock by Tn or

●    Tn wait Tm - for a wait of Tn due to Tm (where n and m are transaction numbers).

(b) Does a deadlock exist in this transaction sequence?  Explain why you came to this conclusion.

Q10. [ 5 marks]

Given two transactions:

T1 – R(X), W(X)

T2 – R(Y), W(Y), R(X), W (X)

Where R(X) means Read(X) and W(X) means Write(X).

(a) If we wish to complete both of these transactions, explain the difference between a serial and non-serial ordering of these two transactions. Provide an example of each as part of your answer.

(b) What transaction ACID property does a non-serial ordering of these two transactions potentially violate.