Question DetailsNormal
$ 20.00
CSC 352 / 452: DATABASE PROGRAMMING
Question posted by

CSC 352 / 452: Database Programming

assignment #2 (60 Points)

 

Due on Tuesday, 1/19/2016 at 11:59PM

 

Unless prior arrangements are made, homework turned in late but within 24 hours of the due time will be graded at 75% credit, homework turned in between 24 and 48 hours will be graded at 50% credit, and homework turned in later than 48 hours will not be accepted.

 

Please note that only TEXT files will be accepted. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept.

 

·         If you modified the DEPARTMENT and EMPLOYEEtablescreated in Assignment #1, you need to delete andre-populate them.

·         Do not try to use complicated queries(e.g., joins) to get the results.You can use multiple PL/SQL statements to get the results easily.

·         You cannot use hard-coded values (e.g., IFemployee_id = 7839THEN ……) in your programs.

·         You are not allowed to create temporary tables, views, functions, or procedures.

·         Explicit cursors are NOT allowed in your programs.

·         The EXCEPTION Section is NOT allowed in your programs.

·         Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file.

1) (CSC 352 - 30 points | CSC 452 – 20 points)

 

Based on the DEPARTMENT and EMPLOYEE tables created in Assignment #1, write a PL/SQL anonymous block that acceptsanemployeeIDfrom the user input and displays 1) the employee’sname, hire date,and total pay (salary + commission), 2) his/her manager’s name, hire date,and total pay (salary + commission), 3) the name of the department where the employee works, and 4) the maximum total pay (salary + commission) for the department where the employee works.

 

·         Submitting more than one PL/SQL program will receive 0 points.

·         If the employee ID from the user input is not in the EMPLOYEE table (EMPLOYEE_ID), you display a message telling the user that the employee ID is not in the table.

·         You must display the total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56).

·         The hire date must be displayed in the mm/dd/yyyy format (4-digit year).

·         If the employee does not have a manager, the manager’s name, hire date and total paymust be shown as “N/A”.

·         If the employee does not belong to any department, the department name must be shown as “N/A”, and the maximum total pay (salary + commission) for the department must be shown as “0”.

·         You will lose 5 points if your output does not have “User Input:”, “My Output:”, “----- Employee -----”, “-----Manager-----”, or “-----Department-----”.

·         To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9 and Enter.

 

Example:

 

ACCEPT p_1 PROMPT 'Please enter the employeeID:'

DECLARE

 -- Your statements

 

BEGIN

        -- Your statements

END;

 

Please test your program.The output of your program must match the following:

 

Case 1)

 

 

 

Output:

 

 

 

Case 2)

 

 

 

Output:

 

 

 

Case 3)

 

 

 

Output:

 

 

 

2) (CSC 352 - 30 points | CSC 452 – 25 points)

 

Based on the DEPARTMENT and EMPLOYEE tables created in Assignment #1, write a PL/SQL anonymous block that accepts a department namefrom the user inputand displaysa) the department ID of that department, b) the address of that department, c) the number of employees working in that department, d) the average salary for that department, e) the number of employees in that department whose salary is higher than the average salary for that department, and f) the number of employees in that department whose salary is higher than the average salary of all employees in the company.

 
·         Submitting more than one PL/SQL program will receive 0 points.

·         If the department name from the user input is not in the DEPARTMENT table (DEPARTMENT_NAME), you display a message telling the user that the department name is not in the table.

·         Department name is not case sensitive (e.g., SALES = Sales).  You will lose 5 points if you do not use the UPPER(orLOWER) function in your program.

·         You will lose 5 points if your output does not have “User Input:” or “My Output:”.

 

Please test your program.The output of your program must match the following:

 

Case 1)

 

 

 

 

Output:

 

 

 

Case 2)

 

 

 

Output:

 

 

 

Case 3)

 

 

 

Output:

 

 

 

 

 

3) (CSC 452 only - 15 points)

 

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that displays the number of employees earned salaries in each of the following ranges: $0.00 - $499.99, $500.00 - $999.99, …, $4500.00 - $4999.99, $5000.00 - $5499.99. You can only use ONESELECT-INTO statement in your program.

 

·         Submitting more than one PL/SQL program will receive 0 points.

·         You will lose 10 points if more than one SELECT-INTO statement is used.

 

Hint:    FOR idx IN 0..10 LOOP … SELECT … INTO … FROM …; … END LOOP;

 

Creating cleanly formatted output is a common programming requirement. The format of your output must match mine EXACTLY. If your output does not match mine EXACTLY, you will lose some points.

 

Please test your program.The output of your program must match the following:

 

 

 

Please submit a text file containing all the source codes to D2L before or on due date.

 

Optional Question

 

Just for fun (no credit, no extra credit, no need to submit, just for if you are a curious person and like database programming).

 

In a bug tracking database, there is a table called BUGS. The table has several columns: BUG_ID, REPORTED_DATE, DESCRIPTION, PRIORITY, ASSIGNED_TO, CLOSED_DATE, and NOTE.

 

Create and populate the BUGStable.

 

CREATE TABLE bugs

(

       BUG_ID               NUMBER PRIMARY KEY,

       REPORTED_DATE        DATE NOT NULL,

       DESCRIPTION          VARCHAR2(20),

       PRIORITY             NUMBER(2),

       ASSIGNED_TO          VARCHAR2(10),

       CLOSED_DATE          DATE,

       NOTE                 VARCHAR2(20)

);

 

INSERT INTO BUGS VALUES (1230, '25-APR-14', NULL, 3, 'Team 3', '28-APR-14', NULL);

INSERT INTO BUGS VALUES (1231, '29-APR-14', NULL, 1, 'Team 1', '29-APR-14', NULL);

INSERT INTO BUGS VALUES (1232, '03-MAY-14', NULL, 1, 'Team 1', '03-MAY-14', NULL);

INSERT INTO BUGS VALUES (1233, '03-MAY-14', NULL, 1, 'Team 3', '08-MAY-14', NULL);

INSERT INTO BUGS VALUES (1234, '04-MAY-14', NULL, 2, 'Team 5', '15-MAY-14', NULL);

INSERT INTO BUGS VALUES (1235, '04-MAY-14', NULL, 2, 'Team 1',  NULL, NULL);

INSERT INTO BUGS VALUES (1236, '05-MAY-14', NULL, 1, 'Team 2', '06-MAY-14', NULL);

INSERT INTO BUGS VALUES (1237, '05-MAY-14', NULL, 3, 'Team 3', '10-MAY-14', NULL);

INSERT INTO BUGS VALUES (1238, '09-MAY-14', NULL, 4, 'Team 5', '16-MAY-14', NULL);

INSERT INTO BUGS VALUES (1239, '09-MAY-14', NULL, 5, 'Team 6',  NULL,       NULL);

INSERT INTO BUGS VALUES (1240, '12-MAY-14', NULL, 5, 'Team 2', '30-MAY-14', NULL);

INSERT INTO BUGS VALUES (1241, '12-MAY-14', NULL, 1, 'Team 1', '20-MAY-14', NULL);

INSERT INTO BUGS VALUES (1242, '13-MAY-14', NULL, 4, 'Team 4', '25-MAY-14', NULL);

INSERT INTO BUGS VALUES (1243, '14-MAY-14', NULL, 4, 'Team 3', '01-JUN-14', NULL);

INSERT INTO BUGS VALUES (1244, '14-MAY-14', NULL, 2, 'Team 4', '25-MAY-14', NULL);

INSERT INTO BUGS VALUES (1245, '20-MAY-14', NULL, 2, 'Team 4',  NULL, NULL);

INSERT INTO BUGS VALUES (1246, '22-MAY-14', NULL, 2, 'Team 4', '25-MAY-14', NULL);

INSERT INTO BUGS VALUES (1247, '25-MAY-14', NULL, 2, 'Team 1', '29-MAY-14', NULL);

INSERT INTO BUGS VALUES (1248, '30-MAY-14', NULL, 1, 'Team 1', '01-JUN-14', NULL);

INSERT INTO BUGS VALUES (1249, '05-JUN-14', NULL, 1, 'Team 2', '07-JUN-14', NULL);

COMMIT;

 

 “Open Bugs” - A bug is considered open on a given day if (1) its “REPORTED_DATE” is on or before that day, and (2) its “CLOSED_DATE” is on or after that day (or is unknown (NULL)). For example, we have 5 open bugs on 5/5/2014.

 

Write a PL/SQL anonymous block that generates a report to show the number of open bugsfrom 5/1/2014 through 5/31/2014. At the end of the report, the maximum number of open bugs on a single dayis displayed. Assume that there were no open bugs on 4/30/2014.
 

The output of your program should match the following:

 

  Date      Number of Open Bugs

01-MAY-14            0

02-MAY-14            0

03-MAY-14            2

04-MAY-14            3

05-MAY-14            5

06-MAY-14            5

07-MAY-14            4

08-MAY-14            4

09-MAY-14            5

10-MAY-14            5

11-MAY-14            4

12-MAY-14            6

13-MAY-14            7

14-MAY-14            9

15-MAY-14            9

16-MAY-14            8

17-MAY-14            7

18-MAY-14            7

19-MAY-14            7

20-MAY-14            8

21-MAY-14            7

22-MAY-14            8

23-MAY-14            8

24-MAY-14            8

25-MAY-14            9

26-MAY-14            6

27-MAY-14            6

28-MAY-14            6

29-MAY-14            6

30-MAY-14            6

31-MAY-14            5

---------------------------------------

The maximum number of open bugs on a single day is 9.

There were 9 open bugs on 14-MAY-14.

There were 9 open bugs on 15-MAY-14.

There were 9 open bugs on 25-MAY-14.

Available Solution
$ 20.00
CSC 352 / 452: DATABASE PROGRAMMING
  • This solution has not purchased yet.
  • Submitted On 02 Jul, 2017 05:14:20
Solution posted by
CSC 352 / 452: DATABASE PROGRAMMING ASSIGNMENT #2 (60 POINTS) Due on Tuesday, 1/19/2016 at 11:59PM Unless prior arrangements are made, homework turned in late but within 24 hours of the due time will be graded at 75% credit, homework turned in between 24 and 48 hours will be graded at 50% credit, and homework turned in later than 48 hours will not be accepted. Please note that only TEXT files will be accepted. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept. • If you modified the DEPARTMENT and EMPLOYEEtablescreated in Assignment #1, you need to delete andre-populate them. • Do not try to use complicated queries(e.g., joins) to get the results.You can use multiple PL/SQL statements to get the results easily. • You cannot use hard-coded values (e.g., IFemployee_id = 7839THEN ……) in your programs. • You are not allowed to create temporary tables, views, functions, or procedures. • Explicit cursors are NOT allowed in your programs. • The EXCEPTION Section is NOT allowed in your programs. • Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file. 1) (CSC 352 - 30 points | CSC 452 – 20 points) Based on the DEPARTMENT and EMPLOYEE tables created in Assignment #1, write a PL/SQL anonymous block that acceptsanemployeeIDfrom the user input and displays 1) the employee’sname, hire date,and total pay (salary + commission), 2) his/her manager’s name, hire date,and total pay (salary + commission), 3) the name of the department where the employee works, and 4) the maximum total pay (salary + commission) for the department where the employee works. • Submitting more than one PL/SQL program will receive 0 points. • If the employee ID from the user input is not in the EMPLOYEE table (EMPLOYEE_ID), you display a message telling the user that the employee ID is not in the table. • You must display the total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56). • The hire date must be displayed in the mm/dd/yyyy format (4-digit year). • If the employee does not have a manager, the manager...
Buy now to view full solution.


Other Related Solutions

$ 629.35