School of Computing and Information Technology Session: Autumn 2017 University of Wollongong Australia CSCI835 Database systems Assignment 3 (Total 6 marks) Due on Saturday, 29 April 2017 at 11:55 pm Scope The tasks of this assignment include implementation, implementation and testing of advanced data manipulation statements of SQL, granting access rights to database objects, design and implementation of hierarchical data structures in XML. The assignment follows the laboratory classes 6, 7, and 8. Objectives The objective of this assignment is: • To implement advanced data manipulations in SQL. • To grant access rights. • To Design and implement XML documents. Prologue Download files a3create.sql, a3drop.sql, list.sql and transport.xml. Connect to your account on one of the Oracle 12c servers (data-pc01, … , datapc40, csci) and execute a script a3create.sql. The script creates the relational tables of a sample database used in Assignment 3. The sample database contains information about the employment positions offered by the companies, applicants, who apply for the positions, skills possessed by the applicants and skills required by the positions, courses passed by the applicants, former employers of the applicants. Read the script, discover and draw a conceptual schema of the sample database. You can use a script a3drop.sql to drop all relational tables created by a3create.sql. Do not drop the relational tables now. You can use a script list.sql to list the names of relational tables created so far. No deliverables from the prologue actions are expected. Tasks Task 1: Implementation of advanced data manipulations in SQL (2 marks) It is recommended to do Experiment 6.2 included in Homework 6 before implementation of task 1. Implement SQL script task1.sql that performs the following modification of the sample database.(1) Create a relational table CONTACT that contains information about applicant number, address, city, state, phone#, fax# and email of each applicant. In the next step copy the appropriate contents of a relational table APPLICANT to a relational table CONTACT. Note that you must enforce appropriate primary and foreign key constraints on a relational table CONTACT before copying data from a relational table APPLICANT. Finally, list the contents of a relational table CONTACT. (2) Add a column TOTAPP to a relational table APPLICANT and use one UPDATE statement to fill the column with information about the total number of applications submitted by each applicant. Note, that it is possible that some of applicants did not submit any applications yet and in such a case your UPDATE statement must set the respective value in a column TOTAPP to zero (0). Next, list the numbers of applicants (column A#) and the total number of applications submitted by each one of them (column TOTAPP). (3) Restore the original state of sample database through dropping a relational table CONTACT and dropping a column TOTAPP. Note, that you must nicely format the outputs from a script task1.sql. The marks will be deducted for the "broken" printouts! It is explained in an Experiment 2.5 how to use formatting statement of SQL*Plus. Task 2: Granting access rights to database objects (1 mark) It is recommended to do Experiment 6.4 included in Homework 6 before implementation of task 2. Implement SQL script task2.sql that grants and later on revokes the following access rights to/from a user SCOTT. A user SCOTT is available on all data-pc. and csci database servers. (1) A user SCOTT should be able to update a relational table APPLICANT and it should also be able to pass such access right to another user. (2) A user SCOTT should be able to create any relational table that has a foreign key referencing a primary key in a relational table POSITION. (3) A user SCOTT should be able to access in read mode information about the positions that require a skill SQL PROGRAMMING. (4) Finally revoke all access rights granted above from a user SCOTT;Task 3: Stored procedure and function (1 mark – 0.5 marks each) It is recommended to do Experiment 7.1 and 7.2 included in Homework 7 before implementation of task 3. Implement the stored PL/SQL procedure and functions in a file task3.sql. 1. Implement a PL/SQL stored function SkillPossessed that takes a skill name as a parameter, returns all applicants' full names and skill levels that possess the skill. Execute the function by selecting all skill names from a table LSkill, and display applicants' full names and skill levels for each skill. Note: Use newline to separate different applicants' data. For example: C PROGRAMMING: PETER JONES 4 C++ PROGRAMMING: MARY WHITE 10 COOKING: PETER JONES 9 STEPHEN STAUNTON 9 JANUSZ GETTA 1 CLAUDIA HOFFMAN 10 2. Implement a PL/SQL stored procedure FindApplicants that takes no parameter, list all the position titles and employers, and list all the applicants that satisfying the position needed skills for each position. Note: An applicant satisfying a position means the applicant possess all the skills and the skill levels are higher or equals to the needed skills. Execute the procedure and display required results. For example: 1 LECTURER UNSW: 2 LECTURER UOW: 3 SENIOR LECTURER UTS: 4 ASSOC. PROFESSOR UOW: 5 MARGARET FINCH 6 GARY KASPAROW 7 CLAUDIA HOFFMAN 10 JANUSZ GETTA Task 4: Design and implementation of hierarchical data structures in XML (1 mark) It is recommended to do Experiment 8.1 included in Homework 8 before implementation of task 4.Read the following specification of a sample database domain. A multinational network of hotels owns the hotels in the countries all over the world. etc. The network of hotels is described by a name and an address of its headquarters. An address of headquarters consists of country, city, street and building number and it optionally includes phone and email address. The network has many hotels in many different countries. However each one of its hotels is located in a different city. A hotel is described by a name and address that consists of country, city, street and building number and it optionally includes phone and email address. A name of a hotel together with a city name uniquely identifies each hotel, e.g. Sheraton at Suwa, or Holiday Inn at Port Villa. The employees work at the hotels. An employee is described by a unique employee number, full name (first name, optional initials, last name) and position he/she is assigned to. An employee works at only one hotel. A hotel consists of rooms. A room is described by a number, category, total area and a list if equipment items included in a room. For example, tv set, fridge, hairdryer etc. The numbers of rooms are unique within a hotel and different hotels may have the rooms with the same numbers. (1) Design a conceptual schema for a sample database domain given above. Use a notation of simplified UML class diagrams explained to you in this subject. Save your diagram in a file task4.pdf. (2) Create a sample XML document that contains information about at least one sample instance of the objects from each class included in your conceptual schema. Save your document in a file task4.xml. Start Command Prompt on Win 7 system and use a program oraxml available on Win 7 system to make sure that your document is well formed. You can use xmllint on UNIX system. Task 5: Creation of DTD and validation of XML documents against DTD (1 mark) It is recommended to do Experiment 8.2 and Experiment 8.3 included in Homework 8. Consider the following conceptual schema.(1) Create a sample XML document that contains information about at least two sample instances of the objects from each class included in the conceptual schema above. Save your document in a file task5.xml. Your document must be designed such that it does not contain any redundant information! Start Command Prompt on Win 7 system and use a program oraxml available on Win 7 system to make sure that your document is well formed. You can use xmllint on UNIX system. (2) Implement an external DTD that validates any XML document that contains information about the instances of objects from each class included in the conceptual schema above. Save your DTD in a file task5.dtd. Use a program oraxml (or xmllint on UNIX system) to make sure that document in a file task5.xml validates against DTD in a file task5.dtd. Deliverables Task 1 Submit a report file task1.lst that contains the execution results of SQL script file task1.sql with SQL*Plus statement SET ECHO ON in the first line. A report that contains no listing of executed SQL statements scores no marks and report that contains errors also scores no marks! Task 2 Submit a report file task2.lst that contains the execution results of SQL script file task2.sql with SQL*Plus statement SET ECHO ON in the first line. A report that contains no listing of executed SQL statements scores no marks and report that contains errors also scores no marks! Task 3 Submit a report file task3.lst that contains execution results of SQL script file task3.sql with SQL*Plus statement SET ECHO ON in the first line.A report that contains no listing of executed SQL statements scores no marks and report that contains errors also scores no marks! Task 4 A file task4.pdf with a conceptual schema of the sample database, a file task4.xml with a well formed XML document that contains sample contents of the database. Task 5 A file task5.dtd with an external DTD and a file task5.xml with a well formed XML document that can be used to comprehensively test DTD included in task5.dtd. Submissions This Assignment is to be submitted on Moodle. Submit the files through Moodle in the following way: (1) Connect to the Moodle site for the subject. (2) Navigate to a folder ASSIGNMENT SUBMISSIONS (3) Click at Assignment 3, Submit your solutions here link. (4) Click at Add submission button, (5) In File submissions, click Add … button. (6) Click Choose File. (7) Navigate to a location where files task1.lst, task2.lst, task3.lst, task4.pdf, task4.xml, task5.dtd and task5.xml have been saved. (8) Select a file and click Open button, then click at Upload this file button. (9) Repeat (7) and (8) if submit multiple files. (10)Click at Save changes button. (11)You may update the submission files by click Edit submission button before click Submit assignment button. (12)Click Submit assignment button if you are sure nothing need to be changed. (13)Tick the checkbox, then click Continue button. A policy regarding late submissions is included in the course outline. Only one submission of this assignment is allowed. This assignment must be submitted as soft copy only. This assignment is an individual assignment and it is expected that all its tasks will be solved individually without any cooperation with the other students. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or office hours. Plagiarism will result in a FAIL grade being recorded for that assessment task.Late submissions do not have to be requested. Late submissions will be allowed for a few days after close of scheduled submission (up to 3 days). Late submissions attract a mark penalty; this penalty may be waived if an appropriate request for academic consideration (for medical or similar problem) is made via the university SOLS system before the Due date. No work can be submitted after the late submission time. Marks and comments on the assignments will be returned to the students in two weeks after the submission on Moodle. End of specification