Produce a department report from an employee file (Emps.dat) and a department fi

Question

Produce a department report from an employee file (Emps.dat) and a department file (Depts.txt). Both files are on the tomcat drive under cosc210 and uploaded to Moodle.

Input:

Employee file - binary with the following fields:

                  Employee ID – 4 bytes two’s compliment integer

First Name – 10 bytes ASCII blank padded

Last Name – 15 bytes ASCII blank padded

Department Code – 2 bytes ASCII

Hire Date – 1 byte 2’s comp for month, 1 byte 2’s comp for day, 2 byte 2’s comp for year

Employee Type – 1 byte ASCII ‘S’ for Salaried, ‘E’ for Exempt, ‘H’ for Part Time Hourly

Salary – double precision floating point

Hourly Rate – double precision floating point

Vacation Days – 2 bytes 2’s comp integer

Training – 1 byte binary

Department file - comma delimited text. Each line in the file represents a department. The comma delimited fields are:

                  Department Code

                  Department Name

                  Employee ID of the manager for the department

Output:

Print a report listing of Employees by Department. For each department, print the department name, manager’s name, number of employees in the department, and the sum of the vacation days of its employees. For each employee, print the employee ID, name, hire date, salary, exempt status, and vacation days. A sample report is provided below:

Administration Department

Manager:            Herbert Johnston   

Staff Size:         36                 

Vacation Days:      40

ID     Employee Name               Hire Date   Typ Salary      Rate     Vac

10640 Nick Stover                 05/13/2004   S    30,000.00   15.00    30

10688 John Cochran                09/15/2004   H                13.75    22

10923 Brian Dunlap                01/09/2000   E   105,000.00             4

Engineering Department

Manager:            Joseph Johnston    

Staff Size:         41                 

Vacation Days:      40

ID     Employee Name               Hire Date   Typ Salary      Rate     Vac

10646 Virginia Clawson            01/25/2004 H                16.25    24

10666 Beverly Noel                05/11/2002   S    37,500.00   18.75    18

Implementation Requirements:

1)The data from the files must be loaded into memory before producing the report.

2)Provide formatting of the date field as shown (MM/DD/YYYY).

3)Provide formatting of the Salary field as shown (###,###.##).

4)Internally use a char for Employee Type and Date for the hire date.

5)Print blanks for Salary of zero, and for Hourly Rate of zero.

Explanation / Answer

CREATE TABLE Employee( EmpNo INTEGER, EmpName VARCHAR(30), EmpHireDate DATE, CONSTRAINT PKEmployee PRIMARY KEY (EmpNo) )

CREATE TABLE SalaryEmp ( EmpNo INTEGER, EmpSalary DECIMAL(10,2),

CONSTRAINT PKSalaryEmp PRIMARY KEY (EmpNo),

CONSTRAINT FKSalaryEmp FOREIGN KEY (EmpNo) REFERENCES Employee

ON DELETE CASCADE )

CREATE TABLE HourlyEmp( EmpNo INTEGER, EmpRate DECIMAL(10,2),

CONSTRAINT PKHourlyEmp PRIMARY KEY (EmpNo),

CONSTRAINT FKHourlyEmp FOREIGN KEY (EmpNo) REFERENCES Employee

ON DELETE CASCADE )

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.