CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 1
Faculty of Science and Technology
ITECH1006 /5006
Database Management Systems
Assignment 2 Specification – Semester 3 2014 (201427)
Database Implementation and Queries
Due Date:
See Course Description for date and time.
Objectives:
To analyse and comprehend a provided ER diagram and Database Schema
To implement a database based on the provided ER diagram and Database Schema
To manipulate the data in the database
To write required SQL statements to query the database
Project Specification
The Case Study: CQR Accounting
After reviewing all the designs, defining the scope and long deliberations with Alan,
Edward and Peter of CQR, the design of the database has been finalised. In this
assignment you will use a ‘simplified’ database model as depicted by the ERD in the
accompanying document - ITECH1006-5006_Assignment2_201427_ER_Diagram.
Changes from assignment 1 have been implemented and include:
• No requirement for the following entities:
O EMPLOYEEQUALIFICATION
O QUALIFICATION
O DEPENDENTQUALIFICATION
O POSTCODE
O CITYSTATE
O STATE
o BUSINESS – implemented within CLIENT
• No requirement for AuditSupervisor functionality so remove column and
relationship from BILLINGEMPLOYEE;
• ClientContact and ClientABN are not always required so allow NULLs in these
columns on CLIENT;CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 2
Faculty of Science and Technology
ITECH1006 /5006
Database Management Systems
• Remove the need to distinguish by client type so remove ClientType attribute from
CLIENT;
• It was felt more information was needed about SERVICETYPE entries so a
ServiceTypeNote column was added;
• Just having EmployeeNumber (provided by the HR system and not autoincremented)
was deemed insufficient information so an additional column was
added to store the EmployeeName from that system;
• BillingEndTime on BILLINGRECORD defaults to 6:00pm when a billing record is
first entered. This is then changed when the employee completes the task.
For this assignment you follow a process of understanding the beginning database through
examination and queries, populating it with appropriate test data and specific requests and
then write some SQL queries. Penalties will apply to queries that use subqueries and
views unnecessarily. The schema file for creating this model is available in the archive
CQR-ass2-schm0901 - this file creates the above tables, their keys (primary and foreign)
and populates all tables except BILLINGONCOST - you should read this schema carefully
so that you are aware of the meaning of the various attributes. You must not alter the
schema file in any manner, it must be used as supplied.
You may need to rerun the schema, especially when you have been experimenting with
your solutions and may have corrupted the database unintentionally. If you suspect that
there might be such a problem, rerun the schema.
A. Understanding and Implementation of the Database
Using the supplied schema file, create the database for CQR. The supplied file
populates all tables to reflect the content of the artefacts provided in Assignment 1 and
is a cut-down version of the practice at the close of business on 15th September 2014. .
At this stage, you are to imagine what the state of the database would be at the close
of business on the next day, subject to the requirements of this task, and all
subsequent tasks.
Write queries to find out the following:
• Find the unused structures e.g. there is one Superannuation client but no
Partnership clients on the starter database.
• Find the unused sectors;
• Find the unrequested services;
• Find the unused billing cost types;
• Find domains for which there is none or only one current employee with that
expertise;
• Find the minimum billing rate for each employee type for the most recent billing
rate effective date; Faculty of Science and Technology
ITECH1006/5006
Database Management Systems
CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 3
Include these queries and their output in a separate file with the identifier
ITECH1006_5006_StudentName_StudentNumber_Beginner_and_Specific_Queries.
(15 marks)
B. Test Data
This task will only be completed after you have finished this and all subsequent tasks. You
should create a single script –
ITECH1006_5006_StudentName_StudentNumber_Test_and_Scenario_Data_Script -
which will contain the statements to insert the required data to:
• satisfy this section’s requirements;
• satisfy the specific scenarios outlined below in section C; and
• where necessary provide meaningful data for the SQL queries specified in
sectionD.
Queries that are correct and do not produce output using your test data will lose 50% of
the marks allocated so you should carefully check your test data and ensure it thoroughly
validates your SQL queries. For example if a query asks to display the full details for all
CLIENTS who have a TAS or a NT address then at least two rows are expected to be
displayed. If the data provided in the supplied schema file does not do this you need to
work out a way of doing that and include in your script file.
There should be comments in the script to explain what is being done and to identify what
parts of the assignment are being covered. The script should contain a single COMMIT
statement as the last line of the script, i.e. all inserts should be treated as a single
transaction. The data should be structured in such a way that once it has been inserted
and the -commit- SQL command has been run, the database is in a consistent state.
Specifically for this section, and using the information obtained from the previous section,
you should add as a minimum:
• Three new clients. These clients should:
o have a structure that has not been used yet;
o operate in sectors that have not been used yet (but may include existing
ones if they operate in more than one);
o require services that have not been requested yet (but may include existing
ones);
o have one unique billing on cost type charged to them for the day;
• Two new employees – one Accountant and one Administrator. They need to:
o at least work in domains for which there is none or only one current
employee with that expertise;Faculty of Science and Technology
ITECH1006/5006
Database Management Systems
CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 4
o Have a billing rate the same as that of the same employee type with the
minimum rate for that employee;
(15 marks)
C. Specific Scenarios – Manipulate the Database
In the following section, the SQL scripts must correctly manage transactions. You should
also ensure that any related data impacted by your script actions are correctly managed.
Copies of your SQL scripts should be placed in the
ITECH1006_5006_StudentName_StudentNumber_Test_and_Scenario_Data_Script file
1. A new accountant is to be added (assume it has not been updated from the HR
system). His name is Matthias Window. He has expertise in Taxation and Corporate
affairs and will be charged out at a rate of $160.00 per hour from today (16th
September 2014).
Include this new employee and his details on the CQR database. Assume that the
employee number for this new employee is 1 more than the largest employee in the
BILLINGEMPLOYEE table. (4 marks)
For ITECH5006 students, the value for employee number should be obtained through
SQL, not hardcoded. (+2 marks)
2. His first work is to give some tax advice to an existing client who he thinks is Bill
Wilson. He spends an hour from 8:30 doing so but can’t find the service type code so
creates a new one for donations and uses that. He then moves on to some research
for half an hour for an existing client Richard Smith. In both cases he forgets to key in
an end time.
Include the details of this sequence of billing in the database. (4 marks)
For ITECH5006 students, the clients should be found with SQL, not hardcoded.
(+1 marks)
3. At 10:00 Matthias realises he actually worked for Bill Watson initially not Bill Wilson,
that he should have used the communication code for that work and that he should
have completed the times correctly.
Include the details of these corrections in the database.
(6 marks)
For ITECH5006 students, insert new records and remove the old ones where
applicable rather than modifying the existing ones. (+3 marks)Faculty of Science and Technology
ITECH1006/5006
Database Management Systems
CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 5
4. Matthias is then tasked with completing a corporate return for 2 Boy’s Plumbing Pty.
Ltd. He gets the time wrong and keys in 9:30am and spends 2 hours completing the
return, updating the completion time to 11:30 when he is finished. As part of the
process he raises an on-cost record for the lodgement fee ($110.00) for the return.
Include the above changes in the database. (5 marks)
For ITECH5006 students, prevent the duplication of start times (he has already
recorded a 9:30am time for another client) in your SQL. (+3 marks)
5. Alan Counting reduces Matthias Window’s billing rate by 25%. (1 marks)
For ITECH5006 students, also remove his domain expertise in taxation.
(+1 marks)
D. Querying of Database using SQL Statements
1. Display the full details for all clients - the name details (firstname and lastname) should
be shown in one column called 'Client Name' and the address details (street1, street2,
city, state and postcode) in one column called ‘Client Address'. (2 marks)
2. Display the full details for all clients who have a TAS or a NT address. (2 marks)
3. Display the full details for all service types in the CQR service type table which have
the word 'advice' (upper or lowercase) in any of their columns. (2 marks)
4. Alan is considering what the effect of increasing the billing rate on all employees by 5%
would be. Display the employee number, name, effective date and increased billing
rate of all employees in CQR. (3 marks)
5. Display the full details for the cheapest billing employee provided by CQR. (3 marks)
6. Display the details of all clients for whom no billing on cost records exist for the current
year (2014). Display in client concatenated firstname, lastname order. (3 marks)
7. Provide the total number of employees, total billing rate, average billing rate for the
practice. (3 marks)
8. Calculate the total charges, excluding on-costs per client for the month of September
2014. (3 marks)
9. Display the employee number, client number, service ID and billingstarttime for service
types of “Advice” or “Communication” type where the actual charge on any billing
record is cheaper than $150 and the time duration is between 15 and 30 minutes.
Order the list such that the billingrecords which are least expensive are listed first.
(4 marks)Faculty of Science and Technology
ITECH1006/5006
Database Management Systems
CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 6
10. For all clients currently in the CQR system, display details about the clients and those
with sector records and those without sector records:
• for each client with sectors: display the string 'With sectors', the client number,
client first name concatenated with the client last name, and the total number of
sectors the client is involved in, and
• for each client without sector records: display the string 'Without sectors', the client
number, client first name concatenated with the client last name, and the total
number of sectors as a string of ‘Not applicable’.
Note that that the results from this listing should be displayed in a single result output.
Hint: Make use of the “UNION” relational operator as part of your SQL statement.
(5 marks)
ITECH5006 students should also complete the following.
11. Display details of all employees (number, name) for whom billingrecords have been
created when they are not identified as having that expertise i.e. those employees who
have charged to a service that they do not have a domain record for. (2 marks)
12. Select employee number and the distinct service from the billingrecords where any
record for that service took in excess of 1 hour to complete. You should only display
one employee number, service and description combination even when multiple
records qualify. (3 marks)
13. Display the structure details for which the smallest total billable activity (time) has been
performed in the last month – (17-08-2014 to 16-09-2014) (3 marks)
14. Report the average number of billing records per employee per day. (2 marks)
Include the queries in the
ITECH1006_5006_StudentName_StudentNumber_Beginner_and_Specific_Queries file.
Note: There are some general requirements when defining your select queries:
You are required to adhere to the following output formatting conventions:
• All monetary values should be printed with a dollar symbol ($), two digits after the
decimal point, and with space for 7 digits before the decimal point
• You must use consistent and legible formatting in laying out your SQL queries. Include
(brief) comments for any query or procedure that uses an -unusual- approach.
What to submitFaculty of Science and Technology
ITECH1006/5006
Database Management Systems
CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 7
An electronic copy of your assignment should be submitted through Moodle and should
include a copy of your report, completed according to the Federation University Australia
General Guide for the Presentation of Academic Work and the two files described in
Sections A to D –
ITECH1006_5006_StudentName_StudentNumber_Beginner_and_Specific_Queries
ITECH1006_5006_StudentName_StudentNumber_Test_and_Scenario_Data_Script
Your document should include:
• A copy of the input and output of you running all your SQL required for this
assignment including:
o Queries from section A;
o Inserts of Data from section B;
o Execution of Data Manipulation from section C; and
o Queries from section D.
• A bibliography containing a list of all resources used to complete the assignment.
If no resources, apart from the course materials, have been used please indicate
this.
Assessment Criteria
• How clear and well organised your presentation is. On the front page of your report you
should include a list of acknowledgements of all people who have assisted you with this
assignment including fellow students, along with a statement of completion.
• Adherence to our standards. How clear and well organised your presentation is. You
should write all the queries in consistent style and use indent format.
• Data correctness and quality. Please use appropriate data for your examples (e.g.
do not use inappropriate person names)
• Joining of data from multiple tables should be completed using a WHERE
statement only. JOINs are not to be used within any of the SQL statements. Use of
any JOINs will result in 0 (zero) marks being allocated for each SQL statement that
utilizes them.
• Please refer to the provided marking guide (below) to see the distribution of marks.
Assignment Resources:
• This Assignment Specification
• The Standard ER Diagram
• Relational Database Schema
Hint: you need to decide the order that tables need to be created; and the order of
tables in which data need to be inserted into. CRICOS Provider No. 00103D ITECH1006/5006 Assignment 2 Specification – Semester 3 2014 (201427) Page 8
Faculty of Science and Technology
ITECH1006/5006
Database Management Systems
Assignment 2 – Marking Overview
Item ITECH1006 ITECH5006
Documentation – deductions up to -5
A. Understanding and Implementation
of the Database including queries
/15 /15
B. Test Data /15 /15
C. Specific Scenarios - Manipulate the
Database
/20 /30
D. Querying of Database Using SQL
Statements
/30 /40
Formatting – deductions up to -5
Total:
/20
/80 /100