School of Computing and Information Technology Sesion: Autumn 2018
CSIT115/CSIT815 Data Management and Security
Scope
This asignment is related to application of advanced data manipulation statements of
SQL and relational views.
Important mesages
Please read the mesages listed below before implementation of the tasks included in
a specification of Asignment 3.
More implementation related information can be found in "How to … ?" Cookbook
available through Moodle or at: .
The outcomes of Asignment 3 are due by Saturday, 12 May, 2018, 10.00pm (sharp).
Asignment 3 contributes to 6% (5% for CSIT815 students) of the total evaluation in the
subject.
A submision procedure is explained at the end of this document.
Only one submision of the outcomes of Asignment 3 is alowed and only one
submision per student is acepted. Please make sure that you submit the correct files.
A submision that contains an incorrect file atached is treated as a correct submision
with al consequences coming from the evaluation of the file atached.
Compresed (zipped, rared, tared, etc) files wil not be evaluated.
A submision marked by Moodle as "late" is treated as a late submision no mater how
many seconds it is late.
The reports from procesing of SQL scripts must return NO ERORS ! A solution with
the erors is worth no marks !
A policy regarding late submisions is included in CSIT15/815 Subject Outline.
Prologue
Download the files dbcreate.sql, dbdrop.sql, dbload.sql, and
dbschema.pdf. Copy the files to your USB drive or email the files to yourself such
that you can aces al of them either through command line interface mysql or graphical
user interface MySQL Workbench.
Connect to MySQL database server either through command line interface mysql or
graphical user interface MySQL Workbench.
When connected, select a database csit115 with a command use csit115.
You must drop al tables earlier created in csit115 database. A script. dbdrop.sql
can be used to drop the relational tables. To make sure that al tables have been dropped
use a command show tables.
Next, re-create al tables of csit115 database and load data into the database before
implementation of both tasks included in the asignment.
To re-create the relational tables of a sample database, proces SQL script.
dbcreate.sql.
To load data into the relational tables proces SQL script. dbload.sql.
Your SQL statements must operate on the sample database loaded with data.
To list the names of relational tables created, use a command show tables.
To list a structure of a relational table use a command describe
Use a pdf viewer to open a file dbschema.pdf with a conceptual schema of the sample
database. The gren blobs represent the relational tables that implement the clases of
objects and asociations.
No report is expected from the implementation of the steps listed above.
Tasks
Task 1 (4 marks)
Implement the following advanced manipulations on data in SQL.
(1) Create an empty relational table to store information about the names of employers
and the total number of positions offered by each employer. A name of relational
table and the names of columns are up to you. Enforce the appropriate primary key
and referential integrity constraints (if any).
Next, load into the table information about the names of employers together with the
total number of positions offered by each employer. If an employer ofers no position
then its name must be recorded with a number zero (0).
(2) Create a relational table that contains information about the names of al skils and
the largest skil level possesed by an applicant and a number of applicant who
posseses a skil at the highest level. Ignore the skils not possesed by any applicant.
Al data must be loaded into the table by the same SQL statement that creates the
table.
Enforce the appropriate primary key and referential integrity constraints (if any) after
data is loaded.
(3) Add a column to a relational table POSITIONS to store information about the total
number of skils needed by each advertised position. A name of the column is up to
you. Asume that no more than 9 skils are needed for each position.
Next, use a single UPDATE statement to set the values in the new column consistent
with the present contents of the sample database.
(4) Use a single DELETE statement to remove from a relational table APPLIES all
applications submited by the applicants who do not have any skils. DELETE
statement must be correct for any contents of the sample database.
When ready save your implementations in SQL script. file solution1.sql and
proces a script. solution1.sql. Note, that your script. must contain only SQL
statements implementing the actions (1), (2), (3), (4). Save a report from procesing of a
file solution1.sql in a file solution1.rpt.
A file solution1.rpt must NOT contain the reports from procesing of the scripts
dbdrop.sql, dbcreate.sql and dbload.sql.
You can also find more information about creating reports from procesing of SQL
scripts in Cookbok, Recipe 3.1 How to use “mysql? Comand based interface to
MySQL database server? Step 4 How to save the results of SQL procesing in a file?”
Your report must contain a listing of al SQL statements procesed. You can find more
information on how to display SQL statements while a script. is procesed in Cookbok,
Recipe 3.1 How to use “mysql? Comand based interface to MySQL database server?
Step 3 How to proces SQL script. ?”
A report that contains no listing of executed SQL statements scores no marks and report
that contains erors of any kind also scores no marks!
Deliverables
A file solution1.rpt with a report from procesing of SQL script.
solution1.sql. The report MUST have no erors and the report MUST list all SQL
statements procesed.
Task 2 (2 marks)
It is recommended to drop al relational tables of a sample database and to create and to
load data into a sample database before implementation of Task 2. It may happen that you
have to drop some referential integrity constraints first.
Create SQL script. solution2.sql that implements of the following query in SQL.
Find full names (first name, last name) and an average skil level of all applicants
whose average skil level is below an average skil level of all skils possesed.
For example, if applicant X has thre skils at the levels 5, 5, and 8 and applicant Y has
one skil at level 2 and applicant Z has no skils then an average skil level of al skils
possesed is (5+5+8+2)/4 = 5. An average skil level of applicant X is (5+5+8)/3
= 6. An average skil level of applicant Y is 2/1 = 2. An average skil level of
applicant Z is 0. Therefore, the full names of applicants Y and Z should be listed together
with average skil level for each one of them.
The query must be implemented in the following way.
(1) First, create a relational view that contains information about an average skil level of
al skils possesed by at least one applicant. A name of relational view is up to you.
(2) Next, create a relational view that for each applicant who posseses at least one skil
contains information about an applicant number and an average level of al his/her
skils. Remember, that the view must contain information about the applicants who
posses at least one skil. A name of relational view is up to you.
(3) Next, create a relational view that for each applicant who posseses no skils contains
information about an applicant number and 0 as his/her an average level of al skils.
A name of relational view is up to you.
(4) Next, create a relational view that is union of the views created in step (2) and step
(3). A name of relational view is up to you.
(5) Next, create a relational view that contains information about applicant numbers
whose average skil level is below an average skil level computed in step (1). The
new relational view created in this step must use information included in the views
created in the steps (1) and (4). A name of relational view is up to you.
(6) Finaly, implement SELECT statement that uses a relational view created in a step
(5) and a relational table APPLICANT to find full names (first name, last name) and
an average skil level of all applicants whose average skil level is below an average
skil level of all skils possesed by at least one applicant.
Include CREATE VIEW statements implementing the steps from (1) to (5) and the final
SELECT statement into SQL script. solution2.sql When ready proces the script.
and save a report from the procesing in a file solution2.rpt.
Deliverables
Submit a report file solution2.rpt with a report from procesing of SQL script.
solution2.sql. The report MUST have no erors and the report MUST list all SQL
statements procesed. The report MUST include ONLY SQL statements and control
statements that implement a specification of Task 2 and NO THER statements.
Submision
Note, that you have only one submision. So, make it absolutely sure that you submit the
correct files with the correct contents and correct types. No other submision is possible !
Submit the files solution1.rpt and solution2.rpt through Moodle in the
following way:
(1) Aces Moodle at http://moodle.uowplatform.edu.au/
(2) To login use a Login link located in the right upper corner the Web page or in
the middle of the bottom of the Web page
(3) When logged select a site CSIT115/DPIT115/CSIT815 (S118)Data
Management and Security
(4) Scrol down to a section Submissions
(5) Click at a link In this place you can submit the outcomes of
Assignment 3
(6) Click at a button Add Submission
(7) Move a file solution1.rpt into an area You can drag and drop
files here to add them. You can also use a link Add…
(8) Repeat step (7) for a file solution2.rpt.
(9) Click at a button Save changes
(10) Click at a button Submit assignment
(11) Click at the checkbox with a text atached: By checking this box, I
confirm that this submission is my own work, … in order to
confirm the authorship of your submision
(12) Click at a button Continue
It is expected that al its tasks included within Asignment 3 wil be implemented
individually without any cooperation with the other students. If you have any doubts,
questions, etc. please consult your lecturer or tutor during lab clases or office hours.
Plagiarism wil result in a FAIL grade being recorded for that asesment task.
End of specification