Arnob Fakibaz 2

-- a)
-- Storing DOB and Age is redundant. Remove age
-- Storing House Rent, and Total Salary is redundant, as it can be calculated easily from basic salary

-- ID attribute should not change over time. Can cause inconsistency.
-- b) (i)
CREATE TABLE EMP
(
    EMP_ID VARCHAR2(10) PRIMARY KEY,
    EMP_NAME VARCHAR2(20),
    DOB DATE,
    JOIN_DATE DATE,
    BASIC_SALARY NUMBER,
    CHILD_COUNT NUMBER
);

-- b) (ii)
CREATE OR REPLACE FUNCTION
FIND_SALARY(ID IN VARCHAR2)
RETURN NUMBER
AS
BASIC_SAL NUMBER;
CHILD_CO NUMBER;
TOTAL_SAL NUMBER;
BEGIN
    SELECT BASIC_SALARY, CHILD_COUNT INTO BASIC_SAL, CHILD_CO
    FROM EMP
    WHERE EMP_ID = ID;

    TOTAL_SAL := BASIC_SAL + (0.4 * BASIC_SAL) + (CHILD_CO * 50);
    RETURN TOTAL_SAL;
END;
/