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...