Review The Additional Requirements For The New System ✓ Solved

Review The Addiitonal Requirements For The New System That The Curren

Review the addiitonal requirements for the new system (that the current system cannod do) on page 64 (also copied below) and identify any changes to the database (as presented on pages ) needed in order to meet these additional requirments. You deliverable will consist of the follwoing: - List the new/additional requirement - State if it necessitates a database changes - If it does not necessitate a database change, state why - If it does necessitate a database change, identify table and/or columns that change impacts - Provide SQL DDL to update such tables and SQL DML to insert sample data demonstrating that the requirement is met - Provide SQL Selects for the test cases above ... Capability New to DIWS 2 The following enhancements shall be included in the DIWS 2 HR application. The DIWS 2 HR capabilities shall include: 1. Provide the ability for the user to select whether documents are presented in date order or document type order for document retrieval. 2. Provide the user with the ability to select the column for sorting when looking at a list of documents.  For example, this could be implemented similar to the arrow in Excel column headings for ascending and descending sorts. 3.

Provide the ability to send scanned documents via e-mail as either an attachment or as a link to the document. 4. Automatically ingest HR documents and supporting documents sent via email to a particular in-box, whether the documents are included in the body of the e-mail or as an attachment. ï‚· The process should be automatic in that an incoming e-mail inbox is monitored and the e-mail is automatically captured from the inbox and information about the sender, send date/time, and subject are used for indexing the body of the e-mail and/or attachments. 5. Automatically ingest HR documents and supporting documents sent via fax. ï‚· For example, capture the incoming fax document without requiring the fax document to be printed and scanned. ï‚· The process should be automatic in that an incoming fax line is monitored and the fax is automatically captured, and information about the sender, send date/time, page count, and subject are used for indexing the fax document. 6. Provide the following exit interview capabilities: exit interview surveys checklists for exit interviews folders and document types for all electronic exit interview documents queries and reports on the statistics on the reasons for leaving ability to export the exit statistics to Excel spreadsheets.

Sample Paper For Above instruction

Analysis of Additional System Requirements and Database Implications

The new system requirements largely focus on enhancing document management, automation, and reporting capabilities within the HR application, DIWS 2. These enhancements involve user interface improvements, automated data ingestion, and advanced reporting functionalities. To integrate these functionalities, specific modifications to the existing database schema are necessary. This analysis provides a detailed examination of each requirement, its impact on the database, and the corresponding SQL code needed for implementation.

Requirement 1: Document Sorting Options

The first enhancement allows users to choose whether documents are displayed in date order or document type order during retrieval. This feature improves flexibility and user experience. Since this pertains to how data is sorted during retrieval, it primarily influences the querying mechanism rather than the underlying database schema. Therefore, no database changes are necessary for this feature. It can be implemented through query parameters or application logic to specify sorting fields. For example, the application can pass sorting options to SQL statements, such as ORDER BY date or ORDER BY documentType.

Requirement 2: User-Selectable Sorting Columns

This feature further enhances user control by allowing selection of the sorting column, similar to Excel's column sorting arrows. Like the previous requirement, this capability is achieved through dynamic query parameters and does not necessitate database schema modifications. Proper indexing on relevant columns like 'date' and 'documentType' can optimize performance.

Requirement 3: Sending Documents via Email as Attachments or Links

This feature involves the ability to email documents either as attachments or as hyperlinks. Implementing this function is primarily on the application level. However, storing document URLs in the database is essential if links are used. The current document table should have a column for the link or references to the stored documents. This requires adding a new column, e.g., document_link, to the documents table.

Database Change Implication and Sample SQL

  • Table impacted: Documents
  • Changes: Add a new column document_link VARCHAR(255)
ALTER TABLE Documents


ADD COLUMN document_link VARCHAR(255);


-- Insert sample document with link

INSERT INTO Documents (document_id, document_type, date, description, document_link)


VALUES (101, 'HR Policy', '2024-03-15', 'Updated HR Policy', 'http://documents.company.com/hrpolicy2024.pdf');

Requirement 4: Automatic Ingestion of HR Documents from Email

This enhancement involves automatic ingestion of email attachments or inline bodies into the document repository, capturing sender, date/time, and subject for indexing. To support this, the database requires changes to store email metadata and links to the stored documents or bodies.

Database Changes

  • New table: EmailIngestedDocuments
  • Columns:
    • email_id INT PRIMARY KEY
    • sender VARCHAR(255)
    • received_datetime DATETIME
    • subject VARCHAR(255)
    • document_path VARCHAR(255)
CREATE TABLE EmailIngestedDocuments (


email_id INT PRIMARY KEY AUTO_INCREMENT,


sender VARCHAR(255),


received_datetime DATETIME,


subject VARCHAR(255),


document_path VARCHAR(255)


);


-- Sample data insertion

INSERT INTO EmailIngestedDocuments (sender, received_datetime, subject, document_path)


VALUES ('hrmanager@company.com', '2024-03-16 09:30:00', 'New Hire Contract', '/documents/email_ingest/contract20240316.pdf');

Requirement 5: Automatic Ingestion of Faxed HR Documents

This process captures incoming faxes automatically without printing or scanning—indexed by sender, date/time, page count, and subject. Similar to email ingestion, a new table is needed to catalog fax documents.

Database Changes

  • New table: FaxDocuments
  • Columns:
  • fax_id INT PRIMARY KEY AUTO_INCREMENT
  • sender VARCHAR(255)
  • received_datetime DATETIME
  • page_count INT
  • subject VARCHAR(255)
  • document_path VARCHAR(255)
CREATE TABLE FaxDocuments (


fax_id INT PRIMARY KEY AUTO_INCREMENT,


sender VARCHAR(255),


received_datetime DATETIME,


page_count INT,


subject VARCHAR(255),


document_path VARCHAR(255)


);


-- Sample insert

INSERT INTO FaxDocuments (sender, received_datetime, page_count, subject, document_path)


VALUES ('faxline@phone.com', '2024-03-16 10:00:00', 3, 'Applicant Resume', '/documents/fax/recipient20240316.pdf');

Requirement 6: Exit Interview Management

This complex feature encompasses surveys, checklists, categorization of documents, querying, reporting, and exporting. The database schema needs to be expanded to accommodate survey data, checklist items, interview documents, reasons for leaving, and export tracking.

Database Changes

  • New tables:
  • ExitInterviews: stores interview details
  • SurveyQuestions: stores checklist questions
  • InterviewResponses: stores responses and associated documents
  • ReasonsForLeaving: stores reasons with counts for statistical analysis
CREATE TABLE ExitInterviews (


interview_id INT PRIMARY KEY AUTO_INCREMENT,


employee_id INT,


interview_date DATETIME,


interviewer VARCHAR(255)


);


CREATE TABLE SurveyQuestions (


question_id INT PRIMARY KEY AUTO_INCREMENT,


question_text VARCHAR(500)


);


CREATE TABLE InterviewResponses (


response_id INT PRIMARY KEY AUTO_INCREMENT,


interview_id INT,


question_id INT,


answer TEXT,


response_date DATETIME,


response_document_path VARCHAR(255)


);


CREATE TABLE ReasonsForLeaving (


reason_id INT PRIMARY KEY AUTO_INCREMENT,


reason_text VARCHAR(255),


count INT DEFAULT 0


);


-- Sample data inserts

INSERT INTO ReasonsForLeaving (reason_text, count)


VALUES ('Voluntary resignation', 15),


('Retirement', 5),


('Layoff', 3);

Test Queries for Validation

1. Document Retrieval with Sorting

-- Date order


SELECT * FROM Documents ORDER BY date ASC;


-- Document type order


SELECT * FROM Documents ORDER BY document_type ASC;


-- Dynamic column sorting (e.g., user selects 'description')


SET @sort_column = 'description';


PREPARE stmt FROM 'SELECT * FROM Documents ORDER BY ?? ASC';


EXECUTE stmt USING @sort_column;


DEALLOCATE PREPARE stmt;

2. Lookup of Email Ingested Documents

SELECT * FROM EmailIngestedDocuments WHERE sender='hrmanager@company.com';

3. Fax Documents Retrieval

SELECT * FROM FaxDocuments WHERE sender='faxline@phone.com';

4. Exit Interview Statistics by Reason

SELECT reason_text, SUM(count) AS total


FROM ReasonsForLeaving


GROUP BY reason_text;

Conclusion

The enhancements outlined necessitate minimal changes for sorting features but require significant schema extensions for document transfer, automatic ingestion, and exit interview management. Proper indexing and foreign key relationships will ensure performance efficiency, while stored procedures and application logic will facilitate automation and dynamic querying. These database modifications will support the new functionalities seamlessly, ensuring a robust and scalable HR information system.

References

  • Elmasri, R., & Navathe, S. B. (2015). Principles of Database Systems. Pearson.

Related Assignments