Monday - Friday8AM - 9PM
OfficesBloomsbury Square, London WC1B 4EA
Visit our social pages

Assessment 3 SQL CSE2DCX Database Fundamentals on the Cloud - Course Rated

July 13, 2020by admin

Assessment 3
SQL
CSE2DCX Database Fundamentals on the Cloud

Objectives
This is an individual assignment. Students are not permitted to work in a group when writing this
assignment.
Copying and Plagiarism
This is an individual assignment. Students are not permitted to work in a group when writing this
assignment. Plagiarism is the submission of another person’s work in a manner that gives the
impression that the work is their own. La Trobe University treats plagiarism seriously. When detected,
penalties are strictly imposed.
Further information can be found on http://www.latrobe.edu.au/students/academicintegrity/explanation/plagiarism
Submission Guidelines
Your assignment submission should be typed, not written/drawn by hand.
Submit the electronic copy of your assignment through the subject LMS.
Submission after the deadline will incur a penalty of 5% of the available assignment mark per day
capped at 5 days. No assignment will be accepted after 5 days. If you have encountered difficulties
that lead to late submission or no submission, you should apply for special consideration.
© Didasko 2020. All rights reserved. 3
Background
Over the past decade Carol’s confections have become one of the leading
confectionary vendors in Melbourne. The number of shops has increased from 1
in 2010 to 4 in 2019. The management has decided to automate its current manual
staff management process. Instead of using their current paper-based system they
wish to use a web-based staff management system.
You are tasked to handle the initial work on the database systems –
constructing tables, populating data and running SQL queries.
They have decided the initial database will contain 3 tables.
• Employee
• Department
• Grade
The Employee table will store employee ID, employee name, manager, job, salary,
commission, hire date and department ID (Department where the employee works).
The Department table will store the Department ID, department name, address and
phone number. The Grade table will store the Grade ID, minimum salary, maximum
salary and annual leave.
The manager column will be used to store the employee id of the manager, for
example Adam’s manager is Eve whose is also an employee and has employee id
1211. The manager column for Adam will have the value 1211. The commission
column represents the sales commission that some employees earn. The grade id
will be a character value for instance employees in grade B earn between 60000
(min salary) and 80000 (max salary). Each grade has an associated annual leave
loading. The other fields are self-explanatory.
Login to the SQL Server database using the credentials (username and password)
that you have created earlier. If you are unable to log in due to any reason, please
do not proceed any further. Please contact the academic team.
Instructions:
Provide a screenshot of the output for all tasks. Failing to do so may result in your assessor being
unable to award marks for that task.
You cannot hardcode queries i.e. doing part of the query manually. For instance, if the query asks
you to identify the highest paid employee. You cannot say “Where eID = 1006” as this involves
manually finding the highest salary. Instead your query should calculate the answer.
4 © Didasko 2020. All rights reserved. 4
Tasks
Task 1
Login to SQL Server and create the Employee table (see table 1) using SQL
statements. You will be assessed on the following,
Is the SQL syntax error free?
Has the primary key been implemented?
Have the proper data types been implemented?
Have you implemented appropriate constraints (e.g. null values, unique,
referential integrity) to help in improving the integrity of data?
Write the finalised SQL statements that you used to create the table. [2 marks]
Task 2
Login to SQL Server and create the Department table (see table 2) using SQL
statements. You will be assessed on the following,
Is the SQL syntax error free?
Has the primary key been implemented?
Have the proper data types been implemented?
Have you implemented appropriate constraints (e.g. null values, unique,
referential integrity) to help in improving the integrity of data?
Write the finalised SQL statements that you used to create the table. [2 marks]
Task 3
Login to SQL Server and create the Grade table (see table 3) using SQL
statements. You will be assessed on the following,
Is the SQL syntax error free?
Has the primary key been implemented?
Have the proper data types been implemented?
Have you implemented appropriate constraints (e.g. null values, unique,
referential integrity) to help in improving the integrity of data?
Write the finalised SQL statements that you used to create the table. [2 marks]
Task 4
Write a query to insert the following data to populate the Employee table. Make any
reasonable modifications so that the data matches your field names and data types
and conform with integrity constraints.
© Didasko 2020. All rights reserved. 5
Table 1: Employee table
eID eName Mgr Job Salary Comm hDate dID
1001 Ken Adams 1004 Salesman 70000 20000 2008-04-12 1
1002 Ru Jones 1004 Salesman 65000 15000 2010-01-18 1
1003 Dhal Sim 1006 Accountant 88000 2001-03-07 2
1004 Ellen Honda 1006 Manager 118000 2003-04-17 1
1005 Mike Bal 1006 Receptionist 68000 2006-06-21 3
1006 Martin Bison CEO 210000 2010-07-12 3
1007 Shen Li 1004 Salesman 86000 18000 2014-09-18 1
1008 Zang Ross 1004 Salesman 65000 10000 2017-02-02 1
1009 Sagar Kahn 1004 Salesman 70000 15000 2016-03-01 1
[1 mark]
Task 5
Write a query to insert the following data to populate the Department table. Make
any reasonable modifications so that the data matches your field names and data
types and conform with integrity constraints.
Table 2: Department table
dID dName Address Phone
1 Sales Sydney 0425 198 053
2 Accounts Melbourne 0429 198 955
3 Admin Melbourne 0428 198 758
4 Marketing Sydney 0427 198 757
[1 mark]
Task 6
Write a query to insert the following data to populate the Grade table. Make any
reasonable modifications so that the data matches your field names and data
types.
Table 3: Grade table
gID MinSal MaxSal Leave
A NULL 60000 20
B 60000 80000 20
C 80000 100000 20
6 © Didasko 2020. All rights reserved. 6
D 100000 120000 25
E 120000 NULL 30
[1 mark]
Task 7
Write an SQL query to list the employee details (eID, eName and job) of all the
employees who work as salesman and were hired after 01/01/2014.
[2 marks]
Task 8
Write an SQL query to display all details for employees whose total earnings (salary + comm) are
greater than 85000.
[2 marks]
Task 9
Write a query to display all information regarding employees, who manage
other employees. The selected employee’s id should appear as mgr for some
other employee.
[3 marks]
Task 10
Write a query to list all employees (eID, eName, job, dID, Address) who work in
Sydney.
[3 marks]
Task 11
Write a query to display all information regarding the highest paid employee for
each department.
[3 marks]
© Didasko 2020. All rights reserved. 7
Task 12
Write a query to display all information about all the views that were created by the
‘sys’ user. Use data dictionary to complete this query.
[3 marks]
Task 13
Create a view called ‘Employee_Location’, the view will display the employee name,
and address (where they work).
[3 marks]
Task 14
Write a stored procedure called ‘Salary_Increment’. The procedure would require an
employee’s id as input and would increment that employee’s salary by 3%. This
procedure will permanently change the employees Salary.
[3 marks]
Task 15
Write a trigger called ‘Expenditure’, the trigger would run whenever a new row is
added or an existing row is updated for the employee table. It would display the sum
of all salaries and commissions under the title Expenditure (see example below).
Expenditure
10132330

[3 marks]
Task 16
Write a query to include a new column ‘GradeID’ to the employee table. This
column will be used to represent the employees’ grade. There should be referential
integrity between this column and the GradeID column in the Grade table.
[3 marks]
8 © Didasko 2020. All rights reserved. 8
Task 17
Once the GradeID column has been added to the employee table all the rows will
show NULL for GradeID. Write a query to assign values to these rows. Using
information provided in grade table calculate each employee’s grade and assign
that value to that employee.
The grade would be calculated by comparing an employee’s salary against the
minSal and maxSal values provided in the Grade table.
[3 marks]
Submitting your assignment
When you have completed your answers, submit the assessment on the Learning
Portal. You should submit the following:
• Submit your answers in a document called xxx_cse2dcx_assessment3.docx
(where xxx is your student number) for your answers for Tasks 1-17.
© Didasko 2020. All rights reserved. 9
Assessment Marking Criteria
Task 1: Creating the
Employee table. Marks
are awarded for (a)
syntax; (b) primary key;
(c) fields/types/sizes;
and (d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.5 marks
Solution was
correct for 2
aspects.
1 point
Solution
was correct
for 3
aspects.
1.5 marks
Solution was
fully correct.
2 marks
Task 2: Creating the
Department table.
Marks are awarded for
(a) syntax; (b) primary
key; (c)
fields/types/sizes; and
(d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.5 marks
Solution was
correct for 2
aspects.
1 point
Solution
was correct
for 3
aspects.
1.5 marks
Solution was
fully correct.
2 marks
Task 3: Creating the
Employee table. Marks
are awarded for (a)
syntax; (b) primary key;
(c) fields/types/sizes;
and (d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.5 marks
Solution was
correct for 2
aspects.
1 point
Solution
was correct
for 3
aspects.
1.5 marks
Solution was
fully correct.
2 marks
Task 4: Employee data. Employee data was not added
to the table. 0 marks
Employee data was
successfully added to the
table. 1 point
Task 5: Department data. Department data was not
added to the table. 0 marks
Department data was
successfully added to the
table. 1 point
Task 6: Grade data Grade data was not added to
the table. 0 marks
Grade data was successfully
added to the table. 1 point
Task 7: Marks are
awarded for table/field
names (1); and
condition (1).
Solution was not
correct. 0 marks
Solution had 1 error. 1
mark
Solution was correct
for all aspects. 2
marks.
Task 8: Marks are
awarded for table/field
names (1); and
condition (1).
Solution was not
correct. 0 marks
Solution had 1 error. 1
mark
Solution was correct
for all aspects. 2
marks.
Task 9: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
10 © Didasko 2020. All rights reserved. 10
Task 10: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
Task 11: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
Task 12: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
Task 13: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
Task 14: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
Task 15: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
Task 16: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.
Task 17: Marks are
awarded for table/field
names (1); and
condition (2).
Solution was not
correct. 0 marks
Solution had
multiple errors.
1 mark
Solution had 1
error. 2 marks
Solution was
correct for all
aspects. 3 marks.

AVANTAGEHeadquarters
Organically grow the holistic world view of disruptive innovation via empowerment.
OUR LOCATIONSWhere to find us?
https://courserated.com/wp-content/uploads/2019/04/img-footer-map.png
GET IN TOUCHAvantage Social links
Taking seamless key performance indicators offline to maximise the long tail.
AVANTAGEHeadquarters
Organically grow the holistic world view of disruptive innovation via empowerment.
OUR LOCATIONSWhere to find us?
https://courserated.com/wp-content/uploads/2019/04/img-footer-map.png
GET IN TOUCHAvantage Social links
Taking seamless key performance indicators offline to maximise the long tail.

Copyright by BoldThemes. All rights reserved.

Copyright by BoldThemes. All rights reserved.