Constraint jobhistory foreign key references employee

School of Computing and Information Technology

Wollongong Campus

Examination Paper

Items permitted by examiner None

Aids supplied None

THE QUESTIONS 3, 4, 5, 6 and 7 REFER TO THE RELATIONAL TABLES LISTED BELOW

CREATE TABLE DEPARTMENT (

CREATE TABLE JOB (

CONSTRAINT JOBHISTORY_PK
PRIMARY KEY (employee_id, start_date), CONSTRAINT JOBHISTORY_FK1 FOREIGN KEY(job_title)
REFERENCES JOB(job_title),
CONSTRAINT JOBHISTORY_FK2 FOREIGN KEY(employee_id) REFERENCES EMPLOYEE(employee_id),
CONSTRAINT JOBHISTORY_FK3 FOREIGN KEY(department_name) REFERENCES DEPARTMENT(department_name) );

A network consists of the service nodes distributed all over a country. Each service node is located at a different address. An address consists of city name, street, name and building number. Each node has a name, that is unique in a city it is located at. It is possible that two nodes located in different cities have the same names. Each node has a unique phone number and unique email address.

The nodes employ mechanics, administration, and management staff members. An employee is described by a unique employee#, first name, last name, date of birth, and hire date. A collection of four attributes that includes first name, last name, date of birth, and hire date is unique for each employee. Additionally, mechanics are described by a list of qualifications possessed, administration members are described by a list of IT skills possessed and management staff members are described by a position occupied. An employee can work only at one service node.

There is NO NEED to provide a detailed analysis how a conceptual schema has been created. The final conceptual schema expressed in a notation of UML simplified classes is completely sufficient.

For each relational schema clearly list the names of attributes, primary key, candidate keys (if any), and foreign keys (if any). Assume, that subset method must be used to implement a generalization.

(2) It should be possible, after a modification, to add information that a department manager is an employee and a manager of an employee is an employee as well.

(2 marks)

more than one data manipulation statement of SQL.

(1) James Bound, employee id007, phone number123.456.7890has been hired on5 March 2012

information about the employee’s job history. Note, that a foreign key JOBHISTORY_FK2 does not have

ON DELETE CASCADE clause. Also assume that the employee is not a manager.

(4) A department Shareholder Service has been renamed to Share Service. Update all related

data in the database.

(1) Find the full names of employees who are the topmost level managers, i.e. who are not managed by any other employee.

(2 marks)

(2 marks)

(5) Find the employee id, first name and last name for each employee who is directly managed by Matthew Weiss.

Assume that a user root with a password 'root' created a database called HR and the user executed

CREATE TABLE statements given on page 2 of the examination paper to create the relational tables in the

read access rights must be granted such that a user boss is allowed to grant access in read mode to all

tables to the other users.

end_date in a relational table JOBHISTORY to a user boss. A user boss is not allowed to grant the

same privilege to the other users.

other users.

(7) The script grants the rights to read all relational tables in all databases created on a database server to

(1) Describe a technique used in your implementation of verification of logical database consistency. Write what components of the database management system were used, what actions on a database system were necessary to verify the logical consistency.

(2 marks)

In your answers to this question you are allowed to extensively quote the sample solution of Assignment 4, task 3 published by your lecturer.