Database: Sample Data Analysis: Continued...

    GENDER_LKP Analysis
  • Since “male” and “female” values will be repeated for several records in EMPLOYEE_DTL table, you have to design this lookup for saving disk space. Sometimes the detail table may not show the exact business requirements also.
    E.g. Since the example data contains both genders; you know that there are two genders. What would have happened if all records were male? By seeing the sample data, you might have come to a conclusion that there are only ‘males’ allowed as employees. By discussing with Business Analyst, you would come to know that both genders are allowed. For this purpose, you design a lookup table, which identifies the business requirements also.
  • You have to assign NOT NULL constraint for all columns since lookup tables in general should not have null value.
  • You have to create a column Gender Code, which is not present in sample data and this column should be assigned Primary Key to validate the detailed data in EMPLOYEE_DTL table.

Sample Source Data

Entity Name Table Name Attribute Name Column Name Datatype Null Option Primary Key Foreign Key
Gender Lookup GENDER_LKP Gender Code GNDR_CD VARCHAR2(10) NOT NULL Yes No
Gender Lookup GENDER_LKP Gender Description GNDR_DESC VARCHAR2(50) NOT NULL No No
Gender Lookup GENDER_LKP DateTime Stamp DTTM_STMP DATE NOT NULL No No


Please proceed to next page for continuation.


Database: Sample Data       Sample Data Analysis: Continued...